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內部臨時儲存了分析結果

CUUG筆記 ORACLE索引學習筆記

相關文章