集群因子 例子

测试:
create table t1 as select trunc((rownum-1)/100) id,
                  rpad(rownum,100) t_pad
                  from dba_source
              where rownum<100000;

create index t1_idx1 on t1(id);


BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'HW',
                                tabname          => 'T1',
                                estimate_percent => 100,
                                method_opt       => 'for all columns size skewonly',
                                no_invalidate    => FALSE,
                                degree           => 8,
                                cascade          => TRUE);
END;



SQL> select index_name,clustering_factor from user_indexes where table_name='T1';  

INDEX_NAME		       CLUSTERING_FACTOR
------------------------------ -----------------
T1_IDX1 				    1536


SQL>  select count(distinct dbms_rowid.rowid_block_number(rowid)) from t1;

COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))
---------------------------------------------------
					       1536

SQL> select * from (select id,count(*) from t1 group by id order by id,count(*)) where rownum<10;

	ID   COUNT(*)
---------- ----------
	 0	  100
	 1	  100
	 2	  100
	 3	  100
	 4	  100
	 5	  100
	 6	  100
	 7	  100
	 8	  100

9 rows selected.


SQL> select * from t1 where id=1;

100 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2623418078

---------------------------------------------------------------------------------------
| Id  | Operation		    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |	      |   100 | 10500 |     3	(0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1      |   100 | 10500 |     3	(0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN	    | T1_IDX1 |   100 |       |     1	(0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   2 - access("ID"=1)


Statistics
----------------------------------------------------------
	  1  recursive calls
	  0  db block gets
	 19  consistent gets
	  0  physical reads
	  0  redo size
      12649  bytes sent via SQL*Net to client
	586  bytes received via SQL*Net from client
	  8  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	100  rows processed

集群因子接近表的块数,索引效率较高。



2)

create table t2 as select mod(rownum,100) id,
                  rpad(rownum,100) t_pad
                  from dba_source
              where rownum<100000;
     create index t1_idx2 on t2(id);
         BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'HW',
                                tabname          => 'T2',
                                estimate_percent => 100,
                                method_opt       => 'for all columns size skewonly',
                                no_invalidate    => FALSE,
                                degree           => 8,
                                cascade          => TRUE);
END;

SQL> select index_name,clustering_factor from user_indexes where table_name='T2';

INDEX_NAME		       CLUSTERING_FACTOR
------------------------------ -----------------
T1_IDX2 				   99999

SQL> select count(*) from t2;

  COUNT(*)
----------
     99999

SQL> select count(*) from t2 where id=1;

  COUNT(*)
----------
      1000

SQL> select 1000/99999 * 100 from dual;

1000/99999*100
--------------
       1.00001

这个数据比例应该走索引啊！



SQL> select * from t2 where id=1;

1000 rows selected.


SQL> set pagesize 200
SQL> set autot trace
SQL> select * from t2 where id=1;

1000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 |  1000 |   101K|   423   (1)| 00:00:06 |
|*  1 |  TABLE ACCESS FULL| T2	 |  1000 |   101K|   423   (1)| 00:00:06 |
--------------------------------------------------------------------------

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

   1 - filter("ID"=1)


Statistics
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
       1588  consistent gets
       1517  physical reads
	  0  redo size
     118709  bytes sent via SQL*Net to client
       1246  bytes received via SQL*Net from client
	 68  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
       1000  rows processed


确走全表扫描了,逻辑读为1588

强制走索引的逻辑读:

SQL> select /*+ index(t2 T1_IDX2)*/   * from t2 where id=1;

1000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1418564783

---------------------------------------------------------------------------------------
| Id  | Operation		    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |	      |  1000 |   101K|  1002	(0)| 00:00:13 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2      |  1000 |   101K|  1002	(0)| 00:00:13 |
|*  2 |   INDEX RANGE SCAN	    | T1_IDX2 |  1000 |       |     2	(0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   2 - access("ID"=1)


Statistics
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
       1071  consistent gets
       1004  physical reads
	  0  redo size
     121909  bytes sent via SQL*Net to client
       1246  bytes received via SQL*Net from client
	 68  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
       1000  rows processed