首页
登录 | 注册

精通Oracle10g系统管理(十八)

第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管理分区表和分区索引

相关文章

  • C/C++拾遗(十八):面向对象——句柄类与继承
          昨天由于时间的关系剩下一个小尾巴,今天忙里偷闲来把这个洞洞填上昨天学习了"面向对象编程"的部分,详细讨论了复制控制与类作用域需要注意的问题.这里有一个新的问题,如何实现一个类似"购物车"的 ...
  • 1.设备驱编译的驱动可以编译进内核,也可以作为内核模块,通过/etc/modprobe.conf进行配置[实际文件并不存在],存在的是 /etc/modprobe.d/ 这个目录,目录下有些文件的样子.具体的情况是 Redhat 在这个目录 ...
  • 1.红帽的安装程序名称是 Anaconda ,Redhat 尽量只发布带有源码开源的软件,除非没有选择,比如IBM的JRE(Java Runtime Environment)就是其中之一. 2.Anaconda的两个阶段设计 ①第一阶段: ...
  • LeaRun.Framework━ .NET快速开发框架 ━ 工作流程组件介绍
    LeaRun.Framework.NET,基于.NET的快速开发框架.整合框架,为企业或个人在.NET环境下快速开发系统提供了强大的支持,开发人员不需要开发系统的基础功能和公共模块,框架自身提供了强大的函数库和开发包,开发人员只须集中精力专 ...
  • 再谈如何学习Linux,一线Linux专家学习经验谈
    记得最早接触linux是在2000年,那个时候,还在上大学,一个同学从荷兰回来,带回来了一个Linux的拷贝版,记得版本还是Redhat6.2.曾经为安装一个系统让我们忘记疲劳,挑灯夜战,不亦乐乎.那时Linux的学习资料还很少,能够学习的 ...
  • 《循序渐进Linux》第二版即将出版发行(附封面)
    从<循序渐进Linux>第一版发布,到现在已经近6年了,6年的时间,技术发生了很大的变化,Linux系统的内核版本从2.6.9(RHEL4.x)已经更新到了现在的3.10(Centos7.x),第一版中的部分内容已经陈旧,Lin ...

2020 unjeep.com webmaster#unjeep.com
12 q. 0.012 s.
京ICP备10005923号