ACCESSORACLE位图索引

内容简介:

1.个图索引

1.1个图索引使用注意事项;

1.2
使用各图索引;

1.3
号图索引对DML操作的震慑;

2.各类图连接索引

2.1
明确要求后采取各图索引;

2.1创办各项图连接索引的注意事项:

 

1.员图索引:

1.1员图索引使用注意事项:

❏ 一般适用于低基数列;

❏ 适合数据仓库;

❏ 对于启用位图索引的表,应尽量压缩或避免DML操作;

❏ 如果对平摆放带有多列位图索引的说明展开大气DML操作,应考虑将各项图索引删除,DML操作了晚重建各项图索引;

❏ 不适用于数持续发出DML操作的OLTP系统,会出现在锁定,阻碍创新性能;

1.2
使用各图索引

个图索引与B-TREE索引发生那个怪之两样,一个个图索引由多单各类串组成,每个位串都表示基础列被一个单身的有效值;每个位串是打开或关闭,表示该值是否用于某同尽;以食指信息表th03 为例,性别(gender)字段的值(男、女、未记录),假如为那创立各项图索引,那么每个位串(男、女、未记录)中之单个位表示一个被定行的价值是男性、女还是免记录;当判断有同列是否切合创建各项图索引时,需要考虑是不是合乎”低基数列”,根据应用程序、数据整合及数据库中之说明的景象不同,是否创造各项图索引的定论也可能两样;通常用来判断的一致长主干经验法则是:”如果该列的有效值数目不足表中行数之1%,那么她便合创建各项图索引,以th03来说,表行数也:500万执,而性列的有效值数目只有为3单(
男、女、未记录),可以规定其抱创建各项图索引:

口信息表(th03)

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操作的震慑:

为了方便测试,我创建另一样摆分区表 th04,并为th04的性别、出生日期列创建各项图索引,最后为她插入100万实行数据:

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

于结果可以见到,插入100万实施之数码消费40秒,从外表上看花费的日如是情理之中之,但当数据量不断长时,特别对于一个数据仓库环境,一天处理几百万竟然亿行数据是普通的,合理的状况应考虑去位图索引(如果是分区表则将对象分区标记为未可用),执行完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

受用各类图索引后,插入100万执数据只有费23 秒,对于那个数量而言即或可以增长装载数据的性能;数据装载完毕晚重建各项图索引:

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操作,请观察下的人口信息表数据:

人口信息表(th04)

ID

NAME

GENDER

IDCARD

HOMEADDR

JOBNO

BIRTHDATE

1

998698

李天

440623197007253619

水晶洞1

526456

25-JUL-70

2

998699

李四

510802197007251223

水晶洞2

5785452

25-JUL-70

…..

….

数据展示他们之出生日期是一模一样的,并且出生日期列(BIRTHDATE)还建了个图索引( IND_TH04_BIRTHDATE)
,因工作错误记录点滴口之出生日期,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
的用户因作业繁忙没有当即送交,这时别一个业务员以初的对话 session2遭受修该其他一个破绽百出记录:

—-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(191)此时有着一个6级事务锁,并且杜绝session
2(194),它们呼吁的资源是千篇一律的,这决不巧合;只有当session
1(191)提交或掉退后,这个6级事物锁才会给释放,session
2(194)才会拥有锁修改数据;

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

从而成立各类图索引时,应仔细分析说明结构以及表数据,作出明智、合理选择;以上测试为条件、版本、数据库状态测试结果也许两样;

2.个图连接索引

2.1
明确要求后以各类图索引

在开创各项图连接索引时,它是鲜只说明要多个表之间的索引值的连年,连接的结果存储于目自身面临;通过前期开连接并储存结果,当查问时通过扫描索引(避免简单申明或多表全表扫描)来获取数据,当然对建这样的目,建立前需要要明白;
请观察如下人员信息表(th04)和人口单位干信息表(tbbsj)

口信息表(th04)

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

总经理小秘

暂无

..

…..

既领略人员信息数据量为:100万,人员单位关系消息数据量为:1106642,在建立日常索引的情况下得到人员IDCARD为:429005198911261805
的人口单位信息:

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

解析结果获悉为博得IDCARD为:
429005198911261805之人手单位信息,两摆设大表都开了全表扫描;因为落人员单位关系消息需求都明显,为夫创立各项图连接索引:

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′;


| 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创各项图连接索引的注意事项:

2.2.1创立各项图连接索引时WHERE
子句被之关联条件列必须是主键或唯一约束(不符合条件会报错ORA-25954: missing primary
key or unique constraint on dimension);

2.2.2每当指定索引列时,如果少阐明还持有相同列,必须利用表名.列名之法子指定要变更名.列名的办法指定(不符合条件会报错:ORA-00918: column
ambiguously defined);

2.2.3
创建各项图索引同样DML操作效率差,建立前要仔细分析说明结构及数码DML操作率;

2.2.4
更新表数据时同样会有锁定主导表、关联表问题;

相关文章