表主键排序和离散数据查询的速度测试

SQL> create table clustered (x int,data char(255));

Table created.

SQL> insert /*+append+*/ into clustered (x,data) select rownum,dbms_random.random from all_objects;

40960 rows created.

Elapsed: 00:00:06.68
insert /*+ append */ into clustered (x,data) select rownum,dbms_random.random from all_objects;

一个创建主关键字
alter table clustered add constraint clustered_pk primary key(x);

analyze table clustered compute statistics;

create table non_clustered(x int,data char(255));

insert /*+ appedn */ into non_clustered select x,data from clustered order by data;

alter table non_clustered add constraint non_clustered_pk primary key (x);

analyze table non_clustered compute statistics;

select index_name,clustering_factor from user_indexes where index_name like '%CLUSTERED_PK%';

INDEX_NAME CLUSTERING_FACTOR
------------------------------ -----------------
NON_CLUSTERED_PK 40935
CLUSTERED_PK 1518

看看结果没有排序的比较快 CPU使用比较少
set autotrace traceonly explain
set timing on

SQL> select * from clustered where x between 50 and 2750;
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1763666373

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2702 | 683K| 108 (0)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| CLUSTERED | 2702 | 683K| 108 (0)| 00:00:02 |
|* 2 | INDEX RANGE SCAN | CLUSTERED_PK | 2702 | | 7 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("X">=50 AND "X"<=2750)

select * from non_clustered where x between 50 and 2750;

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 681052411

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2702 | 683K| 347 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| NON_CLUSTERED | 2702 | 683K| 347 (1)| 00:00:05 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("X"<=2750 AND "X">=50)