Partitioning - Expert Oracle Database Architecture - page 643

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.00 0.00 0 49 0 20
total 5 0.00 0.00 0 11 0 20

select * from t where owner = :o

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.00 0.00 0 665 0 26
total 5 0.00 0.00 0 628 0 26

Each of the queries had the same outcome answer-wise, but consumed significantly more I/Os to accomplish
it—this is not good. The root cause? The index-partitioning scheme. Notice in the preceding plan the partitions listed
in the last line: 1 through 16.

1 1 1 PARTITION HASH ALL PARTITION: 1 16 (cr=34 pr=0 pw=0 time=95...
1 1 1 TABLE ACCESS BY LOCAL INDEX ROWID BATCHED T PARTITION: ...
1 1 1 INDEX RANGE SCAN T_IDX PARTITION: 1 16 (cr=33 pr=0 pw=0...

This query has to look at each and every index partition here. The reason for that is because entries for SCOTT
may well be in each and every index partition and probably is. The index is logically hash partitioned by OBJECT_ID ;
any query that uses this index and that does not also refer to the OBJECT_ID in the predicate must consider every
index partition! So, what is the solution here? You should globally partition your index. Using the previous case as the
example, we could choose to hash partition the index:

■ hash partitioning of indexes was a feature added in Oracle 10 g that is not available in Oracle9 i . there are
considerations to be taken into account with hash partitioned indexes regarding range scans, which we'll discuss later in
this section.

Note

EODA@ORA12CR1> create index t_idx
2 on t(owner,object_type,object_name)
3 global
4 partition by hash(owner)
5 partitions 16
6 /
Index created.

Much like the hash partitioned tables we investigated earlier, Oracle will take the OWNER value, hash it to a
partition between 1 and 16, and place the index entry in there. Now when we review the TKPROF information for these
three queries again, as follows, we can see we are much closer to the work performed by the nonpartitioned table
earlier—that is, we have not negatively impacted the work performed by our queries:

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 4 0 1
total 5 0.00 0.00 0 11 0 20
total 5 0.00 0.00 0 628 0 26