欧美激情视频一区二区三区在线播放 ,懂色午夜精品久久久久久无码小说 ,亚洲欧美国产日韩制服bt

主頁 > 知識庫 > Oracle 分區索引介紹和實例演示

Oracle 分區索引介紹和實例演示

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

1、分區索引的相關概念

a、分區索引的幾種方式：表被分區而索引未被分區；表未被分區，而索引被分區；表和索引都被分區
b、分區索引可以分為本地分區索引以及全局分區索引

本地分區索引：

   本地分區索引信息的存放依賴于父表分區。也就是說對于本地索引一定是基于分區表創建的。
   缺省情況下，創建本地索引時，如未指定索引存放表空間，會自動將本地索引存放到數據所在分區定義時的表空間。
   本地索引的分區機制和表的分區機制一樣，本地索引可以是是B樹索引或位圖索引。
   本地索引是對單個分區的，每個分區索引只指向一個表分區，為對等分區。
   本地索引支持分區獨立性，因此對于這些單獨的分區增加，截取，刪除，分割，脫機等處理無需同時刪除或重建。
   本地索引多應用于數據倉庫環境中。

全局分區索引：

   全局分區索引時分區表和全局索引的分區機制不一樣，在創建時必須定義分區鍵的范圍和值。
   全局分區索引在創建時應指定Global關鍵字且全局分區索引只能是B樹索引。
   全局索引可以分區，也可以是不分區索引，全局索引必須是前綴索引，即索引列必須包含分區鍵。
   全局索引分區中，一個分區索引能指向n個表分區，同時，一個表分區，也可能指向n個索引分區。
   默認情況下全局索引對于分區增加，截取，刪除，分割等都必須重建或修改時指定update global indexs。
   全局分區索引只按范圍或者散列hash分區。
   全局分區索引多應用于oltp系統中。

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

本地和全局分區索引又分為兩個子類型即有前綴索引和無前綴索引。
前綴和非前綴索引都可以支持索引分區消除，前提是查詢的條件中包含索引分區鍵。
有前綴索引:
   有前綴索引包含了分區鍵，即分區鍵列被包含在索引中。
   有前綴索引支持本地分區索引以及全局分區索引。
無前綴索引：
   無前綴索引即沒有把分區鍵的前導列作為索引的前導列。
   無前綴索引僅僅支持本地分區索引。

2、本地分區索引演示

復制代碼代碼如下:

--環境
SQL> select * from v$version where rownum2;

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;

--創建演示需要用到的表空間
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

-- 創建一個lookup表
CREATE TABLE lookup (
id NUMBER(10),
description VARCHAR2(50)
);

--添加主鍵約束
ALTER TABLE lookup ADD (
CONSTRAINT lookup_pk PRIMARY KEY (id)
);

--插入數據
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 ) ;

--填充數據到分區表
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;
/

--未指定索引分區及存儲表空間情形下創建索引
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

--從下面的查詢可知，索引直接存放到分表表對應的表空間
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;

--指定索引分區名表空間名創建索引
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 rownum2;

ID CREATED_ LOOKUP_ID DATA
---------- -------- ---------- --------------------------------------------------
1413 20120625 2 This is some data for 1413

--查看local index是否被使用，從下面的執行計劃中可知，索引被使用，支持分區消除
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、全局分區索引演示

復制代碼代碼如下:

--為表添加主鍵
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 |       |       |
-------------------------------------------------------------------------------------------------------------------
--如上，在其執行計劃中，Pstart與Pstop都為ROWID
--出現了GLOBAL INDEX ROWID，我們添加主鍵時并未指定Global，但其執行計劃表明執行了全局索引訪問
--這個地方有待證實，對于分區表，非分區鍵上的主鍵或唯一索引是否一定是全局索引

SQL> drop index bita_created_date_i;

--下面創建全局索引，創建時需要指定分區鍵的范圍和值
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

--下面是其執行計劃，可以看出支持分區消除
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，同樣支持分區消除
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 |
------------------------------------------------------------------------------------------------------

您可能感興趣的文章: