Oracle 分区索引介绍和实例演示(2)

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