These tables had the following number total blocks apiece select

Chapter 11 ■ Indexes

select /*+ index( disorganized disorganized_pk ) */ * from disorganized where x between 20000 and 40000

100,000+ rows processed).

I think this is pretty incredible. What a difference physical data layout can make! Table 11-5 summarizes the results.

In my database using an 8KB block size, these tables had the following number of total blocks apiece:

The query against the disorganized table bears out the simple math we did earlier: we did 20,000+ logical I/Os (100,000 total blocks queried and five runs of the query). We processed each and every block 20 times! On the other hand, the physically COLOCATED data took the logical I/Os way down. Here is the perfect illustration of why rules of thumb are so hard to provide—in one case, using the index works great, and in the other it doesn’t. Consider this the next time you dump data from your production system and load it into development, as it may very well provide at least part of the answer to the question, “Why is it running differently on this machine—aren’t they identical?” They are not identical.

■Note recall from Chapter 6 that increased logical I/O is the tip of the iceberg here. each logical I/O involves one or

In the example earlier in this section, we were using the sQL*plus default array fetch size of 15 rows (if you divide the total rows fetched (100005) by the number of fetch calls (6675), the result is very close to 15). If we were to compare the execution of the previous queries using 15 rows per fetch versus 100 rows per fetch, we would observe the following for the COLOCATED table:

select * from colocated a15 where x between 20000 and 40000

20001 20001 20001 INDEX RANGE SCAN COLOCATED_PK (cr=245 pr=0 pw=0 ...

460