Oracle 基础篇 --- 索引选项
生活随笔
收集整理的這篇文章主要介紹了
Oracle 基础篇 --- 索引选项
小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.
2019獨角獸企業(yè)重金招聘Python工程師標準>>>
###4.4 選項
####4.4.1 唯一索引
確保每個索引值都是唯一的.
主鍵、唯一鍵與唯一索引的區(qū)別
一般，我們看到術語“索引”和“鍵”交換使用，但實際上這兩個是不同的。索引是存儲在數(shù)據(jù)庫中的一個物理結構，鍵純粹是一個邏輯概念。鍵代表創(chuàng)建來實施業(yè)務規(guī)則的完整性約束。索引和鍵的混淆通常是由于數(shù)據(jù)庫使用索引來實施完整性約束。
主鍵約束、唯一索引的區(qū)別:
SQL> create table test(id int, name varchar2(20), constraint pk_id primary key(id));Table created.SQL> select constraint_name, constraint_type from user_constraints where table_name = 'TEST';CONSTRAINT_NAME C ------------------------------ - PK_ID P#在test表中，我們指定了ID列作為主鍵，Oracle數(shù)據(jù)庫會自動創(chuàng)建一個同名的唯一索引：SQL> select index_name, index_type,uniqueness from user_indexes where table_name = 'TEST';INDEX_NAME INDEX_TYPE UNIQUENES -------------------- -------------------- --------- PK_ID NORMAL UNIQUE#此時，如果我們再試圖在ID列上創(chuàng)建一個唯一索引，Oracle會報錯，因為該列上已經(jīng)存在一個唯一索引：SQL> create unique index ind_test_uk on test(id); create unique index ind_test_uk on test(id)* ERROR at line 1: ORA-01408: such column list already indexedSQL> create index ind_test_uk on test(id); create index ind_test_uk on test(id)* ERROR at line 1: ORA-01408: such column list already indexed唯一鍵約束、唯一索引的區(qū)別:
SQL> drop table test purge;Table dropped.SQL> create table test(2 id int,3 name varchar2(20),4 constraint uk_test unique(id));Table created.SQL> select constraint_name, constraint_type from user_constraints where table_name = 'TEST';CONSTRAINT_NAME C ------------------------------ - UK_TEST USQL> select index_name, index_type, uniqueness2 from user_indexes3 where table_name = 'TEST';INDEX_NAME INDEX_TYPE UNIQUENES -------------------- -------------------- --------- UK_TEST NORMAL UNIQUE#Oracle同樣自動創(chuàng)建了一個同名的唯一索引，而且也不允許再在此列上創(chuàng)建唯一索引或非唯一索引。主鍵約束要求列值非空（NOT NULL），那么唯一鍵約束是否也要求非空呢？
SQL> insert into test values(1, 'sally');1 row created.SQL> insert into test values(null, 'Tony');1 row created.SQL> commit;Commit complete.SQL> select * from test;ID NAME ---------- --------------------1 sallyTony#從實驗結果來看， 唯一鍵約束并沒有非空要求。 唯一索引對列值非空不做要求。鍵約束或者唯一鍵約束失效，Oracle自動創(chuàng)建的唯一索引是否會受到影響？
SQL> drop table test purge;Table dropped.create table test(id int,name varchar2(20),constraint uk_test unique(id));Table created.SQL> select index_name, index_type, uniqueness, status from user_indexes where table_name = 'TEST';INDEX_NAME INDEX_TYPE UNIQUENES STATUS -------------------- -------------------- --------- -------- UK_TEST NORMAL UNIQUE VALIDSQL> alter table test disable constraint uk_test;Table altered.SQL> select index_name, index_type, uniqueness, status from user_indexes where table_name = 'TEST';no rows selectedSQL> alter table test enable constraint uk_test;Table altered.SQL> select index_name, index_type, uniqueness, status from user_indexes where table_name = 'TEST';INDEX_NAME INDEX_TYPE UNIQUENES STATUS -------------------- -------------------- --------- -------- UK_TEST NORMAL UNIQUE VALID#當主鍵約束或者唯一鍵約束失效時，Oracle會標記隱式創(chuàng)建的唯一索引為刪除狀態(tài)。先創(chuàng)建唯一索引，再創(chuàng)建主鍵或者唯一鍵約束，情況又會怎樣呢？
SQL> drop table test purge;Table dropped.SQL> create table test(2 id int,3 name varchar(20));Table created.SQL> create unique index idx_test_id on test(id);Index created.SQL> select index_name, index_type, uniqueness, status2 from user_indexes3 where table_name = 'TEST';INDEX_NAME INDEX_TYPE UNIQUENES STATUS -------------------- -------------------- --------- -------- IDX_TEST_ID NORMAL UNIQUE VALIDSQL> alter table test add constraint uk_test unique(id);Table altered.SQL> select index_name, index_type, uniqueness, status2 from user_indexes3 where table_name = 'TEST';INDEX_NAME INDEX_TYPE UNIQUENES STATUS -------------------- -------------------- --------- -------- IDX_TEST_ID NORMAL UNIQUE VALIDSQL> select constraint_name, constraint_type2 from user_constraints3 where table_name = 'TEST';CONSTRAINT_NAME C ------------------------------ - UK_TEST USQL> alter table test disable constraint uk_test;Table altered.SQL> select constraint_name, constraint_type, status2 from user_constraints3 where table_name = 'TEST';CONSTRAINT_NAME C STATUS ------------------------------ - -------- UK_TEST U DISABLEDSQL> select index_name, index_type, uniqueness, status2 from user_indexes3 where table_name = 'TEST';INDEX_NAME INDEX_TYPE UNIQUENES STATUS -------------------- -------------------- --------- -------- IDX_TEST_ID NORMAL UNIQUE VALID####4.4.2 反向關鍵字索引
“反向關鍵字索引”會按相反順序存儲索引值的字節(jié)。這可以減少索引中特定熱點的活 動量。如果許多用戶正按同一順序處理數(shù)據(jù)，那么在任何給定時刻，關鍵字值的前綴部分 （當前正在處理的）是非常接近的值。因此，在索引結構的該區(qū)域中會發(fā)生大量的活動。 為反向字節(jié)樣式的關鍵字值建立索引后，反向關鍵字索引可在索引結構中分散這些活動。
REVERSE索引也是一種B樹索引，但它物理上將按照列順序保存的每個索引鍵值進行了反轉。例如，索引鍵是20，用16進制存儲這個標準B樹索引鍵的兩個字節(jié)是C1，15，那么反向索引存儲的字節(jié)就是15，C1。
反向索引主要解決的是葉子塊的爭用問題。在RAC中，這個問題更加明顯，可能多實例反復修改同一個塊。舉個例子，在一張按照主鍵順序存儲的表中，一個實例增加記錄20，另一個增加21，這兩個值的鍵存儲于同一個索引葉子塊的左右兩側。
在反向索引中，插入操作會被反序字節(jié)排列分發(fā)到索引的全部葉子鍵中。就像上面的例子，20和21兩個鍵，在標準鍵索引中，他們應該是相鄰的，但在反向索引中，他們會被分開存儲。因此按順序鍵插入操作的IO會更加平均。
因為索引上的數(shù)據(jù)不是按照列存儲的順序，反向索引會禁止一些案例中可以用到的索引范圍掃描。例如，如果一個用戶查詢ID值大于20的記錄，那么數(shù)據(jù)庫不能從包含這個ID的塊開始查找，而是從所有的葉子塊。
這種索引的設計目的是消除插入操作的索引熱點。對于插入的性能提升有幫助，但也是有限的，因為數(shù)據(jù)庫不能使用索引范圍掃描了。
Sometimes, using a reverse-key index can make an OLTP Oracle Real Application Clusters application faster. For example, keeping the index of mail messages in an e-mail application: some users keep old messages, and the index must maintain pointers to these as well as to the most recent.
SQL> create table t(2 a number,3 b varchar(10),4 c date );Table created.SQL> begin2 for i int 1..1000 loop3 insert into t values(i, 'Test', sysdate);4 end loop;5 commit;6 end;7 /SQL> create index ind_t_rev on t(a,b,c) reverse;Index created.SQL> select index_name, index_type from user_indexes where table_name = 'T';INDEX_NAME INDEX_TYPE ------------------------------ --------------------------- IND_T_REV NORMAL/REV#Using the reverse key arrangement eliminates the ability to run an index range scanning query on the index. Because lexically adjacent keys are not stored next to each other in a reverse-key index, only fetch-by-key or full-index (table) scans can be performed.SQL> select * from t where a = 1000;------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 29 | 2 (0)| 00:00:01 | |* 1 | INDEX RANGE SCAN| IND_T_REV | 1 | 29 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------SQL> select count(*) from t where trunc(c) = to_date('24-JUL-15','DD-MM-YY');--------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 9 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 9 | | | |* 2 | TABLE ACCESS FULL| T | 1000 | 9000 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------#取消reverse SQL> alter index ind_t_rev rebuild noreverse;Index altered.SQL> select index_name, index_type from user_indexes where table_name = 'T';INDEX_NAME INDEX_TYPE ------------------------------ --------------------------- IND_T_REV NORMAL####4.4.3 組合索引
在Oracle中可以創(chuàng)建組合索引，即同時包含兩個或兩個以上列的索引。
- 當使用基于規(guī)則的優(yōu)化器（RBO）時，只有當組合索引的前導列出現(xiàn)在SQL語句的where子句中時，才會使用到該索引；
- 在使用Oracle9i之前的基于成本的優(yōu)化器（CBO）時， 只有當組合索引的前導列出現(xiàn)在SQL語句的where子句中時，才可能會使用到該索引，這取決于優(yōu)化器計算的使用索引的成本和使用全表掃描的成本，Oracle會自動選擇成本低的訪問路徑（請見下面的測試1和測試2）；
- 從Oracle9i起，Oracle引入了一種新的索引掃描方式——索引跳躍掃描（index skip scan），這種掃描方式只有基于成本的優(yōu)化器（CBO）才能使用。這樣，當SQL語句的where子句中即使沒有組合索引的前導列，并且索引跳躍掃描的成本低于其他掃描方式的成本時，Oracle就會使用該方式掃描組合索引（請見下面的測試3）；
- Oracle優(yōu)化器有時會做出錯誤的選擇，因為它再“聰明”，也不如我們SQL語句編寫人員更清楚表中數(shù)據(jù)的分布，在這種情況下，通過使用提示（hint），我們可以幫助Oracle優(yōu)化器作出更好的選擇（請見下面的測試4）。
創(chuàng)建測試表T
#T表創(chuàng)建 SQL> create table t as select * from all_objects;Table created.#數(shù)據(jù)分布 SQL> select object_type, count(*) from t group by object_type;OBJECT_TYPE COUNT(*) ------------------- ---------- EDITION 1 INDEX PARTITION 512 TABLE SUBPARTITION 32 CONSUMER GROUP 2 SEQUENCE 245 SYNONYM 27889 JOB 15 ......SQL> select count(*) from t;COUNT(*) ----------74051#創(chuàng)建復合索引 SQL> create index indx_t on t(object_type,object_name);Index created.SQL> select INDEX_NAME, INDEX_type from user_indexes where table_name = 'T';INDEX_NAME INDEX_TYPE --------------- --------------- INDX_T NORMALSQL> select index_name, table_name, column_name from user_ind_columns where TABLE_NAME = 'T';INDEX_NAME TABLE_NAME COLUMN_NAME --------------- --------------- ------------------------------ INDX_T T OBJECT_TYPE INDX_T T OBJECT_NAMESQL> analyze table t compute statistics2 for table3 for all indexes4 for all indexed columns5 /Table analyzed.測試一：使用了組合索引的前導列并且訪問了表中的少量記錄
SQL> set autotrace traceonlySQL> select * from t where object_type='JOB';15 rows selected.Execution Plan ---------------------------------------------------------- Plan hash value: 723869532-------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 15 | 1500 | 14 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 15 | 1500 | 14 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | INDX_T | 15 | | 3 (0)| 00:00:01 | --------------------------------------------------------------------------------------Predicate Information (identified by operation id): ---------------------------------------------------2 - access("OBJECT_TYPE"='JOB')Statistics ----------------------------------------------------------0 recursive calls0 db block gets16 consistent gets0 physical reads0 redo size2980 bytes sent via SQL*Net to client524 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)15 rows processed #正如我們所期望的，由于使用了組合索引的前導列并且訪問了表中的少量記錄，Oracle明智地選擇了索引掃描。測試二：使用了組合索引的前導列，是由于訪問了表中的大量數(shù)據(jù)
SQL> select * from t where object_type='SYNONYM';27889 rows selected.Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873-------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 27889 | 2723K| 297 (1)| 00:00:04 | |* 1 | TABLE ACCESS FULL| T | 27889 | 2723K| 297 (1)| 00:00:04 | --------------------------------------------------------------------------Predicate Information (identified by operation id): ---------------------------------------------------1 - filter("OBJECT_TYPE"='SYNONYM')Statistics ----------------------------------------------------------1 recursive calls0 db block gets2894 consistent gets0 physical reads0 redo size1381826 bytes sent via SQL*Net to client20973 bytes received via SQL*Net from client1861 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)27889 rows processed# 很明顯，即使使用了組合索引的前導列，但是由于訪問了表中的大量數(shù)據(jù)，Oracle選擇了不使用索引而直接使用全表掃描，因為優(yōu)化器認為全表掃描的成本更低，但事實是不是真的這樣的？我們通過增加提示（hint）來強制它使用索引來看看：SQL> select /*+ index(T indx_t) */ * from t where object_type = 'SYNONYM';27889 rows selected.Execution Plan ---------------------------------------------------------- Plan hash value: 723869532-------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 27889 | 2723K| 20012 (1)| 00:04:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 27889 | 2723K| 20012 (1)| 00:04:01 | |* 2 | INDEX RANGE SCAN | INDX_T | 27889 | | 173 (0)| 00:00:03 | --------------------------------------------------------------------------------------Predicate Information (identified by operation id): ---------------------------------------------------2 - access("OBJECT_TYPE"='SYNONYM')Statistics ----------------------------------------------------------0 recursive calls0 db block gets24661 consistent gets0 physical reads0 redo size3236139 bytes sent via SQL*Net to client20973 bytes received via SQL*Net from client1861 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)27889 rows processed #從以上結果可以看出，在訪問大量數(shù)據(jù)的情況下，使用索引確實會導致更高的執(zhí)行成本，這從statistics部分的邏輯讀取數(shù)（consistent gets）就可以看出，使用索引導致的邏輯讀取數(shù)是不使用索引導致的邏輯讀的10倍還多。因此，Oracle明智地選擇了全表掃描而不是索引掃描。測試三： where子句中沒有索引前導列的情況
SQL> select * from t where object_name = 'DBA_TAB_COLS';Execution Plan ---------------------------------------------------------- Plan hash value: 2722864248-------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 200 | 43 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 2 | 200 | 43 (0)| 00:00:01 | |* 2 | INDEX SKIP SCAN | INDX_T | 2 | | 41 (0)| 00:00:01 | --------------------------------------------------------------------------------------Predicate Information (identified by operation id): ---------------------------------------------------2 - access("OBJECT_NAME"='DBA_TAB_COLS')filter("OBJECT_NAME"='DBA_TAB_COLS')Statistics ----------------------------------------------------------1 recursive calls0 db block gets35 consistent gets0 physical reads0 redo size1753 bytes sent via SQL*Net to client524 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)2 rows processed由于只查詢了2條數(shù)據(jù)，即使沒有使用前導列，Oracle正確地選擇了索引跳躍掃描。我們再來看看如果不使用索引跳躍掃描，該語句的成本：SQL> select /*+ no_index(t indx_t)*/ * from t where object_name = 'DBA_TAB_COLS';Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873-------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 200 | 296 (1)| 00:00:04 | |* 1 | TABLE ACCESS FULL| T | 2 | 200 | 296 (1)| 00:00:04 | --------------------------------------------------------------------------Predicate Information (identified by operation id): ---------------------------------------------------1 - filter("OBJECT_NAME"='DBA_TAB_COLS')Statistics ----------------------------------------------------------1 recursive calls0 db block gets1060 consistent gets0 physical reads0 redo size1747 bytes sent via SQL*Net to client524 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)2 rows processed測試四：不選擇使用索引的情況
SQL> select * from t where object_name like 'DE%';101 rows selected.Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873-------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 267 | 26700 | 296 (1)| 00:00:04 | |* 1 | TABLE ACCESS FULL| T | 267 | 26700 | 296 (1)| 00:00:04 | --------------------------------------------------------------------------Predicate Information (identified by operation id): ---------------------------------------------------1 - filter("OBJECT_NAME" LIKE 'DE%')Statistics ----------------------------------------------------------1 recursive calls0 db block gets1065 consistent gets0 physical reads0 redo size8012 bytes sent via SQL*Net to client590 bytes received via SQL*Net from client8 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)101 rows processed#這次只選擇了101條數(shù)據(jù)，跟表T中總的數(shù)據(jù)量74051條相比，顯然只是很小的一部分，但是Oracle還是選擇了全表掃描，有1065個邏輯讀。這種情況下，如果我們強制使用索引，情況會怎樣呢？SQL> select /*+ index(t indx_t)*/ * from t where object_name like 'DE%';101 rows selected.Execution Plan ---------------------------------------------------------- Plan hash value: 2722864248-------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 267 | 26700 | 455 (0)| 00:00:06 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 267 | 26700 | 455 (0)| 00:00:06 | |* 2 | INDEX SKIP SCAN | INDX_T | 267 | | 265 (0)| 00:00:04 | --------------------------------------------------------------------------------------Predicate Information (identified by operation id): ---------------------------------------------------2 - access("OBJECT_NAME" LIKE 'DE%')filter("OBJECT_NAME" LIKE 'DE%')Statistics ----------------------------------------------------------1 recursive calls0 db block gets119 consistent gets0 physical reads0 redo size11862 bytes sent via SQL*Net to client590 bytes received via SQL*Net from client8 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)101 rows processed#通過添加提示（hint），我們強制Oracle使用了索引掃描（INDEX SKIP SCAN），執(zhí)行了119個邏輯讀，比使用全表掃描的時候少。#由此可見，Oracle優(yōu)化器有時會做出錯誤的選擇，因為它再“聰明”，也不如我們SQL語句編寫人員更清楚表中數(shù)據(jù)的分布，在這種情況下，通過使用提示（hint），我們可以幫助Oracle優(yōu)化器作出更好的選擇。####4.4.4 基于函數(shù)的索引
使用Oracle函數(shù)索引，無疑是提高查詢效率的有效方法之一。談到任何對列的操作都可能導致全表掃描，例如：
SQL> select employee_id, first_name from employees where substr(first_name,1,2) = 'Sa';------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 11 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 11 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------但是這種查詢在客服系統(tǒng)又經(jīng)常使用，我們可以創(chuàng)建一個帶有substr函數(shù)的基于Oracle函數(shù)索引，
create index emp_fname_substr on employees(substr(first_name, 1, 2));SQL> select index_name, index_type from user_indexes where table_name = 'EMPLOYEES';INDEX_NAME INDEX_TYPE ------------------------------ --------------------------- EMP_LAST_NAME_IDX NORMAL EMP_PHONE_IX NORMAL EMP_FNAME_SUBSTR FUNCTION-BASED NORMALSQL> select employee_id, first_name from employees where substr(first_name,1,2) = 'Sa';------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 14 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 14 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | EMP_FNAME_SUBSTR | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------------這樣在執(zhí)行上面的查詢語句時，這個基于函數(shù)的索引將排上用場，執(zhí)行計劃將是（INDEX RANGE SCAN）。 上面的例子中，我們創(chuàng)建了基于函數(shù)的索引，但是如果執(zhí)行下面的查詢：
SQL> select employee_id, first_name from employees where substr(first_name,1,1) = 'S';13 rows selected.Execution Plan ---------------------------------------------------------- Plan hash value: 1445457117------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 11 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 11 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------得到的執(zhí)行計劃將還是（TABLE ACCESS FULL），因為只有當數(shù)據(jù)列能夠等式匹配時，基于函數(shù)的索引才能生效，這樣對于這種索引的計劃和維護的要求都很高。請注意，向表中添加索引是非常危險的操作，因為這將導致許多查詢執(zhí)行計劃的變更。然而，如果我們使用基于函數(shù)的索引就不會產(chǎn)生這樣的問題，因為Oracle只有在查詢使用了匹配的內(nèi)置函數(shù)時才會使用這種類型的索引。
####4.4.5 壓縮索引
oracle 索引壓縮（key compression）是oracle 9i 中引入的一項新特性。該特性可以壓縮索引或者索引組織表中的重復鍵值，從而節(jié)省存儲空間。非分區(qū)的unique 索引和non-unique（至少兩列）索引都能夠被壓縮。bitmap 索引不能夠進行壓縮。
在oracle 索引壓縮中有幾個比較糾結的術語，需要說明一下。索引壓縮是通過將索引中的鍵值拆分成兩部分實現(xiàn)的，也就是grouping piece 也稱作prefix 和 unique piece 也稱作suffix 。grouping piece 是用來壓縮的被unique piece 共享的部分。如果鍵值不能提供unique piece，那么oracle 將會使用rowid 來唯一標識。只有B-tree 索引的葉子節(jié)點能夠被壓縮，分支節(jié)點不能夠被壓縮。索引壓縮是在單個block 中完成的，不能夠跨blocks進行索引壓縮。grouping piece (prefix) 和 unique piece (suffix) 存儲在同一個索引 block 中。
具體prefix 和 suffix 是怎么劃分的呢？默認prefix 長度等于索引列的數(shù)量減去1。當然我們可以人為控制prefix 的長度，非唯一索引的最大prefix 長度等于索引列的數(shù)量。唯一索引的最大prefix 長度等于索引列的數(shù)量減去1。比如，假設索引有三個列： 默認的時候：prefix (column1,column2) suffix (column3) 如果有以下幾組鍵值（1,2,3),（1,2,4),(1,2,7),(1,3,5),(1,3,4),(1,4,4) 那么在prefix中重復的（1,2），(1,3) 將會被壓縮至保留一份。
索引壓縮適合于那些鍵值重復率高的索引，這樣才能夠達到壓縮鍵值，節(jié)省存儲空間目的。索引壓縮以后一個索引塊可以存放更多的鍵值，這樣當進行full index scan,full fast index scan 的時候IO性能會更好，但是CPU的負載會增加，至于總體的性能就要看IO性能的提高和CPU負載增加那個是主要方面了。我不認為索引壓縮性能總是提高的，更多的意義在于節(jié)省存儲空間，減少IO時間。
SQL> create table objects1 as select object_id, object_name from dba_objects;SQL> create table objects2 as select 100 object_id, object_name from dba_objects;SQL> create table objects3 as select object_id, object_name from dba_objects;SQL> create index objects1_idx on objects1(object_id) compress 1;Index created.SQL> create index objects2_inx on objects2(object_id) compress 1;Index created.SQL> create index objects3_inx on objects3(object_id);Index created.SQL> select index_name, compression, leaf_blocksfrom user_indexeswhere index_name in ('OBJECTS1_IDX','OBJECTS2_INX','OBJECTS3_INX');INDEX_NAME COMPRESS LEAF_BLOCKS ------------------------------ -------- ----------- OBJECTS1_IDX ENABLED 230 OBJECTS2_INX ENABLED 116 OBJECTS3_INX DISABLED 167SQL> select object_id,object_name from objects1 where object_id = 100;-------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 29 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| OBJECTS1 | 1 | 29 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | OBJECTS1_IDX | 1 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------------SQL> select object_id,object_name from objects2 where object_id = 100;------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 75203 | 1982K| 98 (2)| 00:00:02 | |* 1 | TABLE ACCESS FULL| OBJECTS2 | 75203 | 1982K| 98 (2)| 00:00:02 | ------------------------------------------------------------------------------SQL> select object_id,object_name from objects3 where object_id = 100;-------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 29 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| OBJECTS3 | 1 | 29 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | OBJECTS3_INX | 1 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------------我們可以看到對于objects1 和 objects3 因為object_id 都是唯一的，所以沒有壓縮的空間，壓縮以后索引反而占用了更大的空間，還不如不壓縮。而objects2 中 object_id 都是重復的壓縮效果明顯。
除了創(chuàng)建的時候進行索引壓縮，還可以在rebuild index 的時候指定索引壓縮和解壓縮。
SQL> alter index objects1_idx rebuild nocompress; Index altered. SQL> alter index objects1_idx rebuild compress; Index altered.注：壓縮也是會引入存儲開銷的，只是很多時候壓縮節(jié)省的空間比壓縮需要的存儲開銷更大，所以壓縮以后整體的存儲開銷減小了。 compress 后面接的數(shù)字表示的是prefix 的深度，也就是需要用來壓縮的columns 的數(shù)量。
####4.4.6 順序索引
The DESC keyword on the CREATE INDEX statement is no longer ignored. It specifies that the index should be created in descending order. Indexes on character data are created in descending order of the character values in the database character set. Neither this, nor the ASC keyword, may be specified for a domain index. DESC cannot be specified for a bitmapped index.:
# would benefit from an index like this: CREATE INDEX c_id_desc ON Citites(city_id DESC) SELECT * FROM Cities ORDER BY city_id DESC# would benefit from an index like this: CREATE INDEX f_miles_desc on Flights(miles DESC) SELECT MAX(miles) FROM Flight# would benefit from an index like this: CREATE INDEX arrival_time_desc ON Flights(dest_airport, arrive_time DESC) SELECT * FROM Flights WHERE dest_airport = 'LAX' ORDER BY ARRIVAL DESC SQL> create table t_objects as 2 select object_name, object_id, created, owner3 from all_objects;SQL> select count(*) from t_objects;COUNT(*) ----------74101#創(chuàng)建升序索引 SQL> create index t_idx_1 on t_objects (object_name, owner); ---the usual index.SQL> select index_name, index_type from user_indexes where table_name = 'T_OBJECTS';INDEX_NAME INDEX_TYPE -------------------- --------------- T_IDX_1 NORMALSQL> select index_name, table_name, column_name, descend from user_ind_columns where index_name = 'T_IDX_1';INDEX_NAME TABLE_NAME COLUMN_NAME DESC -------------------- -------------------- -------------------- ---- T_IDX_1 T_OBJECTS OBJECT_NAME ASC T_IDX_1 T_OBJECTS OWNER ASC#the database does not use descending indexes until you first analyze the index and the table on which the index is defined SQL> select * from t_objects2 where object_name between 'Y' and 'Z'3 order by object_name asc, owner asc;-------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1004 | 43172 | 141 (2)| 00:00:02 | | 1 | SORT ORDER BY | | 1004 | 43172 | 141 (2)| 00:00:02 | |* 2 | TABLE ACCESS FULL| T_OBJECTS | 1004 | 43172 | 140 (1)| 00:00:02 | --------------------------------------------------------------------------------SQL> analyze table t_objects2 compute statistics3 for all columns4 for all indexes;Table analyzed.SQL> select * from t_objectswhere object_name between 'Y' and 'Z'order by object_name asc, owner asc;----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 82 | 3280 | 43 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T_OBJECTS | 82 | 3280 | 43 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | T_IDX_1 | 82 | | 3 (0)| 00:00:01 | -----------------------------------------------------------------------------------------SQL> select * from t_objectswhere object_name between 'Y' and 'Z'order by object_name desc, owner desc;------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 82 | 3280 | 43 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | T_OBJECTS | 82 | 3280 | 43 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN DESCENDING| T_IDX_1 | 82 | | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------------#創(chuàng)建降序索引 SQL> create index t_inx_1 on t_objects(object_name desc, owner desc);Index created.SQL> select index_name, table_name, column_name, descend from user_ind_columns where index_name = 'T_INX_1';INDEX_NAME TABLE_NAME COLUMN_NAME DESC -------------------- -------------------- -------------------- ---- T_INX_1 T_OBJECTS SYS_NC00005$ DESC T_INX_1 T_OBJECTS SYS_NC00006$ DESCSQL> select * from t_objectswhere object_name between 'Y' and 'Z'order by object_name asc, owner asc;------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 82 | 3280 | 29 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | T_OBJECTS | 82 | 3280 | 29 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN DESCENDING| T_INX_1 | 47 | | 5 (0)| 00:00:01 | ------------------------------------------------------------------------------------------SQL> select * from t_objectswhere object_name between 'Y' and 'Z'order by object_name desc, owner desc;----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 82 | 3280 | 29 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T_OBJECTS | 82 | 3280 | 29 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | T_INX_1 | 47 | | 5 (0)| 00:00:01 | -----------------------------------------------------------------------------------------轉載于:https://my.oschina.net/wangbinbin0326/blog/490519
總結
以上是生活随笔為你收集整理的Oracle 基础篇 --- 索引选项的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： [转]EntityFramework走马
- 下一篇： 关于xcode6打包以及上线前企业部署测