[15]

SQL> set feedback off

SQL>

SQL> create table t as select * from all_objects ;

SQL>

SQL> insert into t select * from t;

SQL> insert into t select * from t;

SQL> insert into t select * from t;

SQL> insert into t select * from t;

SQL>

SQL> select count(*) from t;

COUNT(*)

----------

807360

SQL> update t set object_id = rownum;

SQL>

SQL> -- 이하는 rowid를 이용하는 경우에 대한 테스트임

SQL> create index t_owner_idx on t(owner);

SQL>

SQL> column rid new_value rid

SQL> select * from (

2     select rowid rid from t where owner = 'SYS' order by rowid desc

3   ) where rownum <= 5 ;

RID

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

AAAPVTAAEAAAliIABH

AAAPVTAAEAAAliIABF

AAAPVTAAEAAAliIABD

AAAPVTAAEAAAliIABB

AAAPVTAAEAAAliIAA/

SQL>

SQL>

SQL> set autotrace on statistics

SQL> select /*+ index(t t_owner_idx) */ rowid

2    from   t

3    where  owner = 'SYS'

4    and    rowid >= chartorowid('&rid')

5    and    rownum <= 5 ;

구   4:   and    rowid >= chartorowid('&rid')

신   4:   and    rowid >= chartorowid('AAAPVTAAEAAAliIAA/')

ROWID

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

AAAPVTAAEAAAliIAA/

AAAPVTAAEAAAliIABB

AAAPVTAAEAAAliIABD

AAAPVTAAEAAAliIABF

AAAPVTAAEAAAliIABH

Statistics

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

8  recursive calls

0  db block gets

951  consistent gets

0  physical reads

0  redo size

554  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)

5  rows processed

SQL>

SQL> set autotrace off

SQL>

SQL> -- 이하는 PK 같은 Unique 속성을 이용하는 경우에 대한 테스트임

SQL> create index t_owner_idx2 on t(owner, object_id);

SQL>

SQL> column oid new_value oid

SQL> select * from (

2      select object_id oid from t where owner = 'SYS' order by rowid desc

3    ) where rownum <= 5 ;

OID

----------

322055

322053

322051

322049

322047

SQL>

SQL> set autotrace on statistics

SQL> select /*+ index(t t_owner_idx2) */ object_id

2    from   t

3    where  owner = 'SYS'

4    and    object_id >= &oid

5     and    rownum <= 5 ;

구   4:   and    object_id >= &oid

신   4:   and    object_id >=     322047

OBJECT_ID

----------

322047

322049

322051

322053

322055

Statistics

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

8  recursive calls

0  db block gets

186  consistent gets

0  physical reads

0  redo size

498  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)

5  rows processed

SQL>

SQL>