13.读书笔记收获不止Oracle之 簇表
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) 关于避免排序,还有一种方法:排序列列正好是索引列时,可以避免排序。 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |