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

--下面创建全局索引，创建时需要指定分区键的范围和值
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 |
---------------------------------------------------------------------------------------------------------------------------