Database Reference
In-Depth Information
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ----------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=100138 pr=0 pw=0 time=111897 us)
100000 100000 100000 TABLE ACCESS BY INDEX ROWID BATCHED DISORGANIZED
(cr=100138 pr=0 pw=0 time=203332 us cost=100158 size=7600000 card=100000)
100000 100000 100000 INDEX FULL SCAN DISORGANIZED_PK (cr=209 pr=0 pw=0...
In both cases, the index needed to perform 209 logical I/Os (
cr=209
in the
Row Source Operation
lines). If you
subtract 209 from the total consistent reads and measure just the number of I/Os against the table, then you'll find
that they are identical to the clustering factor for each respective index. The
COLOCATED_PK
is a classic “the table is
well ordered” example, whereas the
DISORGANIZED_PK
is a classic “the table is very randomly ordered” example. It is
interesting to see how this affects the optimizer now. If we attempt to retrieve 25,000 rows, Oracle will now choose a
full table scan for both queries (retrieving 25 percent of the rows via an index is not the optimal plan, even for the very
ordered table). However, if we drop down to 10 percent (bear in mind that 10 percent is
not a threshold value
—it is just
a number less than 25 percent that caused an index range scan to happen in this case) of the table data:
EODA@ORA12CR1> set autotrace traceonly explain
EODA@ORA12CR1> select * from colocated where x between 20000 and 30000;
Execution Plan
----------------------------------------------------------
Plan hash value: 2792740192
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost ...
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10002 | 791K| 142 ...
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| COLOCATED | 10002 | 791K| 142 ...
|* 2 | INDEX RANGE SCAN | COLOCATED_PK | 10002 | | 22 ...
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("X">=20000 AND "X"<=30000)
EODA@ORA12CR1> select * from disorganized where x between 20000 and 30000;
Execution Plan
----------------------------------------------------------
Plan hash value: 2727546897
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10002 | 791K| 333 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| DISORGANIZED | 10002 | 791K| 333 (1)| 00:00:01 |
----------------------------------------------------------------------------------
