第20章 管理分区表和分区索引
使用分区表，将一个大表的数据分布到多个表分区段；使用分区索引，将一个大索引的数据分布到多个索引分区段；
一、建立分区表
执行SQL语句访问分区表时，服务器进程直接访问某个分区表段，而不需要访问整张表的所有数据，从而减少IO，提高系统性能；
Oracle提供了范围分区、散列分区（HASH分区）、列表分区、组合分区四种分区方法：
1、范围分区
例如：sales表年数据1000G，每个季度250G，使用4个分区表段存放四个季度数据，统计季度数据时，只用访问某一个表段即可；
（1）建立范围分区表
指定分区方法（RANGE）、分区列、列值范围；
alter session set nls_date_language=american;
alter session set nls_date_format='dd-mon-yyyy';
CREATE TABLE sales(
sale_id NUMBER(10),
order_id NUMBER(10),
customer_id NUMBER(10),
sale_amount NUMBER(10,2),
sale_date DATE
)PARTITION BY RANGE(sale_date) (
PARTITION p1 VALUES LESS THAN ('01-APR-2011') TABLESPACE USER01,
PARTITION p2 VALUES LESS THAN ('01-JUL-2011') TABLESPACE USER02,
PARTITION p4 VALUES LESS THAN ('01-OCT-2011') TABLESPACE USER03,
PARTITION p5 VALUES LESS THAN ('01-JAN-2012') TABLESPACE USER04
);
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USER01 ONLINE
USER03 ONLINE
UNDOTBS ONLINE
TEMP01 ONLINE
TEMP02 ONLINE
SQL> col name format a50;
SQL> select file#,status,enabled,name from v$datafile;
FILE# STATUS ENABLED NAME
---------- ------- ---------- --------------------------------------------------
1 SYSTEM READ WRITE D:\ORACLE_DATABASE\DEMO\SYSTEM01.DBF
2 ONLINE READ WRITE D:\ORACLE_DATABASE\DEMO\UNDOTBS1.DBF
3 ONLINE READ WRITE D:\ORACLE_DATABASE\DEMO\SYSAUX01.DBF
4 RECOVER READ WRITE D:\ORACLE_DATABASE\DEMO\USER01.DBF
5 ONLINE READ WRITE D:\ORACLE_DATABASE\DEMO\USER02.DBF
6 RECOVER READ WRITE D:\ORACLE_DATABASE\DEMO\UNDOTBS.DBF
7 ONLINE READ WRITE D:\ORACLE_DATABASE\DEMO\USER01_2.DBF
SQL> c/user03 include/user01 including
1* drop tablespace user01 including contents and datafiles
SQL> r
1* drop tablespace user01 including contents and datafiles
drop tablespace user01 including contents and datafiles
*
第 1 行出现错误:
ORA-12919: 不能删除默认永久表空间
SQL> alter tablespace user01 online;
alter tablespace user01 online
*
第 1 行出现错误:
ORA-01113: 文件 4 需要介质恢复
ORA-01110: 数据文件 4: 'D:\ORACLE_DATABASE\DEMO\USER01.DBF'
SQL> recover datafile 'D:\ORACLE_DATABASE\DEMO\USER01.DBF';
ORA-00279: 更改 360842 (在 01/04/2012 16:28:45 生成) 对于线程 1 是必需的
ORA-00289: 建议: F:\ORACLE_DATABASE\ARCHIVE\ARC00008_0771675870.001
ORA-00280: 更改 360842 (用于线程 1) 在序列 #8 中
指定日志: {=suggested | filename | AUTO | CANCEL}
已应用的日志。
完成介质恢复。
SQL> alter tablespace user01 online;
表空间已更改。
（2）插入数据
Oracle会根据分区列的列值范围自动把数据插入对应表分区；
SQL> insert into sales values (2011001001,2011000001,2011010001,2502.20,'25-JAN-2011');
已创建 1 行。
SQL> insert into sales values (2011001002,2011000011,2011040001,12502.20,'25-APR-2011');
已创建 1 行。
SQL> insert into sales values (2011001022,2011002011,2011090002,102.20,'21-SEP-2011');
已创建 1 行。
SQL> insert into sales values (2011001022,2011002011,2011120002,102.20,'21-DEC-2011');
已创建 1 行。
（3）查询表所有数据
SQL> select *from sales;
SALE_ID ORDER_ID CUSTOMER_ID SALE_AMOUNT SALE_DATE
---------- ---------- ----------- ----------- --------------
2011001001 2011000001 2011010001 2502.2 25-jan-2011
2011001002 2011000011 2011040001 12502.2 25-apr-2011
2011001022 2011002011 2011090002 102.2 21-sep-2011
2011001022 2011002011 2011120002 102.2 21-dec-2011
（4）查询某个分区的数据
SQL> select *from sales PARTITION(p1);
SALE_ID ORDER_ID CUSTOMER_ID SALE_AMOUNT SALE_DATE
---------- ---------- ----------- ----------- --------------
2011001001 2011000001 2011010001 2502.2 25-jan-2011
（5）在where条件中引用分区列
Oracle会根据条件列的取值自动扫描特定分区
（6）显示分区段信息
user_segments
2、散列分区（哈希分区）
按照Oracle提供的散列（HASH）函数计算列值数据，根据结算结果分区大表数据；
（1）建立哈希分区表
CREATE TABLE product(
product_id number(6),
description varchar2(50)
) PARTITION BY HASH(product_id) (
PARTITION p1 TABLESPACE user01,
PARTITION p2 TABLESPACE user02,
PARTITION p3 TABLESPACE user03,
PARTITION p4 TABLESPACE user04
);
（2）插入数据
Oracle自动根据内置Hash函数计算分区列数据，根据结果分布数据；
insert into product values(1,'pliphs');
insert into product values(2,'totoya');
insert into product values(3,'pliphss');
insert into product values(4,'pSliphs');
insert into product values(5,'pliFphs');
insert into product values(6,'pliSphs');
insert into product values(7,'pliFphs');
insert into product values(8,'pliGphs');
insert into product values(9,'pDSliphs');
insert into product values(10,'pliGGSDphs');
insert into product values(11,'pliGDphs');
insert into product values(12,'plDDiphs');
insert into product values(13,'pliDGHJphs');
（3）查询数据
SQL> select * from product;
SQL> select * from product where product_id=1;
（4）显示分区段信息
user_segments；
3、列表分区
（1）建立列表分区
CREATE TABLE sales_by_region(
sale_id number(10),
address varchar2(30)
)PARTITION BY LIST(address)(
PARTITION p1 VALUES ('北京','天津') TABLESPACE user01,
PARTITION p2 VALUES ('上海','重庆') TABLESPACE user02,
PARTITION p3 VALUES ('河南','河北') TABLESPACE user03,
PARTITION p4 VALUES ('广东','香港') TABLESPACE user04
);
4、组合分区
（1）范围、哈希组合分区
（a）建立范围、哈希组合分区表：
假设经常需要按季度统计、且按产品统计；
CREATE TABLE sales_product(
sale_id NUMBER(10),
product_id NUMBER(10),
sale_date DATE
)PARTITION BY RANGE(sale_date)
SUBPARTITION BY HASH(product_id) SUBPARTITIONS 4
STORE IN(user01,user02,user03,user04) (
PARTITION p1 VALUES LESS THAN ('01-APR-2011') TABLESPACE USER01,
PARTITION p2 VALUES LESS THAN ('01-JUL-2011') TABLESPACE USER02,
PARTITION p4 VALUES LESS THAN ('01-OCT-2011') TABLESPACE USER03,
PARTITION p5 VALUES LESS THAN ('01-JAN-2012') TABLESPACE USER04
);
（b）查看分区信息
SQL> select segment_name,partition_name,tablespace_name from user_segments where segment_name='SALES_PRODUCT';
SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME
--------------------------------------------------------------------------------- ------------------------------ -------
-----------------------
SALES_PRODUCT SYS_SUBP21 USER01
SALES_PRODUCT SYS_SUBP22 USER01
SALES_PRODUCT SYS_SUBP23 USER01
SALES_PRODUCT SYS_SUBP24 USER01
SALES_PRODUCT SYS_SUBP25 USER02
SALES_PRODUCT SYS_SUBP26 USER02
SALES_PRODUCT SYS_SUBP27 USER02
SALES_PRODUCT SYS_SUBP28 USER02
SALES_PRODUCT SYS_SUBP29 USER03
SALES_PRODUCT SYS_SUBP30 USER03
SALES_PRODUCT SYS_SUBP31 USER03
SALES_PRODUCT SYS_SUBP32 USER03
SALES_PRODUCT SYS_SUBP33 USER04
SALES_PRODUCT SYS_SUBP34 USER04
SALES_PRODUCT SYS_SUBP35 USER04
SALES_PRODUCT SYS_SUBP36 USER04
已选择16行。
（2）范围、列表组合分区
（a）建立范围、列表组合分区表
假设经常要按季度统计，且按地区统计；
CREATE TABLE sales_region(
sale_id number(10),
sale_date date,
address varchar2(30)
)PARTITION BY RANGE(sale_date) SUBPARTITION BY LIST(address)(
PARTITION r1 VALUES LESS THAN ('01-APR-2011') TABLESPACE USER01(
SUBPARTITION r1_1 VALUES('北京','天津'),
SUBPARTITION r1_2 VALUES('上海','重庆'),
SUBPARTITION r1_3 VALUES('河南','河北'),
SUBPARTITION r1_4 VALUES(DEFAULT)
),
PARTITION r2 VALUES LESS THAN ('01-JUL-2011') TABLESPACE USER02(
SUBPARTITION r2_1 VALUES('北京','天津'),
SUBPARTITION r2_2 VALUES('上海','重庆'),
SUBPARTITION r2_3 VALUES('河南','河北'),
SUBPARTITION r2_4 VALUES(DEFAULT)
),
PARTITION r3 VALUES LESS THAN ('01-OCT-2011') TABLESPACE USER03(
SUBPARTITION r3_1 VALUES('北京','天津'),
SUBPARTITION r3_2 VALUES('上海','重庆'),
SUBPARTITION r3_3 VALUES('河南','河北'),
SUBPARTITION r3_4 VALUES(DEFAULT)
),
PARTITION r4 VALUES LESS THAN ('01-JAN-2012') TABLESPACE USER04(
SUBPARTITION r4_1 VALUES('北京','天津'),
SUBPARTITION r4_2 VALUES('上海','重庆'),
SUBPARTITION r4_3 VALUES('河南','河北'),
SUBPARTITION r4_4 VALUES(DEFAULT)
)
);
（b）查看分区信息
SQL> select segment_name,partition_name,tablespace_name from user_segments where segment_name='SALES_REGION';
SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME
--------------------------------------------------------------------------------- ------------------------------ -------
-----------------------
SALES_REGION R1_1 USER01
SALES_REGION R1_2 USER01
SALES_REGION R1_3 USER01
SALES_REGION R1_4 USER01
SALES_REGION R2_1 USER02
SALES_REGION R2_2 USER02
SALES_REGION R2_3 USER02
SALES_REGION R2_4 USER02
SALES_REGION R3_1 USER03
SALES_REGION R3_2 USER03
SALES_REGION R3_3 USER03
SALES_REGION R3_4 USER03
SALES_REGION R4_1 USER04
SALES_REGION R4_2 USER04
SALES_REGION R4_3 USER04
SALES_REGION R4_4 USER04
已选择16行。
二、修改分区表
1、增加分区
ALTER TABLE sales ADD PARTITION p5 VALUES LESS THAN('01-APR-2012');
alter table sales SPLIT PARTITION p4 AT('01-08-2011') INTO(PARTITION p4_1,PARTITION p4_2)
SQL> alter table sales_region add partition r5 values less than('01-04-2012');
表已更改。
SQL> alter table sales_region modify partition r5 add subpartition r5_1 values('山东','山西');
alter table sales_region modify partition r5 add subpartition r5_1 values('山东','山西')
*
第 1 行出现错误:
ORA-14621: 在 DEFAULT 子分区已存在时无法添加子分区
SQL> alter table sales_region add partition r6 values less than('01-08-2012') (subpartition r6_1 values('黑龙江','辽宁','吉林'));
表已更改。
SQL> alter table sales_region modify partition r6 add subpartition r6_2 values('新疆','西藏');
表已更改。
2、删除分区
alter table ... drop partition...
3、截断分区
alter table ... trancate partition ...
4、修改分区名称
alter table ... rename partition ... to ...
5、合并分区
alter table ... merge partition ...,... into partition ...
6、交换分区数据
将分区表某个分区的数据同普通表交换
SQL> create table sale_1 as select * from sales;
表已创建。
SQL> select * from sale_1;
SALE_ID ORDER_ID CUSTOMER_ID SALE_AMOUNT SALE_DATE
---------- ---------- ----------- ----------- ----------
2011001001 2011000001 2011010001 2502.2 25-01-2011
2011001002 2011000011 2011040001 12502.2 25-04-2011
2011001022 2011002011 2011090002 102.2 21-09-2011
2011001022 2011002011 2011120002 102.2 21-12-2011
SQL> delete from sale_1 where 1=1;
已删除4行。
SQL> alter table sales exchange partition p1 with table sale_1;
表已更改。
SQL> select * from sale_1;
SALE_ID ORDER_ID CUSTOMER_ID SALE_AMOUNT SALE_DATE
---------- ---------- ----------- ----------- ----------
2011001001 2011000001 2011010001 2502.2 25-01-2011
三、建立全局索引和分区索引
1、建立全局索引
SQL> create index gbl_sale_date on sales(sale_date) GLOBAL;
索引已创建。
2、建立分区索引
只能基于分区表建立分区索引，索引数据存放在多个分区段中，且分区个数与分区表个数完全一致；
SQL> create index lcl_sale_date on sales(sale_date) LOCAL;
create index lcl_sale_date on sales(sale_date) LOCAL
*
第 1 行出现错误:
ORA-01408: 此列列表已索引
SQL> drop index gbl_sale_date;
索引已删除。
SQL> create index lcl_sale_date on sales(sale_date) LOCAL;
索引已创建。
四、显示分区表和分区索引相关信息
分区信息：dba_part_tables, all_part_tables, user_part_tables;
表分区：dba_tab_partitions, all_tab_partitions, user_tab_partitions;
子分区：dba_tab_subpartitions, all_tab_subpartitions, user_tab_subpartitions;
分区列：dba_part_key_columns, all_part_key_columns, user_part_key_columns;
子分区列：dba_subpart_key_columns, all_subpart_key_columns, user_subpart_key_columns;
分区索引信息：dba_part_indexs, all_part_indexs, user_part_indexs;
索引分区：dba_ind_parttitions, all_ind_partitions, user_ind_partitions;
索引子分区：dba_ind_subparttitions, all_ind_subpartitions, user_ind_subpartitions;
五、使用OEM管理分区表和分区索引
阅读(2104) | 评论(0) | 转发(0) |
