ACCESSORACLE B-TREE(B树)索引

内容简介:

1.普通B-TREE 索引;

2.唯一B-TREE 索引;

三.复合索引;

ORACLE
默许的索引类型为B-TREE 索引,表中的行标识符(ROWID)和行相关的列值被贮存在3个平衡树的树状结构的索引块中;使用B-TREE索引有以下多少个原因:

▢ 升高SQL语句的特性;

▢ 强制执行主键和唯1键约束的唯壹性;

▢ 收缩通过主键和外键约束关联的父表和子表间暧昧的锁定问题;

1.普通B-TREE 索引

在一张未建立任何索引的500万行人士新闻表中依照人口ID查询职员消息

select
id,name,gender,homeaddr from th01 where id=998698;


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


| 0 | SELECT
STATEMENT | | 1 | 38 | 16715 (1)| 00:03:21 |

|* 1 | TABLE ACCESS FULL| TH01 | 1 | 38 | 16715
(1)| 00:03:21 |


Predicate Information
(identified by operation id):


1 –
filter(“ID”=998698)

Statistics


61222 consistent gets

61208 physical reads

通过观看执行陈设,CBO优化器执行了全表扫描,壹致读取612二十多个块,61207个大体读,基于品质的驰念和表结构的分析,为其B-TREE索引:

SQL> CREATE INDEX
IND_TH01_ID ON TH01(ID) TABLESPACE TBS02;

Index created.

Elapsed:
00:00:33.03

SQL> execute
dbms_stats.gather_table_stats(‘sywu’,’th01′,cascade=>true);

PL/SQL procedure
successfully completed.

Elapsed: 00:00:04.17

SQL>
@/oracle/getind

TABLE_NAME    
INDEX_NAME    COLUMN_NAME    SIZE_GB    INDEX_TY   STATUS    LOGGING
   DEGREE    NUM_ROWS    DISTINCT_KEYS





TH01                
   IND_TH01_ID          ID                    .091796875       NORMAL
VALID             YES               1            DISABLED        5000000
  5000000

通晓对于高基数的列创造B-TREE索引是明智之选,对表展开辨析后重新询问:

SQL>select
id,name,gender,homeaddr from th01 where id=998698;

Elapsed: 00:00:00.00


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


| 0 | SELECT
STATEMENT | | 1 | 38 | 4 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY
INDEX ROWID| TH01 | 1 | 38 | 4 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IND_TH01_ID | 1 | |
3 (0)| 00:00:01 |


Predicate Information
(identified by operation id):


2 –
access(“ID”=998698)

Statistics


5 consistent gets

0 physical reads

依据开销的设想,CBO优化器选拔了通过索引的方法读取数据,1致读取陆个块,有效削减了额外的大体读;做个基于索引列的总括查询:

SQL> select
count(id) from th01;

Elapsed: 00:00:00.15

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


| 0 | SELECT
STATEMENT | | 1 | 6 | 3170 (1)| 00:00:39 |

| 1 | SORT AGGREGATE
| | 1 | 6 | | |

| 2 | INDEX FAST FULL SCAN| IND_TH01_ID | 5000K| 28M| 3170 (1)|
00:00:39 |


Statistics


11810 consistent gets

11794 physical reads

CBO
优化器选拔了全索引围观,依旧消耗额外的财富;但当总计列产生转移时:

SQL> select count(*)
from th01;

Elapsed: 00:00:00.14


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


| 0 | SELECT STATEMENT | |
1 | 16707 (1)| 00:03:21 |

| 1 | SORT AGGREGATE | | 1
| | |

| 2 | TABLE ACCESS FULL| TH01 | 5000K| 16707
(1)| 00:03:21 |

Statistics


61221 consistent gets

61208 physical reads

那时CBO
优化器选取了全表扫描,并消耗越来越多的财富;

2.唯一B-TREE 索引:

在为表创设(主键、唯一约束)时,ORACLE
会暗许创立三个B-TREE索引,那样既保险了数量的唯壹性也压实了数码的追寻功能:

SQL> alter table
th01 add constraints cs_th01_uq unique(idcard);

Table altered.

Elapsed:
00:00:56.11

TABLE_NAME     
INDEX_NAME     COLUMN_NAME     SIZE_GB    INDEX_TY      STATUS    
LOGGING        DEGREE    COMPRESS    NUM_ROWS    DISTINCT_KEYS





TH01            
CS_TH01_UQ                  IDCARD                .15625              
NORMAL   VALID              YES                  1              DISABLED
   4969898           4969898

以IDCALANDD查询职员音讯:


ACCESS,| 0 | SELECT
STATEMENT | | 1 | 35 | 3 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY
INDEX ROWID| TH01 | 1 | 35 | 3 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | CS_TH01_UQ | 1 | | 2 (0)| 00:00:01 |


Predicate Information
(identified by operation id):


2 –
access(“IDCARD”=’562456864646565545′)

Statistics


3 consistent gets

0 physical reads

做为默许创立的目录,它照旧能便捷的工作,但ORACLE是不容许将其当作独立的目录删除的,只可以通过删除约束的情势删除;对于主键,它的事态要复杂些,因为还要思量外键的约束;基于那种艺术开创的目录,当自律被删去时还要再一次创立索引,显明在一张大表上海消防费的代价和岁月是昂贵的;
so,选取如下的措施客观的确立约束和目录:

SQL> alter table
th01 add constraints CS_TH01_UQ unique(idcard)

2* using index tablespace tbs03 ;

Table altered.

Elapsed:
00:00:59.27

借使有一天事情发生了改变,唯一约束已经不是必须的,但索引是必须的,那只要求删除约束保留索引:

SQL> alter table
th01 drop constraints CS_TH01_UQ keep index;

Table altered.

Elapsed: 00:00:00.01

再也经过IDCALANDD
查询人士音信:

SQL> select
id,name,idcard from th01 where idcard=’56234256878945′;


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


| 0 | SELECT
STATEMENT | | 1 | 35 | 3 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY
INDEX ROWID| TH01 | 1 | 35 | 3 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | CS_TH01_UQ | 1 | | 2 (0)| 00:00:01 |


Predicate Information
(identified by operation id):


2 –
access(“IDCARD”=’56234256878945′)

Statistics


1 recursive calls

0 db block gets

3 consistent gets

只开创唯一索引:

SQL> create unique index ind_th02 on
th02(idcard) tablespace tbs03;

唯一索引与唯1约束比较,唯一索引只创立索引而不添加约束,它保证索引列数值唯壹性,允许有空值;

叁.复合索引:

可以在八个列上创设索引,其结果叫做复合索引或组合索引:

SQL> create index
ind_th01_union on th01(id,name,idcard) tablespace tbs03;

当查问的WHERE子句引用了目录的全体列可能只是前导列,CBO会使用复合索引

SQL>select
id,name,idcard from th01 where idcard=’9876534655635666′ and id=68956254
and name=’张三’;


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


| 0 | SELECT STATEMENT | |
1 | 35 | 3 (0)| 00:00:01 |

|* 1 | INDEX RANGE SCAN| IND_TH01_UNION | 1 | 35 | 3 (0)|
00:00:01 |


Predicate Information
(identified by operation id):


1 – access(“ID”=68956254
AND “NAME”=’张三’ AND

“IDCARD”=’9876534655635666′)

Statistics


1 recursive calls

0 db block gets

3 consistent gets

2 physical reads

透过查询结果,对于在此以前创造的单列索引(IND_TH01_ID、CS_TH01_UQ),优化器已不再选拔;对于复合索引( IND_TH01_UNION) 来说, id | id, name| id,name,idcard
多少个组西雅图被认为是前导列,就算自身只是在WHERE 子句中援引了第3个着力列ID,那么优化器照旧会选拔复合索引(IND_TH01_UNION )忽略单列索引(
IND_TH01_ID)

SQL> select * from th01
where id=698698;


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


| 0 | SELECT STATEMENT | |
1 | 77 | 4 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX
ROWID| TH01 | 1 | 77 | 4 (0)| 00:00:01 |

|* 2 |
INDEX RANGE SCAN | IND_TH01_UNION | 1 | | 3 (0)| 00:00:01 |


Predicate Information
(identified by operation id):


2 – access(“ID”=698698)

Statistics


0 db block gets

5 consistent gets

 

如果where 子句中的条件不相符复合索引前导列的渴求,那么优化器会忽略复合索引(
IND_TH01_UNION)选择单列索引(CS_TH01_UQ):

SQL> select * from th01
where idcard=’5623546566564665′;


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


| 0 | SELECT STATEMENT | |
1 | 77 | 3 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX
ROWID| TH01 | 1 | 77 | 3 (0)| 00:00:01 |

|* 2 |
INDEX UNIQUE SCAN | CS_TH01_UQ | 1 | | 2 (0)| 00:00:01 |


Predicate Information
(identified by operation id):


2 –
access(“IDCARD”=’5623546566564665′)

Statistics


1 recursive calls

0 db block gets

3 consistent gets

1 physical reads

对于此时的询问条件 where
idcard=’56235465665646六伍’ 已经不复符合( id | id, name| id,name,idcard) 复合索引前导列的尺度,优化器选用单列索引( CS_TH01_UQ);又比方,作者的WHERE 子句条件相符复合索引前导列需要但不是任何满足:

SQL> select * from th01
where id=698698 and name=’张三’;


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


| 0 | SELECT STATEMENT | |
1 | 77 | 4 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX
ROWID| TH01 | 1 | 77 | 4 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IND_TH01_UNION | 1 | | 3 (0)| 00:00:01 |


Predicate Information
(identified by operation id):


2 – access(“ID”=698698 AND
“NAME”=’张三’)

Statistics


3 consistent gets

0 physical
reads

通过分析(where id=69869八 and name=’张3′ )符合复合索引前导列须要,优化器选拔复合索引( IND_TH01_UNION) 忽略单列索引( IND_TH01_ID);

开创复合索引时,排序是个一点都不小的题材,ORACLE
提议将最频仍造访的列放在目录中最靠前的职务,应防止选用低基数的列作为复合索引的引导列.

 

相关文章