[分区表] 局部分区索引

# 表分区与局部索引分区是 1 对 1 的关系, 局部索引不能跨分区去索引其他分区数据
# 局部前缀索引, 表分区键在索引定义的前几列上
# 局部非前缀索引, 表分区键不在索引定义的前几列上, 或索引列根本不包含表分区键
# 利用分区消除, 使查询操作高效

1. 分区表
CREATE TABLE partitioned_table(a int,b int,data char(20))
PARTITION BY RANGE (a)
(
PARTITION part_1 VALUES LESS THAN(2) tablespace p1,
PARTITION part_2 VALUES LESS THAN(3) tablespace p2
)

2. 创建前缀局部索引
create index local_prefixed on partitioned_table (a,b) local;

3. 创建非前缀局部索引
create index local_nonprefixed on partitioned_table (b) local;

4. 插入测试数据
insert into partitioned_table select mod(rownum-1,2)+1,rownum,'x' from all_objects;

5. 收集统计信息
begin dbms_stats.gather_table_stats (user,'PARTITIONED_TABLE',cascade=>TRUE);end;

6. 脱机 p2 表空间
alter tablespace p2 offline;

set autotrace on explain

-- 利用前缀局部索引查询, 消除 p2 分区, 查询成功
select * from partitioned_table where a = 1 and b = 1;
         A          B DATA
---------- ---------- ----------------------------------------
         1          1 x

Execution Plan
----------------------------------------------------------
Plan hash value: 1622054381

-------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                   |     1 |    28 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE            |                   |     1 |    28 |     2   (0)| 00:00:01 |     1 |     1 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TABLE |     1 |    28 |     2   (0)| 00:00:01 |     1 |     1 |
|*  3 |    INDEX RANGE SCAN                | LOCAL_PREFIXED    |     1 |       |     1   (0)| 00:00:01 |     1 |     1 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("A"=1 AND "B"=1)

7. 因为不确定 b=1 的值在哪个分区, 所以 p1 和 p2 的索引都要查看, 报错
select * from partitioned_table where b = 1;
ERROR:
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: 'D:\ORACLE\ORADATA\DEMO\DATAFILES\P2.DBF'

Execution Plan
----------------------------------------------------------
Plan hash value: 440752652

----------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                   |     1 |    28 |     4   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ALL               |                   |     1 |    28 |     4   (0)| 00:00:01 |     1 |     2 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TABLE |     1 |    28 |     4   (0)| 00:00:01 |     1 |     2 |
|*  3 |    INDEX RANGE SCAN                | LOCAL_NONPREFIXED |     1 |       |     3   (0)| 00:00:01 |     1 |     2 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("B"=1)

8. 非前缀索引也可以利用分区消除, 主要要保证谓词中存在可以分区消除的键
drop index local_prefixed;
select * from partitioned_table where a = 1 and b = 1;
         A          B DATA
---------- ---------- ----------------------------------------
         1          1 x

Execution Plan
----------------------------------------------------------
Plan hash value: 904532382

----------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                   |     1 |    28 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE            |                   |     1 |    28 |     2   (0)| 00:00:01 |     1 |     1 |
|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TABLE |     1 |    28 |     2   (0)| 00:00:01 |     1 |     1 |
|*  3 |    INDEX RANGE SCAN                | LOCAL_NONPREFIXED |     1 |       |     1   (0)| 00:00:01 |     1 |     1 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("A"=1)
   3 - access("B"=1)