目錄

• 一、Oracle索引簡介
  • 1.1 索引分類
  • 1.2 索引數據結構
  • 1.3 索引特性
  • 1.4 索引使用注意要點
  • 1.5、索引的缺點
  • 1.6、索引失效
• 二、索引分類介紹
  • 2.1、位圖索引
  • 1.2、函數索引
  • 1.3、反向索引
  • 1.4、全文索引

@

一、Oracle索引簡介

在看《收獲，不止SQL優化》一書，并根據書中例子進行實踐，整理成筆記

1.1 索引分類

Oracle索引分為BTree索引、位圖索引、反向索引、函數索引、全文索引等等。

1.2 索引數據結構

Oracle索引中最常用的是BTree索引，所以就以BTree索引為例，講一下BTree索引，BTree索引數據結構是一種二叉樹的結構，索引由根塊(Root)、莖塊(Branch)、葉子塊(Leaf)組成，其中葉子塊主要存儲索引列具體值(Key Column Value)以及能定位到數據塊具體位置的Rowid，莖塊和根塊主要保存對應下級對應索引

1.3 索引特性

索引特性：

• 索引本身是有序的
• 索引本身能存儲列值

1.4 索引使用注意要點

• (1)、僅等值無范圍查詢時，組合的順序不影晌性能

drop table t purge; create table t as select * from dba objects; update t set object_id=rownum ; commit; create index idx_id_type on t(object_id, object_type) ; create index idx_type_id on t(object_type , object_id) ; set autotrace off; alter session set statistics_level=all ; select /*+index(t idx_id_type)*/ * from t where object_id=20 and object_type='TABLE'; select * from table(dbms_xplan.display cursor(null , null , 'allstats last')); select /*+index(t,idx_type id)*/ * from t where object_id=20 and object_type= 'TABLE'; select * from table(dbms_xplan.display cursor(null , null , 'allstats last'));

• (2)、范圍查詢時，組合索引最佳順序一般是將等值查詢的列置前

select /*+index (t, idx_id_type)*/ * from t where object_id>=20 and object_id<2000 and object_type='TABLE';select /*+index (t , idx_type_id) */ * from t where object_id>=20 and object_id<2000 and object type='TABLE';

• (3)、Oracle不能同時在索引根的兩段尋找最大值和最小值

set autotrace on select max(object_id) , min(object_id) from t;

笛卡爾乘積寫法：

set autotrace onselect max, min from (select max(object_id) max from t ) a , (select min(object_id) min from t ) b;

• (4)、索引最新的數據塊一般是在最右邊

1.5、索引的缺點

• 熱快競爭：索引最新的數據塊一般在最右邊，而訪問也一般是訪問比較新的數據，所以容易造成熱快競爭
• 更新新增問題：索引本身是有序的，所以查詢時候很快，但是更新時候就麻煩了，新增更新索引都需要保證排序

1.6、索引失效

索引失效分為邏輯失效和物理失效

• 邏輯失效
  邏輯失效是因為一些sql語法導致索引失效，比如加了一些函數，而索引列不是函數索引
• 物理失效
  物理失效是真的失效,比如被設置unusable屬性,分區表的不規范操作也會導致索引失效等等情況

alter index index_name unusable;

二、索引分類介紹

索引分類：BTree索引、位圖索引、函數索引、反向索引、全文索引

2.1、位圖索引

位圖索引：位圖索引儲存的就是比特值

環境準備，位圖索引性質適用于count時，效率最高

drop table t purge; create table t as select * from dba_objects; update t set object_id = rownum; commit;

不用索引的情況：

SQL> set autotrace on SQL> select count(*) from t;COUNT(*) ----------72016 執行計劃 ---------------------------------------------------------- Plan hash value: 2966233522 ------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 288 (1)| 00:00:04 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| T | 86565 | 288 (1)| 00:00:04 | ------------------------------------------------------------------- Note ------ dynamic sampling used for this statement (level=2) 統計信息 ----------------------------------------------------------4 recursive calls0 db block gets1111 consistent gets0 physical reads0 redo size432 bytes sent via SQL*Net to client419 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)1 rows processed SQL>

創建位圖索引：

create bitmap index idx_bitm_t_status on t(status);

再次查詢，走位圖索引查詢：

SQL> set autotrace on SQL> select count(*) from t;COUNT(*) ----------72016 執行計劃 ---------------------------------------------------------- Plan hash value: 4272013625 -------------------------------------------------------------------------------- ----------- | Id | Operation | Name | Rows | Cost (%CPU)|Time | -------------------------------------------------------------------------------- ----------- | 0 | SELECT STATEMENT | | 1 | 5 (0)|00:00:01 | | 1 | SORT AGGREGATE | | 1 | || | 2 | BITMAP CONVERSION COUNT | | 86565 | 5 (0)|00:00:01 | | 3 | BITMAP INDEX FAST FULL SCAN| IDX_BITM_T_STATUS | | || -------------------------------------------------------------------------------- ----------- Note ------ dynamic sampling used for this statement (level=2) 統計信息 ----------------------------------------------------------0 recursive calls0 db block gets6 consistent gets0 physical reads0 redo size432 bytes sent via SQL*Net to client419 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)1 rows processed SQL>

注意要點：

位圖索引更新列容易造成死鎖，所以查詢比較多列才適合建位圖索引，更新比較多的列就盡量不要建索引

1.2、函數索引

函數索引：就是將一個函數計算的結果存儲在行的列中

環境準備：

drop table t purge; create table t (id int, status varchar2(2)); insert into t select 1,'N' from dual; insert into t select rownum ,'Y' from dual connect by rownum <1000; commit;

不走索引的查詢：

SQL> set autotrace on SQL> select * from t where (case when status='N' then 'No' end)='No';ID STAT ---------- ----1 N 執行計劃 ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 16 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T | 1 | 16 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------1 - filter(CASE "STATUS" WHEN 'N' THEN 'No' END ='No') Note ------ dynamic sampling used for this statement (level=2) 統計信息 ----------------------------------------------------------0 recursive calls0 db block gets8 consistent gets0 physical reads0 redo size486 bytes sent via SQL*Net to client419 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)1 rows processed SQL>

創建函數索引：

create index idx_status on t (case when status ='N' then 'No' end);

走函數索引的查詢：

SQL> select * from t where (case when status='N' then 'No' end)='No';ID STAT ---------- ----1 N 執行計劃 ---------------------------------------------------------- Plan hash value: 3908194542 -------------------------------------------------------------------------------- ---------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- ---------- | 0 | SELECT STATEMENT | | 10 | 200 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 10 | 200 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_STATUS | 4 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------- ---------- Predicate Information (identified by operation id): ---------------------------------------------------2 - access(CASE "STATUS" WHEN 'N' THEN 'No' END ='No') Note ------ dynamic sampling used for this statement (level=2) 統計信息 ----------------------------------------------------------0 recursive calls0 db block gets2 consistent gets0 physical reads0 redo size486 bytes sent via SQL*Net to client419 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)1 rows processed SQL>

注意要點：

自定義函數時要加上deterministic 關鍵字，不然不能建立函數索引

建立一個自定義函數：

create or replace function f_addusl(i int) return int is beginreturn(i + 1); end;

嘗試建立函數索引：

create index idx_ljb_test on t(f_addusl(id));

提示：ORA-30553:函數不能確定

用deterministic 關鍵字，就可以建立函數索引

create or replace function f_addusl(i int) return int deterministic is beginreturn(i + 1); end;

在自定義函數代碼更新時，對應的函數索引也要重建，否則不能用到原來的函數索引

1.3、反向索引

反向索引：反向索引其實也是BTree索引的一種特例，不過在列中字節會反轉的(反向索引是為了避免熱快競爭，比如索引列中存儲的列值是遞增的，比如250101,250102，按照BTree索引的特性，一般是按照順序存儲在索引右邊的，所以容易形成熱快競爭，而反向索引可以避免這種情況，因為反向索引是這樣存儲的，比如101052,201052，這樣列值就距離很遠了，避免了熱快競爭)

反向索引不能用到范圍查詢

SQL> set autotrace on SQL> select * from t where created=sysdate; 未選定行 執行計劃 ---------------------------------------------------------- Plan hash value: 913247507 -------------------------------------------------------------------------------- --------------- | Id | Operation | Name | Rows | Bytes | Cost (%C PU)| Time | -------------------------------------------------------------------------------- --------------- | 0 | SELECT STATEMENT | | 12 | 2484 | 286 (0)| 00:00:04 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 12 | 2484 | 286 (0)| 00:00:04 | |* 2 | INDEX RANGE SCAN | IDX_REV_CREATED | 346 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------- --------------- Predicate Information (identified by operation id): ---------------------------------------------------2 - access("CREATED"=SYSDATE@!) Note ------ dynamic sampling used for this statement (level=2) 統計信息 ----------------------------------------------------------0 recursive calls0 db block gets2 consistent gets0 physical reads0 redo size1191 bytes sent via SQL*Net to client408 bytes received via SQL*Net from client1 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)0 rows processed SQL>

范圍查詢，發現不走反向索引查詢

SQL> select * from t where created>= sysdate-10 and created <= sysdate-1; OWNER ------------------------------------------------------------ OBJECT_NAME -------------------------------------------------------------------------------- SUBOBJECT_NAME OBJECT_ID ------------------------------------------------------------ ---------- DATA_OBJECT_ID OBJECT_TYPE CREATED -------------- -------------------------------------- -------------- LAST_DDL_TIME TIMESTAMP STATUS TE GE SE -------------- -------------------------------------- -------------- -- -- --NAMESPACE EDITION_NAME ---------- ------------------------------------------------------------ SYS ICOL$202 TABLE 15-6月 -19 02-4月 -10 2010-04-02:13:18:38 VALID N N N1 SYS I_USER14646 INDEX 14-6月 -19 OWNER ------------------------------------------------------------ OBJECT_NAME -------------------------------------------------------------------------------- SUBOBJECT_NAME OBJECT_ID ------------------------------------------------------------ ---------- DATA_OBJECT_ID OBJECT_TYPE CREATED -------------- -------------------------------------- -------------- LAST_DDL_TIME TIMESTAMP STATUS TE GE SE -------------- -------------------------------------- -------------- -- -- --NAMESPACE EDITION_NAME ---------- ------------------------------------------------------------ 02-4月 -10 2010-04-02:13:18:38 VALID N N N4 SYS CON$2828 TABLE 13-6月 -19 02-4月 -10 2010-04-02:13:18:38 VALID N N N1 SYS OWNER ------------------------------------------------------------ OBJECT_NAME -------------------------------------------------------------------------------- SUBOBJECT_NAME OBJECT_ID ------------------------------------------------------------ ---------- DATA_OBJECT_ID OBJECT_TYPE CREATED -------------- -------------------------------------- -------------- LAST_DDL_TIME TIMESTAMP STATUS TE GE SE -------------- -------------------------------------- -------------- -- -- --NAMESPACE EDITION_NAME ---------- ------------------------------------------------------------ UNDO$1515 TABLE 12-6月 -19 02-4月 -10 2010-04-02:13:18:38 VALID N N N1 SYS C_COBJ#2929 CLUSTER 11-6月 -19 02-4月 -10 2010-04-02:13:18:38 VALID N N N OWNER ------------------------------------------------------------ OBJECT_NAME -------------------------------------------------------------------------------- SUBOBJECT_NAME OBJECT_ID ------------------------------------------------------------ ---------- DATA_OBJECT_ID OBJECT_TYPE CREATED -------------- -------------------------------------- -------------- LAST_DDL_TIME TIMESTAMP STATUS TE GE SE -------------- -------------------------------------- -------------- -- -- --NAMESPACE EDITION_NAME ---------- ------------------------------------------------------------5 SYS I_OBJ#33 INDEX 10-6月 -19 02-4月 -10 2010-04-02:13:18:38 VALID N N N4 SYS PROXY_ROLE_DATA$ OWNER ------------------------------------------------------------ OBJECT_NAME -------------------------------------------------------------------------------- SUBOBJECT_NAME OBJECT_ID ------------------------------------------------------------ ---------- DATA_OBJECT_ID OBJECT_TYPE CREATED -------------- -------------------------------------- -------------- LAST_DDL_TIME TIMESTAMP STATUS TE GE SE -------------- -------------------------------------- -------------- -- -- --NAMESPACE EDITION_NAME ---------- ------------------------------------------------------------2525 TABLE 09-6月 -19 02-4月 -10 2010-04-02:13:18:38 VALID N N N1 SYS I_IND14141 INDEX 08-6月 -19 02-4月 -10 2010-04-02:13:18:38 VALID N N N4 OWNER ------------------------------------------------------------ OBJECT_NAME -------------------------------------------------------------------------------- SUBOBJECT_NAME OBJECT_ID ------------------------------------------------------------ ---------- DATA_OBJECT_ID OBJECT_TYPE CREATED -------------- -------------------------------------- -------------- LAST_DDL_TIME TIMESTAMP STATUS TE GE SE -------------- -------------------------------------- -------------- -- -- --NAMESPACE EDITION_NAME ---------- ------------------------------------------------------------ SYS I_CDEF25454 INDEX 07-6月 -19 02-4月 -10 2010-04-02:13:18:38 VALID N N N4 已選擇9行。 執行計劃 ---------------------------------------------------------- Plan hash value: 1322348184 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 12 | 2484 | 292 (2)| 00:00:04 | |* 1 | FILTER | | | | | | |* 2 | TABLE ACCESS FULL| T | 12 | 2484 | 292 (2)| 00:00:04 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------1 - filter(SYSDATE@!-10<=SYSDATE@!-1)2 - filter("CREATED">=SYSDATE@!-10 AND "CREATED"<=SYSDATE@!-1) Note ------ dynamic sampling used for this statement (level=2) 統計信息 ----------------------------------------------------------5 recursive calls0 db block gets1112 consistent gets0 physical reads0 redo size1770 bytes sent via SQL*Net to client419 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)9 rows processed SQL>

1.4、全文索引

全文索引：所謂Oracle全文索引是通過Oracle詞法分析器(lexer)將所有的表意單元term存儲dr$開頭的表里并存儲term出現的位置、次數、hash值等等信息，Oracle提供了basic_lexer(針對英語)、chinese_vgram_lexer(漢語分析器)、chinese_lexer(新的漢語分析器)。

• basic_lexer：是一種適用于英文的分析器，根據空格或者標點符號將詞元分離，不管對于中文來說是沒有空格的，所以這種分析器不適合中文
• chinese_vgram_lexer：這是一種原先專門的中文分析器，支持所有的漢字字符集，比如zhs16gbk單點。這種分析器，分析過程是按字為單元進行分析的，舉個例子，“索引本身是有序的”，按照這種分析器，會分成詞元“索”、“索引”、“引本”、“本身”、“身是”、“是有”、“有序”、“序的”、“的”這些詞元，然后你發現像“序的”這些詞在中文中基本是不成立的，不過這種Oracle分析器本身就不認識中文，所以只能全部分析，很明顯效率是不好的
• chinese_lexer：這是一種新的中文分析器，前面提到chinese_vgram_lexer這種分析器雖然支持所有的中文字符集，但是效率不高，所以chinese_lexer是對其的改進版本，這種分析器認識很多中文詞匯，能夠比較快查詢，提高效率，不過這種分析器只能支持utf-8字符集

Oracle的全文索引具體可以采用通配符查找、模糊匹配、相關分類、近似查找、條件加權和詞意擴充等方法

環境準備

drop table t purge; create table t as select * from dba_objects where object_name is not null; update t set object_name ='高興' where rownum<=2; commit; select * from t where object_name like '%高興%';

設置詞法分析器

//設置詞法分析器 BEGINctx_ddl.create_preference ('lexer1', 'chinese_vgram_lexer'); END;

解鎖ctxsys用戶，同時給你的測試賬號(我這里用scott)授權使用ctx_ddl

//解鎖ctxsys用戶同時授權 grant ctxapp to scott; alter user ctxsys account unlock; alter user ctxsys identified by ctxsys; connect ctxsys/ctxsys; grant execute on ctx_ddl to scott; connect scott/11;

建立全文索引

//刪除全文索引 drop index idx_content; //查看數據文件信息 select * from v$datafile; //建立全文索引 CREATE INDEX idx_content ON t(object_name) indextype is ctxsys.context parameters('lexer lexer1');

注意要點：更新數據時候記得執行全文索引同步命令，否則將看不到更新數據

exec ctx_ddl.sync_index('idx_content','20M');

轉載于:https://www.cnblogs.com/mzq123/p/11033238.html

總結

以上是生活随笔為你收集整理的Oracle索引知识学习笔记的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。