ORACLE位图索引

内容简介:

1.位图索引

一.一位图索引使用注意事项;

1.2使用位图索引;

一.叁位图索引对DML操作的熏陶;

二.位图连接索引

二.一鲜明必要后使用位图索引;

二.一开立位图连接索引的注意事项:

 

一.位图索引:

一.一位图索引使用注意事项:

❏ 一般适用于低基数列;

❏ 适合数据仓库;

❏ 对于启用位图索引的表,应尽量收缩或防止DML操作;

❏ 倘使对一张带有多列位图索引的表展开大气DML操作,应考虑将位图索引删除,DML操作甘休后重建位图索引;

❏ 不适用于频仍持续产生DML操作的OLTP系统,会出现在锁定,阻碍立异性能;

一.2使用位图索引

位图索引与B-TREE索引有极大的例外,贰个位图索引由多少个位串组成,各类位串都意味基础列中多少个单独的有效值;种种位串是打开或关闭,表示该值是或不是用于某一行;以人口新闻表th0三 为例,性别(gender)字段的值(男、女、未记录),假设为其成立位图索引,那么每一种位串(男、女、未记录)中的单个位表示二个给定行的值是男、女依然未记录;当判断某壹列是或不是适合创设位图索引时,要求思考是或不是切合”低基数列”,依照应用程序、数据整合以及数据库中的表的事态各异,是或不是成立位图索引的结论也大概两样;日常用来判断的一条基湖南药物志验法则是:”如若该列的有效值数目不足表中央银行数的1%,那么它就符合成立位图索引,以th03来说,表行数为:500万行,而性别列的有效值数目仅为二个(
男、女、未记录),能够规定它适合成立位图索引:

人口消息表(th0三)

ID

NAME

GENDER

IDCARD

HOMEADDR

JOBNO

BIRTHDATE

1

998698

李天

440623197007253619

水晶洞1

526456

25-JUL-70

2

998699

李花

510802197007251223

水晶洞2

5785452

25-JUL-70

3

584625

李某

未记录

564551545265642155

水晶洞3

1565452

01-JUL-88

SQL> create bitmap
index ind_th03_gender on th03(gender) tablespace tbs03;

Index created.

Elapsed: 00:00:01.05

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

PL/SQL procedure
successfully completed.

Elapsed: 00:00:04.31

SQL>
@/oracle/getind

TABLE_NAME  INDEX_NAME
 COLUMN_NAME   SIZE_GB  INDEX_TY  STATUS  NUM_ROWS  DISTINCT_KEYS




TH03                
IND_TH03_GENDER   GENDER   .001953125
 
BITMAP    VALID     453    3

位图索引的创导充足快并且占用的半空中也万分小;位图索引和B-TREE索引存款和储蓄值的秘籍差异,它存款和储蓄表中的每一行值(包含空值),对于B-TREE索引,单列索引不存款和储蓄空值,复合索引只要有三个非空值就能够储存;所以当执行 IS NULL
或许 IS NOT NULL
查询时位图索引的频率要高于B-TREE索引:

SQL> select count(*)
from th03 where gender is null;

Elapsed:
00:00:00.03


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


| 0 | SELECT STATEMENT | |
1 | 5 | 1 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1
| 5 | | |

| 2 | BITMAP CONVERSION
COUNT | | 1 | 5 | 1 (0)| 00:00:01 |

|* 3 | BITMAP INDEX SINGLE VALUE| IND_TH03_GENDER | | | | |


Predicate Information
(identified by operation id):


3 – access(“GENDER” IS
NULL)

Statistics


1 recursive calls

0 db block gets

2 consistent gets

1 physical
reads

一经在相同表相同列建立B-TREE索引,则该实施必须全表扫描:

SQL> select count(*)
from th03 where gender is null;

Elapsed:
00:00:00.16


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


| 0 | SELECT STATEMENT | |
1 | 5 | 14908 (1)| 00:02:59 |

| 1 | SORT AGGREGATE | | 1
| 5 | | |

|* 2 | TABLE ACCESS FULL| TH03 | 1 | 5 | 14908 (1)| 00:02:59 |


Predicate Information
(identified by operation id):


2 – filter(“GENDER” IS
NULL)

Statistics


1 recursive calls

0 db block gets

54574 consistent gets

54562 physical
reads

位图索引还是可以在此外①些场地采用,如采纳聚合函数:

SQL> select
count(*),count(gender) from th03;

COUNT(*) COUNT(GENDER)


5000000 5000000

Elapsed:
00:00:00.11


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


| 0 | SELECT STATEMENT | |
1 | 5 | 203 (0)| 00:00:03 |

| 1 | SORT AGGREGATE | | 1
| 5 | | |

| 2 | BITMAP CONVERSION TO
ROWIDS | | 5000K| 23M| 203 (0)| 00:00:03 |

| 3 | BITMAP INDEX FAST FULL SCAN| IND_TH03_GENDER | | | | |


Statistics


0 recursive calls

0 db block gets

232 consistent gets

0 physical reads

假定你控制在应用程序中落到实处位图索引,应时不时的检查建立了位图索引的列的数码整合,那点很要紧,如若您算错了蕴藏位图索引的其余列的基数,大概会对应用程序造成负面影响,如(位图索引的蕴藏空间会追加、查询质量会回落、重建索引的年月将大增);

1.3人图索引对DML操作的熏陶:

为了有利于测试,小编创造另一张分区表 th0肆,并为th0四的性别、出出生之日期列创立位图索引,最后向它插入十0万行数据:

SQL> create table th04

partition by range(id)(

partition th04_part1
values less than(1000000) tablespace tbs03,

partition th04_part2
values less than(2000000) tablespace tbs03,

partition th04_part3
values less than(3000000) tablespace tbs03,

partition th04_part4
values less than(4000000) tablespace tbs03,

partition th04_part5
values less than(5000000) tablespace tbs03,

partition th04_part6
values less than(maxvalue) tablespace tbs03

) as select * from tbbase
where 1=0 ;

Table created.

Elapsed: 00:00:00.04

SQL> create bitmap
index ind_th04_gender on th04(gender) local tablespace tbs03;

Index created.

Elapsed:
00:00:00.04

SQL> create bitmap
index ind_th04_birthdate on th04(birthdate) local tablespace tbs03;

Index created.

Elapsed:
00:00:00.09

SQL>insert into th04
select * from tbbase where rownum<1000001;

1000000 rows created.

Elapsed:
00:00:40.24

从结果能够看出,插入拾0万行的数额消费40秒,从外表上看花费的光阴就像是创建的,但当数据量不断追加时,越发对于贰个数据仓库环境,1天处理几百万居然亿行数据是数见不鲜的,合理的情形应考虑删除位图索引(倘诺是分区表则将对象分区标记为不可用),执行完DML加载操作后重建位图索引;

SQL> alter index
IND_TH04_GENDER unusable;

Index altered.

Elapsed:
00:00:00.18

SQL> alter index
IND_TH04_BIRTHDATE unusable;

Index altered.

Elapsed:
00:00:00.41

SQL> insert into th04
select * from tbbase where rownum<1000001;

1000000 rows created.

Elapsed:
00:00:23.74

禁止使用位图索引后,插入十0万行数据只开销二三 秒,对于大数目而言那恐怕能够抓牢装载数据的习性;数据装载达成后重建位图索引:

SQL> alter index
ind_th04_gender rebuild partition TH04_PART1;

alter index
ind_th04_gender rebuild partition TH04_PART2;

alter index
ind_th04_gender rebuild partition TH04_PART3;

alter index
ind_th04_gender rebuild partition TH04_PART4;

alter index
ind_th04_gender rebuild partition TH04_PART5;

alter index
ind_th04_gender rebuild partition TH04_PART6;

alter index
ind_th04_birthdate rebuild partition
th04_part1;

………

重建分区索引也得以因而上边包车型大巴命令重建:

SQL> alter table th04
modify partition th04_part1 rebuild unusable local indexes;

Table altered.

Elapsed:
00:00:05.81

此命令尽管简易,但它也有不足之处,对于一个点名的分区,它只好按梯次执行;而对此每贰个指令发出的命令重建分区,它可以而且推行三个语句,完毕相互之间重建索引;

除装载数据的影响外,位图索引也会影响多少的DML操作,请旁观下边包车型客车职员消息表数据:

人士消息表(th0四)

ID

NAME

GENDER

IDCARD

HOMEADDR

JOBNO

BIRTHDATE

1

998698

李天

440623197007253619

水晶洞1

526456

25-JUL-70

2

998699

李四

510802197007251223

水晶洞2

5785452

25-JUL-70

…..

….

多少呈现他们的出生日期是平等的,并且出出生之日期列(BI中华VTHDATE)还建立了位图索引( IND_TH04_BI本田CR-VTHDATE)
,因工作错误记录五个人的出生日期,so,今后对其修改:

—session
1—–

SQL> select distinct
sid from v$mystat;

SID


191

SQL> update th04 set
birthdate=’26-JUL-70′ where idcard=’440623197007253619′;

1 row updated.

Elapsed: 00:00:00.11

那儿session 1的用户因为工作繁忙未有立刻送交,那时另多个业务员在新的对话 session第22中学期维修该另2个不当记录:

—-session 2

SQL> select distinct
sid from v$mystat;

SID


194

SQL> update th04 set
birthdate=’27-JUL-70′ where
idcard=’510802197007251223′;

session 2中的用户会直接处于等候状态,因为她们修改的谬误人士出出生之日期在更新以前在同二个位图索引位串中,当修改位串中的位信息时位串会被锁定,直到更新提交后更新位串中的位值;观望此时的锁状态:

SQL>select
sid,type,id1,id2,lmode,request,ctime,block from v$lock where sid
in(191,194) order by sid

SID TY ID1 ID2 LMODE
REQUEST CTIME BLOCK



191 AE 100 0 4 0 2052 0

191 TM 75667 0 3 0 263 0

191 TX 65562 848 6 0 263 1

191 TM 75668 0 3 0 263 0

191 TO 65927 1 3 0 286 0

194 TM 75668 0 3 0 238 0

194 TX 131081 1071 6 0 238
0

194 TM 75667 0 3 0 238 0

194 TX 65562 848 0 4 238 0

194 AE 100 0 4 0 1406 0

对此session
一(1九壹)此时具备四个六级事务锁,并且杜绝session
2(1玖肆),它们呼吁的能源是同样的,那毫不巧合;唯有当session
一(1九一)提交或回退后,这些6级事物锁才会被放出,session
2(1玖4)才能有所锁修改数据;

SID TY ID1 ID2 LMODE
REQUEST CTIME BLOCK



191 AE 100 0 4 0 2832 0

191 TO 65927 1 3 0 1066 0

194 TX 131081 1071 6 0 1018 0

194 TM 75667 0 3 0 1018 0

194 TM 75668 0 3 0 1018 0

194 AE 100 0 4 0 2186 0

故而建立位图索引时,应密切分析表结构和表数据,作出明智、合理选取;以上测试因条件、版本、数据库状态测试结果可能分化;

二.位图连接索引

二.一明显要求后使用位图索引

在创立位图连接索引时,它是多个表或多个表之间的索引值的连接,连接的结果存款和储蓄在目录本人中;通太早先时期做连接并储存结果,当查问时经过扫描索引(防止两表或多表全表扫描)来获取数据,当然对于树立那样的目录,建立前供给必须旗帜显然;
请旁观如下职员新闻表(th0四)和人口单位关系信息表(tbbsj)

人士消息表(th0四)

ID

NAME

GENDER

IDCARD

HOMEADDR

JOBNO

BIRTHDATE

1

789524

张三

429005198911261805

水晶洞1

1300440

05-AUG-79

2

564895

李四

429005198911296178

水晶洞2

1000209

14-AUG-79

…..

….

职员单位涉及新闻表(tbbsj)

ID(单位ID)

IDCARD(人员身份证)

DNAME(单位名称)

JOINDATE(进入单位时间)

SRZW(所任职位)

GZZT(工作状态)

TSGX(特殊贡献)

1

785652

429005198911261805

太空建筑有限公司

2013-12-22 21:51:33

总经理助理

暂无

2

5689556

429005198911296178

飞轮实业有限公司

2013-12-22 21:51:42

总经理小秘

暂无

..

…..

已知人士消息数据量为:拾0万,人士单位关系音信数据量为:110664二,在确立普通索引的情况下取得人士IDCA路虎极光D为:429005一玖八七1126180五的人手单位音讯:

SQL> select t1.*,t2.*
from th04 t1,tbbsj t2

2 where
t1.idcard=t2.idcard and
t1.idcard=’429005198911261805′;


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


| 0 | SELECT STATEMENT | |
1 | 253 | 10959 (1)| 00:02:12 | | |

|* 1 | HASH JOIN | | 1 |
253 | 10959 (1)| 00:02:12 | | |

| 2 | PARTITION RANGE ALL|
| 1 | 65 | 2661 (1)| 00:00:32 | 1 | 6 |

|* 3 | TABLE ACCESS FULL | TH04 | 1 | 65 | 2661 (1)| 00:00:32 | 1 |
6 |

|* 4 | TABLE ACCESS FULL | TBBSJ | 1 | 188 | 8297 (1)| 00:01:40 | |
|


Predicate Information
(identified by operation id):


1 –
access(“T1″.”IDCARD”=”T2″.”IDCARD”)

3 –
filter(“T1”.”IDCARD”=’429005198911261805′)

4 –
filter(“T2”.”IDCARD”=’429005198911261805′)

Statistics


1 recursive calls

0 db block gets

40165 consistent gets

40137 physical reads

分析结果获悉为获得IDCASportageD为:
429005198九11261805的职员单位新闻,两张大表都做了全表扫描;因为获得人员单位关系新闻供给已经鲜明,为其创制位图连接索引:

SQL> create bitmap
index ind_th04uniontbbsj on th04(t1.idcard)

2 from th04 t1,tbbsj t2

3 where
t1.idcard=t2.idcard

4 tablespace tbs03

5* local

SQL> /

Index created.

Elapsed: 00:00:01.65

制造位图连接索引后再一次询问:

SQL> select t1.*,t2.*
from th04 t1,tbbsj t2

2* where
t1.idcard=t2.idcard and
t1.idcard=’429005198911261805′;


ACCESS,| Id | Operation | Name |
Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |


| 0 | SELECT STATEMENT | |
1 | 253 | 5 (0)| 00:00:01 | | |

| 1 | NESTED LOOPS | | 1 |
253 | 5 (0)| 00:00:01 | | |

| 2 | TABLE ACCESS BY
INDEX ROWID | TBBSJ | 1 | 188 | 3 (0)| 00:00:01 | | |

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

| 4 | TABLE ACCESS BY
GLOBAL INDEX ROWID| TH04 | 1 | 65 | 2 (0)| 00:00:01 | ROWID | ROWID |

|* 5 | INDEX UNIQUE SCAN | CS_IDCARD | 1 | | 1 (0)| 00:00:01 | | |


Predicate Information
(identified by operation id):


3 –
access(“T2”.”IDCARD”=’429005198911261805′)

5 –
access(“T1”.”IDCARD”=’429005198911261805′)

Statistics


0 recursive calls

0 db block gets

3 consistent gets

0 physical reads

对于位图连接索引,它将主导表和关联表中的ROWID以及大旨列的列值存款和储蓄在目录中,查询数据时通过扫描索引升高查询效用:

人员信息表(TH04)ROWID

人员单位关系信息表(tbbsj)ROWID

IDCARD

AAASdEAAGAAB6w6AAY

AAASfXAAIAAAmCQAAE

440621197102274116

AAASdEAAGAAB6z5AAf

AAASfXAAIAAAmCGAAX

440621197311244423

AAASdEAAGAAB6zCAAl

AAASfXAAIAAAmCPAAW

440621197405202427

二.2创制位图连接索引的注意事项:

二.2.1创办位图连接索引时WHERE
子句中的关联条件列必须是主键或唯1约束(不符合条件会报错ORA-2595四: missing primary
key or unique constraint on dimension);

二.2.二在内定索引列时,假诺两表都怀有相同列,必须采纳表名.列名的法子钦赐可能别称.列名的办法钦命(不符合条件会报错:ORA-0091捌: column
ambiguously defined);

二.贰.三创制位图索引同样DML操作效能差,建立前请密切分析表结构和数目DML操作率;

二.2.四更新表数据时同样会有锁定主导表、关联表难题;

相关文章