Database Reference
In-Depth Information
We've mapped the
CUST_ID
column of this table to the hash key for the sorted hash cluster and the
ORDER_DT
column to the
SORT
column. We can observe using AUTOTRACE in SQL*Plus that the normal sort operations we
expect are missing when accessing the sorted hash cluster:
EODA@ORA12CR1> set autotrace traceonly explain
EODA@ORA12CR1> variable x number
EODA@ORA12CR1> select cust_id, order_dt, order_number
2 from cust_orders
3 where cust_id = :x
4 order by order_dt;
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 1 (0)| 00:00:01 |
|* 1 | TABLE ACCESS HASH| CUST_ORDERS | 1 | 39 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------
EODA@ORA12CR1> select job, hiredate, empno
2 from scott.emp
3 where job = 'CLERK'
4 order by hiredate;
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 112 | 2 (0)| 00:00:01 |
| 1 | SORT ORDER BY | | 4 | 112 | 2 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 4 | 112 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | JOB_IDX | 4 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
EODA@ORA12CR1> set autotrace off
I added the query against the normal
SCOTT.EMP
table (after indexing the
JOB
column for this demonstration) to
compare what we normally expect to see: the
SCOTT.EMP
query plan versus what the sorted hash cluster can do for
us when we want to access the data in a FIFO mode (like a queue). As you can see, the sorted hash cluster has one
step: it takes the
CUST_ID=:X
, hashes the input, finds the first row, and just starts reading the rows, as they are in order
already. The regular table is much different: it finds all of the
JOB='CLERK'
rows (which could be anywhere in that
heap table), sorts them, and then returns the first one.
So, the sorted hash cluster has all the retrieval aspects of the hash cluster, in that it can get to the data without
having to traverse an index, and many of the features of the IOT, in that the data will be sorted within that key by some
field of your choice. This data structure works well when the input data arrives in order by the sort field, by key. That
is, over time the data arrives in increasing sort order for any given key value. Stock information fits this requirement as
an example. Every night you get a new file full of stock symbols, the date (the date would be the sort key and the stock
symbol would be the hash key), and related information. You receive and load this data in sort key order. The stock
data for stock symbol
ORCL
for yesterday does not arrive after today—you would load yesterday's value, and then today's
value, and later tomorrow's value. If the information arrives randomly (not in sort order), this data structure quickly
breaks down during the insert process, as much data has to be moved to put the rows physically in order on disk. A
sorted hash cluster is not recommended in that case (an IOT, on the other hand, could well be useful for that data).
