index索引

pwz1688發表於2014-04-24
Index索引
一、索引簡介
索引分b-tree索引和點陣圖索引，若基數大，重複值小，update比較多則用b-tree索引，反之，用點陣圖索引。
檢視索引與約束資訊
SQL> col index_name for a10;
SQL> col index_type for a10;
SQL> col table_name for a10;
SQL> col tablespace_name for a20;
SQL> col column_name for a10;
SQL> col constraint_name for a10;
SQL> col constraint_type for a10;
SQL> col constraint_column for a10;
--檢視索引資訊
SQL> select index_name,index_type,table_name,tablespace_name from dba_indexes where owner='SCOTT';
INDEX_NAME INDEX_TYPE TABLE_NAME TABLESPACE_NAME
---------- ---------- ---------- --------------------
PK_EMP NORMAL EMP USERS
PK_DEPT NORMAL DEPT USERS
--檢視索引列資訊
SQL> select index_name,table_name,column_name from dba_ind_columns where index_owner='SCOTT';
INDEX_NAME TABLE_NAME COLUMN_NAM
---------- ---------- ----------
PK_DEPT DEPT DEPTNO
PK_EMP EMP EMPNO
-檢視約束資訊
SQL> select constraint_name,constraint_type,table_name from dba_constraints where owner='SCOTT';
CONSTRAINT CONSTRAINT TABLE_NAME
---------- ---------- ----------
PK_DEPT P DEPT
PK_EMP P EMP
FK_DEPTNO R EMP
--檢視約束列資訊
SQL> select constraint_name,table_name,column_name from dba_cons_columns where owner='SCOTT';
CONSTRAINT TABLE_NAME COLUMN_NAM
---------- ---------- ----------
PK_DEPT DEPT DEPTNO
PK_EMP EMP EMPNO
FK_DEPTNO EMP DEPTNO
二、索引建立與分析
例項：建立lxtb表，並新增相應索引
--建立lxtb表
SQL> create table lxtb(id number(8),gender varchar2(2),name varchar2(30));
Table created.
--隨機插入男1，女0約20000人
SQL> declare
  2 v_num number(2);
  3 begin
  4 for i in 1..20000 loop
  5 v_num:=round(dbms_random.value(0,1),0);
  6 if v_num>0 then
  7 insert into lxtb values(i,'m','male'||i);
  8 else
  9 insert into lxtb values(i,'f','female'||i);
 10 end if;
 11 if mod(i,1000)=0 then
 12 commit;
 13 end if;
 14 end loop;
 15 commit;
 16 end;
 17 /
PL/SQL procedure successfully completed.
SQL> select count(1) from lxtb;
  COUNT(1)
----------
     20000
SQL> select * from lxtb where rownum<=10;
        ID GE NAME
---------- -- ------------------------------
         1 f female1
         2 m male2
         3 m male3
         4 m male4
         5 m male5
         6 f female6
         7 f female7
         8 f female8
         9 m male9
        10 f female10
10 rows selected.
--先檢視此表的索引資訊
SQL> select index_name,index_type,table_name,tablespace_name,blevel,leaf_blocks
  2 from dba_indexes
  3 where table_name='LXTB';
no rows selected
--建立索引表空間（索引表空間與表所在表空間最好分開，這樣可以減少io搶佔利用，提高查詢效能，另外索引表空間怕碎片，最好採用uniform size方式）
SQL> col name for a50;
SQL> select file#,name from v$datafile;
     FILE# NAME
---------- --------------------------------------------------
         1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf
         2 /u01/app/oracle/oradata/PROD/disk4/undotbs01.dbf
         3 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf
         4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf
         5 /u01/app/oracle/oradata/PROD/disk3/lstbs01.dbf
         6 /u01/app/oracle/oradata/PROD/disk3/lstbs02.dbf
         7 /u01/app/oracle/oradata/PROD/disk3/lxtbs03.dbf
         8 /u01/app/oracle/oradata/PROD/disk5/undotbs02.dbf
8 rows selected.
SQL> create tablespace indx datafile '/u01/app/oracle/oradata/PROD/disk4/indx01.dbf' size 50m
  2 autoextend on next 10m maxsize unlimited uniform size 1m;
Tablespace created.
SQL> select tablespace_name,contents,status from dba_tablespaces;
TABLESPACE_NAME CONTENTS STATUS
------------------------------ --------- ---------
SYSTEM PERMANENT ONLINE
UNDOTBS UNDO ONLINE
SYSAUX PERMANENT ONLINE
TEMPTS TEMPORARY ONLINE
USERS PERMANENT ONLINE
TEMPTS2 TEMPORARY ONLINE
LXTBS1 PERMANENT ONLINE
LXTBS2 PERMANENT ONLINE
LXTBS3 PERMANENT ONLINE
UNDOTBS2 UNDO ONLINE
TEMPTS3 TEMPORARY ONLINE
TABLESPACE_NAME CONTENTS STATUS
------------------------------ --------- ---------
INDX PERMANENT ONLINE
--為lxtb表建立索引（系統預設為b-tree索引）
SQL> create index i_gender on lxtb(gender) tablespace indx;
Index created.
--分析索引二種方式（注意與分析表的區別 分析表為analyze table tbname computer statistics）
方式一：
SQL> analyze index i_gender validate structure;
Index analyzed.
方式二：
SQL> exec dbms_stats.gather_index_stats('sys','i_gender');
PL/SQL procedure successfully completed.
推薦用方式二進行分析，因為分析的更全面
--檢視此索引資訊
SQL> select index_name,index_type,table_name,tablespace_name,blevel,leaf_blocks from dba_indexes where table_name='LXTB';
INDEX_NAME INDEX_TYPE TABLE_NAME TABLESPACE_NAME BLEVEL LEAF_BLOCKS
---------- ---------- ---------- -------------------- ---------- -----------
I_GENDER NORMAL LXTB INDX 1 37
以上資訊可知，此索引有二層結構（blevel(=1)+1），37個葉結點。
--刪除此索引，建立點陣圖索引
SQL> drop index i_gender;
Index dropped.
SQL> create bitmap index i_gender on lxtb(gender) tablespace indx;
Index created.
--分析索引並檢視索引資訊
SQL> analyze index i_gender validate structure;
Index analyzed.
SQL> select index_name,index_type,table_name,tablespace_name,blevel,leaf_blocks from dba_indexes where table_name='LXTB';
INDEX_NAME INDEX_TYPE TABLE_NAME TABLESPACE_NAME BLEVEL LEAF_BLOCKS
---------- ---------- ---------- -------------------- ---------- -----------
I_GENDER BITMAP LXTB INDX 0 1
以上查詢結果可知，此索引只有一層（blevel=0），一個葉節點，類似於一個矩陣。
注意：在oltp系統中，絕對不用點陣圖索引，因為點陣圖索引維護複雜，dml操作時很慢，只有資料倉儲系統才用點陣圖索引。
三、索引管理（索引碎片整理、開啟/關閉索引監控、重建/遷移索引）
索引碎片整理
SQL> alter index i_gender coalesce;
Index altered.
索引監控
--檢視此索引監控資訊
SQL> select * from v$object_usage where index_name='I_GENDER';
no rows selected
--開啟此索引的監控
SQL> alter index i_gender monitoring usage;
Index altered.
--再次檢視此索引監控資訊
SQL> select * from v$object_usage where index_name='I_GENDER';
INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
---------- ---------- --- --- ------------------- -------------------
I_GENDER LXTB YES NO 04/16/2014 23:02:27
注意：開啟索引監控，通過上面的use列可得知此索引是否用過，要是一直以來都是no，若能確保程式無問題，此說明此索引建的不合理，從未使用過，可以刪除。
--利用此索引，執行查詢操作，使此索引利用上
SQL>select * from lxtb where gender='m';
--檢視索引監控資訊
SQL> select * from v$object_usage where index_name='I_GENDER';
INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
---------- ---------- --- --- ------------------- -------------------
I_GENDER LXTB YES YES 04/16/2014 23:02:27
--關閉索引監控資訊
SQL> alter index i_gender nomonitoring usage;
Index altered.
SQL> select * from v$object_usage where index_name='I_GENDER';
INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
---------- ---------- --- --- ------------------- -------------------
I_GENDER LXTB NO YES 04/16/2014 23:02:27 04/16/2014 23:12:49
--rebuild遷移索引表空間（注意與move遷移表的區別）
SQL> select index_name,index_type,table_name,tablespace_name,blevel,leaf_blocks from dba_indexes where table_name='LXTB';
INDEX_NAME INDEX_TYPE TABLE_NAME TABLESPACE_NAME BLEVEL LEAF_BLOCKS
---------- ---------- ---------- -------------------- ---------- -----------
I_GENDER BITMAP LXTB INDX 0 1
SQL> alter index i_gender rebuild tablespace system;
Index altered.
SQL> select index_name,index_type,table_name,tablespace_name,blevel,leaf_blocks from dba_indexes where table_name='LXTB';
INDEX_NAME INDEX_TYPE TABLE_NAME TABLESPACE_NAME BLEVEL LEAF_BLOCKS
---------- ---------- ---------- -------------------- ---------- -----------
I_GENDER BITMAP LXTB SYSTEM 0 1
--遷移回來
SQL> alter index i_gender rebuild tablespace indx nologging online;
Index altered.
注意：生產庫系統，建立或重建/遷移（尤其重建）索引的時候，最好加上nologging online條件，避免索引佔用資源，影響實際業務操作需要。下面說下rebuild索引時，有沒online的區別：
rebuild:直接讀取原索引的資料，因為不讀表，所以為了一致，加鎖禁止dml操作。因為掃索引，時間短。
rebuild online:全表掃描表中資料，不阻塞dml操作，用臨時日誌表同步，時間較長。
當我們對索引進行rebuild時，如果不加online選項，oracle則直接讀取原索引的資料，當我們新增online選項時，oracle是直接掃描表中的資料，維護索引段資料的一致性就是從索引開始建立到索引建立完成這段時間的資料改變的同步。

四、其它型別索引（函式索引、反向索引、複合索引）
--無索引情況下，執行計劃
SQL> set autotrace traceonly;
SQL> select * from lxtb where name='male9999';
Execution Plan
----------------------------------------------------------
Plan hash value: 3274939490
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 33 | 19 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LXTB | 1 | 33 | 19 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("NAME"='male9999')
Note
-----
   - dynamic sampling used for this statement
Statistics
----------------------------------------------------------
          5 recursive calls
          0 db block gets
        134 consistent gets
          0 physical reads
          0 redo size
        527 bytes sent via SQL*Net to client
        385 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed
--建立索引，再檢視此查詢的執行計劃
SQL> create index i_name on lxtb(name) tablespace indx;
SQL> select * from lxtb where name='male9999';
Execution Plan
----------------------------------------------------------
Plan hash value: 3908778746
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 33 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| LXTB | 1 | 33 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_NAME | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("NAME"='male9999')
Note
-----
   - dynamic sampling used for this statement
Statistics
----------------------------------------------------------
          9 recursive calls
          0 db block gets
         73 consistent gets
          1 physical reads
          0 redo size
        531 bytes sent via SQL*Net to client
        385 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed
--修改查詢語句，索引列使用函式後，不走索引，代價更高
SQL> select * from lxtb where upper(name)='MALE9999';
Execution Plan
----------------------------------------------------------
Plan hash value: 3274939490
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 33 | 20 (5)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LXTB | 1 | 33 | 20 (5)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(UPPER("NAME")='MALE9999')
Note
-----
   - dynamic sampling used for this statement
Statistics
----------------------------------------------------------
          5 recursive calls
          0 db block gets
        134 consistent gets
          0 physical reads
          0 redo size
        527 bytes sent via SQL*Net to client
        385 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed
函式索引
--建立函式索引後，再檢視查詢條件為函式列的語句執行計劃（執行計劃雖然走了函式索引，但代價仍很高）
SQL> create index i_upper_name on lxtb(upper(name)) tablespace indx;
Index created.
SQL> select * from lxtb where upper(name)='MALE9999';
Execution Plan
----------------------------------------------------------
Plan hash value: 2231413184
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 33 | 17 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| LXTB | 1 | 33 | 17 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_UPPER_NAME | 80 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(UPPER("NAME")='MALE9999')
Note
-----
   - dynamic sampling used for this statement
Statistics
----------------------------------------------------------
         28 recursive calls
          0 db block gets
         72 consistent gets
          1 physical reads
          0 redo size
        527 bytes sent via SQL*Net to client
        385 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed
由此可見，函式索引效率低於一般索引，能不用函式索引的情況，儘量不要使用，如果要函式處理，可以在入參值上，先做函式轉換，這樣最後查詢語句中就可以不要使用函式轉換，就能正常走索引，若實在無法做替換，就可再建函式索引。
反向索引
有的記錄插入時鍵值是連線的，導致都寫在連續的索引塊上，導致索引塊的分裂，索引樹就會很深，用反向索引就會使索引樹分散在樹中。
反向鍵索引也有它的侷限性，如果在where語句中，需要對索引列的值進行範圍性的搜尋，如between，>,、=的比較操作時，其反向索引才會得到使用。實際應用中一般不用反向索引。
--lxtb表中id列建立一般索引
SQL> create index i_id on lxtb(id) tablespace indx;
Index created.
SQL> select index_name,index_type,table_name,tablespace_name,blevel,leaf_blocks from dba_indexes where table_name='LXTB';
INDEX_NAME INDEX_TYPE TABLE_NAME TABLESPACE_NAME BLEVEL LEAF_BLOCKS
---------- ---------- ---------- -------------------- ---------- -----------
I_GENDER BITMAP LXTB INDX 0 1
I_NAME NORMAL LXTB INDX 1 60
I_UPPER_NA FUNCTION-B LXTB INDX 1 60
ME ASED NORMA
           L
I_ID NORMAL LXTB INDX 1 44
SQL> drop index i_id;
Index dropped.
--lxtb表中id列建立反向索引
SQL> create index i_id on lxtb(id) reverse tablespace indx;
Index created.
SQL> select index_name,index_type,table_name,tablespace_name,blevel,leaf_blocks from dba_indexes where table_name='LXTB';
INDEX_NAME INDEX_TYPE TABLE_NAME TABLESPACE_NAME BLEVEL LEAF_BLOCKS
---------- ---------- ---------- -------------------- ---------- -----------
I_GENDER BITMAP LXTB INDX 0 1
I_NAME NORMAL LXTB INDX 1 60
I_UPPER_NA FUNCTION-B LXTB INDX 1 60
ME ASED NORMA
           L
I_ID NORMAL/REV LXTB INDX 1 44
複合索引
索引可以包含一個或多個列，多個列上的索引稱為複合索引。複合索引的第一列稱為前導列，講前導性和選擇性。建索引的時候優先考慮建複合索引，複合索引一般不超過三列。複合索引走的前提是第一列一定要出現，另外第一列要求刷選資料能力比較強，不要如性別之類欄位，一般建時間或姓名之類欄位。
如：create index i_com on lxtb(id,name) tablespace indx;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/21251711/viewspace-1148932/，如需轉載，請註明出處，否則將追究法律責任。

相關文章