oracle 修改聚簇因子,Oracle11gR2-聚簇因子浅析

创建表t1,t2

SQL> conn n1/n1

Connected.

SQL>

SQL>

SQL> create table t1 as select trunc(rownum/100) id ,object_name from all_objects where rownum<1000 ;

Table created.

SQL> create table t2 as select mod(rownum,100) id ,object_name from all_objects where rownum<1000;

Table created.

创建索引

SQL> create index inx_t1 on t1(id);

Index created.

SQL> create index inx_t2 on t2(id);

Index created.

SQL> exec dbms_stats.gather_table_stats(null,'T1',CASCADE=>true);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(null,'T2',CASCADE=>true);

PL/SQL procedure successfully completed.

SQL> conn / as sysdba

Connected.

###比较执行计划 查询t1走的索引范围扫描,t2确走的全表扫描

SQL> set autotrace trace exp stat

SQL> set linesize 300

SQL> select * from n1.t1 where id =2;

100 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 2808986199

--------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 100 | 2100 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 100 | 2100 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | INX_T1 | 100 | | 1 (0)| 00:00:01 |

--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("ID"=2)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

18 consistent gets

0 physical reads

0 redo size

4386 bytes sent via SQL*Net to client

589 bytes received via SQL*Net from client

8 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

100 rows processed

SQL> select * from n1.t2 where id =2;

10 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 1513984157

--------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 10 | 210 | 4 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| T2 | 10 | 210 | 4 (0)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("ID"=2)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

6 consistent gets

0 physical reads

0 redo size

842 bytes sent via SQL*Net to client

523 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

10 rows processed

###查看聚簇因子,可以看到t2 CLUSTERING_FACTOR很高,说明数据分布很散 SQL> conn n1/n1 Connected. SQL> set linesize 300 SQL> col table_name format a30 SQL> col index_name format a30 SQL> select i.table_name,i.index_name,i.CLUSTERING_FACTOR,t.blocks,i.NUM_ROWS from 2 user_tables t,user_indexes i where t.table_name=i.table_name and t.table_name in 3 ('T1','T2');

TABLE_NAME INDEX_NAME CLUSTERING_FACTOR BLOCKS NUM_ROWS

------------------------------ ------------------------------ ----------------- ---------- ----------

T1 INX_T1 4 7 999

T2 INX_T2 400 7 999

###解决方案:按照索引的顺序重新导入数据！