B-TREE索引，btree
B-TREE索引，btree
郑重声明：由于本人水平有限，如有不对的地方请大神指正不甚感激（327568824@qq.com）
三种索引的区别http://blog.csdn.net/bzfys/article/details/45824549
创建表T1
SQL> createtable t1(id int primary key,name varchar2(10));
Table created.
创建表T2
SQL> createtable t2(id int primary key,name varchar2(10));
Table created.
创建表T3
SQL> create table t3(id int ,namevarchar2(10));
Table created.
创建表T4
SQL>create table t4 as select mod(object_id,2)object_Id,object_name from dba_objects;
Table created.
给T5添加索引
SQL> create indext4_ind on t4(object_id);
Index created.
SQL> select count(*)from t4;
COUNT(*)
----------
71744
给T1添加表内容
SQL> begin
for i in 1..10000 loop
insert into t1 values(i,'T1');
end loop;
end;
/
SQL> commit ;
给T2添加表内容
SQL> begin
for i in 1..1000000 loop
insert into t2 values(i,'T2');
end loop;
end;
/
给T3添加表内容
SQL> begin
2 for i in 1..10000 loop
3 insert into t3 values(i,'T1');
4 end loop;
5 end;
6 /
PL/SQL proceduresuccessfully completed.
给T4添加表内容
SQL> insert into t4 select object_id from dba_objects;
71743 rows created.
SQL> commit ;
开启
SQL>set autotrace trace exp stat;
SQL>set linesize 150;
分别查从T1,T2,T3中查找id=100的行
SQL>select * from t1 where id=100;
Execution Plan
----------------------------------------------------------
Plan hash value: 1523267052
--------------------------------------------------------------------------------------------
| Id | Operation |Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1 | 20 | 1 (0)| 00:00:01 |
| 1| TABLE ACCESS BY INDEX ROWID| T1 | 1 | 20 | 1 (0)| 00:00:01 |
|* 2| INDEX UNIQUE SCAN | SYS_C0015867 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
2- access("ID"=100)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
457 bytes sent via SQL*Net toclient
512 bytes received via SQL*Netfrom client
1 SQL*Net roundtrips to/fromclient
0 sorts (memory)
0 sorts (disk)
-
rows processed
SQL>select * from t2 where id=2;
Execution Plan
----------------------------------------------------------
Plan hash value: 2557291133
--------------------------------------------------------------------------------------------
| Id | Operation |Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 |
| 1| TABLE ACCESS BY INDEX ROWID| T2 | 1 | 20 | 2 (0)| 00:00:01 |
|* 2| INDEX UNIQUE SCAN | SYS_C0015868 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
2- access("ID"=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
457 bytes sent via SQL*Net toclient
512 bytes received via SQL*Netfrom client
1 SQL*Net roundtrips to/fromclient
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>select * from t3 where id=100;
Execution Plan
----------------------------------------------------------
Plan hash value: 4161002650
--------------------------------------------------------------------------
| Id | Operation | Name |Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1 | 20 | 7 (0)| 00:00:01 |
|* 1| TABLE ACCESS FULL| T3 | 1 | 20 | 7 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
1- filter("ID"=100)
Note
-----
-dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
24 consistent gets
0 physical reads
0 redo size
589 bytes sent via SQL*Net toclient
523 bytes received via SQL*Netfrom client
2 SQL*Net roundtrips to/fromclient
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>select * from t4 where object_id=1;
35864 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2002323537
--------------------------------------------------------------------------
| Id | Operation | Name |Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 36807 | 2839K| 89 (2)| 00:00:02 |
|* 1| TABLE ACCESS FULL| T5 | 36807 | 2839K| 89 (2)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
1- filter("OBJECT_ID"=1)
Note
-----
-dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2706 consistent gets
0 physical reads
0 redo size
1496052 bytes sent via SQL*Net toclient
26813 bytes received via SQL*Netfrom client
2392 SQL*Net roundtrips to/fromclient
0 sorts (memory)
0 sorts (disk)
35864 rows processed
SQL> select /*+ index(t5 t5_ind) */* from t5 where object_id=1;
35864 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4106839317
--------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
------
| 0 | SELECT STATEMENT | | 36807 | 2839K| 389 (1)| 00:0
0:05 |
| 1 | TABLE ACCESS BY INDEX ROWID| T5 | 36807 | 2839K| 389 (1)| 00:0
0:05 |
|* 2 | INDEX RANGE SCAN | T5_IND | 36807 | | 69 (0)| 00:0
0:01 |
--------------------------------------------------------------------------------
------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5149 consistent gets
0 physical reads
0 redo size
1496052 bytes sent via SQL*Net to client
26813 bytes received via SQL*Net from client
2392 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
35864 rows processed
发现T1一致性读为3次，T2为4次，T3为24次，而只有71744行的T4表带却是TABLE ACCESS FULL（全表扫描）一致性读为2706，但是强制使用了索引一致性读为5149。
查看trace文件路径
SQL> select tracefile from v$process where addr in(select paddr from v$session where sid in (select sid from v$mystat));
TRACEFILE
------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/racdb007/racdb0071/trace/racdb0071_ora_4121.trc
执行下面命令
SQL> alter sessionset sql_trace=true;
Session altered.
SQL> select * fromt1 where id=100;
ID NAME
---------- ----------
100 T1
SQL> select * fromt2 where id=100;
ID NAME
---------- ----------
100 T2
SQL> alter sessionset sql_trace=false;
Session altered.
查看TRACE结果
T1表
*** 2015-05-28 19:24:16.734
*** SESSION ID:(44.29) 2015-05-2819:24:16.734
=====================
PARSING IN CURSOR #139736387538400 len=32dep=0 uid=0 oct=42 lid=0 tim=1432812256734081 hv=1569151342 ad='7f16ea14fe10'sqlid='4tk6t8tfsfqbf'
alter session set sql_trace=true
END OF STMT
EXEC#139736387538400:c=0,e=116,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1432812256734029
*** 2015-05-28 19:24:30.541
CLOSE#139736387538400:c=0,e=16,dep=0,type=0,tim=1432812270541247
=====================
PARSING IN CURSOR #139736387538400 len=29dep=0 uid=0 oct=3 lid=0 tim=1432812270542506 hv=2732024291 ad='b75fd0f8'sqlid='c9bya8ajdfsg3'
select * from t1 where id=100
END OF STMT
PARSE#139736387538400:c=1000,e=872,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1523267052,tim=1432812270542504
EXEC#139736387538400:c=0,e=63,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1523267052,tim=1432812270542727
FETCH#139736387538400:c=0,e=656,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=1523267052,tim=1432812270543482
STAT #139736387538400 id=1 cnt=1 pid=0pos=1 obj=86892 op='TABLE ACCESS BY INDEX ROWID T1 (cr=3 pr=0 pw=0 time=654 uscost=2 size=20 card=1)'
STAT #139736387538400 id=2 cnt=1 pid=1pos=1 obj=86893 op='INDEX UNIQUE SCAN SYS_C0015867 (cr=2 pr=0 pw=0 time=636 uscost=1 size=0 card=1)'
FETCH#139736387538400:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=1523267052,tim=1432812270545801
T2表
*** 2015-05-28 19:25:01.981
CLOSE #139736387538400:c=0,e=20,dep=0,type=0,tim=1432812301981911
=====================
PARSING IN CURSOR #139736387538400 len=29dep=0 uid=0 oct=3 lid=0 tim=1432812301982484 hv=2555944085 ad='b75f4d20'sqlid='dmn1mxuc5j74p'
select * from t2 where id=100
END OF STMT
PARSE #139736387538400:c=1000,e=323,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2557291133,tim=1432812301982482
EXEC#139736387538400:c=0,e=63,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2557291133,tim=1432812301982688
FETCH#139736387538400:c=0,e=148,p=0,cr=4,cu=0,mis=0,r=1,dep=0,og=1,plh=2557291133,tim=1432812301983421
STAT #139736387538400 id=1 cnt=1 pid=0pos=1 obj=86894 op='TABLE ACCESS BY INDEX ROWID T2 (cr=4 pr=0 pw=0 time=103 uscost=2 size=20 card=1)'
STAT #139736387538400 id=2 cnt=1 pid=1pos=1 obj=86895 op='INDEX UNIQUE SCAN SYS_C0015868 (cr=3 pr=0 pw=0 time=90 uscost=1 size=0 card=1)'
FETCH#139736387538400:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=2557291133,tim=1432812301985923
T3表
*** 2015-05-28 22:35:58.622
CLOSE#139736387539552:c=0,e=48,dep=0,type=0,tim=1432823758622512
=====================
PARSING IN CURSOR #139736387539552 len=29dep=0 uid=0 oct=3 lid=0 tim=1432823758640669 hv=1848951736 ad='b75ea238' sqlid='90duryjr39hxs'
select * from t3 where id=100
END OF STMT
PARSE#139736387539552:c=9998,e=17914,p=0,cr=24,cu=0,mis=1,r=0,dep=0,og=1,plh=4161002650,tim=1432823758640667
EXEC#139736387539552:c=0,e=33,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=1432823758644143
FETCH#139736387539552:c=0,e=61,p=0,cr=4,cu=0,mis=0,r=1,dep=0,og=1,plh=4161002650,tim=1432823758645007
FETCH#139736387539552:c=1000,e=400,p=0,cr=20,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=1432823758646917
STAT #139736387539552 id=1 cnt=1 pid=0pos=1 obj=86999 op='TABLE ACCESS FULL T3 (cr=24 pr=0 pw=0 time=44 us cost=7size=20 card=1)'
FETCH 表示当前的动作是在抽取数据，e 表示数据抽取动作消耗的时间，cr 表示一致性 读取的次数（consistent reads），我们同样可以清楚地看到前二者在资源消耗上相差无几，但是T3与前两者相差很多。
总结：通过上面实验说明以下几个结论：
1、无论是10000条还是1000000条数据（因为测试环境性能问题，所以没法用太高的数据量），只要数据重复度非常低，那么搜索效率非常高，基本不受数据量的影响。
2、但是对于重复度非常度的表，oracle自动选择全盘扫描，而强制让oracle使用索引效率更差。所以在重复度较高的表不建议使用B-TREE索引
评论暂时关闭