ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORACLE INDEXES

ORACLE INDEXES

原创 Linux操作系统 作者:tolywang 时间:2007-11-21 00:00:00 0 删除 编辑
B*Tree Indexes
SQL> SQL> select index_name||' '||blevel||' '||num_rows
2 from dba_indexes where table_name='TM_VEHICLE'
3 /
INDEX_NAME||''||BLEVEL||''||NUM_ROWS

-----------------------------------------------------------------
TM_VEHICLE_LEAVING_DATE 2 3461864
这里BLEVEL不包括LEAF,也就是说仅代表BRANCH(B)
1* analyze index sbpopt.TM_VEHICLE_LEAVING_DATE validate structure
SQL> /
Index analyzed.
SQL> select height||' '||name from index_stats;
HEIGHT||''||NAME
------------------------------------------------------------------------------
3 TM_VEHICLE_LEAVING_DATE
这里的HEIGHT代表整个INDEX TREE的高度,包括LEAF NODE。(参考cost of dual
http://sunmoonking.spaces.live.com/blog/cns!E3BD9CBED01777CA!234.entry
Compression
COMPRESS可能能将INDEX TREE的高度降低,比如从3降到2,但是,ORACLE将花更多的时间在寻址上, 优点是BUFFER中可以放更多的INDEX ENTRIES,可以提高cache-hit的命中率,物理I/O也会随之降低。也就是说compress index在提高I/O的同时会消耗更多CPU
Reverse
REVERSE KEY INDEX能减少leaf block的争用,尤其是在RAC环境中,可以减少访问相同块的几率,同时也就能减少在RAC instance之间传输的BLOCK的数量。
Descending
SQL> create table colocated ( x int, y varchar2(80) );
表已创建。
1 begin
2 for i in 1 .. 100000
3 loop
4 insert into colocated(x,y)
5 values (i, rpad(dbms_random.random,75,'*') );
6 end loop;
7* end;
8 /
PL/SQL 过程已成功完成。
SQL> alter table colocated add constraint colocated_pk primary key(x);
表已更改。
SQL> begin
2 dbms_stats.gather_table_stats( user, 'COLOCATED', cascade=>true );
3 end;
4 /
因为BLOCK SIZE是8K,所以,差不多100行每块。
SQL> select table_name,blocks from user_tables
2 where table_name='COLOCATED'
3 /
TABLE_NAME BLOCKS
------------------------------ ----------
COLOCATED 1252
再来看看INDEX是如何应用的
Set autotrace traceonly
SQL> select x,y from colocated where x<2000
2 /
已选择1999行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=30 Card=1999 Bytes=1
59920)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'COLOCATED' (Cost=30 Card
=1999 Bytes=159920)
2 1 INDEX (RANGE SCAN) OF 'COLOCATED_PK' (UNIQUE) (Cost=6 Ca
rd=1999)
看到INDEX (RANGE SCAN)后面跟着一个 TABLE ACCESS (BY INDEX ROWID),ORACLE先读INDEX,然后根据INDEX ENTIRES读database block然后得到row data。这种读法,在数据量小的时候比较有效(thin表1%-3%,fat表1%-20%)
1* select count(*) from colocated where x<2000
SQL> /
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=5)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'COLOCATED_PK' (UNIQUE) (Cost=6 Ca
rd=1999 Bytes=9995)
这种读法不需要读DATA BLOCK,仅仅读INDEX。
通过index去访问表,我们会发生很多scattered,random I/O,意思是index会告诉我们都block1,block200,block1,block352,block1。。。而不会去顺序读,也就是我们可能多次读一个block。所以2000行TABLE ACCESS BY ROWID可能会导致2000次table blocks读。而这2000行数据可能仅仅保存在20个BLOCK里(这也就是为什么前面说(thin1%-3%fat1%-20%)的原因,%BLOCK所能容纳的ROWS数有关,本例中100row/block,那如果2row/block呢? )
CLUSTERING_FACTORUSER_INDEXES中的CLUSTERING_FACTOR表示表中数据的ORDER和INDEX的ORDER的匹配程度。
select a.index_name,
b.num_rows,
b.blocks,
a.clustering_factor
from user_indexes a, user_tables b
where index_name in ('COLOCATED_PK')
and a.table_name = b.table_name
INDEX_NAME NUM_ROWS
------------------------------------------------------------ ----------
BLOCKS CLUSTERING_FACTOR
---------- -----------------
COLOCATED_PK 100000
1252 1190
另外建一个按colocated的Y排序的表disorganized,来排乱index的顺序。
SQL> create table disorganized as
2 select x,y from colocated order by y;
Table created.
SQL> alter table disorganized
2 add constraint disorganized_pk
3 primary key (x);
Table altered.
SQL> begin
2 dbms_stats.gather_table_stats( user, 'DISORGANIZED', cascade=>true );
3 end;
4 /
PL/SQL procedure successfully completed.
1 select a.index_name,
2 b.num_rows,
3 b.blocks,
4 a.clustering_factor
5 from user_indexes a, user_tables b
6 where index_name in ('COLOCATED_PK','DISORGANIZED_PK')
7* and a.table_name = b.table_name
SQL> /
INDEX_NAME NUM_ROWS
------------------------------------------------------------ ----------
BLOCKS CLUSTERING_FACTOR
---------- -----------------
COLOCATED_PK 100000
1252 1190
DISORGANIZED_PK 100094
1219 99905
大家也可以SQL TRACE下,disorganized的CPU,LOGICAL I/O都会比COLOCATED大很多,可以看到'DISORGANIZED_PK的CLUSTERING_FACTOR和NUM_ROWS很接近。我们如果通过INDEX读数据从头读到尾,会发生99905 I/O,比COLOCATED_PK大很多。
相同的表相同的INDEX如果CLUSTERING_FACTOR不同执行计划也会不同,甚至相差很大。
SQL> select * from colocated where x between 10000 and 20000;
10001 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=142 Card=10005 Byt
es=800400)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'COLOCATED' (TABLE) (Cost
=142 Card=10005 Bytes=800400)
2 1 INDEX (RANGE SCAN) OF 'COLOCATED_PK' (INDEX (UNIQUE)) (C
ost=22 Card=10005)
SQL> select * from DISORGANIZED where x between 10000 and 20000;
10001 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=271 Card=10013 Byt
es=801040)
1 0 TABLE ACCESS (FULL) OF 'DISORGANIZED' (TABLE) (Cost=271 Ca
rd=10013 Bytes=801040)
Bitmap Indexes
Bitmap是7.3版本推出的一种INDEX。Standard Edition.不支持,Enterprise and Personal Editions支持,是为data warehous设计,OLTP不适合。一个index key entry 可以指向多行数据,而B*TREE则是一对一的。Bitmap index存储null entries。适合low distinct cardinality.
Bitmap Join Indexes
这是oracle9 i推出的新的bitmap index类型。
create bitmap index emp_bm_idx
2 on emp( d.dname )
3 from emp e, dept d
4 where e.deptno = d.deptno
应用时
select emp.*
2 from emp, dept
3 where emp.deptno = dept.deptno4 and dept.dname = 'SALES'

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/35489/viewspace-85024/,如需转载,请注明出处,否则将追究法律责任。

下一篇: Oracle I/O问题解析
请登录后发表评论 登录
全部评论
Oracle , MySQL, SAP IQ, SAP HANA, PostgreSQL, Tableau 技术讨论,希望在这里一起分享知识,讨论技术,畅谈人生 。

注册时间:2007-12-10

  • 博文量
    5595
  • 访问量
    13380808