本文共 4106 字,大约阅读时间需要 13 分钟。
D:\Documents and Settings\tian>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on 星期日 5月 5 10:22:14 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn
已连接。SQL> create table t1 as select trunc((rownum-1)/100) id,rpad(rownum,100) t_pad
2 from dba_Source 3 where rownum<10000;Table created
SQL> create index t1_idx1 on t1(id);
Index created
SQL> exec dbms_stats.gather_table_stats(user,'t1',cascade=>true);
PL/SQL procedure successfully completed
SQL> drop table t2;
Table dropped
SQL> create table t2 as select mod(rownum,100) id,rpad(rownum,100) t_pad
2 from dba_Source 3 where rownum<10000;Table created
SQL> create index t2_idx1 on t2(id);
Index created
SQL> exec dbms_stats.gather_table_stats(user,'t2',cascade=>true);
PL/SQL procedure successfully completed
以上分别通过trunc和mod两个函数创建了两张内容相同,但数据排列不一样的表。
注意一下查询中两个表的前十行,ID排列不同。SQL> select * from t1 where rownum<10;ID T_PAD
---------- -------------------------------------------------------------------------------- 0 1 0 2 0 3 0 4 0 5 0 6 0 7 0 8 0 99 rows selected
SQL> select * from t2 where rownum<10;
ID T_PAD
---------- -------------------------------------------------------------------------------- 1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8 9 99 rows selected
分别在两个表上进行查询,选取第二次的结果:SQL> select * from t1 where id=1;已选择100行。
执行计划----------------------------------------------------------Plan hash value: 2623418078---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 100 | 10300 | 3 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| T1 | 100 | 10300 | 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)
统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 19 consistent gets 0 physical reads 0 redo size 12167 bytes sent via SQL*Net to client 451 bytes received via SQL*Net from client 8 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 100 rows processedSQL> select * from t2 where id=1;
已选择100行。
执行计划----------------------------------------------------------Plan hash value: 1513984157--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 100 | 10300 | 39 (3)| 00:00:01 ||* 1 | TABLE ACCESS FULL| T2 | 100 | 10300 | 39 (3)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------1 - filter("ID"=1)
统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 163 consistent gets 0 physical reads 0 redo size 11845 bytes sent via SQL*Net to client 451 bytes received via SQL*Net from client 8 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 100 rows processed T1是顺序排列的,使用索引方式查询,成本比全表扫描要低;T2是分散存储的,使用全表扫描的效率比索引高。以上实验提示我们:数据的分布情况在很大程度上也会影响执行计划。
通常来说,根据执行查询的频率,将数据有序排列,使每次需要获取的一批数据放在一起,能够提高效率。 索引的聚簇因子:向优化器表明具有同样索引值的数据行是不是存放在同一个或连续的一系列数据块中。以上两个表的索引聚簇因子信息为:SQL> select t.table_name||'.'||i.index_name idx_name,
2 i.clustering_factor,t.blocks,t.num_rows 3 from user_indexes i,user_tables t 4 where i.table_name=t.table_name 5 and t.table_name in('T1','T2') 6 order by t.table_name,i.index_name;IDX_NAME CLUSTERING_FACTOR BLOCKS NUM_ROWS
------------------------------------------------------------- ----------------- ---------- ----------T1.T1_IDX1 152 164 9999T2.T2_IDX1 9999 164 9999来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26451536/viewspace-760705/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26451536/viewspace-760705/