Used equivalent amounts cpu time

Chapter 11 ■ Indexes

These queries read every single row in the table by means of the index. The TKPROF report shows us the following:

SELECT /*+ INDEX( t t_idx_2 ) */ COUNT(*) FROM T
WHERE OBJECT_NAME = :B3 AND OBJECT_TYPE = :B2 AND OWNER = :B1

call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.00 0.00 0 0 0 0 Execute 50253 0.68 0.66 0 0 0 0 Fetch 50253 0.48 0.48 0 100834 0 50253------- ------ -------- ---------- ---------- ---------- ---------- ----------total 100507 1.16 1.15 0 100834 0 50253 ...

509

Chapter 11 ■ Indexes

We then looked at bitmap indexes, an excellent method for indexing low to medium cardinality data in a data warehouse (read-intensive, non-OLTP) environment. We covered the times it would be appropriate to use a bitmapped index and why you would never consider one for use in an OLTP environment—or any environment where multiple users must concurrently update the same column.

We moved on to cover function-based indexes, which are actually special cases of B*Tree and bitmapped indexes.

This section covered topics ranging from the simple question “Do indexes work with views?” to the more complex and subtle myth “Space is never reused in an index.” We answered these questions and debunked the myths mostly through example, demonstrating the concepts as we went along.

510