Database Reference
In-Depth Information
Just to wrap up this example, let's look at what happens when we full scan the
DISORGANIZED
table:
select * from disorganized where x between 20000 and 30000
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 668 0.01 0.03 0 1858 0 10001
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 670 0.01 0.03 0 1858 0 10001
...
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ----------------------------------------------------
10001 10001 10001 TABLE ACCESS FULL DISORGANIZED (cr=1858 pr=0 pw=0...
Thus, in this particular case, the full scan is very appropriate due to the way the data is physically stored on disk.
So why didn't the optimizer full scan in the first place for this query? Well, it would have if left to its own design, but in
the first example query against
DISORGANIZED
I purposely hinted the query and told the optimizer to construct a plan
that used the index. In the second case, I let the optimizer pick the best overall plan.
The Clustering Factor
Next, let's look at some of the information Oracle will use. We are specifically going to look at the
CLUSTERING_FACTOR
column found in the
USER_INDEXES
view. The
Oracle Database Reference
manual tells us this column has the following
meaning:
Indicates the amount of order of the rows in the table based on the values of the index:
•
If the value is near the number of blocks, then the table is very well ordered. In this case, the
index entries in a single leaf block tend to point to rows in the same data blocks.
•
If the value is near the number of rows, then the table is very randomly ordered. In this case, it is
unlikely that index entries in the same leaf block point to rows in the same data blocks.
We could also view the clustering factor as a number that represents the number of logical I/Os against the table
that would be performed to read the entire table via the index. That is, the
CLUSTERING_FACTOR
is an indication of how
ordered the table is with respect to the index itself, and when we look at these indexes we find the following:
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
