Indexes - Expert Oracle Database Architecture - page 459

■ I used an assM managed tablespace for this section's example, which explains why the clustering factor for
the COLOCATED table is less than the number of blocks in the table. there are unformatted blocks in the upcoming
COLOCATED table the hWM that do not contain data, as well as blocks used by assM itself to manage space, and we will
not read these blocks ever in an index range scan. Chapter 10 explains hWMs and assM in more detail.

Note

So the database is saying, “If we were to read every row in COLOCATED via the index COLOCATED_PK from start to
finish, we would perform 1,190 I/Os. However, if we did the same to DISORGANIZED , we would perform 99,929 I/Os
against the table.” The reason for the large difference is that as Oracle range scans through the index structure, if it
discovers the next row in the index is on the same database block as the prior row, it does not perform another I/O to
get the table block from the buffer cache. It already has a handle to one and just uses it. However, if the next row is not
on the same block, then it will release that block and perform another I/O into the buffer cache to retrieve the next
block to be processed. Hence the COLOCATED_PK index, as we range scan through it, will discover that the next row is
almost always on the same block as the prior row. The DISORGANIZED_PK index will discover the opposite is true. In
fact, we can actually see this measurement is very accurate. If we hint to the optimizer to use an index full scan to read
the entire table and just count the number of non-null Y values, we can see exactly how many I/Os it will take to read
the entire table via the index:

select count(Y) from
(select /*+ INDEX(COLOCATED COLOCATED_PK) */ * from colocated)

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 2 0.03 0.03 0 1399 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.03 0.03 0 1399 0 1
...
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- --------------------------------------------------
1 1 1 SORT AGGREGATE (cr=1399 pr=0 pw=0 time=34740 us)
100000 100000 100000 TABLE ACCESS BY INDEX ROWID BATCHED
COLOCATED (cr=1399 pr=0 pw=0 time=90620 us cost=1400 size=7600000...
100000 100000 100000 INDEX FULL SCAN COLOCATED_PK (cr=209 pr=0 pw=0 ...
********************************************************************************
select count(Y) from
(select /*+ INDEX(DISORGANIZED DISORGANIZED_PK) */ * from disorganized)

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 2 0.11 0.11 0 100138 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.11 0.11 0 100138 0 1
...