Database Reference
In-Depth Information
It should be noted, however, that a hash partitioned index cannot be range scanned; in general, it is most suitable
for exact equality (equals or in-lists). If you were to query “
WHERE OWNER > :X
” using the preceding index, it would
not be able to perform a simple range scan using partition elimination. You would be back to inspecting all 16 hash
partitions.
USING OrDer BY
this example brought to mind an unrelated but very important fact. When looking at hash partitioned indexes, we
are faced with another case where the use of an index to retrieve data would
not
automatically retrieve the data
sorted. Many people assume that if the query plan shows an index is used to retrieve the data, the data will be
retrieved sorted.
This has never been true.
the only way we can retrieve data in any sort of sorted order is to use
an
ORDER BY
clause on the query. If your query does not contain an
ORDER BY
statement, you cannot make any
assumptions about the sorted order of the data.
a quick example demonstrates this. We create a small table as a copy of
ALL_USERS
and create a hash partitioned
index with four partitions on the
USER_ID
column:
EODA@ORA12CR1> create table t
2 as
3 select *
4 from all_users
5 /
Table created.
EODA@ORA12CR1> create index t_idx
2 on t(user_id)
3 global
4 partition by hash(user_id)
5 partitions 4
6 /
Index created.
now, we will query that table and use a hint to have Oracle use the index. notice the ordering (actually, the lack of
ordering) of the data:
EODA@ORA12CR1> set autotrace on explain
EODA@ORA12CR1> select /*+ index( t t_idx ) */ user_id
2 from t
3 where user_id > 0
4 /
USER_ID
----------
13
...
97
22
...
104
8
