52. You are designing an application for a telecom company and you have been asked to design a
database table to facilitate monthly bill generation. The bill would include details of customer calls, listed in
chronological order.
Which method would you follow to achieve this objective without increasing the overhead of sorting the
rows?
A.create a hash cluster to store the data
B.create an index cluster to store the data
C.create a partitioned table to store the data
D.create a sorted hash cluster to store the data
E.create a heap table with rowid to store the data
Answer: D
Creating a Sorted Hash Cluster
In a sorted hash cluster, the rows corresponding to each value of the hash function are sorted on a specified set of columns in ascending order, which can improve response time during subsequent operations on the clustered data.
For example, a telecommunications company needs to store detailed call records for a fixed number of originating telephone numbers through a telecommunications switch. From each originating telephone number there can be an unlimited number of telephone calls.
Calls are stored as they are made and processed later in first-in, first-out order (FIFO) when bills are generated for each originating telephone number. Each call has a detailed call record that is identified by a timestamp. The data that is gathered is similar to the following:
Originating Telephone Numbers | Call Records Identified by Timestamp |
---|---|
650-555-1212 | t0, t1, t2, t3, t4, ... |
650-555-1213 | t0, t1, t2, t3, t4, ... |
650-555-1214 | t0, t1, t2, t3, t4, ... |
... | ... |
In the following SQL statements, the telephone_number
column is the hash key. The hash cluster is sorted on the call_timestamp
and call_duration
columns. The number of hash keys is based on 10-digit telephone numbers.
CREATE CLUSTER call_detail_cluster ( telephone_number NUMBER, call_timestamp NUMBER SORT, call_duration NUMBER SORT ) HASHKEYS 10000 HASH IS telephone_number SIZE 256; CREATE TABLE call_detail ( telephone_number NUMBER, call_timestamp NUMBER SORT, call_duration NUMBER SORT, other_info VARCHAR2(30) ) CLUSTER call_detail_cluster ( telephone_number, call_timestamp, call_duration );
Given the sort order of the data, the following query would return the call records for a specified hash key by oldest record first.
SELECT * WHERE telephone_number = 6505551212;