1.本文简介
本文主要记录分区索引学习过程。对于分区表本文中不做介绍
2.分区索引
通常情况下,分区索引和分区表是共存的,但是不是必须的。即分区索引可以在分区表上建,也可以在非分区表上建。
2.1分区表的好处
(还没时间进行实践测试,后续测试完成再更新文章)
分区索引与非分区索引相比,具有许多好处。使用分区索引的关键好处如下。
性能方面的优势
1)通过DML操作载人数据
2) 通过DDL操作载入数据
3) 用SELECT语句查询数据
维护方面的好处
1) 重建索引。
2) 在分区一级把索引设置为不可用或不可见
2.2 分区索引的分类
分区索引可以分为本地分区索引和全局分区 索引
分区索引最常见的配置是在分区表上创建本地分区索引,只是因为这种配置的整体优势一般大于分区表上的全局分区索引和非分区索引
2.2.1 本地分区索引
本地分区索引只能存在于分区表。
2.2.2 全局分区索引
全局分区的索引既可以在分区表上创建,也可以在非分区表上创建。还可以在分区表上创建非分区索引。
3. 本地分区索引
本地索引分区只能在分区表上建。使用和分区表分区键同样的索引分区列(这并不需要在创建索引的DDL中特意指定,默认即和分区表相同的分区列)。
本地索引分区有如下优点:
1)如果只有一个分区需要维护,则只有一个本地索引受影响。
2)支持分区独立性。
3)只有本地索引能够支持单一分区的装入和卸载。
4) 表分区和各自的本地索引可以同时恢复。
5)本地索引可以单独重建。
6)位图索引仅由本地索引支持。
3.1 本地分区索引创建
1)最基本的本地分区索引创建
CREATE INDEX employees ON employees_part(hire_date) TABLESPACE empindex_S LOCAL;
NOTE:索引列可以和分区表分区键一致也可以不一致,可以采用单列或者多列索引列,但是索引默认的索引分区键都是和分区表分区键一致。
创建一张以 month_id 为分区键的列表分区表
CREATE TABLE P_TEST
(
column1 VARCHAR2(30)
,column2 VARCHAR2(30)
,MONTH_ID NUMBER(2, 0)
)
PARTITION BY LIST (MONTH_ID)
(
PARTITION M_01 VALUES (1)
,PARTITION M_02 VALUES (2)
,PARTITION M_03 VALUES (3)
,PARTITION M_04 VALUES (4)
,PARTITION M_05 VALUES (5)
,PARTITION M_06 VALUES (6)
,PARTITION M_07 VALUES (7)
,PARTITION M_08 VALUES (8)
,PARTITION M_09 VALUES (9)
,PARTITION M_10 VALUES (10)
,PARTITION M_11 VALUES (11)
,PARTITION M_12 VALUES (12)
);
例子1:创建索引列和表分区键一样的分区索引
create index p_ind1 on p_test (MONTH_ID) local ;
索引的分区情况
set line 200 pages 150
col INDEX_OWNER for a10
col index_name for a10
col COLUMN_NAME for a25
SELECT INDEX_OWNER,INDEX_NAME,TABLE_NAME,COLUMN_NAME,DESCEND FROM DBA_IND_COLUMNS WHERE INDEX_NAME = upper('p_ind1');
INDEX_OWNE INDEX_NAME TABLE_NAME COLUMN_NAME DESC
---------- ---------- ------------------------------ ------------------------- ----
SYSTEM P_IND1 P_TEST MONTH_ID ASC
结论:索引列和创建DDL中指定一致。
select owner,name,object_type,COLUMN_NAME from DBA_PART_KEY_COLUMNS where name=upper('p_ind1');
OWNER NAME OBJEC COLUMN_NAME
------------------------------ ------------------------------ ----- -------------------------
SYSTEM P_IND1 INDEX MONTH_ID
结论:索引分区键默认和分区表分区键一致。
col INDEX_OWNER for a10
col index_name for a10
SELECT INDEX_OWNER,INDEX_NAME,PARTITION_NAME,HIGH_VALUE FROM dba_IND_PARTITIONS WHERE INDEX_NAME = upper('p_ind1');
INDEX_OWNE INDEX_NAME PARTITION_NAME HIGH_VALUE
---------- ---------- -------------------- ---------------------
SYSTEM P_IND1 M_01 1
SYSTEM P_IND1 M_02 2
SYSTEM P_IND1 M_03 3
SYSTEM P_IND1 M_04 4
SYSTEM P_IND1 M_05 5
SYSTEM P_IND1 M_06 6
SYSTEM P_IND1 M_07 7
SYSTEM P_IND1 M_08 8
SYSTEM P_IND1 M_09 9
SYSTEM P_IND1 M_10 10
SYSTEM P_IND1 M_11 11
SYSTEM P_IND1 M_12 12
结论:与分区表拥有相同的分区形式。
例子2:创建索引列和表分区键不一样的组合分区索引
create index p_ind2 on p_test (column1,column2) local ;
***索引列情况
set line 200 pages 150
col INDEX_OWNER for a10
col index_name for a10
col COLUMN_NAME for a25
SELECT INDEX_OWNER,INDEX_NAME,TABLE_NAME,DESCEND FROM DBA_IND_COLUMNS WHERE INDEX_NAME = upper('p_ind2');
INDEX_OWNE INDEX_NAME TABLE_NAME COLUMN_NAME DESC
---------- ---------- ------------------------------ ------------------------- ----
SYSTEM P_IND2 P_TEST COLUMN1 ASC
SYSTEM P_IND2 P_TEST COLUMN2 ASC
结论:索引列和创建DDL中指定一致。
***索引分区键情况
select owner,name,object_type,COLUMN_NAME from DBA_PART_KEY_COLUMNS where name=upper('p_ind1');
OWNER NAME OBJEC COLUMN_NAME
------------------------------ ------------------------------ ----- -------------------------
SYSTEM P_IND1 INDEX MONTH_ID
结论:索引分区键默认和分区表分区键一致,不需要特意指定。
***索引分区情况
col INDEX_OWNER for a10
col index_name for a10
SELECT INDEX_OWNER,INDEX_NAME,PARTITION_NAME,HIGH_VALUE FROM dba_IND_PARTITIONS WHERE INDEX_NAME = upper('p_ind2');
INDEX_OWNE INDEX_NAME PARTITION_NAME HIGH_VALUE
---------- ---------- ------------------------------ ---------------
SYSTEM P_IND2 M_01 1
SYSTEM P_IND2 M_02 2
SYSTEM P_IND2 M_03 3
SYSTEM P_IND2 M_04 4
SYSTEM P_IND2 M_05 5
SYSTEM P_IND2 M_06 6
SYSTEM P_IND2 M_07 7
SYSTEM P_IND2 M_08 8
SYSTEM P_IND2 M_09 9
SYSTEM P_IND2 M_10 10
SYSTEM P_IND2 M_11 11
SYSTEM P_IND2 M_12 12
结论:与分区表拥有相同的分区形式。
文章持续更新。。。