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

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)