(17)

SQL>

SQL> create table t as select rownum no from dual connect by level <= 1000000 ;

테이블이 생성되었습니다.

SQL>

SQL> create index t_idx on t(no) pctfree 0 ;

인덱스가 생성되었습니다.

SQL>

SQL> select /*+ index(t) */ count(*) from t where no > 0;

COUNT(*)

----------

1000000

Statistics

----------------------------------------------------------

32  recursive calls

0  db block gets

2075  consistent gets

2000  physical reads

0  redo size

424  bytes sent via SQL*Net to client

385  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

SQL>

SQL> delete from t where mod(no, 10) < 5 ;

500000 행이 삭제되었습니다.

Statistics

----------------------------------------------------------

583  recursive calls

2027130  db block gets

1914  consistent gets

564  physical reads

218986980  redo size

696  bytes sent via SQL*Net to client

572  bytes received via SQL*Net from client

4  SQL*Net roundtrips to/from client

2  sorts (memory)

0  sorts (disk)

500000  rows processed

SQL>

SQL> commit;

커밋이 완료되었습니다.

SQL>

SQL> select /*+ index(t) */ count(*) from t where no > 0;

COUNT(*)

----------

500000

Statistics

----------------------------------------------------------

0  recursive calls

0  db block gets

2001  consistent gets

0  physical reads

0  redo size

424  bytes sent via SQL*Net to client

385  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

SQL>

SQL>

SQL>

SQL>

SQL> -- Index Rebuild

SQL> alter index t_idx coalesce;

인덱스가 변경되었습니다.

SQL>

SQL> select /*+ index(t) */ count(*) from t where no > 0;

COUNT(*)

----------

500000

Statistics

----------------------------------------------------------

0  recursive calls

0  db block gets

1002  consistent gets

0  physical reads

0  redo size

424  bytes sent via SQL*Net to client

385  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

SQL>

SQL> alter index t_idx shrink space;

인덱스가 변경되었습니다.

SQL>

SQL> alter index t_idx shrink space compact;

인덱스가 변경되었습니다.

SQL>

SQL> alter index t_idx rebuild;

인덱스가 변경되었습니다.

SQL>

SQL> alter index t_idx rebuild online;

인덱스가 변경되었습니다.

SQL>

SQL>