創(chuàng)建表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.

創(chuàng)建索引

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.

比較執(zhí)行計(jì)劃 查詢(xún)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 calls0 db block gets18 consistent gets0 physical reads0 redo size4386 bytes sent via SQL*Net to client589 bytes received via SQL*Net from client8 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)100 rows processedSQL> 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 calls0 db block gets6 consistent gets0 physical reads0 redo size842 bytes sent via SQL*Net to client523 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)10 rows processed

查看聚簇因子,可以看到t2 CLUSTERING_FACTOR很高,說(shuō)明數(shù)據(jù)分布很散

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 from2 user_tables t,user_indexes i where t.table_name=i.table_name and t.table_name in3 ('T1','T2');TABLE_NAME INDEX_NAME CLUSTERING_FACTOR BLOCKS NUM_ROWS ------------------------------ ------------------------------ ----------------- ---------- ---------- T1 INX_T1 4 7 999 T2 INX_T2 400 7 999

解決方案:按照索引的順序重新導(dǎo)入數(shù)據(jù)！

轉(zhuǎn)載于:https://www.cnblogs.com/chinesern/p/8963236.html

總結(jié)

以上是生活随笔為你收集整理的Oracle11gR2-聚簇因子浅析的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。