CUUG笔记 ORACLE索引学习笔记

最新推荐文章于 2024-11-03 09:30:00 发布

cuccntzd50572

最新推荐文章于 2024-11-03 09:30:00 发布

阅读量87

点赞数

文章标签：数据库 php

http://bbs.cuug.com/forum.php?mod=viewthread&tid=1334&extra=page%3D1

索引：
每个表都生成伪列，ROWID-每行
ROWID包括对象号数据文件号块号

数据对象编号文件编号块编号行编号
OOOOOO FFF BBBBBB RRR

我们可以看出，从上面的rowid可以得知：
AAABnl 是数据对象编号
AAF是相关文件编号
AAAAAP是块编号
AAA 是行编号

怎么依据这些编号得到具体的十进制的编码值呢，这是经常遇到的问题。这里需要明白rowid的是基于64位编码的18个字符显示（数据对象编号(6) +文件编号(3) +块编号(6)+ 行编号(3)=18位），其中
A-Z <==> 0 - 25 (26)
a-z <==> 26 - 51 (26)
0-9 <==> 52 - 61 (10)
+/ <==> 62 - 63 (2)

共64位，明白这个后，就可以计算出10进制的编码值，计算公式如下：
d * (b ^ p)
其中：b就是基数，这里就是64，p就是从右到左，已0开始的位置数
比如：上面的例子
文件号AAF,具体的计算应该是:
5*(64^0)=5；
0*(64^1)=0；
0*(64^2)=0；
文件号就是0+0+5=5

最简单的基于rowid的显示方式得到的响应的64位编码对应值的sql：
select rowid ,
substr(rowid,1,6) "OBJECT",
substr(rowid,7,3) "FILE",
substr(rowid,10,6) "BLOCK",
substr(rowid,16,3) "ROW"
from TableName;

OWID OBJECT FILE BLOCK ROW
------------------ ------------ ------ ------------ ------
AAABc4AADAAAGLUAAA AAABc4 AAD AAAGLU AAA
AAABc4AADAAAGLUAAB AAABc4 AAD AAAGLU AAB
AAABc4AADAAAGLUAAC AAABc4 AAD AAAGLU AAC
AAABc4AADAAAGLUAAD AAABc4 AAD AAAGLU AAD
AAABc4AADAAAGLUAAE AAABc4 AAD AAAGLU AAE

通过dbms_rowid这个包，可以直接的得到具体的rowid包含的信息：
select dbms_rowid.rowid_object(rowid) object_id, dbms_rowid.rowid_relative_fno(rowid) file_id,
dbms_rowid.rowid_block_number(rowid) block_id ,dbms_rowid.rowid_row_number(rowid) num from bruce_t where rownum<5;

OBJECT_ID FILE_ID BLOCK_ID NUM
---------- ---------- ---------- ----------
5944 3 25300 0
5944 3 25300 1
5944 3 25300 2
5944 3 25300 3

另外，就是自己写的一些函数：（下面的函数是网友eygle提供）

create or replace function get_rowid
(l_rowid in varchar2)
return varchar2
is
ls_my_rowid varchar2(200);
rowid_type number;
object_number number;
relative_fno number;
block_number number;
row_number number;
begin
dbms_rowid.rowid_info(l_rowid,rowid_type,object_number,relative_fno, block_number, row_number);
ls_my_rowid := 'Object# is :'||to_char(object_number)||chr(10)||
'Relative_fno is :'||to_char(relative_fno)||chr(10)||
'Block number is :'||to_char(block_number)||chr(10)||
'Row number is :'||to_char(row_number);
return ls_my_rowid ;
end;
/

应用上面的函数如下：
SQL> select get_rowid(rowid), name from bruce_t;
GET_ROWID(ROWID) NAME

-------------------------------------------------------------------------------- --------------------------------
Object# is :5944 BruceLau
Relative_fno is :3
Block number is :25300
Row number is :0

Object# is :5944 MabelTang
Relative_fno is :3
Block number is :25300
Row number is :1

定期维护索引，减少高度，减少性能损耗
维护索引的时候是先delete值然后insert

如果表的数据经常update，就会导致索引被删除后空间没有被释放，需要重建索引，必要利用nologging

1. recreate index
2. alter index skate.idx_test rebuild nologging parallel 4 online ;
3. 如果叶块是half-empty or near empty，可以运行“alter index coalesce”来合并

默认是B TREE索引，可以压缩，什么时候压缩，值重复的多的时候
默认是不压缩，压缩之后较少索引块的空间，维护高度和性能较好

而位图索引主要针对大量相同值的列而创建(例如：类别，操作员，部门ID,库房ID等),
索引块的一个索引行中存储键值和起止Rowid,以及这些键值的位置编码,
位置信息就bitmap
? 在重复值很多列使用
? 谓语很多的查询语句
? 使用最少的存储空间
? 在只读系统中使用最适合
? 适合大表

SQL> create BITMAP INDEX departments_idx
2 on departments(manager_id)
3 storage (initial 200k next 200k
4 pctincrease 0 maxextents 50)
5* tablespace indx;

B-Tree索引和位图索引的比较
B-Tree indexes Bitmap indexes
Suitable for high-cardinality Suitable for low-cardinality
columns columns
Updates on keys relatively Updates to key columns very
inexpensive expensive
Inefficient for queries using Efficient for queries using
AND / OR predicates AND / OR predicates
Row-level locking Bitmap segment-level locking
More storage Less storage
Useful for OLTP Useful for DSS

反向索引
当载入一些有序数据时，索引肯定会碰到与I/O相关的一些瓶颈。在数据载入期间，某部分索引和磁盘肯定会比其他部分使用频繁得多。为了解决这个问题，可以把索引表空间存放在能够把文件物理分割在多个磁盘上的磁盘体系结构上。
为了解决这个问题，Oracle还提供了一种反转键索引的方法。如果数据以反转键索引存储，这些数据的值就会与原先存储的数值相反。这样，数据1234、1235和1236就被存储成4321、5321和6321。结果就是索引会为每次新插入的行更新不同的索引块。
不能对位图索引和索引组织表进行反转键处理。
技巧：
如果您的磁盘容量有限，同时还要执行大量的有序载入,必须是有序列存放的列，就可以使用反转键索引。
不可以将反转键索引与位图索引或索引组织表结合使用。

SQL> create unique index i1_t1 ON t1(c1)
2 REVERSE pctfree 30
3 storage(initial 200k next 200k
4 pctincrease 0 maxextents 50)
5 tablespace indx;
SQL> create unique index i2_t1 ON t1(c2);
SQL> alter index i2_t1 REBUILD REVERSE;
SQL> create unique index i2_t1 ON t1(c2);
SQL> alter index i2_t1 REBUILD REVERSE

虚拟索引
没有具体索引值，目的是为告诉ORACLE如果利用索引，看看具体索引的具体代价是多少。再这个基础上看看是否建立真实索引

NING@ning>select
BANNER
* from v$version;
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL
CORE
TNS
Release
10.2.0.3.0
-
Production
Production
10.2.0.3.0
for
32-bit
Windows:
Version
10.2.0.3.0
-
Production
NLSRTL
Version
10.2.0.3.0
-
Production
1.创建虚拟索引
NING@ning>create
table
test(id int,name varchar2(30));
Table
created.
NING@ning>insert
into
test select rownum,object_name from all_objects where rownum<1001;
1000
rows created.
NING@ning>commit;
Commit
complete.
NING@ning>create
unique
table
plan
index
test
ix_test
compute
on
test(id)
nosegment;
Index
created.
NING@ning>analyze
statistics;
Table
analyzed.
2.使用虚拟索引
NING@ning>explain
for
select * from test where id=1;
Explained.
NING@ning>select
* from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation
| Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------
|
0 | SELECT STATEMENT
|
|
TEST
1 |
17 |
3
|
(0)| 00:00:01 |
3 (0)| 00:00:01
|*
1
|
TABLE
ACCESS
FULL|
|
1
|
17
|
--------------------------------------------------------------------------
必须设置隐含参数”_use_nosegment_indexes”=true（默认为 false)后，CBO才能使用虚拟索引 ix_test
NING@ning>alter
session
set
"_use_nosegment_indexes"=true;
Session
altered.
NING@ning>select
* from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 166686173
---------------------------------------------------------------------------------------
| Id | Operation
| Name
| Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------------
|
|
|*
0 | SELECT STATEMENT
|
ROWID|
|
1 |
1
17 |
2
(0)| 00:00:01 |
2 (0)| 00:00:01
1
2
|
|
TABLE
INDEX
ACCESS
UNIQUE
BY
SCAN
INDEX
TEST
|
|
17
|
|
00:00:01 |
|
IX_TEST
|
1
|
|
1
(0)|
---------------------------------------------------------------------------------------
RBO无法使用虚拟索引
NING@ning>alter
session
set
for
optimizer_mode=rule;
Session
altered.
NING@ning>explain
Explained.

NING@ning>select
* from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-----------------------------------------------------------
Plan hash value: 1357081020
----------------------------------
|
Id
|
Operation
| Name |
----------------------------------
|
0 | SELECT STATEMENT
|
|
|*
1
|
TABLE
ACCESS
FULL|
TEST
|
--------------------------------------------------------------
RBO使用 hint可以使用虚拟索引
NING@ning>explain
plan
for
select
/*+ index(test,ix_test)*/*
from
test
where
id=1;
Explained.
NING@ning>select
* from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 166686173
---------------------------------------------------------------------------
| Id | Operation
| Name
| Rows | Bytes | Cost (%CPU)| Time
|
---------------------------------------------------------------------------
|
|
|*
0 | SELECT STATEMENT
|
ROWID|
|
1 |
1
17 |
2
(0)| 00:00:01 |
(0)| 00:00:01 |
(0)| 00:00:01 |
1
2
|
|
TABLE
INDEX
ACCESS
UNIQUE
BY
SCAN
INDEX
TEST
|
|
|
17
|
2
IX_TEST
|
1
|
|
1
----------------------------------------------------------------------------
3.虚拟索引的特性
无法执行 alter index
NING@ning>alter
index ix_test rebuild;
alter
*
ERROR
index
ix_test
rebuild
at
line
can
1:
not
ORA-08114:
alter
a
fake index
NING@ning>alter
index
rename
ix_test
to
rename to ix_test2;
ix_test2
alter
*
index
ix_test
ERROR
ORA-08114:
at
line
can
1:
not
alter
a fake index
不能创建和虚拟索引同名的实际索引
NING@ning>create
index
on
ix_test
test(name)
on
test(name);
create
index
ix_test
*
ERROR
ORA-00955:
at
line 1:
name is
already
used
by
an existing object
可以创建和虚拟索引包含相同列但不同名的实际索引
NING@ning>create
index
ix_test2
on
test(id);
Index
created.
在 10g使用回收站特性的时候，虚拟索引必须显式 drop，或者在 drop table后 purge table后，才能创建同名的索引
NING@ning>drop
table test;
Table
dropped.
NING@ning>create
unique
ix_test
index
on
ix_test on
test2(id)
*
test2(id);
existing
create
unique
index
ERROR
ORA-00955:
at
line 1:
name is
already
index ix_test;
used
by
an
object
NING@ning>drop
drop
index
ix_test
*
1:
not
ERROR
ORA-38301:
at
line
can
perform DDL/DML over objects in Recycle Bin
NING@ning>purge
table test;
Table
purged.
NING@ning>create
unique
index
ix_test
on
test2(id);
Index
created.
查找系统中已经存在的虚拟索引：
SELECT
FROM
WHERE
MINUS
SELECT
FROM
index_owner, index_name
dba_ind_columns
index_name
NOT
LIKE
'BIN$%'
owner,
index_name
dba_indexes;
虚拟索引分析并且有效，但是数据字典里查不到结果，估计是 oracle内部临时保存了分析结果

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

转载于:http://blog.itpub.net/500314/viewspace-1067706/