Oracle 分區(qū)索引介紹和實例演示

當前位置：首頁 > 范文|應(yīng)用文 > IT技術(shù)專欄 > 數(shù)據(jù)庫

來源：易賢網(wǎng) 閱讀：1127 次日期：2014-10-14 10:57:25

溫馨提示：易賢網(wǎng)小編為您整理了“Oracle 分區(qū)索引介紹和實例演示”,方便廣大網(wǎng)友查閱！

分區(qū)索引(或索引分區(qū))主要是針對分區(qū)表而言的。隨著數(shù)據(jù)量的不斷增長，普通的堆表需要轉(zhuǎn)換到分區(qū)表，其索引呢，則對應(yīng)的轉(zhuǎn)換到分區(qū)索引。分區(qū)索引的好處是顯而易見的。就是簡單地把一個索引分成多個片斷，在獲取所需數(shù)據(jù)時，只需要訪問更小的索引片斷(塊)即可實現(xiàn)。同時把分區(qū)放在不同的表空間可以提高分區(qū)的可用性和可靠性。本文主要描述了分區(qū)索引的相關(guān)特性并給出演示示例。

1、分區(qū)索引的相關(guān)概念

a、分區(qū)索引的幾種方式：表被分區(qū)而索引未被分區(qū)；表未被分區(qū)，而索引被分區(qū)；表和索引都被分區(qū)

b、分區(qū)索引可以分為本地分區(qū)索引以及全局分區(qū)索引

本地分區(qū)索引：

本地分區(qū)索引信息的存放依賴于父表分區(qū)。也就是說對于本地索引一定是基于分區(qū)表創(chuàng)建的。

缺省情況下，創(chuàng)建本地索引時，如未指定索引存放表空間，會自動將本地索引存放到數(shù)據(jù)所在分區(qū)定義時的表空間。

本地索引的分區(qū)機制和表的分區(qū)機制一樣，本地索引可以是是B樹索引或位圖索引。

本地索引是對單個分區(qū)的，每個分區(qū)索引只指向一個表分區(qū)，為對等分區(qū)。

本地索引支持分區(qū)獨立性，因此對于這些單獨的分區(qū)增加，截取，刪除，分割，脫機等處理無需同時刪除或重建。

本地索引多應(yīng)用于數(shù)據(jù)倉庫環(huán)境中。

全局分區(qū)索引：

全局分區(qū)索引時分區(qū)表和全局索引的分區(qū)機制不一樣，在創(chuàng)建時必須定義分區(qū)鍵的范圍和值。

全局分區(qū)索引在創(chuàng)建時應(yīng)指定Global關(guān)鍵字且全局分區(qū)索引只能是B樹索引。

全局索引可以分區(qū)，也可以是不分區(qū)索引，全局索引必須是前綴索引，即索引列必須包含分區(qū)鍵。

全局索引分區(qū)中，一個分區(qū)索引能指向n個表分區(qū)，同時，一個表分區(qū)，也可能指向n個索引分區(qū)。

默認情況下全局索引對于分區(qū)增加，截取，刪除，分割等都必須重建或修改時指定update global indexs。

全局分區(qū)索引只按范圍或者散列hash分區(qū)。

全局分區(qū)索引多應(yīng)用于oltp系統(tǒng)中。

c、有前綴索引和無前綴索引

本地和全局分區(qū)索引又分為兩個子類型即有前綴索引和無前綴索引。

前綴和非前綴索引都可以支持索引分區(qū)消除，前提是查詢的條件中包含索引分區(qū)鍵。

有前綴索引:

有前綴索引包含了分區(qū)鍵，即分區(qū)鍵列被包含在索引中。

有前綴索引支持本地分區(qū)索引以及全局分區(qū)索引。

無前綴索引：

無前綴索引即沒有把分區(qū)鍵的前導(dǎo)列作為索引的前導(dǎo)列。

無前綴索引僅僅支持本地分區(qū)索引。

2、本地分區(qū)索引演示

復(fù)制代碼代碼如下:

--環(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)建一個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ū)及存儲表空間情形下創(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

--從下面的查詢可知，索引直接存放到分表表對應(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í)行計劃中可知，索引被使用，支持分區(qū)消除

SQL> set autot trace exp;

SQL> select * from big_table where created_date=to_date('20120625','yyyymmdd');

Execution Plan

----------------------------------------------------------

Plan hash value: 2556877094

--------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------

| 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ū)索引演示

復(fù)制代碼代碼如下:

--為表添加主鍵

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

-------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------

| 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í)行計劃中，Pstart與Pstop都為ROWID

--出現(xiàn)了GLOBAL INDEX ROWID，我們添加主鍵時并未指定Global，但其執(zhí)行計劃表明執(zhí)行了全局索引訪問

--這個地方有待證實，對于分區(qū)表，非分區(qū)鍵上的主鍵或唯一索引是否一定是全局索引

SQL> drop index bita_created_date_i;

--下面創(chuàng)建全局索引，創(chuàng)建時需要指定分區(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í)行計劃，可以看出支持分區(qū)消除

SQL> set autot trace exp;

SQL> select * from big_table where created_date=to_date('20130625','yyyymmdd');

Execution Plan

----------------------------------------------------------

Plan hash value: 1378264218

---------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------------

| 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

------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------

| 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 |

------------------------------------------------------------------------------------------------------

更多信息請查看IT技術(shù)專欄

更多信息請查看數(shù)據(jù)庫

上一篇：Oracle 阻塞(blocking blocked)介紹和實例演示

下一篇：oracle通過行范圍查詢?nèi)?至10行并排序

易賢網(wǎng)手機網(wǎng)站地址：Oracle 分區(qū)索引介紹和實例演示

由于各方面情況的不斷調(diào)整與變化，易賢網(wǎng)提供的所有考試信息和咨詢回復(fù)僅供參考，敬請考生以權(quán)威部門公布的正式信息和咨詢?yōu)闇剩?/div>

相關(guān)閱讀數(shù)據(jù)庫

解決MongoDB 排序超過內(nèi)存限制的問題08月04日

Windows下MongoDb簡單配置教程08月04日

Mongodb實戰(zhàn)之全文搜索功能08月04日

MongoDB 管道的介紹及操作符實例08月04日

在Linux服務(wù)器中配置mongodb環(huán)境的步驟08月04日

mac下安裝和配置mongodb的步驟詳解08月04日

Mongo復(fù)制集同步驗證的實例詳解08月04日

MongoDB 中Limit與Skip的使用方法詳解08月04日

Oracle刪除數(shù)據(jù)報ORA 02292錯誤的巧妙解決方法08月04日

Oracle查詢最近幾天每小時歸檔日志產(chǎn)生數(shù)量的腳本寫法08月04日

Oracle查看表結(jié)構(gòu)命令詳解08月04日

Oracle 11g如何清理數(shù)據(jù)庫的歷史日志詳解08月04日

Oracle表中重復(fù)數(shù)據(jù)去重的方法實例詳解08月04日

WIN7下ORACLE10g服務(wù)端和客戶端的安裝圖文教程08月04日

Oracle查看表結(jié)構(gòu)的幾種方法示例代碼08月04日

pl/sql連接遠程oracle服務(wù)器的配置教程08月04日

IOS 數(shù)據(jù)庫升級數(shù)據(jù)遷移的實例詳解08月04日

mysql exists與not exists實例詳解08月04日

關(guān)于Windows10下解決MySQL5.5數(shù)據(jù)庫命令行中文亂碼問題08月04日

細說MySQL死鎖與日志二三事08月04日

易賢網(wǎng)移動網(wǎng)站

2025國考·省考課程試聽報名

報班類型
姓名
手機號
驗證碼

午夜国产狂喷潮在线观看|国产AⅤ精品一区二区久久|中文字幕AV中文字幕|国产看片高清在线