B-index、bitmap-index、text-index使用場景詳解

索引的種類：
B-tree索引、Bitmap索引、TEXT index

1. B-tree索引

介紹： B-tree 是一種常見的資料結構，也稱多路搜尋樹，並不是二叉樹。B-tree 結構可以顯著減少定位記錄時所經歷的中間過程，從而加快存取速度。

適用場景：索引欄位有著很高的selectivity或者結果集很小的時候。例如 身份證號碼 手機號碼 QQ號等欄位，常用於主鍵 唯一約束，一般在線上交易的專案中用到的多些。

2. Bitmap索引

介紹：點陣圖索引由於只儲存鍵值的起止Rowid和點陣圖，佔用的空間非常少.
適用場景： OLAP、重複率很高的鍵值
缺點： 在bitmap index中一個dml操作，影響的是一個點陣圖段（同一鍵值）。嚴重影響頻繁的DML（極其容易造成會話hang住）。
語法示範： SQL> create bitmap index idx_andy_owner on andy(owner);
Index created.

3. TEXT index 全文索引

使用場景： b-tree,bitmap無法發揮作用的場景，like '%string%'
缺點： 佔用過大的磁碟空間（全文索引大約是原表的1.5倍，重建成本很高）、維護成本高、bug多
使用步驟詳解：
步驟一　檢查和設定資料庫角色
SQL> select username from dba_users where username='CTXSYS';
步驟二　賦權
SQL> grant execute on ctx_ddl to dblink;
步驟三　設定詞法分析器(lexer)
Oracle實現全文檢索，其機制其實很簡單。即透過Oracle專利的詞法分析器(lexer),將文章中所有的表意單元（Oracle 稱為 term）找出來，記錄在一組以dr$開頭的表中，同時記下該term出現的位置、次數、hash 值等資訊。檢索時，Oracle 從這組表中查詢相應的term，並計算其出現頻率，根據某個演算法來計算每個文件的得分（score）,即所謂的‘匹配率’。而lexer則是該機制的核心，它決定了全文檢索的效率。Oracle 針對不同的語言提供了不同的 lexer, 而我們通常能用到其中的三個：

　　n basic_lexer: 針對英語。它能根據空格和標點來將英語單詞從句子中分離，還能自動將一些出現頻率過高已經失去檢索意義的單詞作為‘垃圾’處理，如if , is 等，具有較高的處理效率。但該lexer應用於漢語則有很多問題，由於它只認空格和標點，而漢語的一句話中通常不會有空格，因此，它會把整句話作為一個term,事實上失去檢索能力。以‘中國人民站起來了’這句話為例，basic_lexer 分析的結果只有一個term ,就是‘中國人民站起來了’。此時若檢索‘中國’，將檢索不到內容。

　　n chinese_vgram_lexer: 專門的漢語分析器，支援所有漢字字符集（ZHS16CGB231280 ZHS16GBK ZHT32EUC ZHT16BIG5 ZHT32TRIS ZHT16MSWIN950 ZHT16HKSCS UTF8 該分析器按字為單元來分析漢語句子。‘中國人民站起來了’這句話，會被它分析成如下幾個term: ‘中’，‘中國’，‘國人’，‘人民’，‘民站’，‘站起’，起來’，‘來了’，‘了’。可以看出，這種分析方法，實現演算法很簡單，並且能實現‘一網打盡’，但效率則是差強人意。

　　n chinese_lexer: 這是一個新的漢語分析器，只支援utf8字符集。上面已經看到，chinese vgram lexer這個分析器由於不認識常用的漢語詞彙，因此分析的單元非常機械，像上面的‘民站’，‘站起’在漢語中根本不會單獨出現，因此這種term是沒有意義的，反而影響效率。chinese_lexer的最大改進就是該分析器 能認識大部分常用漢語詞彙，因此能更有效率地分析句子，像以上兩個愚蠢的單元將不會再出現，極大 提高了效率。但是它只支援 utf8, 如果你的資料庫是zhs16gbk字符集，則只能使用笨笨的那個Chinese vgram lexer.

　　如果不做任何設定，Oracle 預設使用basic_lexer這個分析器。要指定使用哪一個lexer, 可以這樣操作：
第一． 當前使用者下下建立一個preference（例：在pomoho使用者下執行以下語句）

　　exec ctx_ddl.create_preference ('my_lexer', 'chinese_vgram_lexer');

　　第二． 在建立oracle全文索引索引時，指明所用的lexer:

　　CREATE INDEX idx_andy_object_name ON andy(object_name) indextype is ntext parameters('lexer my_lexer');

　　這樣建立的全文檢索索引，就會使用chinese_vgram_lexer作為分析器。
步驟五　使用索引
select object_name from andy where contains(object_name,'TABLE')>0;

實驗對比：
-- 建立實驗表
SQL> create table andy as select * from dba_objects;
-- 普通查詢
SQL> select object_name from andy where object_name like '%table%';
228 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1282372638

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3729 | 93225 | 297 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| ANDY | 3729 | 93225 | 297 (1)| 00:00:04 |
--------------------------------------------------------------------------
-- 建立text index索引。
SQL> create index idx_andy_object_name on andy(object_name) indextype is ctxsys.context;
-- 使用text index索引查詢
SQL> select object_name from andy where contains(object_name,'TABLE')>0;
298 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3868341989

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 37 | 1369 | 12 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| ANDY | 37 | 1369 | 12 (0)| 00:00:01 |
|* 2 | DOMAIN INDEX | IDX_ANDY_OBJECT_NAME | | | 4 (0)| 00:00:01 |

----------------------------------------------------------------------------------------------------

OK, 請標明出處。