Oracle 基礎篇 --- 索引

標籤：

第四. 索引 4.1 定義

索引是與表關聯的可選結構。通過建立索引可提高資料更新和檢索的效能。Oracle 索引提供到資料行的直接存取路徑。

可以對錶的一個或多個列建立索引。建立索引後，Oracle 伺服器會自動維護和使用索引。表資料的更新（如添加新行、更新行或刪除行）會自動傳播到所有相關的索引，這些對使用者來說是完全透明的。

索引還可以提高實施主鍵和唯一鍵約束條件時的效能。如果沒有索引，則每次對錶執行DML 操作時都會掃描整個表（全表掃描）。

4.2 類型

有多種類型的索引結構，可以根據需要使用。最常用的兩種
類型是：

4.2.1 B 樹索引

預設的索引類型；
採用平衡樹的形式.
B 樹索引的KVStore for Redis在平衡樹（B 樹）中，這樣可以快速執行二進位搜尋。

B 樹索引的結構

索引的頂層為根，它包含指向索引中下一層次的條目。下一層次為分支塊，它又指向位於索引中下一層次的塊。最底層是分葉節點，它包含指向表行的索引條目。葉塊是雙向關聯的，這便於按索引值升序或降序掃描索引。

索引葉條目的格式

• 條目頭：儲存列數和鎖定資訊
• 鍵列長度/值對：用於定義鍵中的列大小，後面跟隨列值（此類長度/值對的數目就是索引中的最大列數）。
• ROWID：包含索引值的行的行ID

索引葉條目的特性

在非分區表的B 樹索引中：

• 當多個行具有相同的索引值時，如果不壓縮索引，索引值會出現重複
• 當某行包含的所有鍵列為NULL 時，該行沒有對應的索引條目。因此，當WHERE 子句指定了NULL 時，將始終執行全表掃描
• 因為所有行屬於同一個段，所以要使用受限的ROWID 指向表行

對索引執行DML 操作的效果

對錶執行DML 操作時，Oracle 伺服器會維護所有索引。下面說明對索引執行DML 命令產生的效果：

• 執行插入操作導致在相應塊中插入索引條目。
• 刪除一行只導致對索引條目進行邏輯刪除。已刪除行所佔用的空間不可供後面新的葉條目使用。
• 更新鍵列導致對索引進行邏輯刪除和插入。PCTFREE設定對索引沒有影響，但建立時除外。即使索引塊的空間少於PCTFREE指定的空間，也可以向索引塊添加新條目。

4.3 建立

#建立索引create index emp3_name_ix onemp3(emp3_name);#查看索引的資訊select index_name, index_type, table_name, table_type, uniqueness, statusfrom user_indexeswhere table_name = ‘EMP3‘;INDEX_NAME      INDEX_TYPE      TABLE_NAME      TABLE_TYPE  UNIQUENES STATUS--------------- --------------- --------------- ----------- --------- --------EMP3_ID_PK      NORMAL          EMP3            TABLE       UNIQUE    VALIDEMP3_NAME_IX    NORMAL          EMP3            TABLE       NONUNIQUE VALID#查看索引對應的列SQL> select * from user_ind_columns where table_name = ‘EMP3‘;INDEX_NAME      TABLE_NAME      COLUMN_NAME     COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESC--------------- --------------- --------------- --------------- ------------- ----------- ----EMP3_ID_PK      EMP3            EMP3_ID                       1            22           0 ASCEMP3_NAME_IX    EMP3            EMP3_NAME                     1            10          10 ASCSQL> select * from emp3 where emp3_name = ‘qa1‘;   EMP3_ID EMP3_NAME      DEP_ID---------- ---------- ----------         2 qa1                 2Execution Plan----------------------------------------------------------Plan hash value: 215206995--------------------------------------------------------------------------------------------| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |              |     1 |    33 |     1   (0)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID| EMP3         |     1 |    33 |     1   (0)| 00:00:01 ||*  2 |   INDEX RANGE SCAN          | EMP3_NAME_IX |     1 |       |     1   (0)| 00:00:01 |--------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("EMP3_NAME"=‘qa1‘)Statistics----------------------------------------------------------          1  recursive calls          0  db block gets          3  consistent gets          0  physical reads          0  redo size        675  bytes sent via SQL*Net to client        524  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processed

4.4 決定使用全表掃描還是使用索引

在大多數情況下，全表掃描可能會導致更多的物理磁碟輸入輸出，但是全表掃描有時又可能會因為高度並行化的存在而執行的更快。

索引範圍掃描的總體原則是：

• 對於原始**排序的表**僅讀取少於表記錄數40％的查詢應該使用索引範圍掃描。
  反之，讀取記錄數目多於表記錄數的40％的查詢應該使用全表掃描。

• 對於**未排序的表**僅讀取少於表記錄數7％的查詢應該使用索引範圍掃描。
  反之，讀取記錄數目多於表記錄數的7％的查詢應該使用全表掃描。

###決定使用全表掃描還是使用索引 SQL> select index_name, index_type, table_name, uniqueness, status from user_indexes where table_name = ‘EMP3‘;INDEX_NAME      INDEX_TYPE TABLE_NAME      UNIQUENESS STATUS--------------- ---------- --------------- ---------- --------EMP3_ID_PK      NORMAL     EMP3            UNIQUE     VALIDEMP3_NAME_IX    NORMAL     EMP3            NONUNIQUE  VALIDSQL> select count(*) from emp3;  COUNT(*)----------       19#雖然有索引，但是此時是全表掃描SQL> select * from emp3 where emp3_name = ‘qa8‘;   EMP3_ID EMP3_NAME      DEP_ID---------- ---------- ----------        16 qa8                 2Execution Plan----------------------------------------------------------Plan hash value: 2425169977--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |     1 |    11 |     2   (0)| 00:00:01 ||*  1 |  TABLE ACCESS FULL| EMP3 |     1 |    11 |     2   (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("EMP3_NAME"=‘qa8‘)Statistics----------------------------------------------------------          1  recursive calls          0  db block gets          3  consistent gets          0  physical reads          0  redo size        671  bytes sent via SQL*Net to client        524  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processed#往emp3表添加資料[[email protected] sql]# more insert_data.sql#!/bin/bashi=$1;while [ $i -le $2 ]dosqlplus hr/CCM%[email protected] <<EOFinsert into emp3 values($i,‘$3‘,$4);commit;quit;EOFlet i=i+1doneecho "inset into emp3 table"[[email protected] sql]# ./insert_data.sql 90 100 dev 1SQL> select max(emp3_id) from emp3;MAX(EMP3_ID)------------      100000SQL> analyze table emp3 estimate statistics;Table analyzed.SQL> select blocks, empty_blocks, num_rows from user_tables where table_name = ‘EMP3‘;    BLOCKS EMPTY_BLOCKS   NUM_ROWS---------- ------------ ----------       374           10     101081#查看一條資料SQL> select * from emp3 where emp3_name = ‘qa33333‘;   EMP3_ID EMP3_NAME      DEP_ID---------- ---------- ----------     33333 qa33333             2Execution Plan----------------------------------------------------------Plan hash value: 215206995--------------------------------------------------------------------------------------------| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |              |     1 |    14 |     2   (0)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID| EMP3         |     1 |    14 |     2   (0)| 00:00:01 ||*  2 |   INDEX RANGE SCAN          | EMP3_NAME_IX |     1 |       |     1   (0)| 00:00:01 |--------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("EMP3_NAME"=‘qa33333‘)Statistics----------------------------------------------------------          0  recursive calls          0  db block gets          4  consistent gets          0  physical reads          0  redo size        681  bytes sent via SQL*Net to client        524  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 count(*) from emp3;  COUNT(*)----------    100000Execution Plan----------------------------------------------------------Plan hash value: 2418373429----------------------------------------------------------------------------| Id  | Operation             | Name       | Rows  | Cost (%CPU)| Time     |----------------------------------------------------------------------------|   0 | SELECT STATEMENT      |            |     1 |    70   (2)| 00:00:01 ||   1 |  SORT AGGREGATE       |            |     1 |            |          ||   2 |   INDEX FAST FULL SCAN| EMP3_ID_PK |   101K|    70   (2)| 00:00:01 |----------------------------------------------------------------------------Statistics----------------------------------------------------------          2  recursive calls          2  db block gets        262  consistent gets         37  physical reads        176  redo size        526  bytes sent via SQL*Net to client        524  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 count(*) from emp3 where dep_id = 2;  COUNT(*)----------     85726Execution Plan----------------------------------------------------------Plan hash value: 1396384608---------------------------------------------------------------------------| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------|   0 | SELECT STATEMENT   |      |     1 |     2 |   104   (1)| 00:00:02 ||   1 |  SORT AGGREGATE    |      |     1 |     2 |            |          ||*  2 |   TABLE ACCESS FULL| EMP3 | 50541 |    98K|   104   (1)| 00:00:02 |---------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - filter("DEP_ID"=2)Statistics----------------------------------------------------------          1  recursive calls          0  db block gets        373  consistent gets          0  physical reads          0  redo size        528  bytes sent via SQL*Net to client        524  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processedSQL> select count(*) from emp3 where emp3_name like ‘qa%‘;  COUNT(*)----------     85726Execution Plan----------------------------------------------------------Plan hash value: 3884997069----------------------------------------------------------------------------------| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |----------------------------------------------------------------------------------|   0 | SELECT STATEMENT  |              |     1 |     8 |     2   (0)| 00:00:01 ||   1 |  SORT AGGREGATE   |              |     1 |     8 |            |          ||*  2 |   INDEX RANGE SCAN| EMP3_NAME_IX |     7 |    56 |     2   (0)| 00:00:01 |----------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("EMP3_NAME" LIKE ‘qa%‘)       filter("EMP3_NAME" LIKE ‘qa%‘)Statistics----------------------------------------------------------          0  recursive calls          0  db block gets        395  consistent gets          0  physical reads          0  redo size        528  bytes sent via SQL*Net to client        524  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processed

Oracle 基礎篇 --- 索引