精华内容
下载资源
问答
  • ORACLE数据库优化分区技术.pdf
  • ORACLE性能优化之表分区 一、概念 表分区一般指分区表,将表中的数据在物理上存放到多个“表空间”(物理文件上)的这个过程称为表分区。 二、什么时候用? 在构建千兆字节数据系统或超高可用性系统时,当表数据...

    ORACLE性能优化之表分区

    一、概念

    表分区一般指分区表,将表中的数据在物理上存放到多个“表空间”(物理文件上)的这个过程称为表分区。

    二、什么时候用?

    在构建千兆字节数据系统或超高可用性系统时,当表数据量大小达到2G时推荐使用表分区。 
    2G:ORACLE官方文档给出的数字,这也是常见的一道面试题。

    三、表分区的几种类型以及操作方法

    1.范围分区

    1.1概念

    范围分区将数据基于某个字段值的范围映射到每一个分区。

    1.2 使用规则

    前提:创建分区要和创建表的语句一块用 
    1)每一个分区都必须有一个VALUES LESS THEN子句,它指定了一个不包括在该分区中的上限值。分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个高一些的分区中。 
    2)在最高的分区中,MAXVALUE被定义。MAXVALUE代表了一个不确定的值。这个值高于其它分区中的任何分区键的值,也可以理解为高于任何分区中指定的VALUE LESS THEN的值,同时包括空值。 
    3)语法上请注意:) 这之间不能有换行符PARTITION BY RANGE
    示例1:假设有一个CUSTOMER表,表中有数据200000行,我们将此表通过CUSTOMER_ID进行分区,每个分区存储100000行,我们将每个分区保存到单独的表空间中。
    CREATE TABLE CUSTOMER 
    (
    CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY, 
    FIRST_NAME VARCHAR2(30) NOT NULL, 
    LAST_NAME VARCHAR2(30) NOT NULL, 
    PHONE VARCHAR2(15) NOT NULL, 
    EMAIL VARCHAR2(80), 
    STATUS CHAR(1) 
    ) 
    PARTITION BY RANGE (CUSTOMER_ID) 
    ( 
    PARTITION CUS_PART1 VALUES LESS THAN (100000) TABLESPACE TEST_SPACE1, 
    PARTITION CUS_PART2 VALUES LESS THAN (200000) TABLESPACE TEST_SPACE2 
    );

    验证上限值: 该值存放在CUS_PART2
    insert into CUSTOMER values(100000, ‘sc’, ‘s’, ‘12312121222’, ‘sasd@sds.cn’, ‘y’);

    示例2:按时间划分
    CREATE TABLE ORDER_ACTIVITIES
    (
        ORDER_ID      NUMBER(7) NOT NULL,
        ORDER_DATE    DATE,
        TOTAL_AMOUNT  NUMBER,
        CUSTOTMER_ID  NUMBER(7),
        PAID           CHAR(1)
    )
    PARTITION BY RANGE (ORDER_DATE)
    (
    PARTITION ORD_ACT_PART01 VALUES LESS THAN (TO_DATE('01-05-2017','DD-mm-YYYY')) TABLESPACE TEST_SPACE1,
    PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-09-2017','DD-mm-YYYY')) TABLESPACE TEST_SPACE2,
    PARTITION ORD_ACT_PART03 VALUES LESS THAN (TO_DATE('31-12-2017','DD-mm-YYYY')) TABLESPACE TEST_SPACE3
    );

    示例3:MAXVALUE
    CREATE TABLE RangeTable
    (
      idd     INT PRIMARY KEY ,
      iNAME   VARCHAR(10),
      grade   INT 
    )
    PARTITION  BY  RANGE (grade)
    (
     PARTITION  part1 VALUES  LESS  THAN (1000) TABLESPACE  TEST_SPACE1,
     PARTITION  part2 VALUES  LESS  THAN (MAXVALUE) TABLESPACE  TEST_SPACE2
    );

    2.列表分区

    该分区的特点是某列的值只有几个,基于这样的特点我们可以采用列表分区。
    示例1:
    CREATE TABLE PROBLEM_TICKETS
    (
        PROBLEM_ID   NUMBER(7) NOT NULL PRIMARY KEY,
        DESCRIPTION  VARCHAR2(2000),
        CUSTOMER_ID  NUMBER(7) NOT NULL,
        DATE_ENTERED DATE NOT NULL,
        STATUS       VARCHAR2(20)
    )
    PARTITION BY LIST (STATUS)
    (
          PARTITION PROB_ACTIVE   VALUES ('ACTIVE') TABLESPACE TEST_SPACE1,
          PARTITION PROB_INACTIVE  VALUES ('INACTIVE') TABLESPACE TEST_SPACE2
    );

    插入实验值:
    insert into PROBLEM_TICKETS values(11, 'sssssdsdsd', 212, to_date('2017-11-20','yyyy-mm-dd'), 'ACTIVE');
    insert into PROBLEM_TICKETS values(12, 'sssssdsdsd', 212, to_date('2017-11-20','yyyy-mm-dd'), 'INACTIVE');


    列表外的其他值插入会报错:--ORA-14400:插入的分区关键字未映射到任何分区
    insert into PROBLEM_TICKETS values(11, 'sssssdsdsd', 212, to_date('2017-11-20','yyyy-mm-dd'), 'ACTIVE2');


    3.散列分区

    这类分区是在列值上使用散列算法,以确定将行放入哪个分区中。当列的值没有合适的条件时,建议使用散列分区。
    散列分区为通过指定分区编号来均匀分布数据的一种分区类型,通过在I/O设备上进行散列分区,使得这些分区大小一致。
    例1:
    CREATE TABLE HASH_TABLE
    (
      COL NUMBER(8),
      INF VARCHAR2(100)
    )
    PARTITION BY HASH (COL)
    (
      PARTITION PART01 TABLESPACE TEST_SPACE1,
      PARTITION PART02 TABLESPACE TEST_SPACE2,
      PARTITION PART03 TABLESPACE TEST_SPACE3,
      PARTITION PART04 TABLESPACE TEST_SPACE4
    );


    简写法:
    PARTITION BY HASH (COL) PARTITIONS 4 STORE IN (TEST_SPACE1, TEST_SPACE2, TEST_SPACE3, TEST_SPACE4);


    注意:该简写无需指定分区名称,分区名系统自动生成:SYS_PXXX
    hash分区最主要的机制是根据hash算法来计算具体某条纪录应该插入到哪个分区中,
    hash算法中最重要的是hash函数,Oracle中如果你要使用hash分区,只需指定分区的数量即可。
    建议分区的数量采用2的n次方,这样可以使得各个分区间数据分布更加均匀。
    备注:和hash算法的原理有关
    官方文档:create multiple partitions and subparttions for each parttion 
    that is a power of two.For example,2,4,8,16,32,64,128,and so on.

    4.组合(composite)范围列表分区

    这种分区是基于范围分区和列表分区,表首先按某列进行范围分区,
    然后再按某列进行列表分区,分区之中的分区被称为子分区。
    示例1:
    CREATE TABLE SALES
    (
    PRODUCT_ID VARCHAR2(5),
    SALES_DATE DATE,
    SALES_COST NUMBER(10),
    STATUS VARCHAR2(20)
    )
    PARTITION BY RANGE(SALES_DATE) SUBPARTITION BY LIST (STATUS)
    (
       PARTITION P1 VALUES LESS THAN(TO_DATE('2017-06-01','YYYY-MM-DD'))TABLESPACE TEST_SPACE1
              (
                  SUBPARTITION P1SUB1 VALUES ('ACTIVE') TABLESPACE TEST_SPACE3,
                  SUBPARTITION P1SUB2 VALUES ('INACTIVE') TABLESPACE TEST_SPACE4
              ),
       PARTITION P2 VALUES LESS THAN (TO_DATE('2017-12-31','YYYY-MM-DD')) TABLESPACE TEST_SPACE2
              (
                  SUBPARTITION P2SUB1 VALUES ('ACTIVE') TABLESPACE TEST_SPACE5,
                  SUBPARTITION P2SUB2 VALUES ('INACTIVE') TABLESPACE TEST_SPACE6
              )
    );


    5.复合范围散列分区

    这种分区是基于范围分区和散列分区,表首先按某列进行范围分区,然后再按某列进行散列分区。
    create table dinya_test
     (
     transaction_id   number primary key,
     item_id        number(8) not null,
     item_description varchar2(300),
     transaction_date date
     )
     partition by range(transaction_date)subpartition by hash(transaction_id)  subpartitions 4 store in 
    (TEST_SPACE4,TEST_SPACE5,TEST_SPACE6,TEST_SPACE7)
     (
         partition part_01 values less than(to_date('2017-01-01','yyyy-mm-dd')),
         partition part_02 values less than(to_date('2018-01-01','yyyy-mm-dd')),
         partition part_03 values less than(maxvalue)
     );


    四.表分区的维护

    注意点:
    1.在对表分区进行DDL操作时,会使分区全局索引失效,结尾加上update global indexes重建全局索引。忘记请追加alter index  PART_KEY rebuild online;
    可以通过:select index_name,status from user_indexes;查看到UNUSABLE状态的索引(表格有数据时才能体现)
    2.表分区的维护对象为分区表,不能是普通表

    4.1添加分区 

    以下代码给SALES表添加了一个P3分区(可以指定表空间)
    ALTER TABLE SALES ADD PARTITION P3 VALUES LESS THAN(TO_DATE('2018-06-01','YYYY-MM-DD'));


    注意:以上添加的分区界限应该高于最后一个分区界限。
    以下代码给SALES表新增P3分区和一个P3SUB1子分区
    ALTER TABLE SALES ADD PARTITION P3 VALUES LESS THAN(TO_DATE('2018-06-01','YYYY-MM-DD'))(
          SUBPARTITION P3SUB1 VALUES('COMPLETE')
    );


    建议:子分区和分区一起建立,如果先建立分区再建立子分区,需要先删除默认子分区,再增加子分区

    4.2删除分区

    以下代码删除了P3表分区:
    ALTER TABLE SALES DROP PARTITION P3;

    在以下代码删除了P2SUB2子分区:
    ALTER TABLE SALES DROP SUBPARTITION P2SUB2;

    注意:如果删除的分区是表中唯一的分区,那么此分区将不能被删除,要想删除此分区,必须删除表。(drop table)
     

    4.3截断分区

    截断某个分区是指删除某个分区中的数据,并不会删除分区,也不会删除其它分区中的数据。当表中即使只有一个分区时,也可以截断该分区。

    通过以下代码截断分区:

    ALTER TABLE SALES TRUNCATE PARTITION P2;

    通过以下代码截断子分区:

    ALTER TABLE SALES TRUNCATE SUBPARTITION P1SUB1;

    4.4合并分区

    合并分区是将相邻的分区合并成一个分区,结果分区将采用较高分区的界限,值得注意的是,不能将分区合并到界限较低的分区。
    以下代码实现了P1 P2分区的合并:
    ALTER TABLE SALES MERGE PARTITIONS P1,P2 INTO PARTITION P2;

    4.5拆分分区

    拆分分区将一个分区拆分两个新分区,拆分后原来分区不再存在。注意不能对HASH类型的分区进行拆分。 
    ALTER TABLE SALES SPLIT PARTITION P2 AT(TO_DATE('2003-02-01','YYYY-MM-DD')) INTO (PARTITION P21,PARTITION P22);

    4.6接合分区(coalesce)

    结合分区是将散列分区中的数据接合到其它分区中,当散列分区中的数据比较大时,可以增加散列分区,然后进行接合,值得注意的是,接合分区只能用于散列分区中。
    通过以下代码进行接合分区: 
    ALTER TABLE HASH_TABLE coalesce PARTITION;

    4.7重命名表分区 

    以下代码将P21更改为P2
    ALTER TABLE SALES RENAME PARTITION P22 TO P2;

    4.8相关查询

    4.8.1跨分区查询 

    select count(*) from
    (
    select count(*) cn from CUSTOMER PARTITION (CUS_PART1)
    union all
    select count(*) cn from CUSTOMER PARTITION (CUS_PART2)
    );
    -- select 1和select 2的结果加起来,并且不处理重复项。

    4.8.2查询表上有多少分区 

    SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME='CUSTOMER';

    4.8.3查询索引信息

    select object_name,object_type,tablespace_name,sum(value)
    from v$segment_statistics
    where statistic_name IN ('physical reads','physical write','logical reads')and object_type='INDEX'
    group by object_name,object_type,tablespace_name
    order by object_name desc

    4.8.4显示数据库所有分区表的信息 (SYSDBA)

    select * from DBA_PART_TABLES;

    4.8.5显示当前用户可访问的所有分区表信息 

    select * from ALL_PART_TABLES;

    4.8.6显示当前用户所有分区表的信息 

    select * from USER_PART_TABLES;

    4.8.7显示表分区信息(显示数据库所有分区表的详细分区信息)

    select * from DBA_TAB_PARTITIONS;

    4.8.8显示当前用户可访问的所有分区表的详细分区信息

    select * from ALL_TAB_PARTITIONS;

    4.8.9显示当前用户所有分区表的详细分区信息 

    select * from USER_TAB_PARTITIONS;

    4.8.10显示子分区信息(显示数据库所有组合分区表的子分区信息 )

    select * from DBA_TAB_SUBPARTITIONS;

    4.8.11显示当前用户可访问的所有组合分区表的子分区信息

    select * from ALL_TAB_SUBPARTITIONS;

    4.8.12显示当前用户所有组合分区表的子分区信息

    select * from USER_TAB_SUBPARTITIONS;

    4.8.13显示分区列:显示数据库所有分区表的分区列信息

    select * from DBA_PART_KEY_COLUMNS;

    4.8.14显示当前用户可访问的所有分区表的分区列信息

    select * from ALL_PART_KEY_COLUMNS;

    4.8.15显示当前用户所有分区表的分区列信息

    select * from USER_PART_KEY_COLUMNS;

    4.8.16显示子分区列 显示数据库所有分区表的子分区列信息 

    select * from DBA_SUBPART_KEY_COLUMNS;

    4.8.17显示当前用户可访问的所有分区表的子分区列信息 

    select * from ALL_SUBPART_KEY_COLUMNS;

    4.8.18显示当前用户所有分区表的子分区列信息 

    select * from USER_SUBPART_KEY_COLUMNS;

    4.8.19查询出oracle数据库中所有的的分区表

    select * from user_tables a where a.partitioned='YES';

    4.8.20 查询分区数据

    select * from 表名 partition (分区名称);

    五.在线重定义(普通表转分区表)

    1.在线重定义表具有以下功能:

    修改表的存储参数;
    可以将表转移到其他表空间;
    增加并行查询选项;
    增加或删除分区;
    重建表以减少碎片;
    将堆表改为索引组织表或相反的操作;
    增加或删除一个列。
    调用DBMS_REDEFINITION包需要EXECUTE_CATALOG_ROLE角色,除此之外,还需要CREATE ANY TABLE、ALTER ANY TABLE、DROP ANY TABLE、LOCK ANY TABLE和SELECT ANY TABLE的权限。

    2.在线重定义的大致操作流程如下: 

    (1)创建基础表A,如果存在,就不需要操作
    (2)创建临时的分区表B
    (3)开始重定义,将基表A的数据导入临时分区表B
    (4)结束重定义,此时在DB的 Name Directory里,已经将2个表进行了交换。即此时基表A成了分区表,我们创建的临时分区表B 成了普通表。此时我们可以删除我们创建的临时表B。
    示例:
    a. 创建基本表和索引(重定义的表必须有主键)
    create table unpartTAB(
      aid number,siteid number,vdate date,seid number,sename varchar2(4000),swordnum number,pv number,vt number,uv number,ip number,
      stime number,atime number,ltime number,jump number,jump_atime number,time_month number
    );
    alter table unpartTAB add constraint unpart_KEY primary key (aid);
    b. 收集表的统计信息  (普通用户权限不足使用的SYSDBA用户)
    exec dbms_stats.gather_table_stats('test','unpartTAB', cascade => true); 
    c. 创建临时分区表 
    create table partTAB(
      aid number,siteid number,vdate date,seid number,sename varchar2(4000),swordnum number,pv number,vt number,uv number,ip number,
      stime number,atime number,ltime number,jump number,jump_atime number,time_month number
    )
    partition by list(time_month)(
        partition p1 values(1) tablespace TESTSPACE1,
        partition p2 values(2) tablespace TESTSPACE1,
        partition p3 values(3) tablespace TESTSPACE1,
        partition p4 values(4) tablespace TESTSPACE1,
        partition p5 values(5) tablespace TESTSPACE1,
        partition p6 values(6) tablespace TESTSPACE1,
        partition p7 values(7) tablespace TESTSPACE1,
        partition p8 values(8) tablespace TESTSPACE1,
        partition p9 values(9) tablespace TESTSPACE1,
        partition p10 values(10) tablespace TESTSPACE1,
        partition p11 values(11) tablespace TESTSPACE1,
        partition p12 values(12) tablespace TESTSPACE1
    ); 

    普通表和临时分区表都必须有主键,不然会报错

    alter table partTAB add constraint part_KEY primary key (aid);

    d. 进行重定义操作
    d1. 检查重定义的合理性 
    exec dbms_redefinition.can_redef_table('test', 'unpartTAB');
    d2. 如果d1没有问题,开始重定义,这个过程可能要等一会
    这里要注意:如果分区表和原表列名相同,可以用如下方式进行: 
    EXEC DBMS_REDEFINITION.start_redef_table(uname =>'test',orig_table =>'unpartTAB',int_table =>'partTAB');
    如果分区表的列名和原表不一致,那么在开始重定义的时候,需要重新指定
    映射关系:(注意写上主键aid aid 对应的映射关系为siteid site)
    EXEC DBMS_REDEFINITION.START_REDEF_TABLE('test','unpartTAB','partTAB','aid aid,siteid site',dbms_REDEFINITION.CONS_USE_PK);
    这一步操作结束后,数据就已经同步到这个临时的分区表partTAB里来了。
    d3. 同步新表:sync_interim_table的目的是为了缩短finish时锁定表的时间。
    exec DBMS_REDEFINITION.sync_interim_table(uname =>'test',orig_table =>'unpartTAB',int_table =>'partTAB');
    d4. 收集新表的统计信息
    exec dbms_stats.gather_table_stats('test', 'partTAB', cascade => true); 
    d5. 结束重定义 
    exec dbms_redefinition.finish_redef_table(uname => 'test',orig_table =>'unpartTAB',int_table =>'partTAB');
    结束重定义的意义: 
    基表unpartTAB和临时分区表partTAB进行了交换。  此时临时分区表
    partTAB成了普通表,我们的基表unpartTAB成了分区表。  
    我们在重定义的时候,基表unpartTAB是可以进行DML操作的。 只有在2个表进行切换的时候会有短暂的锁表。
    d6. 删除临时表
    drop table test.partTAB cascade constraints purge;
    d7. 重命名索引
    alter index PART_KEY rename to UNPART_KEY;

    六.Oracle 11g 新特性(表分区方面)

    6.1、引用分区(Reference Partition)

    概念:处理父子表对等分区的问题,以这种方式处理分区,父表与子表的分区是一对一关系。

    6.2、间隔分区(Interval Partition)

    概念:以一个区间分区表为“起点”,并在定义中增加了一个规则(即间隔),使数据库知道将来如何增加分区。

    6.3、虚拟列分区(Partitioning Virtual Columns)

    概念:在表中引入了虚拟列,根据虚拟列的值进行分区操作。
    虚拟列:是一个表达式,在运行时计算,不存储在数据库中,不能更新虚拟列的值。

    6.4、系统分区(System Partition)

    概念:不需要指定分区列,完全根据程序来控制数据存储在那个分区中。

    6.5.拓展组合分区(Extended Composite Partitioning)

    概念:同复合分区。
    新增4种复合分区:RANGE-RANGE、LIST-RANGE、LIST-HASH和LIST-LIST。 

    七.表分区查询性能优化演示

    在数据量达到1000M以上的时候,普通表的查询时间是27秒左右,分区表的查询时间为4秒左右。
    --普通表ws_se
    create table ws_se(
      aid number,siteid number,vdate date,seid number,sename varchar2(4000),swordnum number,pv number,vt number,uv number,ip number,
      stime number,atime number,ltime number,jump number,jump_atime number,time_month number
    )
    --分区表ws_se2
    create table ws_se2(
      aid number,siteid number,vdate date,seid number,sename varchar2(4000),swordnum number,pv number,vt number,uv number,ip number,
      stime number,atime number,ltime number,jump number,jump_atime number,time_month number
    )
    partition by list(time_month)(
        partition p1 values(1) tablespace TEST_SPACE1,
        partition p2 values(2) tablespace TEST_SPACE2,
        partition p3 values(3) tablespace TEST_SPACE3,
        partition p4 values(4) tablespace TEST_SPACE4,
        partition p5 values(5) tablespace TEST_SPACE5,
        partition p6 values(6) tablespace TEST_SPACE6,
        partition p7 values(7) tablespace TEST_SPACE7,
        partition p8 values(8) tablespace TEST_SPACE8,
        partition p9 values(9) tablespace TEST_SPACE9,
        partition p10 values(10) tablespace TESTSPACE,
        partition p11 values(11) tablespace TESTSPACE,
        partition p12 values(12) tablespace TESTSPACE
    );
    --制造数据
    alter table ws_se nologging PARALLEL 4;--设置:不打印日志,并行度4
    insert  /*+APPEND*/into ws_se3
      (aid,
       siteid,
       vdate,
       seid,
       sename,
       swordnum,
       pv,
       uv,
       vt,
       ip,
       stime,
       atime,
       ltime,
       jump,
       jump_atime,
       time_month)
    select rownum,
           TRUNC(DBMS_RANDOM.VALUE(0, 300)),
           TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2455198, 2455198 + 364)), 'J'),
           TRUNC(DBMS_RANDOM.VALUE(0, 10)),
           DBMS_RANDOM.STRING('A', 10),
           TRUNC(DBMS_RANDOM.VALUE(0, 100)),
           TRUNC(DBMS_RANDOM.VALUE(0, 50)),
           TRUNC(DBMS_RANDOM.VALUE(0, 50)),
           TRUNC(DBMS_RANDOM.VALUE(0, 50)),
           TRUNC(DBMS_RANDOM.VALUE(0, 50)),
           TRUNC(DBMS_RANDOM.VALUE(0, 30)),
           TRUNC(DBMS_RANDOM.VALUE(0, 30)),
           TRUNC(DBMS_RANDOM.VALUE(0, 30)),
           TRUNC(DBMS_RANDOM.VALUE(0, 30)),
           TRUNC(DBMS_RANDOM.VALUE(0, 30)),
           TRUNC(DBMS_RANDOM.VALUE(1, 12))
    from xmltable('1 to 16000000');--更大的数据量采用此方式(Oracle 10g支持)
    --from dual  connect by level<200000; 此循环connect by level有上限,如果超出上限,系统会报,"connect by level"内存不足
    
    
    --查询表格数据实际占用物理空间大小
    --先手动分析
    analyze table WS_SE2 compute statistics;
    --查询
    select num_rows * avg_row_len/1024/1024/1024 "GB" 
    from user_tables 
    where table_name = 'WS_SE';
    
    
    --查询效率比较 注意sename的值和time_month的值是随机的,请自行查询表数据输入
    select * from ws_se t where t.sename = 'EryZlPisHD' and t.time_month=8;
    select * from ws_se2 t where t.sename = 'FocufArOEF' and t.time_month=7;







    展开全文
  • Oracle最新分区特性优化实践.pptx
  • 基于ORACLE分区技术实现省级价格监测系统数据存储优化.pdf
  • Oracle分区技术 分区前后处理上的差异 1.大批量数据清理 2.查询操作 3.高可用性方面,数据可管理性 技术概述 1.范围(range)分区 按对一张表指定的字段值或多个字段值范围进行分区  2.Hash分区 ...
    Oracle表分区技术
    分区前后处理上的差异
    1.大批量数据清理
    2.查询操作
    3.高可用性方面,数据可管理性
    技术概述
    1.范围(range)分区
    按对一张表指定的字段值或多个字段值范围进行分区

     2.Hash分区
    通过oracle内部hash分区算法,将指定字段名输入进行散列运算,以此为标准均匀分布到各个分区

     为保证数据散列分布均匀化,一般hash分区为2的幂
    特点:总体性能最佳,不会有特别大的分区,分布均匀。以哈希分区字段进行访问,所有记录访问性能相当
               使用oracle内部哈希算法,实施简单
                适用于静态数据。此类数据一般永久储存在数据库中不需要迁移。
    局限:不适合大批量数据迁移和删除
    3.列表(list)分区

     4.组合分区
        几种分区技术选取两种组合,11g大幅增强
    5.间隔分区(11g)
        指定时间间隔如月,系统会在将来时间自动生成按月产生的分区
    6.基于虚拟列的分区技术(virtual column_based)
        支持在字段函数基础上直接进行分区,而不需要新建一个分区字段通过触发器维护
    7.引用(reference)分区
        针对主表通过外键关联了子表的情况,根据主表的分区自动凭借外键对子表进行相同的分区,一体管理无需分开设计。

    分区索引技术
    1.本地前缀分区索引(local prefixed partitioned index)
        假设分区表为交易流水表,以交易日期字段按年度进行范围分区,欲创建交易日期字段的索引

         所谓本地,是指索引分区方法和表分区方法一致。数据表按年度分成若干份后,索引也按同上方法分成了相等数量,索引树高度降低了。如下

         所谓前缀是指分区字段是索引字段的前缀。例如可以在日期和地区字段建立复合索引,仍为前缀索引

         除了性能提高外,本地前缀分区索引还有一个好处,某一个分区表drop或merge后,其他索引仍为有效状态,无需进行重建索引工作。
    2.全局分区索引(globle partitioned index)
         比如在area字段建立索引

         全局分区索引示意图如下

         三个索引分别是三个地区。也就是说杭州的数据可以分布在各个年份。此时按地区查询数据时,索引树高度就下降了。全局索引在大部分情况下性能都非常好,有些情况甚至优于本地前缀索引。
        之所以叫全局索引,是因为索引分区的字段和表分区字段无关,如上例地区和日期字段的差别。
        缺陷:数据高可用性方面差,加入某年数据删除了,当前全局索引全部失效,需要rebuild,索引量越大重建时间越长,大大降低数据可访问性
    3.本地非前缀分区索引(local non-prefixed partitioned index)
        还是建立地区字段的索引,我们建在本地模式上,即使用local的同时,索引依据字段和表分区字段又不一样,就是本地非前缀索引了

         为何要这样做?这样做的弊端显而易见,本地意味着索引分区同样按分区表字段即日期进行划分,所以我们每个年份的划分的索引中包含当年所有的地区索引。当我们查询某一地区A维度的数据时,需要从每个数据块(被年份划分)中查询,因为地区A的数据可能在任一时间块中存在!这时候查询效率甚至比分区前还低。就很尴尬了。
        但是这样做的好处是同本地前缀类似,大大提高了按索引访问数据的可用性,如果我们要维护数据块,比如drop掉2010年的数据,通过这种索引方法就不用再重新建立失效的索引了。
        注:其实本地非前缀索引效率也没有那么不堪,当查询条件为符合语句时,Oracle会智能选择部分符合条件的索引进行查询。单一如地区维度这种就没办法了。。

    分区索引建立的建议
        如果日常查询性能很重要,清理和维护历史数据不甚频繁,推荐设计全局分区索引。如果清理数据频繁且不能承受重建索引的代价,则推荐本地非前缀索引咯。

     更神奇的分区技术
        需求:假设有一张保存历史交易数据的history表,按年分区,现在要把实时交易记录的current表03年数据全部导入history表,并删除current表03年数据。这是典型的历史数据清理需求。如何实现
        境界1-菜鸟

        境界2-进阶

        境界3-牛人(使用exchange)

         创建过渡表stage,通过oracle交换技术几乎瞬间将p_2003分区数据交换到stage表中,再使用同样技术交换至目标表。神奇之处在于exchange并没有搬动数据,而是将p_2003分区与stage表物理段位置在数据字典中进行了交换。所以实际效率与数据量没有关系。
        exchange其他应用:如果要对一个分区超大表的某一分区进行数据批处理时,最好的办法是将这一分区交换至外面一张普通表,对其处理完后再交换回去。

    展开全文
  • oracle分区

    2018-08-24 18:13:49
    “分而治之”— 没有能够比 Oracle 数据库的分区特性更好地阐释这个象征性原则的了。 从版本 8 开始,您就可以将一个表或索引划分成多个段,然后将它们放在不同的表空间内。该表仍作为一个 逻辑实体定址,各个分区则...

    “分而治之”— 没有能够比 Oracle 数据库的分区特性更好地阐释这个象征性原则的了。 从版本 8 开始,您就可以将一个表或索引划分成多个段,然后将它们放在不同的表空间内。该表仍作为一个 逻辑实体定址,各个分区则存储为单独的段,这就简化了数据操作。

    在版本 11 中,引用分区、间隔分区、虚拟列分区以及扩展的组合分区等增强功能支持无限的分区设计可 能性,并提高了可管理性。

    如果您希望在分区的基础以及对您选择分区列或方案的决定有影响的因素上进一步提高,请参阅《Oracle Magazine》2006 年 9/10 月刊上我的文章

    扩展的组合分区

    使用组合分区 — Oracle8i 数据库中引入的方案 — 您可以在分区中创建子分区, 从而进一步细分表。但在该情况下,您只能通过散列子分区对已按范围分区的表进行再次分区。 Oracle9i 中对组合分区进了扩展,使之包括范围-列表子分区。

    这些方案满足了大多数环境(但并非所有环境)的需要。例如,假设您有一个名为 SALES 的包含多列的表,其中包括两个特殊的列,它们是分区的候选列:state_code,它存储一个表示销售状态的两位代码,表面上用于计算销售税;以及 product_code,一个用于识别销售记录所销售的产品的三位数字。用户通过对两列进行同等的筛选对该表进行查询,存档要求也基于这两列。应用分区决策的原则时,您会发现这两列都是合适的分区键候选者。

    在 Oracle Database 11g 中,您可以相当轻松地解决此问题。在该版本中,并不局限于范围- 散列和范围-列表组合分区。您的选择实际上没有任何限制;您可以使用任何组合创建组合分区。

    在这个示例中,您可以决定根据 product_code(因为该列具有更多离散的值)对表进行列表分区,然后 根据 state_code 再次进行列表分区。下面的代码示例显示了如何实现该操作:

    create table sales
    (
       sales_id     number,
       product_code number,
       state_code   varchar2(2)
    )
    partition by list (product_code)
    subpartition by list (state_code)
    (
       partition p101 values (101)
       (
          subpartition p101_ct values ('CT'),
          subpartition p101_ny values ('NY'),
          subpartition p101_def values (default)
       ),
       partition p201 values (201)
       (
          subpartition p201_ct values ('CT'),
          subpartition p201_ny values ('NY'),
          subpartition p201_def values (default)
       )
    )
    

     

    选择并不仅限于此处显示的方法。您还可以创建列表-范围组合分区。在上面的示例中,假设产品代码不是离散的,而是在一个范围内。您将希望根据 state_code 进行列表分区,然后再根据 product_code 划分子分区。下面是实现该操作的代码示例。

    create table sales1
    (
       sales_id     number,
       product_code number,
       state_code   varchar2(2)
    )
    partition by list (state_code)
    subpartition by range (product_code)
    (
       partition CT values ('CT')
       (
          subpartition ct_100 values less than (101),
          subpartition ct_200 values less than (201)
       ),
       partition NY values ('NY')
       (
          subpartition NY_100 values less than (101),
          subpartition NY_200 values less than (201)
       )
    )
    

     

    您也可以创建范围-范围组合分区,如果您有两个日期域,该方法将非常方便。例如,考虑一个用于销售处理系统的表,该表包括一个交易日期和一个交货日期。您可能希望根据一个日期进行范围分区,然后再根据另一个日期进行子范围分区。该方案允许您根据日期进行备份、存档和清除。

    总而言之,您在 Oracle Database 11g 中可以创建以下类型的组合分区:

    • 范围-范围
    • 范围-散列
    • 范围-列表
    • 列表-范围
    • 列表-散列
    • 列表-列表

    引用分区

    下面是设计分区方案过程中的一个典型问题:并非所有表都具有您需要根据其进行分区的列。假设您要创建一个销售系统,该系统包括两个简单的表(sales 和 customers):

    create table customers
    (
       cust_id   number primary key,
       cust_name varchar2(200),
       rating    varchar2(1) not null
    )
    partition by list (rating)
    (
       partition pA values ('A'),
       partition pB values ('B')
    );
    

     

    sales 表的创建如下所示。它是 customers 表的一个子表。

    create table sales
    (
       sales_id    number primary key,
       cust_id     number not null,
       sales_amt   number,
       constraint  fk_sales_01
        foreign key (cust_id)
        references customers
    );
    

     

    理想情况下,您希望用相同的方式对 sales 表和 customers 表分区:根据 rating 列进行列表分区。但有一个严重问题:sales 表没有名为 rating 的列!那么如何根据一个不存在的列进行分区呢?

    在 Oracle Database 11g 中,您可以使用一个称为引用分区的新特性。下面的示例显示了如何将该特性应用于 sales 表:

    create table sales
    (
       sales_id    number primary key,
       cust_id     number not null,
       sales_amt   number,
       constraint  fk_sales_01
        foreign key (cust_id)
        references customers
    )
    partition by reference (fk_sales_01);
    

     

    这段代码创建了与父表 customers 中相同的分区。注意,虽然没有名为 rating 的列,但仍根据该列对表进行了分区。partition by reference (fk_sales_01) 子句包括了分区定义中的外键名。该语句指示 Oracle Database 11g 确认通过父表(在该示例中为 customers)中使用的方案进行了分区。注意 cust_id 列的 NOT NULL 约束;这是引用分区所必需的。

    如果您检查 sales 表中分区的分区边界:

    SQL> select partition_name, high_value
      2  from user_tab_partitions
      3  where table_name = 'SALES';
    
    PARTITION_NAME  HIGH_VALUE
    ---------------        -------------------------------
    PA
    PB
    

     

    高值为空,这意味着此处的边界派生自父表。分区的名称与父表中的名称相同。您可以通过查询 user_part_tables 视图来检查分区的类型。一个名为 ref_ptn_constraint_name 的特殊列显示了外键约束 名称。

    SQL> select table_name, partitioning_type, ref_ptn_constraint_name
      2  from user_part_tables
      3  where table_name in ('CUSTOMERS','SALES');
    
    TABLE_NAME                     PARTITION REF_PTN_CONSTRAINT_NAME
    ------------------------------                ---------     --------------------------
    CUSTOMERS                      LIST
    SALES                          REFERENCE FK_SALES_01
    

     

    如果您希望按照父表分区的方式对子表进行分区,但没有相同的列,您又不想仅仅为了分区而引入这些列,此时引用分区将非常方便。而且,您不必针对每个子表显式声明一个很长的分区子句。

    间隔分区

    范围分区允许您根据分区键列的值的范围创建分区。下面是一个按范围分区的表的示例:

     

    create table sales6
    (
       sales_id    number,
       sales_dt    date
    )
    partition by range (sales_dt)
    (
       partition p0701 values less than (to_date('2007-02-01','yyyy-mm-dd')),
       partition p0702 values less than (to_date('2007-03-01','yyyy-mm-dd'))
    );
    

     

    您在此处仅针对 2007 年 1 月和 2007 年 2 月定义了分区,如果表中插入一条 sales_dt 在 2007 年 3 月的记录,会发生什么情况?插入将失败,并显示以下错误:

    ORA-14400: inserted partition key does not map to any partition
    

     

    显然,您需要针对 2007 年 3 月添加一个分区,然后才能插入一条记录。但通常说起来容易做起来难。您通常无法容忍事先创建大量分区,但其中很少一部分可能会产生此错误。

    如果 Oracle 以某种方式自动察觉到对新分区的需要,然后创建它们,这样不是更好吗?Oracle Database 11g 可以,它可以使用一个称为间隔分区的特性。此时,您不必定义分区及它们的边界,只需定义 一个定义了每个分区边界的间隔。下面是使用间隔分区的示例:

    create table sales6
    (
       sales_id    number,
       sales_dt    date
    )
    partition by range (sales_dt)
                                   
    interval (numtoyminterval(1,'MONTH'))
    (
       partition p0701 values less than (to_date('2007-02-01','yyyy-mm-dd'))
    );
                                

    注意子句:interval 后面跟着时间间隔。您在此处指示 Oracle 为每个月份创建一个时间间隔。您已经为 2007 年 1 月的数据创建了名为 p0701 的初始分区。现在,假设您插入了一条包括 2007 年 6 月数据的 记录:

    SQL> insert into sales6 values (1,'01-jun-07');
    
    1 row created.
    

     

    Oracle 不会返回错误,而是成功执行该语句。那么这条记录将转向何处?p0701 分区不能包括该记录,我们没有为 2007 年 6 月定义分区。但此时,如果您检查该表的分区:

    SQL> select partition_name, high_value
      2  from user_tab_partitions
      3  where table_name = 'SALES6';
    
    PARTITION_NAME  HIGH_VALUE
    ---------------        ----------------------------------------------------------------
    P0701           TO_DATE(' 2007-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_C
                    ALENDAR=GREGORIA
    
    SYS_P41         TO_DATE(' 2007-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_C
                    ALENDAR=GREGORIA
    

     

    注意名为 SYS_P1、高值为 2007 年 7 月 1 日的分区,它最多可以容纳到 6 月底的数据。该分区是由 Oracle 动态创建的,并具有一个系统生成的名称。

    现在,假设您输入一个小于最高值的值,如 2007 年 5 月 1 日。理想情况下,它应该具有自己的分区,因 为您的分区时间间隔是一个月。

    SQL> insert into sales6 values (1,'01-may-07');
    
    1 row created.
    
    SQL> select partition_name, high_value
      2  from user_tab_partitions
      3  where table_name = 'SALES6';
    
    PARTITION_NAME  HIGH_VALUE
    ---------------        ----------------------------------------------------------------
    P0701           TO_DATE(' 2007-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_C
                    ALENDAR=GREGORIA
    
    SYS_P41         TO_DATE(' 2007-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_C
                    ALENDAR=GREGORIA
    
    SYS_P42         TO_DATE(' 2007-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_C
                    ALENDAR=GREGORIA
    

     

    注意新分区 SYS_P42,其上限为 6 月 1 日,因此该分区可以保留 2006 年 5 月的数据。该分区是通过拆分 SYS_P41 分区创建的(针对 6 月份)。因此,当您定义一个间隔分区方案时,Oracle 会自动创建和维 护分区。

    如果您希望将分区存储在特定表空间中,可以使用 store in 子句执行该操作:

    interval (numtoyminterval(1,'MONTH'))
    store in (TS1,TS2,TS3)
    

     

    该子句以循环方式将分区存储在表空间 TS1、TS2 和 TS3 中。

    应用程序开发人员如何定位特定分区?一种方法是知道名称,这种方法可能不可行,即使您知道名称,这种方法也非常容易出错。为了便于访问特定分区,Oracle Database 11g 为分区 SQL 提供了一个新 语法:

    SQL> select * from sales6 partition for (to_date('15-may-2007','dd-mon-yyyy'));
    
      SALES_ID SALES_DT
      ----------    ---------
          1    01-MAY-07
    

     

    注意新子句 for (值),它允许您直接引用分区,而不必通过它们的确切名称进行显式调用。如果您希望截断或删除一个分区,可以调用这个扩展的分段语法。


    以此方式创建表之后,DBA_PART_TABLES 视图中的 PARTITIONING_TYPE 列会显示时间间隔。

    系统分区

    尽管 Oracle 对此特性的实际用途期望很少,但我还是希望至少描述一下此特性,因为它真的很酷。

    下面是一个很少见但并非不可能发生的用例:假设您有一个无法用任何逻辑方法分区的表。结果将是一个巨大的、单一的表,这会导致需要扩展的索引维护和其他操作等问题。

    因此,开发人员提出一个解决方案:他们承诺,如果该表不能以某种方式分区,他们可以通过一种智能方式写入分区。这样,应用程序就可以控制特定记录进入哪个分区。DBA 只需定义分区。示例如下:

    create table sales3 
    (
        sales_id   number,
        product_code number,
        state_code   number
    )
    partition by system
    
       partition p1 tablespace users,
       partition p2 tablespace users
    );
    


    注意,没有任何分区键或边界。因此,以物理方式将表分成两个段,但仍然是一个逻辑表。以这种方式定义之后,数据库会为该表创建两个段,而不是只有一个单一的表。您可以通过以下语句对其进行检查:

    SQL> select partition_name
      2  from user_segments
      3  where segment_name = 'SALES3';
    
    PARTITION_NAME
    ------------------------------
    P1
    P2
    


    创建本地索引时,也是以相同的方式对其进行分区。

    SQL> create index in_sales3_state on sales3 (state_code) local;
    
    Index created.
    
    SQL> select partition_name
      2  from user_segments
      3  where segment_name = 'IN_SALES3_STATE';
    
    PARTITION_NAME
    ------------------------------
    P1
    P2
    


    可以通过检查 user_part_tables 来检查分区的类型:

    SQL> select partitioning_type
      2  from user_part_tables
      3  where table_name = 'SALES3';
    
    PARTITION
    ---------
    SYSTEM
    


    这显示为 SYSTEM,当然,指示系统分区。需要注意的一点是,这些表类型的 high_value 列值为空。

    SQL> select partition_name, high_value
      2  from user_tab_partitions
      3  where table_name = 'SALES3';
    
    PARTITION_NAME HIGH_VALUE
    --------------        ---------------------
    P1               
    P2
    


    下面是一个有趣的问题:如果没有分区键或方案(如范围、列表或散列),Oracle 如何知道传入的记录应进入哪个分区?


    答案是:Oracle 不知道。下面的示例显示了如果您向表中插入一条记录时将发生的情况:

    SQL> insert into sales3 values (1,101,1);
    insert into sales3 values (1,101,1)
                *
    ERROR at line 1:
    ORA-14701: partition-extended name or bind variable must be used for DMLs on
    tables partitioned by the System method
    

    分区边界是未知的,因此应用程序必须在插入数据的同时使用分区敏感语法来提供该信息。您需要将该语句 重新编写为:

     

    SQL> insert into sales3 partition (p1) values (1,101,1);
    
    1 row created.
    


    删除时,您不必提供分区敏感语法,但是记住,没有分区边界的概念。因此,当您发出如下语句时:

    SQL> delete sales3 where state_code = 1;
    


    Oracle 必须扫描所有分区,以查看该行所在的位置。为了避免出现这种情况,您应将其重新编写为:

    SQL> delete sales3 partition (p1) where state_code = 1;
    


    更新也是一样的。这限制了搜索该记录的分区范围。

    如果一个表不能以任何逻辑方式进行分区,那么系统分区将提供惊人的优势。它们使您能够利用分区的优势,同时允许开发人员自由决定记录将进入哪个分区。

    单个分区的表空间传输

    在 Oracle Database 的早期版本中,您能够传输表空间,并且稍后能将其插入不同数据库或同一个数据 库中。该过程涉及复制数据文件,因此它是跨数据库传输数据的最快方法。然而直到现在,您仍然无法传输 单个分区的表空间,然后再将其插回数据库中。在 Oracle Database 11g 中,您可以这么做。


    假设您有一个名为 SALES5 的表,它包括了几个名为 CT、NY 等的分区。

    SQL> select partition_name, tablespace_name
      2  from user_tab_partitions
      3  where table_name = 'SALES5';
    
    
    PARTITION_NAME TABLESPACE_NAME
    -------------- ---------------
    CT             TS1
    NY             TS2
    
    

    现在,您可以使用如下命令传输 CT 分区:

     

    $ expdp tables=scott.sales5:ct transportable=always directory=data_pump_dir dumpfile=p_ct.dmp 
    
    Export: 
    Release 11.1.0.4.0 - Beta on Sunday, 10 June, 2007 16:05:40 Copyright (c) 2003, 2005, Oracle. 
    All rights reserved. Username: / as sysdba
    
    Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.4.0 - Beta
    With the Partitioning, Oracle Label Security, OLAP, Data Mining
    and Oracle Database Vault options
    Starting "SYS"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA 
    
    tables=scott.sales5:ct transportable=
      always directory=data_pump_dir dumpfile=p_ct.dmp 
    Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK
    Processing object type TABLE_EXPORT/TABLE/TABLE
    Processing object type TABLE_EXPORT/TABLE/END_PLUGTS_BLK
    Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
    ****************************************************************************
    Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
      /home/oracle/oracle/admin/PROBE2/dpdump/p_ct.dmp
    ******************************************************************************
    Datafiles required for transportable tablespace TS1:
      /home/oracle/oradata/PROBE2/PROBE2/ts1_01.dbf
    Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 16:05:55
    
    

    现在,可以将 p_ct.dmp 和 ts1_01.dmp 这两个文件传递到其他系统,然后尝试将其插入到数据库中。出 于学习目的,我们尝试插入到相同的数据库中。首先,您需要删除该表,然后删除 ts1 表空间。

    SQL> drop table scott.sales5;
     
    Table dropped.
     
    SQL> drop tablespace ts1 including contents;
     
    Tablespace dropped.
    


    现在,将表空间插入到数据库中。但这里有个小问题:sales5 表不再存在,您最初只导出了一个分区 (ct) ,并未导出整个表。那么如何只导入不存在的表的一个分区呢?

    在 Oracle Database 11g 中,Data Pump 导入中的一个名为 partition_options 的新命令行 选项可实现此操作。如果您指定了值 departition,Data Pump 将根据导出的分区创建一个新表。该方法以 这种方式“拆分”分区,因此它相应地被命名为取消分区。我们来看一下它的工作原理。

    $ impdp partition_options=departition dumpfile=p_ct.dmp
       transport_datafiles='/home/oracle/oradata/PROBE2/PROBE2
    /ts1_01.dbf'
    
    Import: Release 11.1.0.4.0 - Beta on Sunday, 10 June, 2007 21:58:08
    
    Copyright (c) 2003, 2005, Oracle.  All rights reserved.
    
    Username: / as sysdba
    
    Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.4.0 - Beta
    With the Partitioning, Oracle Label Security, OLAP, Data Mining
    and Oracle Database Vault options
    Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_04" successfully 
    
    loaded/unloaded
    Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_04":  /******** AS SYSDBA 
    
    partition_options=
       departition dumpfile=p_ct.dmp 
    
    transport_datafiles=/home/oracle/oradata/PROBE2/PROBE2/ts1_01.dbf 
    Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK
    Processing object type TABLE_EXPORT/TABLE/TABLE
    Processing object type TABLE_EXPORT/TABLE/END_PLUGTS_BLK
    Job "SYS"."SYS_IMPORT_TRANSPORTABLE_04" successfully completed at 21:58:23
    


    该 SQL 创建了一个名为 sales5_ct 的表,这个表只是由先前可传输的表空间导出的 SALES5 表的 ct 分区 。如您所见,表名是原始表名和分区名的组合。可以通过检查 DBA_SEGMENTS 视图来确认段的存在。

    SQL> select segment_name
      2  from dba_segments
      3  where tablespace_name = 'TS1';
     
    SEGMENT_NAME
    -----------------
    SALES5_CT
    


    您可以使用单个分区的可传输表空间特性将一个表的单个分区插入到其他数据库中。插入之后,您可能希望 执行交换分区操作,以便将其作为一个分区置于某个表上。


    根据虚拟列分区

    我们来看另一个常见问题。在名为 sales 的表中,您具有以下列:

    SQL> desc sales
     Name                                      Null?    Type
     -----------------------------------------                     --------     ------
     SALES_ID                                  NOT NULL NUMBER
     CUST_ID                                   NOT NULL NUMBER
     SALES_AMT                                          NUMBER
    


    假设您希望按照某个允许您进行清除的方案对该表进行分区,并且基于销售额进行存档。以下是销售的四个 类别:

     

     

     

    如果 sale_amt 为

    且 cust_id 为

    则 sale_category 为

    0-10000

    任何内容

    LOW

    10001-100000

    0-100

    LOW

    10001-100000

    101-200

    MEDIUM

    10001-100000

    >200

    HIGH

    100001-1000000

    0-100

    MEDIUM

    100001-1000000

    101-200

    HIGH

    100001-1000000

    >200

    ULTRA

    >1000000

    任何内容

    ULTRA


    您希望根据 sale_category 列对该表进行分区,但有一个问题:没有名为 sale_category 的列。这是您从 sale_amt 列派生的列。那么您如何对该表进行分区呢?

    在 Oracle 的早期版本中,您可能已经在表中插入了名为 sale_category 的新列,并使用一个触发器用 表中所示的逻辑填充该列。但是由于触发器,这个新列的存在可能会导致其他性能影响。

    在 Oracle Database 11g 中,一个称为虚拟列的新特性使您能够创建一个并不存储在表中的列 ,但在运行时将计算该列。您还可以根据该列进行分区。使用此特性,对该表进行分区就变得轻而易举。

    create table sales
    (
       sales_id      number,
       cust_id       number,
       sales_amt     number,
       sale_category varchar2(6)
       generated always as
       (
          case
             when sales_amt <= 10000
                then 'LOW'
             when sales_amt > 10000
                and sales_amt <= 100000
                then case
                   when 
    
    cust_id < 101 then 'LOW'
                   when 
    
    cust_id between 101 and 200 then 'MEDIUM'
                   else 
    
    'MEDIUM'
                end
             when sales_amt > 100000
                and sales_amt <= 1000000
                then case
                   when 
    
    cust_id < 101 then 'MEDIUM'
                   when 
    
    cust_id between 101 and 200 then 'HIGH'
                   else 
    
    'ULTRA'
                end
             else 'ULTRA'
          end
        ) virtual
    )
    partition by list (sale_category)
    (
       partition p_low values ('LOW'),
       partition p_medium values ('MEDIUM'),
       partition p_high values ('HIGH'),
       partition p_ultra values ('ULTRA')
    )
    


    现在,如果您插入以下行:

    SQL> insert into sales (sales_id,cust_id,sales_amt) values (1,1,100);  1 row created.  
    SQL> insert into sales (sales_id,cust_id,sales_amt) values (2,1,1500);  1 row created.  
    SQL> insert into sales (sales_id,cust_id,sales_amt) values (3,102,1500);  1 row created.  
    SQL> insert into sales (sales_id,cust_id,sales_amt) values (4,102,10000);  1 row created.  
    SQL> commit;  Commit complete.
    


    该记录放在相应的分区中。
     
    根据虚拟列分区使您能够创建对业务重要的分区,即使列本身是不存在的。这里,您已经对虚拟列使用了一个非常简单的计算方法,但它也可以如您希望的那样复杂。在这些情况下,根据虚拟列进行分区将变得更有价值。

    Partition Advisor

    在设计分区方案的过程中,您考虑的最主要的问题可能是选择分区方案和分区列的决定。这个任务最好留给进行广泛工作负载分析的经验丰富的专业人员,即使是他们可能也无法做出正确的决定。您可以在 Oracle Database 11g 中通过一个名为 Partition Advisor 的新顾问工具获得帮助,该工具可以分析数据 、访问模式并建议分析方案。您可以在这一部分中了解有关该工具的更多信息。

    总结

    分区一直是 Oracle Database 11g 中最有用的工具之一,它将变得越来越有用:

    • 引用分区允许您对同一数据库中的相关表进行均值分区,即使所有子表中都不存在相应的列。
    • 间隔分区包括了非常有用的即发即弃功能 — 您定义一个时间间隔,Oracle 会永远进行维护 。
    • 将组合分区扩展到范围-范围、列表-范围、列表-散列和列表-列表可以提供更好的分区选择以及可 管理性。
    • Data Pump 现在允许您传输和插入单个分区,该特性在存档和保留方面肯定非常有用。
    • 最后,您可以设计最可能的分区策略,通过根据虚拟列进行分区来模拟业务流。

    “分而治之”的策略从未提供过如此多的选择。可以将它们设想为另一套可以割下火鸡身上最好部分的锋利刀子!

     

    本文摘自:通过分区进行优化

    展开全文
  • Oracle分区类型: Oracle10只有 Range 、 List 11g:Range、List、Hash List-List List-Range List-Hash Range-Range Range-List Range-Hash range:范围分区、 List:列值分区 hash:哈希分区 优化: 1.当查询...

    Oracle分区类型:

    Oracle10只有 Range 、 List  

    11g:Range、List、Hash

    List-List List-Range List-Hash

    Range-Range Range-List Range-Hash


    range:范围分区、 List:列值分区 hash:哈希分区

    优化:

    1.当查询时候,通过执行计划可以观察扫描分区个数,经常会遇到全分区扫描(局部索引),根据索引的高度特性,这样的性能 相比 走全表索引较差,因为Oracle索引高度一般很低。

    解决:对于查询优化:需要有相对的字段落在特定分区里面。

    2.对exchange、add、drop 、truncate等对分区操作,特别注意索引是否失效。

    3.对于分区字段和索引:简而言之分区字段如果不是索引字段,就不会走索引

    展开全文
  • ORACLE分区与索引

    2014-05-05 09:20:25
    对orcle大数据的查询的优化ORACLE分区表、分区索引ORACLE对于分区表方式其实就是将表分段存储,一般普通表格是一个段存储,而分区表会分成多个段,所以查找数据过程都是先定位根据查询条件定位分区范围,即数据在...
  • Oracle分区表及分区索引

    千次阅读 2017-08-10 17:06:52
    Oracle分区索引 索引与表类似,也可以分区; 分区索引分为两类: Locally partitioned index(局部分区索引)Globally partitioned index(全局分区索引) 下面就来详细解析一下这两类索引。   一:...
  • 本课程详细介绍了Oracle数据库的分区表机制、分区表应用场景、分区表与分区索引类型/创建/维护操作、分区裁剪等,结合大量的案例程序,对Oracle分区表的使用进行了详细的分析讲解。
  • 内容介绍原文档由会员 灵活的胖子 发布浅议Oracle分区管理与优化本文共计7页,5419字;摘要本文首先介绍了Oracle分区管理。先从分区的6个优点入手,分析了使用分区的原因和常用方法。着重介绍了基于量程的分区:...
  • Oracle分区

    2010-06-03 19:34:00
    使用分区表进行大数据量的优化查询。
  • 基于Oracle分区技术的HIS系统优化.pdf
  • 首先我们从一个图书馆引出话题(又是那个图书馆,...后面有学校再来接的时候说我要烹饪类 的书,他就可以直接找到,无需“全表扫描”,也不用翻目录了,这也就是我们今天所讲的分区分区表的概念 分区是Or
  • oracle优化

    2020-04-07 10:02:42
    oracle优化分区分区索引 分区 原文请点击 子分区 索引
  • oracle分区

    2011-09-19 22:59:35
    Oracle 性能优化——创建分区表 1、准备工作 创建相应的表空间,使各分区放于不同的表空间上: SQL> conn / as sysdba 已连接。 SQL> create tablespace test01 datafile 'D:xtdbtest01.dbf'
  • 上一篇文章中我们了解了oracle普通表转化为分区表的方法的相关内容,接下来的这篇文章,我们将探讨oracle分区索引的失效和重建问题,提供了相关代码示例供大家参考,具体如下。 --创建测试表 SQL> create table t ...
  • Oracle分区技术

    2010-09-01 23:42:00
    ORACLE分区(Partitioning Option)是一种处理超大型表的技术。分区是一种“分而治之”的技术,通过将大表和索引分成可以管理的小块,从而避免了对每个表作为一个大的、单独的对象进行管理,为大量数据提供了可伸缩...
  • oracle分区表按时间自动创建

    千次阅读 2017-05-09 20:28:23
    oracle分区表是oracle数据库提供的一种表分区的实现形式。表进行分区后,逻辑上仍然是一张表,原来的查询SQL同样生效,同时可以采用使用分区查询来优化SQL查询效率,不至于每次都扫描整个表。 根据年: INTERVAL...
  • Oracle分区表常用命令

    2014-04-11 09:07:03
    一、Oracle分区简介 ORACLE的分区是一种处理超大型表、索引等的技术。分区是一种“分而治之”的技术,通过将大表和索引分成可以管理的小块,从而避免了对每个表作为一个大的、单独的对象进行管理,为大量数据提供了...
  • 导入导出 Oracle 分区表数据

    万次阅读 2011-03-13 19:39:00
    --****************************-- 导入导出 Oracle 分区表数据--**************************** 导入导入Oracle 分区表数据是Oracle DBA 经常完成的任务之一。分区表的导入导出同样普通表的导入导出方式,只不过...
  • oracle分区技术-大批量数据操作,优化处理,大数据量的处理
  • oracle分区表执行计划

    千次阅读 2014-12-18 09:37:40
    分区表有很多好处,以大化小,一小化了,加上并行的使用,在loap中能往往能提高几十倍甚至几百倍的效果。当然表设计得不好也会适得其反,效果比普通表跟糟糕。 为了更好的使用分区表,这里看一下分区表的执行计划。 ...
  • oracle分区知识

    2010-10-15 10:52:58
    Oracle分区的类型 1) 范围分区实例 2) 哈希分区实例 3) 列表分区实例 4) 复合分区实例 Oracle分区对应用系统程序开发的影响和优化策略
  • 】在用分析命令对分区索引进行分析时,每一个分区的数据值的范围信息会放入Oracle的数据字典中。Oracle可以利用这个信息来提取出那些只与SQL查询相关的数据分区。例如,假设你已经定义了一个分区索引,并且某个SQL...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 35,710
精华内容 14,284
关键字:

oracle分区优化