Database Reference
In-Depth Information
select /*+ index( disorganized disorganized_pk ) */ * from disorganized
where x between 20000 and 40000
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 0.00 0.00 0 0 0 0
Execute 5 0.00 0.00 0 0 0 0
Fetch 6675 0.12 0.41 0 106830 0 100005
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6685 0.12 0.41 0 106830 0 100005
...
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ------------------------------------------------------
20001 20001 20001 TABLE ACCESS BY INDEX ROWID BATCHED DISORGANIZED...
20001 20001 20001 INDEX RANGE SCAN DISORGANIZED_PK (cr=1374 pr=0 pw=0...
■
I ran each query five times in order to get a good average runtime for each (hence the tKprOF output shows
100,000+ rows processed).
Note
I think this is pretty incredible. What a difference physical data layout can make! Table
11-5
summarizes the results.
Table 11-5.
Investigating the Effect of Physical Data Layout on the Cost of Index Access
Table
CPU Time
Logical I/O
Co-located
0.21 seconds
14,495
Disorganized
0.41 seconds
106,830
Co-located %
~50%
13%
In my database using an 8KB block size, these tables had the following number of total blocks apiece:
EODA@ORA12CR1> select a.index_name,
2 b.num_rows,
3 b.blocks,
4 a.clustering_factor
5 from user_indexes a, user_tables b
6 where index_name in ('COLOCATED_PK', 'DISORGANIZED_PK' )
7 and a.table_name = b.table_name
8 /
INDEX_NAME NUM_ROWS BLOCKS CLUSTERING_FACTOR
-------------------- ---------- ---------- -----------------
COLOCATED_PK 100000 1252 1190
DISORGANIZED_PK 100000 1219 99929
