--聚合因子试验准备:

--分别建两张有序和无序的表
CREATE TABLE T_COLOCATED ( ID NUMBER, COL2 VARCHAR2(100) );
BEGIN
        FOR I IN 1 .. 100000
        LOOP
            INSERT INTO T_COLOCATED(ID,COL2)
            VALUES (I, RPAD(DBMS_RANDOM.RANDOM,95,'*') );
        END LOOP;
END;
/

ALTER TABLE T_COLOCATED ADD CONSTRAINT PK_T_COLOCATED PRIMARY KEY(ID);

CREATE TABLE T_DISORGANIZED
     AS
    SELECT ID,COL2
    FROM T_COLOCATED
    ORDER BY COL2;

ALTER TABLE T_DISORGANIZED ADD CONSTRAINT PK_T_DISORG PRIMARY KEY (ID);


--分别分析两张表的聚合因子层度
                                                         
SELECT INDEX_NAME,                                                         
              BLEVEL,                                                          
              LEAF_BLOCKS,                                                     
              NUM_ROWS,                                                        
              DISTINCT_KEYS,                                                   
              CLUSTERING_FACTOR                                                
         FROM USER_IND_STATISTICS                                              
        WHERE TABLE_NAME IN( 'T_COLOCATED','T_DISORGANIZED');    


INDEX_NAME                         BLEVEL LEAF_BLOCKS   NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR
------------------------------ ---------- ----------- ---------- ------------- -----------------
PK_T_COLOCATED                          1         208     100000        100000              1469
PK_T_DISORG                             1         208     100000        100000             99932
--首先观察有序表的查询性能



执行并比较性能差异
select /*+index(t)*/ * from  t_colocated t  where id>=20000 and id<=40000;

执行计划
----------------------
Plan hash value: 4204525375

----------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------
|   0 | SELECT STATEMENT            |                | 21104 |  1339K|   389   (1)| 00:00:05 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_COLOCATED    | 21104 |  1339K|   389   (1)| 00:00:05 |
|*  2 |   INDEX RANGE SCAN          | PK_T_COLOCATED | 21104 |       |    53   (2)| 00:00:01 |
----------------------------------------------------------

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

   2 - access("ID">=20000 AND "ID"<=40000)

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------
          0  recursive calls
          0  db block gets
       2986  consistent gets
          0  physical reads
          0  redo size
    2293678  bytes sent via SQL*Net to client
      15048  bytes received via SQL*Net from client
       1335  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      20001  rows processed

再观察无序表的查询性能
select /*+index(t)*/ * from  t_disorganized t  where id>=20000 and id<=40000;

已用时间:  00: 00: 09.75

执行计划
----------------------
Plan hash value: 4204525375

----------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------
|   0 | SELECT STATEMENT            |                | 21104 |  1339K|   389   (1)| 00:00:05 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_COLOCATED    | 21104 |  1339K|   389   (1)| 00:00:05 |
|*  2 |   INDEX RANGE SCAN          | PK_T_COLOCATED | 21104 |       |    53   (2)| 00:00:01 |
----------------------------------------------------------

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

   2 - access("ID">=20000 AND "ID"<=40000)

Note
-----
   - dynamic sampling used for this statement


统计信息
----