13.读书笔记收获不止Oracle之 簇表

普通还有一点缺陷，就是ORDERBY 语句中的排序不可避免。

有序簇表可以避免排序。

试验如下：

先创建簇如下：

create cluster shc

( cust_id number,

order_dt timestamp SORT

)

hashkeys 10000

hash is cust_id

size 8192

/

创建表

Create table cust_orders

(cust_id number,

Order_dt timestamp SORT,

Order_number number,

Username varchar2(30),

Ship_addr number,

Bill_addr number,

Invoice_num number

)

Cluster shc ( cust_id,order_dt);

开始执行分析：

sql> set autotrace traceonly explain

sql> variable x number

sql> select cust_id,order_dt,order_number

from cust_orders

where cust_id =:x

order by order_dt;

Execution Plan

----------------------------------------------------------

Plan hash value: 465084913

--------------------------------------------------------------------------------

-

| 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

|

--------------------------------------------------------------------------------

-

Predicate Information (identified byoperation id):

---------------------------------------------------

1- access("CUST_ID"=TO_NUMBER(:X))

Note

-----

-dynamic statistics used: dynamic sampling (level=2)

关于避免排序，还有一种方法：排序列列正好是索引列时，可以避免排序。