OCP-043 Sorted Hash Cluster

最新推荐文章于 2021-07-08 10:13:54 发布

xiadingling

最新推荐文章于 2021-07-08 10:13:54 发布

阅读量721

点赞数

CC 4.0 BY-SA版权

分类专栏： OCP 文章标签： table sorting each application numbers include

本文链接：https://blog.youkuaiyun.com/xiadingling/article/details/7949870

OCP 专栏收录该内容

72 篇文章

订阅专栏

本文介绍为电信公司设计应用时如何使用排序哈希簇来存储按时间顺序排列的客户通话记录，以简化月结账单生成过程。通过创建一个排序哈希簇集群，电话号码对应的数据被按指定列排序，提高了后续操作的响应速度。实例展示了如何存储固定起始电话号码的大量通话记录，并通过SQL语句实现特定电话号码的通话记录查询。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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;