Hello All

Slimdave, the LIO thing is based on my own observations of autotrace and chapter 7 of 'Expert One-on-One' which gave me the re-org idea in the first place. I've recreated the test case from it below, as I think it's quite interesting:

SQL> create table colocated (x int, y varchar2(2000)) pctfree 0;

Table created.

SQL> begin
2 for i in 1 .. 100000
3 loop
4 insert into colocated values (i, rpad(dbms_random.random,75,'*'));
5 end loop;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> create table disorganized nologging pctfree 0
2 as
3 select x,y from colocated order by y
4 /

Table created.

SQL> alter table colocated add constraint colocated_pk primary key(x);

Table altered.

SQL> alter table disorganized add constraint disorganized_pk primary key(x);

Table altered.

SQL> set autotrace traceonly
SQL> select * from colocated where x between 20000 and 40000;

20001 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'COLOCATED'
2 1 INDEX (RANGE SCAN) OF 'COLOCATED_PK' (UNIQUE)




Statistics
----------------------------------------------------------
152 recursive calls
1 db block gets
2933 consistent gets
43 physical reads
0 redo size
1935140 bytes sent via SQL*Net to client
108331 bytes received via SQL*Net from client
1335 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
20001 rows processed

SQL> select * from disorganized where x between 20000 and 40000;

20001 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'DISORGANIZED'
2 1 INDEX (RANGE SCAN) OF 'DISORGANIZED_PK' (UNIQUE)




Statistics
----------------------------------------------------------
152 recursive calls
3 db block gets
21385 consistent gets
1107 physical reads
0 redo size
1935140 bytes sent via SQL*Net to client
108331 bytes received via SQL*Net from client
1335 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
20001 rows processed

Tamilselvan, originally an IOT was my preferred approach but the primary key doesn't contain the columns that appear in the WHERE clause of the query which is why I went for the re-org (intending to go for an index clustered table in the longterm.) However, as my testing showed this had the effect of degrading the other indexes that previously benefitted from a good 'natural' clustering factor and means I have to scrap the idea of an IOT/Index Clustered Table/Re-org.

Thanks

Austin