Oracle 分區(qū)索引介紹和實(shí)例演示
分區(qū)索引(或索引分區(qū))主要是針對(duì)分區(qū)表而言的。隨著數(shù)據(jù)量的不斷增長(zhǎng)，普通的堆表需要轉(zhuǎn)換到分區(qū)表，其索引呢，則對(duì)應(yīng)的轉(zhuǎn)換到分區(qū)索引。分區(qū)索引的好處是顯而易見的。就是簡(jiǎn)單地把一個(gè)索引分成多個(gè)片斷，在獲取所需數(shù)據(jù)時(shí)，只需要訪問更小的索引片斷(塊)即可實(shí)現(xiàn)。同時(shí)把分區(qū)放在不同的表空間可以提高分區(qū)的可用性和可靠性。本文主要描述了分區(qū)索引的相關(guān)特性并給出演示示例。
1、分區(qū)索引的相關(guān)概念
a、分區(qū)索引的幾種方式：表被分區(qū)而索引未被分區(qū)；表未被分區(qū)，而索引被分區(qū)；表和索引都被分區(qū)
b、分區(qū)索引可以分為本地分區(qū)索引以及全局分區(qū)索引
本地分區(qū)索引：
本地分區(qū)索引信息的存放依賴于父表分區(qū)。也就是說對(duì)于本地索引一定是基于分區(qū)表創(chuàng)建的。
缺省情況下，創(chuàng)建本地索引時(shí)，如未指定索引存放表空間，會(huì)自動(dòng)將本地索引存放到數(shù)據(jù)所在分區(qū)定義時(shí)的表空間。
本地索引的分區(qū)機(jī)制和表的分區(qū)機(jī)制一樣，本地索引可以是是B樹索引或位圖索引。
本地索引是對(duì)單個(gè)分區(qū)的，每個(gè)分區(qū)索引只指向一個(gè)表分區(qū)，為對(duì)等分區(qū)。
本地索引支持分區(qū)獨(dú)立性，因此對(duì)于這些單獨(dú)的分區(qū)增加，截取，刪除，分割，脫機(jī)等處理無需同時(shí)刪除或重建。
本地索引多應(yīng)用于數(shù)據(jù)倉庫環(huán)境中。
全局分區(qū)索引：
全局分區(qū)索引時(shí)分區(qū)表和全局索引的分區(qū)機(jī)制不一樣，在創(chuàng)建時(shí)必須定義分區(qū)鍵的范圍和值。
全局分區(qū)索引在創(chuàng)建時(shí)應(yīng)指定Global關(guān)鍵字且全局分區(qū)索引只能是B樹索引。
全局索引可以分區(qū)，也可以是不分區(qū)索引，全局索引必須是前綴索引，即索引列必須包含分區(qū)鍵。
全局索引分區(qū)中，一個(gè)分區(qū)索引能指向n個(gè)表分區(qū)，同時(shí)，一個(gè)表分區(qū)，也可能指向n個(gè)索引分區(qū)。
默認(rèn)情況下全局索引對(duì)于分區(qū)增加，截取，刪除，分割等都必須重建或修改時(shí)指定update global indexs。
全局分區(qū)索引只按范圍或者散列hash分區(qū)。
全局分區(qū)索引多應(yīng)用于oltp系統(tǒng)中。
c、有前綴索引和無前綴索引
本地和全局分區(qū)索引又分為兩個(gè)子類型即有前綴索引和無前綴索引。
前綴和非前綴索引都可以支持索引分區(qū)消除，前提是查詢的條件中包含索引分區(qū)鍵。
有前綴索引:
有前綴索引包含了分區(qū)鍵，即分區(qū)鍵列被包含在索引中。
有前綴索引支持本地分區(qū)索引以及全局分區(qū)索引。
無前綴索引：
無前綴索引即沒有把分區(qū)鍵的前導(dǎo)列作為索引的前導(dǎo)列。
無前綴索引僅僅支持本地分區(qū)索引。
2、本地分區(qū)索引演示
--環(huán)境
SQL> select * from v$version where rownum<2;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
SQL> create user leshami identified by xxx;
SQL> grant dba to leshami;
--創(chuàng)建演示需要用到的表空間
SQL> create tablespace tbs_tmp datafile '/u02/database/SYBO2/oradata/tbs_tmp.dbf' size 10m autoextend on;
SQL> alter user leshami default tablespace tbs_tmp;
SQL> create tablespace tbs1 datafile '/u02/database/SYBO2/oradata/tbs1.dbf' size 10m autoextend on;
SQL> create tablespace tbs2 datafile '/u02/database/SYBO2/oradata/tbs2.dbf' size 10m autoextend on;
SQL> create tablespace tbs3 datafile '/u02/database/SYBO2/oradata/tbs3.dbf' size 10m autoextend on;
SQL> create tablespace idx1 datafile '/u02/database/SYBO2/oradata/idx1.dbf' size 10m autoextend on;
SQL> create tablespace idx2 datafile '/u02/database/SYBO2/oradata/idx2.dbf' size 10m autoextend on;
SQL> create tablespace idx3 datafile '/u02/database/SYBO2/oradata/idx3.dbf' size 10m autoextend on;
SQL> conn leshami/xxx
-- 創(chuàng)建一個(gè)lookup表
CREATE TABLE lookup (
id NUMBER(10),
description VARCHAR2(50)
);
--添加主鍵約束
ALTER TABLE lookup ADD (
CONSTRAINT lookup_pk PRIMARY KEY (id)
);
--插入數(shù)據(jù)
INSERT INTO lookup (id, description) VALUES (1, 'ONE');
INSERT INTO lookup (id, description) VALUES (2, 'TWO');
INSERT INTO lookup (id, description) VALUES (3, 'THREE');
COMMIT;
CREATE TABLE big_table (
id NUMBER(10),
created_date DATE,
lookup_id NUMBER(10),
data VARCHAR2(50)
)
PARTITION BY RANGE (created_date)
(PARTITION big_table_2012 VALUES LESS THAN (TO_DATE('01/01/2013', 'DD/MM/YYYY')) tablespace tbs1,
PARTITION big_table_2013 VALUES LESS THAN (TO_DATE('01/01/2014', 'DD/MM/YYYY')) tablespace tbs2,
PARTITION big_table_2014 VALUES LESS THAN (MAXVALUE)tablespace tbs3 ) ;
--填充數(shù)據(jù)到分區(qū)表
DECLARE
l_lookup_id lookup.id%TYPE;
l_create_date DATE;
BEGIN
FOR i IN 1 .. 10000 LOOP
IF MOD(i, 3) = 0 THEN
l_create_date := ADD_MONTHS(SYSDATE, -24);
l_lookup_id := 2;
ELSIF MOD(i, 2) = 0 THEN
l_create_date := ADD_MONTHS(SYSDATE, -12);
l_lookup_id := 1;
ELSE
l_create_date := SYSDATE;
l_lookup_id := 3;
END IF;
INSERT INTO big_table (id, created_date, lookup_id, data)
VALUES (i, l_create_date, l_lookup_id, 'This is some data for ' || i);
END LOOP;
COMMIT;
END;
/
--未指定索引分區(qū)及存儲(chǔ)表空間情形下創(chuàng)建索引
SQL> CREATE INDEX bita_created_date_i ON big_table(created_date) LOCAL;
Index created.
SQL> select index_name, partitioning_type, partition_count from user_part_indexes;
INDEX_NAME PARTITI PARTITION_COUNT
------------------------------ ------- ---------------
BITA_CREATED_DATE_I RANGE 3
--Author : Leshami
--從下面的查詢可知，索引直接存放到分表表對(duì)應(yīng)的表空間
SQL> select partition_name, high_value, tablespace_name from user_ind_partitions;
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
------------------------------ ---------------------------------------- ------------------------------
BIG_TABLE_2014 MAXVALUE TBS3
BIG_TABLE_2013 TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-M TBS2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
BIG_TABLE_2012 TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-M TBS1
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
--刪除索引
SQL> drop index bita_created_date_i;
--指定索引分區(qū)名表空間名創(chuàng)建索引
SQL> CREATE INDEX bita_created_date_i
2 ON big_table (created_date)
3 LOCAL (
4 PARTITION idx_2012 TABLESPACE idx1,
5 PARTITION idx_2013 TABLESPACE idx2,
6 PARTITION idx_2014 TABLESPACE idx3)
7 PARALLEL 3;
Index created.
SQL> select partition_name, high_value, tablespace_name from user_ind_partitions;
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
------------------------------ ---------------------------------------- ------------------------------
IDX_2014 MAXVALUE IDX3
IDX_2013 TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-M IDX2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
IDX_2012 TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-M IDX1
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SQL> select * from big_table where rownum<2;
ID CREATED_ LOOKUP_ID DATA
---------- -------- ---------- --------------------------------------------------
1413 20120625 2 This is some data for 1413
--查看local index是否被使用，從下面的執(zhí)行計(jì)劃中可知，索引被使用，支持分區(qū)消除
SQL> set autot trace exp;
SQL> select * from big_table where created_date=to_date('20120625','yyyymmdd');
Execution Plan
----------------------------------------------------------
Plan hash value: 2556877094
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 41 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 41 | 2 (0)| 00:00:01 | 1 | 1 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| BIG_TABLE | 1 | 41 | 2 (0)| 00:00:01 | 1 | 1 |
|* 3 | INDEX RANGE SCAN | BITA_CREATED_DATE_I | 1 | | 1 (0)| 00:00:01 | 1 | 1 |
--------------------------------------------------------------------------------------------------------------------------
3、全局分區(qū)索引演示
--為表添加主鍵
SQL> ALTER TABLE big_table ADD (
2 CONSTRAINT big_table_pk PRIMARY KEY (id)
3 );
Table altered.
SQL> select index_name,index_type,tablespace_name,global_stats,partitioned
2 from user_indexes where index_name='BIG_TABLE_PK';
INDEX_NAME INDEX_TYPE TABLESPACE_NAME GLO PAR
------------------------------ --------------------------- ------------------------------ --- ---
BIG_TABLE_PK NORMAL TBS_TMP YES NO
SQL> set autot trace exp;
SQL> select * from big_table where id=1412;
Execution Plan
----------------------------------------------------------
Plan hash value: 2662411593
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 62 | 2 (0)| 00:00:01 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| BIG_TABLE | 1 | 62 | 2 (0)| 00:00:01 | ROWID | ROWID |
|* 2 | INDEX UNIQUE SCAN | BIG_TABLE_PK | 1 | | 1 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------------
--如上，在其執(zhí)行計(jì)劃中，Pstart與Pstop都為ROWID
--出現(xiàn)了GLOBAL INDEX ROWID，我們添加主鍵時(shí)并未指定Global，但其執(zhí)行計(jì)劃表明執(zhí)行了全局索引訪問
--這個(gè)地方有待證實(shí)，對(duì)于分區(qū)表，非分區(qū)鍵上的主鍵或唯一索引是否一定是全局索引
SQL> drop index bita_created_date_i;
--下面創(chuàng)建全局索引，創(chuàng)建時(shí)需要指定分區(qū)鍵的范圍和值
SQL> CREATE INDEX bita_created_date_i
ON big_table (created_date)
GLOBAL PARTITION BY RANGE (created_date)
(
PARTITION
idx_1 VALUES LESS THAN (TO_DATE ('01/01/2013', 'DD/MM/YYYY'))
TABLESPACE idx1,
PARTITION
idx_2 VALUES LESS THAN (TO_DATE ('01/01/2014', 'DD/MM/YYYY'))
TABLESPACE idx2,
PARTITION idx_3 VALUES LESS THAN (maxvalue) TABLESPACE idx3);
SQL> select index_name, partitioning_type, partition_count,locality from user_part_indexes;
INDEX_NAME PARTITI PARTITION_COUNT LOCALI
------------------------------ ------- --------------- ------
BITA_CREATED_DATE_I_G RANGE 3 GLOBAL
SQL> select partition_name, high_value, tablespace_name from user_ind_partitions;
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
------------------------------ --------------------- ------------------------------
IDX_1 TO_DATE(' 2013-01-01 IDX1
IDX_2 TO_DATE(' 2014-01-01 IDX2
IDX_3 MAXVALUE IDX3
--下面是其執(zhí)行計(jì)劃，可以看出支持分區(qū)消除
SQL> set autot trace exp;
SQL> select * from big_table where created_date=to_date('20130625','yyyymmdd');
Execution Plan
----------------------------------------------------------
Plan hash value: 1378264218
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 41 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 41 | 2 (0)| 00:00:01 | 2 | 2 |
| 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| BIG_TABLE | 1 | 41 | 2 (0)| 00:00:01 | 2 | 2 |
|* 3 | INDEX RANGE SCAN | BITA_CREATED_DATE_I | 1 | | 1 (0)| 00:00:01 | 2 | 2 |
---------------------------------------------------------------------------------------------------------------------------
--以下為范圍查詢，Pstart為1，Pstop為2，同樣支持分區(qū)消除
SQL> select * from big_table
2 where created_date>=to_date('20120625','yyyymmdd') and created_date<=to_date('20130625','yyyymmdd');
Execution Plan
----------------------------------------------------------
Plan hash value: 213633793
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3334 | 133K| 14 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ITERATOR| | 3334 | 133K| 14 (0)| 00:00:01 | 1 | 2 |
|* 2 | TABLE ACCESS FULL | BIG_TABLE | 3334 | 133K| 14 (0)| 00:00:01 | 1 | 2 |
------------------------------------------------------------------------------------------------------
相關(guān)文章
這篇文章介紹了Oracle在PL/SQL中使用存儲(chǔ)過程的方法，文中通過示例代碼介紹的非常詳細(xì)。對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2022-05-05
這篇文章介紹了oracle重置序列的方法，文中通過示例代碼介紹的非常詳細(xì)。對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2022-02-02
oracle幾個(gè)常用的SQL2009-11-11
oracle基礎(chǔ)教程之多表關(guān)聯(lián)查詢
在實(shí)際開發(fā)中每個(gè)表的信息都不是獨(dú)立的,而是若干個(gè)表之間存在一定的聯(lián)系,如果用戶查詢某一個(gè)表的信息時(shí),可能需要查詢關(guān)聯(lián)表的信息,這就是多表關(guān)聯(lián)查詢,這篇文章主要給大家介紹了關(guān)于oracle基礎(chǔ)教程之多表關(guān)聯(lián)查詢的相關(guān)資料,需要的朋友可以參考下2023-12-12
這篇文章主要介紹了CentOS7下Oracle19c rpm安裝過程,包含環(huán)境配置、依賴安裝問題，對(duì)Oracle19c rpm安裝相關(guān)知識(shí)感興趣的朋友一起看看吧2021-07-07
這篇文章主要為大家詳細(xì)介紹了ora-00119和ora-00132問題的解決方法，感興趣的小伙伴們可以參考一下2016-07-07
存儲(chǔ)過程是在大型數(shù)據(jù)庫系統(tǒng)中存儲(chǔ)過程在數(shù)據(jù)庫中經(jīng)過第一次編譯后就不需要再次編譯，用戶通過指定存儲(chǔ)過程的名字并給出參數(shù)來，通過本篇文章帶領(lǐng)大家去學(xué)習(xí)oracle存儲(chǔ)過程常用的技巧，感興趣的朋友一起來學(xué)習(xí)吧2015-08-08
這篇文章主要介紹了ORACLE如何批量插入(Insert),具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-12-12
Oracle查詢表里的重復(fù)數(shù)據(jù)方法
這篇文章主要介紹了Oracle查詢表里的重復(fù)數(shù)據(jù)方法,需要的朋友可以參考下2017-05-05
這篇文章主要介紹了oracle數(shù)據(jù)庫的刪除方法詳解，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-08-08