精华内容
下载资源
问答
  • Oracle修改表及分区表的表空间

    千次阅读 2019-05-23 10:11:00
    一、oracle 移动分区表到指定表空间,及修改表的默认表空间 1、修改分区表的默认表空间:select 'alter table '||table_name||' modify default attributes tablespace TSDAT01' from dba_tables where table_name ...

    一、oracle 移动分区表到指定表空间,及修改表的默认表空间

    1、修改分区表的默认表空间:
    select 'alter table '||table_name||' modify default attributes tablespace TSDAT01' from dba_tables where table_name in ('T');


    查询出的语句直接执行
    2、移动现有分区到其他表空间
    begin
    for x in(select TABLE_OWNER,table_name,partition_name from dba_tab_partitions where table_name in ('T'))
    loop
    execute immediate 'alter table '||x.table_owner||'.'||x.table_name||' move partition ' || x.partition_name || ' tablespace users';
    end loop;
    end;
    /

     

    二、Oracle修改表Table所属表空间及Clob、Blob字段的处理
    SELECT TABLE_NAME,TABLESPACE_NAME FROM USER_TABLES -- WHERE TABLE_NAME='test' --可以加上条件
    SELECT 'alter table '||TABLE_NAME||' move tablespace TEST_TS;' FROM USER_TABLES WHERE TABLESPACE_NAME = 'USERS';
    SELECT 'alter index '|| INDEX_NAME ||' rebuild tablespace TEST_TS;' FROM user_indexes;
    ALTER TABLE TEST2 MOVE TABLESPACE USERS LOB(col_lob1,col_lob2) STORE AS(TABLESPACE TEST_TS);

    转载于:https://www.cnblogs.com/ayumie/p/10910312.html

    展开全文
  • ORACLE 分区表简介

    2014-10-15 11:10:37
    ORACLE-分区表 此文从以下几个方面来整理关于分区表的概念及操作: 1.表空间及分区表的概念 2.表分区的具体作用 3.表分区的优缺点 4.表分区的几种类型及操作方法 5.对表分区的维护性操作....
    ORACLE-分区表
    
    此文从以下几个方面来整理关于分区表的概念及操作:


    1.表空间及分区表的概念


    2.表分区的具体作用


    3.表分区的优缺点


    4.表分区的几种类型及操作方法


    5.对表分区的维护性操作.


    6.表的分区做过改变后索引问题


    7.将一个非分区表转换为分区表


      
    (1) 表空间及分区表的概念


    表空间:
      是一个或多个数据文件的集合,所有的数据对象都存放在指定的表空间中,但主要存放的是表, 所以称作表空间。


    分区表: 
           当表中的数据量不断增大,查询数据的速度就会变慢,应用程序的性能就会下降,这时就应该考虑对表进行分区。表进行分区后,逻辑上表仍然是一张完整的表,只是将表中的数据在物理上存放到多个“表空间”(物理文件上),这样查询数据时,不至于每次都扫描整张表而只是从当前的分区查到所要的数据大大提高了数据查询的速度。


      
    (2).表分区的具体作用


    Oracle的表分区功能通过改善可管理性、性能和可用性,从而为各式应用程序带来了极大的好处。通常,分区可以使某些查询以及维护操作的性能大大提高。此外,分区还可以极大简化常见的管理任务,分区是构建千兆字节数据系统或超高可用性系统的关键工具。 分区功能能够将表、索引或索引组织表进一步细分为段,这些数据库对象的段叫做分区。每个分区有自己的名称,还可以选择自己的存储特性。从数据库管理员的角度来看,一个分区后的对象具有多个段,这些段既可进行集体管理,也可单独管理,这就使数据库管理员在管理分区后的对象时有相当大的灵活性。但是,从应用程序的角度来看,分区后的表与非分区表完全相同,使用 SQL DML 命令访问分区后的表时,无需任何修改。


     什么时候使用分区表:
           1、表的大小超过2GB。


    2、表中包含历史数据,新的数据被增加到新的分区中。


      
    (3).表分区的优缺点


    表分区有以下优点: 
           1、改善查询性能:对分区对象的查询可以仅搜索自己关心的分区,提高检索速度。


    2、增强可用性:如果表的某个分区出现故障,表在其他分区的数据仍然可用;


    3、维护方便:如果表的某个分区出现故障,需要修复数据,只修复该分区即可;


    4、均衡I/O:可以把不同的分区映射到不同磁盘以平衡I/O,改善整个系统性能。


     缺点: 
            分区表相关:已经存在的表没有方法可以直接转化为分区表。不过 Oracle 提供了在线重定义表的功能。


      
    (4).表分区的几种类型及操作方法


     


    一.范围分区:
           范围分区将数据基于范围映射到每一个分区,这个范围是你在创建分区时指定的分区键决定的。这种分区方式是最为常用的,并且分区键经常采用日期。举个例子:你可能会将销售数据按照月份进行分区。


    当使用范围分区时,请考虑以下几个规则:


    1、每一个分区都必须有一个VALUES LESS THAN子句,它指定了一个不包括在该分区中的上限值。分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个高一些的分区中。


    2、所有分区,除了第一个,都会有一个隐式的下限值,这个值就是此分区的前一个分区的上限值。


    3、在最高的分区中,MAXVALUE被定义。MAXVALUE代表了一个不确定的值。这个值高于其它分区中的任何分区键的值,也可以理解为高于任何分区中指定的VALUE LESS THAN的值,同时包括空值。


    例一:


    假设有一个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 CUS_TS01, 
        PARTITION CUS_PART2 VALUES LESS THAN (200000) TABLESPACE CUS_TS02 
    )


    例二:按时间划分


    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- MAY -2003','DD-MON-YYYY')) TABLESPACEORD_TS01,


      PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUN-2003','DD-MON-YYYY')) TABLESPACE ORD_TS02,


      PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUL-2003','DD-MON-YYYY')) TABLESPACE ORD_TS03


    )


    例三:MAXVALUE


    CREATE TABLE RangeTable

      idd   INT PRIMARY KEY , 
      iNAME VARCHAR(10), 
      grade INT  

    PARTITION  BY  RANGE (grade) 

          PARTITION  part1 VALUES  LESS  THAN (1000) TABLESPACE  Part1_tb, 
          PARTITION  part2 VALUES  LESS  THAN (MAXVALUE) TABLESPACE         Part2_tb 
    );


     




    二.列表分区:


    该分区的特点是某列的值只有几个,基于这样的特点我们可以采用列表分区。


    例一


    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 PROB_TS01, 
          PARTITION PROB_INACTIVE VALUES ('INACTIVE') TABLESPACE PROB_TS02)


    例二


    CREATE  TABLE  ListTable

        id    INT  PRIMARY  KEY , 
        name  VARCHAR (20), 
        area  VARCHAR (10) 

    PARTITION  BY  LIST (area) 

        PARTITION  part1 VALUES ('guangdong','beijing') TABLESPACE  Part1_tb, 
        PARTITION  part2 VALUES ('shanghai','nanjing')  TABLESPACE  Part2_tb 
    );


     


    三.散列分区(也称HASH分区):
           这类分区是在列值上使用散列算法,以确定将行放入哪个分区中。当列的值没有合适的条件时,建议使用散列分区。


    散列分区为通过指定分区编号来均匀分布数据的一种分区类型,因为通过在I/O设备上进行散列分区,使得这些分区大小一致。


    例一:


    CREATE TABLE HASH_TABLE 

      COL NUMBER(8), 
      INF VARCHAR2(100) 

    PARTITION BY HASH (COL) 

      PARTITION PART01 TABLESPACE HASH_TS01, 
      PARTITION PART02 TABLESPACE HASH_TS02, 
      PARTITION PART03 TABLESPACE HASH_TS03 
    )


    简写:


    CREATE TABLE emp
    (
        empno NUMBER (4),
        ename VARCHAR2 (30),
        sal   NUMBER 
    )
    PARTITION BY  HASH (empno) PARTITIONS 8
    STORE IN (tbs01,tbs02,tbs03,tbs04,tbs05,tbs06,tbs07,tbs08);


    hash分区最主要的机制是根据hash算法来计算具体某条纪录应该插入到哪个分区中,hash算法中最重要的是hash函数,Oracle中如果你要使用hash分区,只需指定分区的数量即可。建议分区的数量采用2的n次方,这样可以使得各个分区间数据分布更加均匀。


     


    四.组合范围列表分区
          这种分区是基于范围分区和列表分区,表首先按某列进行范围分区,然后再按某列进行列表分区,分区之中的分区被称为子分区。


    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('2003-01-01','YYYY-MM-DD'))TABLESPACE rptfact2009 
              ( 
                  SUBPARTITION P1SUB1 VALUES ('ACTIVE') TABLESPACE rptfact2009, 
                  SUBPARTITION P1SUB2 VALUES ('INACTIVE') TABLESPACE rptfact2009 
              ), 
       PARTITION P2 VALUES LESS THAN (TO_DATE('2003-03-01','YYYY-MM-DD')) TABLESPACE rptfact2009 
              ( 
                  SUBPARTITION P2SUB1 VALUES ('ACTIVE') TABLESPACE rptfact2009, 
                  SUBPARTITION P2SUB2 VALUES ('INACTIVE') TABLESPACE rptfact2009 
              ) 
    )


     
    五.复合范围散列分区:


    这种分区是基于范围分区和散列分区,表首先按某列进行范围分区,然后再按某列进行散列分区。


    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 3 store in (dinya_space01,dinya_space02,dinya_space03) 
     ( 
         partition part_01 values less than(to_date(‘2006-01-01’,’yyyy-mm-dd’)), 
         partition part_02 values less than(to_date(‘2010-01-01’,’yyyy-mm-dd’)), 
         partition part_03 values less than(maxvalue) 
     );


     


    (5).有关表分区的一些维护性操作: 
    一、添加分区 
    以下代码给SALES表添加了一个P3分区


    ALTER  TABLE  SALES  ADD  PARTITION P3  VALUES  LESS  THAN (TO_DATE('2003-06-01','YYYY-MM-DD'))  TABLESPACE  SPACE_NAME;


    注意:增加一个分区的时候,增加的分区的条件必须大于现有分区的最大值,否则系统将提示ORA-14074 partition bound must collate higher than that of the last partition 错误。


    以下代码给SALES表的P3分区添加了一个P3SUB1子分区


    ALTER  TABLE  SALES  MODIFY  PARTITION  P3  ADD SUBPARTITION P3SUB1 VALUES('COMPLETE')  TABLESPACE  SPACE_NAME;


    二、删除分区 
    以下代码删除了SALES表中名为P3的分区:


    ALTER  TABLE  SALES  DROP  PARTITION  P3;


    在以下代码删除了P4SUB1子分区:


    ALTER  TABLE  SALES  DROP  SUBPARTITION  P4SUB1;


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


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


    ALTER  TABLE  SALES  TRUNCATE  PARTITION  P2;


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


    ALTER  TABLE  SALES  TRUNCATE  SUBPARTITION  P2SUB2;


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


    ALTER  TABLE  SALES  MERGE  PARTITIONS  P1, P2  INTO  PARTITION  P2;


    五、拆分分区 
    拆分分区将一个分区拆分两个新分区,拆分后原来分区不再存在但是如果表存在PMAX分区那么原来的分区还是可以存在的。注意不能对 HASH类型的分区进行拆分。


    ALTER  TABLE  SALES  SBLIT  PARTITION P2  AT(TO_DATE('2003-02-01','YYYY-MM-DD'))  INTO  (PARTITION P21,PARTITION P22);


    此拆分语句是将P2分区拆分为PARTITION P21与PARTITION P22两个分区,日期小于2003-02-01的数据存于 P21分区中否则存于 P22分区中。










    --包含PMAX分区的表拆分分区


    ALTER TABLE  hs_his.HISHOLDSINFO  SPLIT  PARTITION  PMAX  AT(20100900)  INTO (partition P201008, partition PMAX);


    此拆分语句是将 PMAX 分区拆分为P201008与  PMAX 两个分区,数据小于20100900 的数据存于  P201008 分区中否则存于 PMAX 分区中。


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


    ALTER TABLE SALES COALESCA PARTITION;


    七、重命名表分区 
    以下代码将P21更改为P2


    ALTER TABLE SALES RENAME PARTITION P21 TO P2;


    八、相关查询
    跨分区查询


    select sum( *) from


    (select count(*) cn from t_table_SS PARTITION (P200709_1)


    union all


    select count(*) cn from t_table_SS PARTITION (P200709_2)


    );


    查询表上有多少分区


    SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME='tableName'


    查询索引信息


    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 4 desc


     


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


    select * from DBA_PART_TABLES


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


    select * from ALL_PART_TABLES


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


    select * from USER_PART_TABLES


    --显示表分区信息 显示数据库所有分区表的详细分区信息:


    select * from DBA_TAB_PARTITIONS


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


    select * from ALL_TAB_PARTITIONS


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


    select * from USER_TAB_PARTITIONS


    --显示子分区信息 显示数据库所有组合分区表的子分区信息:


    select * from DBA_TAB_SUBPARTITIONS


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


    select * from ALL_TAB_SUBPARTITIONS


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


    select * from USER_TAB_SUBPARTITIONS


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


    select * from DBA_PART_KEY_COLUMNS


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


    select * from ALL_PART_KEY_COLUMNS


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


    select * from USER_PART_KEY_COLUMNS


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


    select * from DBA_SUBPART_KEY_COLUMNS


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


    select * from ALL_SUBPART_KEY_COLUMNS


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


    select * from USER_SUBPART_KEY_COLUMNS


    --怎样查询出oracle数据库中所有的的分区表


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


    --删除一个表的数据是


    truncate table table_name;


    --删除分区表一个分区的数据是


    alter table table_name truncate partition p5;


     


    6.表的分区做过改变后索引问题


    注意,在维护分区的时候可能会对索引产生一定的影响,会引起分区表的全局索引无效,需要重建索引。


      对分区表做了维护操作后,必须检查相关索引,如检查hisdeliverx表的非分区索引:


       select owner,index_name,status from dba_indexes where table_name='HISDELIVERX';


      若有索引的状态为unusable,则必须使用alter index index_name rebuild online;重建该索引。


    一般建议在拆分分区、truncate 分区中的数据、删除分区等功作时最好在语句后面加上update indexes子句,


    这样索引为unusable状态的可能性就会很低,如下语句:


    ALTER TABLE  table_name  SPLIT  PARTITION  partition_name1  AT(20000)  INTO (


     partition partition_name2,partition partition_name3) update indexes;


    alter table table_name truncate partition  partition_name update indexes;


    alter table table_name drop partition partition_name update indexes;


     


    7.将一个非分区表转换为分区表


    使用exchange partition方法


    假设把hisdeliverx由非分区表改为分区表。


    基本思路:hisdeliverx是(数据量上百万条,列比较多)一个非分区表,此时创建一个与hisdeliverx同结构的分区表t_hisdeliverx,交换和维护数据,删除hisdeliverx表,将t_hisdeliverx更名为hisdeliverx。然后再重建hisdeliverx表上的索引。


    --具体步骤:


    --创建分区表(结构和非分区表hisdeliverx相同)


    create table t_HISDELIVERX


    (


      INIT_DATE        NUMBER(10) default to_number(to_char(sysdate,'yyyymmdd')) not null,


      SERIAL_NO        NUMBER(10) default 0 not null,


    ……


    ……


    ---交换数据(数据从非分区表到分区表)


    SQL> alter table t_hisdeliverx exchange partition pmax with table hisdeliverx;


    Table altered


    SQL> drop table hisdeliverx;


    ---删除非分区表     


    Table dropped


    ---将分区表重命名为原非分区表名     


    SQL> alter table t_hisdeliverx rename to hisdeliverx;


    Table altered


    ---检查hideliverx表是否为分区表


    select table_owner,table_name,partition_name from dba_tab_partitions a where a.table_owner='HS_HIS' and


    a.table_name='HISDELIVERX';


    注意,在做exchange partition操作前先对该表进行备份。
    展开全文
  • oracle 表分区 表分区示例

    千次阅读 2008-04-15 15:04:00
    分区方法概述Oracle 提供了一下几种分区方法: 范围分区 列表分区 哈希分区 复合分区 何时应该对表进行分区 图 18-2 列表分区,范围分区,及哈系分区 图 18-2 显示了依据销售区域进行列表分区,以两个月一区间...

    分区方法概述

    Oracle 提供了一下几种分区方法

    范围分区

    列表分区

    哈希分区

    复合分区

     

    何时应该对表进行分区

    18-2 列表分区,范围分区,及哈系分区

     

     

    图 18-2 显示了依据销售区域进行列表分区,以两个月为一区间进行范围分区,以及按哈希组(h1,h2,h3,h4)进行哈希分区。

     

    用户还可以将多种分区方法组合进行复合分区(composite partitioning)。Oracle 支持范围-哈希(range-hash)复合分区及范围-列表(range-list)复合分区。 18-3 展示了这两种复合分区。

    18-3 复合分区

     

     

    图 18-3 显示了使用哈希组(h1,h2,h3,h4)的范围-哈希复合分区,以及范围-列表复合分区(时间区间(January 到 February,March 到 April,May 到 June)及地理区域列表)。

     

     

    范围分区

    范围分区(range partitioning)依据用户创建分区时设定的分区键值(partition key value)范围将数据映射到不同分区。范围分区是较常用的分区方式,通常针对日期数据使用。例如,用户可以将销售数据按月存储到相应的分区中。

    在采用范围分区时,应注意以下规则:

    • 定义分区时必须使用 VALUES LESS THAN 子句定义分区的开区间上限(noninclusive upper bound)。分区键大于等于此修饰符(literal)的数据将被存储到下一个分区中。
    • 除了第一个分区之外,其他所有分区都有一个隐式的下限(lower bound),此下限是由上一个分区的 VALUES LESS THAN 子句指定的。
    • 用户可以为最大分区定义一个 MAXVALUE 修饰符。MAXVALUE 代表一个无穷大值,用于识别大于所有可能分区键的数据(包括 null)。

    下面的语句给出一个典型的范围例子。此语句创建了依据 sales_date 字段进行范围分区的表 sales_range

     

    CREATE TABLE sales_range ( salesman_id NUMBER(5), salesman_name VARCHAR2(30), sales_amount NUMBER(10), sales_date DATE) PARTITION BY RANGE(sales_date)( PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','MM/DD/YYYY')), PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','MM/DD/YYYY')), PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','MM/DD/YYYY')), PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','MM/DD/YYYY')) );

     

    列表分区

    用户可以采用列表分区(list partitioning)显示地控制如何将数据行映射到各个分区。用户在各分区的定义中指定一个分区键(partitioning key)离散值的列表,从而实现列表分区。列表分区与范围分区(range partitioning)有所不同,在范围分区中是为每个分区设定一个分区键值的范围;列表分区与哈希分区也有区别,哈希分区是通过一个哈希函数(hash function)控制数据行与分区间的映射关系。用户可以采用列表分区,将无序(unordered)或互不相关(unrelated)的数据进行分组整理。

    下面是一个列表分区的示例。在此例子中,用户需要按区域对销售数据进行分区。即把地理位置接近的州归为一组。

     

    CREATE TABLE sales_list( salesman_id NUMBER(5), salesman_name VARCHAR2(30), sales_state VARCHAR2(20), sales_amount NUMBER(10), sales_date DATE) PARTITION BY LIST(sales_state)( PARTITION sales_west VALUES('California', 'Hawaii'), PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida'), PARTITION sales_central VALUES('Texas', 'Illinois'), PARTITION sales_other VALUES(DEFAULT) );

     

    在将数据行映射到分区的过程中,Oracle 检查数据行的分区键值是否包含于某分区定义的值列中。以下面的数据为例:

    • (10'Jones''Hawaii'100'05-JAN-2000') 映射到 sales_west 分区
    • (21'Smith''Florida'150'15-JAN-2000') 映射到 sales_east 分区
    • (32'Lee''Colorado'130'21-JAN-2000') 映射到 sales_other 分区

    与范围分区(range partitioning)及哈希分区(hash partitioning)有所区别,列表分区不支持分区键中包含多列。如果一个表采用列表分区方式,那么分区键只能由此表的一个数据列构成。  用户可以定义一个 DEFAULT 分区,在定义了此分区后,定义列表分区表时不必列出所有可能的分区键值,Oracle 在处理数据时也不会出现无法映射的情况.

    哈希分区

    用户可以采用哈希分区(hash partitioning)将不适于采用范围分区(range partitioning)或列表分区(list partitioning)的数据进行分区。哈希分区的语法(syntax)简单且易于实现。在以下情况时哈希分区比范围分区更适用:

    • 用户无法事先确定一个分区可能存储的数据量
    • 各范围分区的容量可能相差很大,或很难通过人工进行平衡
    • 采用范围分区可能导致数据不正常的集中
    • 应用系统对并行 DMLparallel DML),分区剪除(partition pruning),及基于分区的关联(partition-wise joins)等与性能有关的分区特性要求较高

    分割(splitting),移除(dropping ),及融合(merging)等操作不适用于哈希分区。但对哈希分区可以进行添加(add)及接合(coalesce)操作。

    CREATE TABLE sales_hash( salesman_id NUMBER(5), salesman_name VARCHAR2(30), sales_amount NUMBER(10), week_no NUMBER(2)) PARTITION BY HASH(salesman_id) PARTITIONS 4 STORE IN (ts1, ts2, ts3, ts4);

     

     

    复合分区

    复合分区(composite partitioning)首先根据范围(range)进行分区,再使用哈希或列表方式创建子分区。复合范围-哈希分区既能够发挥范围分区的可管理性优势,也能够发挥哈希分区的数据分布(data placement),条带化(striping),及并行化(parallelism)优势。复合范围-列表分区能够发挥范围分区的可管理性优势,也能利用列表分区的显示控制能力。

    复合分区(composite partitioning)便于用户进行与时间相关的维护操作(historical operation),例如添加新的范围分区等。同时复合分区还能够利用子分区(subpartitioning)实现高度的并行 DML 操作,并对数据分布进行精细的控制。  CREATE TABLE sales_composite ( salesman_id NUMBER(5), salesman_name VARCHAR2(30), sales_amount NUMBER(10), sales_date DATE) PARTITION BY RANGE(sales_date) SUBPARTITION BY HASH(salesman_id) SUBPARTITION TEMPLATE( SUBPARTITION sp1 TABLESPACE ts1, SUBPARTITION sp2 TABLESPACE ts2, SUBPARTITION sp3 TABLESPACE ts3, SUBPARTITION sp4 TABLESPACE ts4) (PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','MM/DD/YYYY')) PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','MM/DD/YYYY')) PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','MM/DD/YYYY')) PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','MM/DD/YYYY')) PARTITION sales_may2000 VALUES LESS THAN(TO_DATE('06/01/2000','MM/DD/YYYY')));

    上述语句创建了 sales_composite 表,首先依据 sales_date 字段创建范围分区(range partitioned),再依据 salesman_id 字段创建哈希子分区。如果用户在语句中使用了模板(template),Oracle 命名子分区的模式为分区名下划线再加模板中设定的子分区名。同样,Oracle 将子分区存储在模板中指定的表空间中。在上述语句中,子分区 sales_jan2000_sp1 存储在表空间 ts1 中,而子分区 sales_jan2000_sp4 存储在表空间 ts4 中。同样,子分区 sales_apr2000_sp1 存储在表空间 ts1 中,而子分区 sales_apr2000_sp4 存储在表空间 ts4 中。 18-4 为上述语句的图形化描述。

    18-4 复合范围-哈希分区

     

     

     

     

    复合范围-列表分区示例

     

    CREATE TABLE bimonthly_regional_sales( deptno NUMBER, item_no VARCHAR2(20), txn_date DATE, txn_amount NUMBER, state VARCHAR2(2)) PARTITION BY RANGE (txn_date) SUBPARTITION BY LIST (state) SUBPARTITION TEMPLATE( SUBPARTITION east VALUES('NY', 'VA', 'FL') TABLESPACE ts1, SUBPARTITION west VALUES('CA', 'OR', 'HI') TABLESPACE ts2, SUBPARTITION central VALUES('IL', 'TX', 'MO') TABLESPACE ts3) ( PARTITION janfeb_2000 VALUES LESS THAN (TO_DATE('1-MAR-2000','DD-MON-YYYY')), PARTITION marapr_2000 VALUES LESS THAN (TO_DATE('1-MAY-2000','DD-MON-YYYY')), PARTITION mayjun_2000 VALUES LESS THAN (TO_DATE('1-JUL-2000','DD-MON-YYYY')) );

    上述语句创建了 bimonthly_regional_sales 表,首先依据 txn_date 字段创建范围分区(range partitioned),再依据 state 字段创建子分区。如果用户在语句中使用了模板(template),Oracle 命名子分区的模式为分区名下划线再加模板中设定的子分区名。同样,Oracle 将子分区存储在模板中指定的表空间中。在上述语句中,子分区 janfeb_2000_east 存储在表空间 ts1 中,而子分区 janfeb_2000_central 存储在表空间 ts3 中。同样,子分区 mayjun_2000_east 存储在表空间 ts1 中,而子分区 mayjun_2000_central 存储在表空间 ts3 中。 18-5 显示了表 bimonthly_regional_sales 9 个子分区。

    18-5 复合范围-列表分区

     

     

    何时应该对表进行分区

    以下是关于何时应该对表进行分区的一些建议:

    • 如果表数据量超过 2GB,就应该考虑进行分区。
    • 如果表中包含历史数据,且新数据会被添加到最新的表空间中。典型的例子是一种历史表,其中只有当前月份的数据可以被修改,而其他十一个月的数据为只读

    例子:

    3.2. 分区表操作

      以上了解了三种分区表的建表方法,下面将使用实际的数据并针对按日期的范围分区来测试分区表的数据记录的操作。

    3.2.1. 插入记录

    SQL> insert into dinya_test values(1,12,’BOOKS’,sysdate); 1 row created. SQL> insert into dinya_test values(2,12, ’BOOKS’,sysdate+30); 1 row created.

    SQL> insert into dinya_test values(3,12, ’BOOKS’,to_date(’2006-05-30’,’yyyy-mm-dd’)); 1 row created. SQL> insert into dinya_test values(4,12, ’BOOKS’,to_date(’2007-06-23’,’yyyy-mm-dd’)); 1 row created. SQL> insert into dinya_test values(5,12, ’BOOKS’,to_date(’2011-02-26’,’yyyy-mm-dd’)); 1 row created. SQL> commit;

      按上面的建表结果,2006年前的数据将存储在第一个分区part_01上,而2006年到2010年的交易数据将存储在第二个分区part_02上,2010年以后的记录存储在第三个分区part_03上。

    3.2.2. 查询分区表记录

    SQL> select * from dinya_test partition(part_01);

      插入的数据已经根据交易时间范围存储在不同的分区中。这里是指定了分区的查询,当然也可以不指定分区,直接执行select * from dinya_test查询全部记录。检索的数据量很大的时候,指定分区会大大提高检索速度。

    3.2.3. 更新分区表的记录

    SQL> update dinya_test partition(part_01) t set t.item_description='DESK'  where t.transaction_id=1; 1 row updated. SQL> commit;

      这里将第一个分区中的交易ID=1的记录中的item_description字段更新为“DESK”,可以看到已经成功更新了一条记录。但是当更新的时候指定了分区,而根据查询的记录不在该分区中时,将不会更新数据,请看下面的例子:

    SQL> update dinya_test partition(part_01) t set t.item_description='DESK'  where t.transaction_id=6; 0 rows updated. SQL> commit;

      指定了在第一个分区中更新记录,但是条件中限制交易ID为6,而查询全表,交易ID为6的记录在第三个分区中,这样该条语句将不会更新记录。

    3.2.4. 删除分区表记录

    SQL> delete from dinya_test partition(part_02) t where t.transaction_id=4; 1 row deleted. SQL> commit; Commit complete. SQL>

      上面例子删除了第二个分区part_02中的交易记录ID为4的一条记录,和更新数据相同,如果指定了分区,而条件中的数据又不在该分区中时,将不会删除任何数据。

     

    ---------------------------------------------------------------------------------------------------

     表分区示例

     

    oracle 分区表的作用就是为了使用户的大量的数据在读写操作和查询中速度更快,Oracle提供了对表和索引进行分区的技术,以改善大型应用系统的性能。 具体的优点包括: ·增强可用性:如果表的某个分区出现故障,表在其他分区的数据仍然可用; ·维护方便:如果表的某个分区出现故障,需要修复数据,只修复该分区即可; ·均衡I/O:可以把不同的分区映射到磁盘以平衡I/O,改善整个系统性能; ·改善查询性能:对分区对象的查询可以仅搜索自己关心的分区,提高检索速度。

    Oracle数据库提供对表或索引的分区方法有三种: ·范围分区 ·Hash分区(散列分区) ·复合分区

    实例介绍

    1、准备工作首先建立三个表空间 create tablespace demotbsp01 datafile 'C:/oracle/product/10.2.0/oradata/stone/demo01.dbf' size 50M create tablespace demotbsp02 datafile 'C:/oracle/product/10.2.0/oradata/stone/demo02.dbf' size 50M create tablespace demotbsp03 datafile 'C:/oracle/product/10.2.0/oradata/stone/demo03.dbf' size 50M

    2、建立不同类型的分区表

    2.1.1. 范围分区

      范围分区就是对数据表中的某个值的范围进行分区,根据某个值的范围,决定将该数据存储在哪个分区上。如根据序号分区,根据业务记录的创建日期进行分区等。

      需求描述:有一个物料交易表,表名:material_transactions。该表将来可能有千万级的数据记录数。要求在建该表的时候使用分区表。这时候我们可以使用序号分区三个区,每个区中预计存储三千万的数据,也可以使用日期分区,如每五年的数据存储在一个分区上。

      根据交易记录的序号分区建表:

    SQL> create table dinya_test ( transaction_id number primary key, item_id number(8) not null, item_description varchar2(300), transaction_date date not null ) partition by range (transaction_id) ( partition part_01 values less than(30000000) tablespace demotbsp01, partition part_02 values less than(60000000) tablespace demotbsp02, partition part_03 values less than(maxvalue) tablespace demotbsp03 );

      建表成功,根据交易的序号,交易ID在三千万以下的记录将存储在第一个表空间dinya_space01中,分区名为:par_01,在三千万到六千万之间的记录存储在第二个表空间:

      demotbsp02中,分区名为:part_02,而交易ID在六千万以上的记录存储在第三个表空间demotbsp03中,分区名为part_03.

      根据交易日期分区建表:

    SQL> create table dinya_test ( transaction_id number primary key, item_id number(8) not null,

    item_description varchar2(300), transaction_date date not null ) partition by range (transaction_date) ( partition part_01 values less than(to_date('2006-01-01','yyyy-mm-dd')) tablespace demotbsp01, partition part_02 values less than(to_date('2010-01-01','yyyy-mm-dd')) tablespace demotbsp02, partition part_03 values less than(maxvalue) tablespace demotbsp03 );

      这样我们就分别建了以交易序号和交易日期来分区的分区表。每次插入数据的时候,系统将根据指定的字段的值来自动将记录存储到制定的分区(表空间)中。

      当然,我们还可以根据需求,使用两个字段的范围分布来分区,如partition by range ( transaction_id ,transaction_date), 分区条件中的值也做相应的改变,请读者自行测试。

    2.1.2. Hash分区(散列分区)

      散列分区为通过指定分区编号来均匀分布数据的一种分区类型,因为通过在I/O设备上进行散列分区,使得这些分区大小一致。如将物料交易表的数据根据交易ID散列地存放在指定的三个表空间中:

    SQL> create table dinya_test ( transaction_id number primary key, item_id number(8) not null, item_description varchar2(300), transaction_date date ) partition by hash(transaction_id) ( partition part_01 tablespace demotbsp01, partition part_02 tablespace demotbsp02, partition part_03 tablespace demotbsp03 );

      建表成功,此时插入数据,系统将按transaction_id将记录散列地插入三个分区中,这里也就是三个不同的表空间中。

    2.1.3. 复合分区

      有时候我们需要根据范围分区后,每个分区内的数据再散列地分布在几个表空间中,这样我们就要使用复合分区。复合分区是先使用范围分区,然后在每个分区内再使用散列分区的一种分区方法,如将物料交易的记录按时间分区,然后每个分区中的数据分三个子分区,将数据散列地存储在三个指定的表空间中:

    SQL> 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 3 store in (demotbsp01,demotbsp02,demotbsp03) ( partition part_01 values less than(to_date('2006-01-01','yyyy-mm-dd')), partition part_02 values less than(to_date('2010-01-01','yyyy-mm-dd')), partition part_03 values less than(maxvalue) ); Table created.

      该例中,先是根据交易日期进行范围分区,然后根据交易的ID将记录散列地存储在三个表空间中。

     3.2. 分区表操作

      以上了解了三种分区表的建表方法,下面将使用实际的数据并针对按日期的范围分区来测试分区表的数据记录的操作。

    3.2.1. 插入记录

    SQL> insert into dinya_test values(1,12,’BOOKS’,sysdate); 1 row created. SQL> insert into dinya_test values(2,12, ’BOOKS’,sysdate+30); 1 row created.

    SQL> insert into dinya_test values(3,12, ’BOOKS’,to_date(’2006-05-30’,’yyyy-mm-dd’)); 1 row created. SQL> insert into dinya_test values(4,12, ’BOOKS’,to_date(’2007-06-23’,’yyyy-mm-dd’)); 1 row created. SQL> insert into dinya_test values(5,12, ’BOOKS’,to_date(’2011-02-26’,’yyyy-mm-dd’)); 1 row created. SQL> commit;

      按上面的建表结果,2006年前的数据将存储在第一个分区part_01上,而2006年到2010年的交易数据将存储在第二个分区part_02上,2010年以后的记录存储在第三个分区part_03上。

    3.2.2. 查询分区表记录

    SQL> select * from dinya_test partition(part_01);

      插入的数据已经根据交易时间范围存储在不同的分区中。这里是指定了分区的查询,当然也可以不指定分区,直接执行select * from dinya_test查询全部记录。检索的数据量很大的时候,指定分区会大大提高检索速度。

    3.2.3. 更新分区表的记录

    SQL> update dinya_test partition(part_01) t set t.item_description='DESK'  where t.transaction_id=1; 1 row updated. SQL> commit;

      这里将第一个分区中的交易ID=1的记录中的item_description字段更新为“DESK”,可以看到已经成功更新了一条记录。但是当更新的时候指定了分区,而根据查询的记录不在该分区中时,将不会更新数据,请看下面的例子:

    SQL> update dinya_test partition(part_01) t set t.item_description='DESK'  where t.transaction_id=6; 0 rows updated. SQL> commit;

      指定了在第一个分区中更新记录,但是条件中限制交易ID为6,而查询全表,交易ID为6的记录在第三个分区中,这样该条语句将不会更新记录。

    3.2.4. 删除分区表记录

    SQL> delete from dinya_test partition(part_02) t where t.transaction_id=4; 1 row deleted. SQL> commit; Commit complete. SQL>

      上面例子删除了第二个分区part_02中的交易记录ID为4的一条记录,和更新数据相同,如果指定了分区,而条件中的数据又不在该分区中时,将不会删除任何数据。

    4.分区表的维护

      了解了分区表的建立、索引的建立、表和索引的使用后,在应用的还要经常对分区进行维护和管理。日常维护和管理的内容包括:增加一个分区,合并一个分区及删除分区等等。下面以范围分区为例说明增加、合并、删除分区的一般操作:

    4.1. 增加一个分区

    SQL> alter table dinya_test 2 add partition part_04 values less than(to_date(’2012-01-01’,’yyyy-mm-dd’)) tablespace dinya_spa ce03; Table altered. SQL>

      增加一个分区的时候,增加的分区的条件必须大于现有分区的最大值,否则系统将提示ORA-14074 partition bound must collate higher than that of the last partition 错误。

    4.2. 合并一个分区

    SQL> alter table dinya_test merge partitions part_01,part_02 into partition part_02; Table altered. SQL>

      在本例中将原有的表的part_01分区和part_02分区进行了合并,合并后的分区为part_02,如果在合并的时候把合并后的分区定为part_01的时候,系统将提示ORA-14275 cannot reuse lower-bound partition as resulting partition 错误。

    4.3. 删除分区

    SQL> alter table dinya_test drop partition part_01; Table altered. SQL>

      删除分区表的一个分区后,查询该表的数据时显示,该分区中的数据已全部丢失,所以执行删除分区动作时要慎重,确保先备份数据后再执行,或将分区合并。

      需要说明的是,本文在举例说名分区表事务操作的时候,都指定了分区,因为指定了分区,系统在执行的时候则只操作该分区的记录,提高了数据处理的速度。不要指定分区直接操作数据也是可以的。在分区表上建索引及多索引的使用和非分区表一样。此外,因为在维护分区的时候可能对分区的索引会产生一定的影响,可能需要在维护之后重建索引,相关内容请参考分区表索引部分的文档。

     

     

     

     

    正版全新Oracle数据库管理艺术11g新特性图书 书籍
    51.8元

    正版全新Oracle性能诊断艺术图书 书籍 超低价
    56.3元 
    正版全新数据库系统原理与应用:Oracle版图书 书籍 超
    20.8元 
    关系数据库ORACLE 9I 原理及应用 大学二手书籍/教材
    8.0元 
    正版全新ORACLE数据库精讲与疑难解析图书 书籍 超低价
    94.8元 

     

     

    展开全文
  • Oracle分区表

    万次阅读 2018-07-08 10:53:25
    分区是将一个或索引物理地分解多个更小、更可管理的部分。分区对应用透明,即对访问数据库的应用而言,逻辑上讲只有一个或一个索引(相当于应用“看到”的只是一个或索引),但在物理上这个或索引可能由数...

    一:什么是分区(Partition)?

    分区是将一个表或索引物理地分解为多个更小、更可管理的部分。

    分区对应用透明,即对访问数据库的应用而言,逻辑上讲只有一个表或一个索引(相当于应用“看到”的只是一个表或索引),但在物理上这个表或索引可能由数十个物理分区组成。

    每个分区都是一个独立的对象,可以独自处理,也可以作为一个更大对象的一部分进行处理。

    --------------------------Tips:分表与分区表--------------------------

    分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表(子表);

    比如一个订单表 ORDER,采用年月分表后可能就会除 ORDER 本身外还生成许多如 ORDER_201601、ORDER_201602、ORDER_201603… 等的子表。

    分表在逻辑上是多张不同的表,而分区表在逻辑上是一张表。

    --------------------------------------------------------------------

     

    二:什么时候需要分区?

    来自官网的两个建议:

    1. Tables greater than 2GB should always be considered for partitioning.(表数据量大于2GB时应该考虑使用分区)

    2. Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only.(新数据均加入至最新分区中的用于存储历史数据的表)

     

     

    三:分区带来的好处

    1. 提高数据可用性

    a) 得益于每个分区的独立性,优化器会在查询时有需要的去除未用到的分区(这也叫消除分区)

    比如:一个查询如果只用到了一个表三个分区中的其中一个分区的数据,那么Oracle在执行这个查询时只会扫描用到的这个分区的数据,不会扫描其他两个分区的数据。

    这在OLAP系统中很有用。

    -----------------------延伸阅读:OLTP与OLAP系统---------------------

    OLTP(On-Line Transaction Processing):

    联机事务处理过程,也称为面向交易的处理过程,其基本特征是前台接收的用户数据可以立即传送到计算中心进行处理,并在很短的时间内给出处理结果,实现对用户操作的快速响应;

    这样的系统事务性要求非常高,一般都是高可用的在线系统,以小的事务以及小的查询为主。评估其系统的时候,一般看其每秒执行的 Transaction 以及 Execute SQL 的数量。单个数据库每秒处理的 Transaction 往往超过几百个或是几千个,Select 语句的执行量每秒几千甚至几万个;

    OLTP是传统的关系型数据库的主要应用,典型的OLTP系统有电子商务系统、银行、证券系统等。

    OLAP(On-Line Analytical Processing):

    联机分析处理,是数据仓库系统的主要应用,所谓数据仓库是对于大量已经由OLTP形成的数据的一种分析型的数据库,用于处理商业智能、决策支持等重要的决策信息;

    数据仓库是在数据库应用到一定程度之后而对历史数据的加工与分析,读取较多、更新较少;

    OLTP与OLAP简单对比:

    OLAP_OLTP

    ----------------------------------------------------------------------

    b) 分区还可以通过减少停机时间来提高可用性

    例如:一个100GB的表,中间的数据如果遭到损坏,那么恢复起来简直让人抓狂。

    如果这100GB的表被划分为了50个2GB的分区,当其中某个分区数据遭到破坏时,只需要恢复一个2GB的分区数据即可。

    出现错误时的停机时间将会大大减少,因为恢复所需的工作量大幅减少。

     

    2. 方便管理

    将一个大的对象分解为数个小对象,操作这些小对象明显比直接操作原来的大对象更加容易,且占用的资源也更少。

     

    3. 改善语句性能(多针对OLAP系统)

    a) 并行DML(Parallel DML):

    在 Oracle 9i 以前的版本中,PDML(Parallel DML)要求必须分区;

    9i 及以后的版本中这个限制已经放松,只有两个例外:

    ① 希望在一个表上执行 PDML,而且这个表的一个 LOB列上有一个位图索引,要并行执行操作就必须对这个表分区;

    ② 对于并行访问分区操作,取需要访问的分区数为并行度

    ---------------------延伸阅读:PDML(Parallel DML)---------------------

    什么是Parallel(并行)技术?

    对于一个大的任务,一般的做法是利用一个进程,串行的执行。

    但如果系统资源足够,可以采用Parallel(并行)技术,把一个大的任务分成若干个小的任务,同时启用N个进程(或线程),并行的处理这些小的任务,这些并发的进程称为并行执行服务器(parallel executeion server),它们统一由一个称为并发协调进程的进程来管理。

    注意:

    只有在需要处理一个很大的任务(如需要几个小时的作业),并且要有足够的系统资源(包括CPU、内存、I/O等)的情况下,才应该考虑使用Parallel技术。

    否则,在一个多并发用户环境下,系统本身资源负担已经很大,启用Parallel的话,将会导致某一个会话试图占用所有的资源,其他会话不得不等待,从而导致系统性能反而下降的情况。

    一般情况下,OLTP系统中不要使用Parallel技术,OLAP系统中可以考虑使用。

    PDML分类:

    • Parallel Query(并行查询)
    • Parallel DML(并行DML语句执行)
    • Parallel DDL(并行DDL语句执行)

    并行查询:并行查询允许将一个select语句划分为多个较小的查询,每个部分的查询都并发地运行,然后将各个部分的结果组合起来,提供最终的结果。(多用于全表扫描,索引全扫描等)

    并行DML:Parallel DML包括 insert、update、delete、merge,在PDML期间,Oracle可以使用多个并行执行服务器(即并发进程)来执行 insert、update、delete、merge,多个会话同时执行,同时每个会话(并发进程)都有自己的undo段,都是一个独立的事务,这些事务要么都由并发协调进程提交,要么都rollback。

    -----------------------------------------------------------------------

    b) 查询性能:

    分区对于不同的系统带来的影响可能不同;

    对OLTP系统而言,需要谨慎使用分区操作,因为在传统的OLTP系统中,大多数查询很可能立即返回结果,而且获取大多数数据可能都通过一个很小的索引区间扫描来完成。故分区带来的性能方面的优点在 OLTP 系统中可能根本表现不出来。

    在一个OLTP系统中,分区如果应用不当,甚至可能使性能下降(分区可能会提高某些类型查询的性能,但是这些查询通常不在OLTP系统中使用);

    所以有一点你必须明白:分区并不总是和“性能提升”联系在一起。

    对于OLAP系统而言,分区消除与并行查询将可能带来效率的大幅提升。

     

     

    四:表分区机制

    表分区的四种类型:

    • 范围分区(Range)
    • 散列分区(Hash)
    • 列表分区(List)
    • 组合分区(Range – Hash   或者   Range - List)

    1. 范围分区:

    范围(Range)分区将数据基于指定的分区键映射到每一个分区中。

    这种分区方式最为常用,且常常采用日期作为分区键。

    注意:

    ① 每一个分区都需要有一个 VALUES LESS THEN 子句,它指定了该分区的上限值(即该分区能接受的分区键的最大值)。记录里分区键的值小于这个上限值时,该记录会被放入该分区;而当记录里分区键的值等于或大于这个上限值时该记录会被放入下一个上限值更高的分区中。

    ② 所有分区里,除了第一个分区,其他分区其实都有一个隐式的下限值(即该分区能接受的分区键的最小值),这个下限值就是上一个分区的上限值。

    ③ 在最后一个分区中,可定义上限值为 MAXVALUE(该值可理解为所有分区中的一个最大上限值,包括空值),当记录分区键的值大于之前所有分区的上限值时,这条记录会被放入这最后一个分区中。

    建表语句示例:

    复制代码
    /****************************************************范围分区示例******************************************/
    --创建示例表
    create table range_example
    (
     id number(2),
     done_date date,
     data varchar2(50)
    )
    
    --创建分区,分区键为示例表(range_example)中的 done_date 字段
    partition by range (done_date)
    (
      partition part_1 values less than ( to_date('20160901', 'yyyymmdd') ),
      partition part_2 values less than ( to_date('20161001', 'yyyymmdd') ),
      partition part_3 values less than ( maxvalue )
    )
    
    --查看range_example表的分区信息
    select * from user_tab_partitions where table_name = 'RANGE_EXAMPLE';
    复制代码

     

    分区表创建

    查看表分区信息:

    分区信息查看

    插入数据:

    记录插入

    如图,可以看到示例表 range_example 已经分了三个区。

    记录1的 done_date 为 2016/8/11,小于分区part_1的上限值,则记录1会被放入part_1分区;

    记录2的 done_date 为 2016/9/8 ,大于分区part_1的上限值但小于part_2的上限值,则记录2会被放入part_2分区;

    记录3的 done_date 为 2016/10/20,大于前两个分区的上限值,故会被放入最后一个maxvalue的分区(part_3);

    part_2的隐式的下限值实际就是上一个分区part_1的上限值;

     

    2. 散列分区:

    对一个表执行散列分区时,Oracle会对分区键应用一个散列(Hash)函数,以此确定数据应当放在 N 个分区中的哪一个分区中。

    Oracle建议 N 是 2 的一个幂(如 N = 2、4、8、16 等),从而使表数据得到最佳的总体分布。

    当列的值没有合适的范围条件时,建议使用散列分区。

    注意:

    如果改变散列分区的个数 (向一个散列分区表增加或删除一个分区时),数据会在所有分区中重新分布,即所有数据都会被重写,因为现在每一行可能属于一个不同的分区。

    为表选择的散列键(分区键)应当是惟一的一个列或一组列(该列应有多个不同的值),以便行能在多个分区上均匀地分布。

    如果使用散列分区,你将无法控制一行数据最终会放在哪个分区中(由散列函数控制)。

    建表语句示例:

    复制代码
    /*******************************************散列分区示例***********************************************/
    --创建示例表
    create table hash_example
    (
     id number(2),
     done_date date,
     data varchar2(50)
    )
    
    --创建散列分区,分区键为示例表(hash_example)中的 done_date 字段
    partition by hash (done_date)
    (
      partition part_1,
      partition part_2
    )
    
    select * from user_tab_partitions where table_name = 'HASH_EXAMPLE';
    复制代码

    分区信息:

    hash分区信息

     

     

    3. 列表分区:

    列表分区可以根据分区键的值明确指定哪些值的数据该放在哪个分区。

    注意:

    列表分区中如果指定了 default 分区,则分区键的值不在任何分区值列表中的记录,会被放入 default 分区;

    而一旦创建了一个 default 分区后,就不能再向这个表中增加更多的分区了;

    如果未指定 default 分区,则在插入分区键值不在任何分区值列表中的记录时,Oracle会报错(ORA-14400: inserted partition key does not map to any partition)。

    建表语句示例:

    复制代码
    /*******************************************列表分区示例***********************************************/
    --创建示例表
    create table list_example
    (
     id number(2),
     name varchar(30),
     data varchar2(50)
    )
    
    --创建列表分区,分区键为示例表(list_example)中的 id 字段
    partition by list (id)
    (
      partition part_1 values ( '1', '3', '5', '7' ),
      partition part_2 values ( '2', '4', '6', '8' ),
      partition part_default values ( default )
    )
    
    select * from user_tab_partitions where table_name = 'LIST_EXAMPLE';
    复制代码

    分区信息:

    列表分区信息

    如上,分区键(即list_example表中id字段)值为 1、3、5、7 的记录,会被放入part_1分区;

    分区键值为 2、4、6、8 的记录,会被放入part_2分区;

    分区键值为其他值的记录,会被放入最后一个part_default分区。

     

    4. 组合分区:

    组合分区是范围分区与散列分区的组合,或者是范围分区与列表分区的组合。

    在组合分区中,顶层分区机制总是范围分区,第二级分区机制可能是散列分区也可能是列表分区;

    数据物理的存储在子分区段上,分区(顶层的范围分区)成为了一个逻辑容器,或者是一个指向实际子分区的容器;

    每个顶层分区不需要有相同数目的子分区。

    范围-散列组合分区 建表语句示例:

    复制代码
    /******************************************范围-散列分区**************************************/
    create table range_hash_example
    (
     id number(2),
     done_date date,
     data varchar2(50)
    )
    
    --顶层范围分区的分区键为 range_hash_example 表中的 done_date 字段;
    --第二层散列分区的分区键为 range_hash_example 表中的 id 字段;
    partition by range (done_date) subpartition by hash (id) 
    (
      partition part_1 values less than ( to_date('20160901', 'yyyymmdd') )
      (
        subpartition part_1_sub_1,
        subpartition part_1_sub_2
      ),
      
      partition part_2 values less than ( to_date('20161001', 'yyyymmdd') )
      (
        subpartition part_2_sub_1,
        subpartition part_2_sub_2
      ),
      
      partition part_3 values less than ( maxvalue )
      (
        subpartition part_3_sub_1,
        subpartition part_3_sub_2
      )
    )
    
    select * from user_tab_partitions where table_name = 'RANGE_HASH_EXAMPLE';
    复制代码

    分区信息:

    区间-散列分区信息

    在如上的范围-散列组合分区中,Oracle会首先应用范围(Range)分区规则,得出数据属于哪个区间,(即先通过 done_date 字段确定记录是属于part_1还是part_2还是part_3);

    然后再应用散列(Hash)函数,来确定数据最后要放在哪个子分区(物理分区)中,(即通过 id 字段确定记录是属于一个分区下的哪个子分区中 )

     

    范围-列表组合分区 建表语句示例:

    复制代码
    /******************************************范围-列表分区**************************************/
    create table range_list_example
    (
     id number(2),
     done_date date,
     data varchar2(50)
    )
    
    --顶层范围分区的分区键为 range_list_example 表中的 done_date 字段;
    --第二层列表分区的分区键为 range_list_example 表中的 id 字段;
    partition by range (done_date) subpartition by list (id) 
    (
      partition part_1 values less than ( to_date('20160901', 'yyyymmdd') )
      (
        subpartition part_1_sub_1 values ( '1', '3', '5' ),
        subpartition part_1_sub_2 values ( '2', '4', '6' )
      ),
      
      partition part_2 values less than ( to_date('20161001', 'yyyymmdd') )
      (
        subpartition part_2_sub_1 values ( '11', '13', '15', '17' ),
        subpartition part_2_sub_2 values ( '12', '14' ),
        subpartition part_2_sub_3 values ( '16', '18' )
      ),
      
      partition part_3 values less than ( maxvalue )
      (
        subpartition part_3_sub_1 values ( '21', '23', '25' ),
        subpartition part_3_sub_2 values ( '22', '24', '26' )
      )
    )
    
    select * from user_tab_partitions where table_name = 'RANGE_LIST_EXAMPLE';
    复制代码

    分区信息:

    区间_列表分区信息

    如图,每个顶层的范围分区可以有不同数目的子分区。

     

    5. 小结

    一般来讲,如果需要将数据按照某个值逻辑聚集,多采用范围分区。如基于时间数据的按“年”、“月”等分区就是很典型的例子。在许多情况下,范围分区都能利用到分区消除特性( =  >=  <=  between…and  等筛选条件下)。

    如果在表里无法找到一个合适的属性来按这个属性完成范围分区,但你又想享受分区带来的性能与可用性的提升,则可以考虑使用散列分区。(适合使用 IN 等筛选条件)

    如果数据中有一列或有一组离散值,且按这一列进行分区很有意义,则这样的数据就很适合采用列表分区。

    如果某些数据逻辑上可以进行范围分区,但是得到的范围分区还是太大,不能有效管理,则可以考虑使用组合分区。

    注意:

    分区在最开始创建表时被一同创建,如果后期要更改分区策略的话,需要先重建表。


    本文转自:https://www.cnblogs.com/Dreamer-1/p/6108247.html

    展开全文
  • Oracle分区表修改分区名

    千次阅读 2017-02-10 11:35:29
    declare l_str varchar2(8);begin for x in (select distinct partition_name from user_tab_partitions where table_name='T_DW_XA_GLOG_SHOPTRADE' and partition_name like 'SYS%')loop execute immedi
  • Oracle分区表操作

    2017-09-20 20:50:00
    Oracle提供的基本语法基本可以满足一般性修改,但是对于把普通堆表改为分区表,把索引组织表修改堆表等操作就无法完成了。而且,对于被大量DML语句访问的表,幸运的是,Oracle从9i版本开始提供了在线重定义表功能...
  • http://www.itpub.net/thread-1942951-1-1.html在oracle中,建立分区表,然后建立索引的时候有全局索引和本地索引,因为需要定时删除分区,所以建立本地索引,可以在查询的时候走索引,那么全局索引的意义是什么?...
  • oracle 表分区

    2018-03-26 11:57:22
    oracle大数据分区优缺点oracle给出的建议是按照的大小给出的,10g的建议是2G,也就是说的大小大于2G,那么就最好建立分区什么要建立分区?主要是为了查询方便,因为如果一个每天都有百万条记录,那么...
  • 如果当前业务表不是基于这个业务时间点的分区表设置,那只能insert再delete操作。这种转移数据的方法非常非常低基础。经常在初级的数据库管理人员和开发人员的程序中出现。不是说这个方法不好,对于转移的记录数量在...
  • Oracle分区表创建

    2015-12-05 20:45:57
    一、Oracl分区表的创建
  • oracle分区表

    千次阅读 2011-08-14 15:58:36
    Oracle分区表的使用 前提:  查询分区:Select *From user_extents WHERE partition_name='分区名'; 1)创建表空间 create tablespace HRPM0 datafile '/oradata/mi
  • oracle分区表按时间自动创建

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

    2013-12-19 13:53:03
    为了简化数据库大的管理,从ORACLE8推出了分区选项。分区分离在若干不同的空间上,用分而治之的方法来支撑无限膨胀的大,给大在物理一级的可管理性。将大分割成较小的分区可以改善的维护、备份、...
  • ORACLE 分区表简介2

    2012-05-31 18:03:29
    ORACLE 分区表简介 标签: 杂谈 ‍ORACLE-分区表 此文从以下几个方面来整理关于分区表的概念及操作: ...7.将一个非分区表转换为分区表 (1) 表空间及分区表的概念 表空间:  是一个或多个数据文件的
  • ORACLE 分区表

    千次阅读 2013-07-21 17:32:50
     Oracle 数据库中最基本的数据存储结构。数据在中以行(row)和列(column)的形式存储。用户在定义时,需要设定表名(table name)(例如 employees ),还要设定内各列的列名(column name)(例如 ...
  • ORACLE数据库表分区

    千次阅读 2013-09-06 17:30:31
    ORACLE数据库表分区 ...1.1 分区表PARTITION table 在ORACLE里如果遇到特别大的表,可以使用分区的表来改变其应用程序的性能。 1.1.1 分区表的建立: 某公司的每年产生巨大的销
  • Oracle分区表

    2009-05-31 12:17:00
    Oracle 数据库分区表的创建和操作 摘要:在大量业务数据处理的项目中,可以考虑使用分区表来提高应用系统的性能并方便数据管理,本文详细介绍了分区表的使用。 在大型的企业应用或企业级的数据库应用中,要处理的...
  • oracle创建分区表.pdf

    2012-05-19 16:26:40
    oracle创建分区表ORACLE里如果遇到特别大的表,可以使用分区的表来改变其应用程序的性能。 以system身份登陆数据库,查看 v$option视图,如果其中PartitionTRUE,则支持分区 功能;否则不支持。Partition有...
  • oracle 分区表

    2007-10-06 22:13:00
    1.1分区表PARTITION table在ORACLE里如果遇到特别大的表,可以使用分区的表来改变其应用程序的性能。转换实例:1、exp 要转换的表的数据,2、用TOAD 9导出并修改sql script. ,填加Partition 子句;3、运行该sql语句...
  • oracle数据库表分区

    千次阅读 2012-06-28 20:27:26
    摘要:在大量业务数据处理的项目中,可以考虑使用分区表来提高应用系统的性能并方便数据管理,本文详细介绍了分区表的使用。   在大型的企业应用或企业级的数据库应用中,要处理的数据量通常可以达到几十到几百...
  • Oracle 分区表总结

    2011-05-05 22:30:00
    1.1 分区表PARTITION table在ORACLE里如果遇到特别大的表,可以使用分区的表来改变其应用程序的性能。1.1.1 分区表的建立: 某公司的每年产生巨大的销售记录,DBA向公司建议每季度的数据放在一个分区内,以下示范的...
  • ORACLE-分区表

    2014-08-28 07:19:30
    ORACLE-分区表 此文从以下几个方面来整理关于分区表的概念及操作: 1.表空间及分区表的概念 2.表分区的具体作用 3.表分区的优缺点 4.表分区的几种类型及操作方法 5.对表分区的维护性操作. 6.表...
  • oracle分区表总结

    2013-02-21 18:36:33
    同事的分区表总结,转载一下。 1.1 分区表PARTITION table 在ORACLE里如果遇到特别大的表,可以使用分区的表来改变其应用程序的性能。 1.1.1 分区表的建立: 某公司的每年产生巨大的销售记录,DBA向公司建议每...
  • Oracle提供的基本语法基本可以满足一般性修改,但是对于把普通堆表改为分区表,把索引组织表修改堆表等操作就无法完成了。而且,对于被大量DML语句访问的表,幸运的是,Oracle从9i版本开始提供了在线...
  • Oracle创建表分区

    千次阅读 2015-01-11 21:12:55
    可以这样理解,当一个数据在插入数据的时候,在几百条或者几千条数据中查询目标数据的时候不会花费多长时间,最多一两...中含有历史数据,新的数据被增加到新的分区中. 优点: 提高查询的性能:对分区对象的查询可以

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 23,833
精华内容 9,533
关键字:

oracle改变表为分区表