精华内容
下载资源
问答
  • Oracle 分区表相关视图

    千次阅读 2013-12-13 10:55:03
    小白今天遇到一个oracle分区相关问题,在csdn上搜了半小时,总算将问题搞定,现在将相关视图整理一下,下次就方便啦。   显示当前用户可访问的所有分区表信息﹕ ALL_PART_TABLES   显示当前用户所有分区表的信息...
     
    

    小白今天遇到一个oracle分区问题,在csdn上搜了半小时,总算将问题搞定,现在将相关视图整理一下,下次搜起来就方便啦。

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

         ALL_PART_TABLES

    2. 显示当前用户所有分区表的信息﹕

         USER_PART_TABLES

    3. 显示表分区信息 显示数据库所有分区表的详细分区信息﹕

         DBA_TAB_PARTITIONS

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

         ALL_TAB_PARTITIONS (这是俺的福星O(∩_∩)O~

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

         USER_TAB_PARTITIONS

    6. 显示子分区信息 显示数据库所有组合分区表的子分区信息﹕

         DBA_TAB_SUBPARTITIONS

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

         ALL_TAB_SUBPARTITIONS

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

         USER_TAB_SUBPARTITIONS

    9. 显示分区列 显示数据库所有分区表的分区列信息﹕

         DBA_PART_KEY_COLUMNS

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

         ALL_PART_KEY_COLUMNS

    11.显示当前用户所有分区表的分区列信息﹕

         USER_PART_KEY_COLUMNS

    12.显示子分区列 显示数据库所有分区表的子分区列信息﹕

         DBA_SUBPART_KEY_COLUMNS

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

         ALL_SUBPART_KEY_COLUMNS

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

         USER_SUBPART_KEY_COLUMNS

    展开全文
  • oracle创建分区表.pdf

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

    千次阅读 2017-09-16 00:20:49
    --查看分区表 select * from all_part_tables --查看分区 select * from all_part_partitions --查看分区字段 select * from all_part_key_columns --显示数据库所有分区表的信息: select * from dba_part_...
    --查看分区表
    select * from all_part_tables
    --查看分区
    select * from all_part_partitions
    --查看分区字段
    select * from all_part_key_columns
    --显示数据库所有分区表的信息:
    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;

    展开全文
  • 创建一个视图 视图中时两个大表left join 两个表都是账期分区表 在查看执行计划时 发现两个表是哈希连接。主表走了单个分区 但另一个表走的全表。到时视图的执行效率很慢 解决方法:非主表按照关联条件...
  • Oracle-分区表解读

    千次阅读 2016-10-29 21:46:37
    概述Oracle-OLAP和OLTP解读Oracle-index索引解读Oracle-分区表解读Oracle-锁解读Oracle-等待事件解读Oracle-procedure/cursor解读Oracle关于分区的在线文档当表中的数据量不断增大,查询数据的速度就会变慢,应用...

    概述

    Oracle-OLAP和OLTP解读

    Oracle-index索引解读

    Oracle-分区表解读

    Oracle-锁解读

    Oracle-等待事件解读

    Oracle-procedure/cursor解读


    Oracle关于分区的在线文档

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

    对于数据库中的超大型表,可通过把它的数据分成若干个小表,从而简化数据库的管理活动。对于每一个简化后的小表,我们称为一个单个的分区。

    对于分区的访问,我们不需要使用特殊的SQL查询语句或特定的DML语句,而且可以单独的操作单个分区,而不是整个表。同时可以将不同分区的数据放置到不同的表空间。

    Oracle提供分区技术以支持VLDB(VeryLargeDataBase).

    分区表通过对分区列的判断,把分区列不同的记录,放到不同的分区中。分区完全对应用透明。

    对于外部应用程序来说,虽然存在不同的分区,且数据位于不同的表空间,但逻辑上仍然是一张表。

    Oracle的分区表可以包括多个分区,每个分区都是一个独立的段(SEGMENT),可以存放到不同的表空间中。

    查询时可以通过查询表来访问各个分区中的数据,也可以通过在查询时直接指定分区的方法来进行查询。

    在oracle 10g中最多支持:1024k-1个分区。

    什么时候需要分区表?

    官网给出的建议:

    • 1、表的大小超过2GB。
    • 2、表中包含历史数据,新的数据被增加都新的分区中。需要将历史数据和当前的数据分开单独处理,比如历史数据仅仅需要只读,而当前数据则实现DML

    表分区的优缺点

    优点

    • 1、改善查询性能:只需要搜索特定分区,而非整张表,提高查询速度。
    • 2、增强可用性:如果表的某个分区出现故障,表在其他分区的数据仍然可用;
    • 3、维护方便:可以单独备份和恢复每个分区。如果表的某个分区出现故障,需要修复数据,只修复该分区即可; 节约维护时间,单个分区的数据装载,索引重建,备份,维护等将远小于整张表的维护时间。
    • 4、均衡I/O:将不同的分区映射到不同的磁盘以平衡I/O,提高并发,改善整个系统性能。

    缺点:

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

    特点

    1. 共性:不同的分区之间必须有相同的逻辑属性,比如表名,列名,数据类型,约束等,
    2. 个性:各个分区可以有不同的物理属性,比如pctfree, pctused, and tablespaces.
    3. 分区独立性:即使某些分区不可用,其他分区仍然可用。
    4. 特殊性:含有LONG、LONGRAW数据类型的表不能进行分区

    ORACLE分区类型

    Oracle 10g提供了以下几种分区类型:

    • (1)范围分区(range);
    • (2)哈希分区(hash);
    • (3)列表分区(list);
    • (4)范围-哈希复合分区(range-hash);
    • (5)范围-列表复合分区(range-list)。

    范围分区(range)

    范围分区特性

    Range分区是应用范围比较广的表分区方式,它是以列的值的范围来做为分区的划分条件,将记录存放到列值所在的range分区中,并且分区键经常采用日期。

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

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

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

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

    如果某些记录暂无法预测范围,可以创建maxvalue分区,所有不在指定范围内的记录都会被存储到maxvalue所在分区中。

    创建范围分区时,必须指定以下内容
    分区方法:range
    分区列
    标识分区边界的分区描述

    如:

    create table pdba (
        id number, 
        time date) 
    
        partition by range (time)  --创建基于日期的范围分区并存储到不同的表空间
    
    (
    
    partition p1 values less than (to_date('2010-10-1', 'yyyy-mm-dd')),
    
    partition p2 values less than (to_date('2010-11-1', 'yyyy-mm-dd')),
    
    partition p3 values less than (to_date('2010-12-1', 'yyyy-mm-dd')),
    
    partition p4 values less than (maxvalue)
    
    )
       create table r      --创建基于值范围的分区,分区子句未指定表空间时则位于缺省的表空间
                (a int)
                partition by range (a)
                (
                    partition p1 values less than (10),
                    partition p2 values less than (20),
                    partition p3 values less than (30),
                    partition p4 values less than (maxvalue)
                );
    
        select * from r partition (p1)    --查看分区中的数据

    说明:

        partition by 用于指定分区方式
        range 表示分区的方式是范围划分
        partition pn 用于指定分区的名字
        values less than 指定分区的上界(上限)
    
    
     添加分区:
            ALTER TABLE r
            add partition p5 values less than (xxx ) tablespace xx;
    
     查看分区表相关信息:
            SELECT table_name,partition_name,subpartition_count,
            tablespace_name,user_stats from user_tab_partitions;
    

    栗子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 CUS_TS01,
        PARTITION CUS_PART2 VALUES LESS THAN (200000) TABLESPACE CUS_TS02
        )

    栗子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-MAY-2016','DD-MON-YYYY')) TABLESPACEORD_TS01,
        PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUN-2016','DD-MON-YYYY')) TABLESPACE ORD_TS02,
        PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUL-2016','DD-MON-YYYY')) TABLESPACE ORD_TS03
        )

    栗子3-MAXVALUE

     CREATE TABLE RangeTable
        (
        idd INT PRIMARY KEY ,
        iNAME VARCHAR(10),
        grade INT
        )
        PARTITION BY RANGE (grade)
        (
        PARTITION part1 VALUES LESS THEN (1000) TABLESPACE Part1_tb,
        PARTITION part2 VALUES LESS THEN (MAXVALUE) TABLESPACE Part2_tb
        );

    哈希分区(hash)

    也被成为散列分区。

    这类分区是在列值上使用哈希算法,以确定将行放入哪个分区中。当列的值没有合适的条件时,建议使用散哈希分区。

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

    对于那些无法有效划分范围的表,可以使用hash分区,这样对于提高性能还是会有一定的帮助。hash分区会将表中的数据平均分配到你指定的几个分区中,列所在分区是依据分区列的hash值自动分配,因此你并不能控制也不知道哪条记录会被放到哪个分区中,hash分区也可以支持多个依赖列。

    创建散列分区时,必须指定以下信息
    分区方法:hash
    分区列
    分区数量或单独的分区描述

    分裂、删除和合并分区不能应用于Hash分区,但是,Hash分区能够合并和添加。

    创建hash分区有两种方法:一种方法是指定分区数量,另一种方法是指定分区的名字,
    但两者不能同时指定。

      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 (emp1,emp2,emp3,emp4,emp5,emp6,emp7,emp8);--表空间的名称

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


    列表分区(list)

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

    List分区也需要指定列的值,其分区值必须明确指定,该分区列只能有一个,不能像range或者hash分区那样同时指定多个列做为分区依赖列,但它的单个分区对应值可以是多个。

    在分区时必须确定分区列可能存在的值,一旦插入的列值不在分区范围内,则插入/更新就会失败,因此通常建议使用list分区时,要创建一个default分区存储那些不在指定范围内的记录,类似range分区中的maxvalue分区。

    List分区时必须指定的以下内容
    分区方法:list
    分区列
    分区描述,每个描述指定一串文字值(值的列表),它们是分区列(它们限定将被包括在分区中的行)的离散值

    比如:
    在根据某字段,如城市代码分区时,可以指定default,把非分区规则的数据,全部放到这个default分区。

    create table custaddr
    (
    
    id varchar2(15 byte) not null,
    
    areacode varchar2(4 byte)
    )
    
    partition by list (areacode)
    ( partition t_list025 values ('025'), 
    partition t_list372 values ('372') , 
    partition t_list510 values ('510'),
    
    partition p_other values (default)
    
    )

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

    栗子2

    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
        );
        )

    组合分区

    如果某表按照某列分区之后,仍然较大,或者是一些其它的需求,还可以通过分区内再建子分区的方式将分区再分区,即组合分区的方式。

    组合分区呢在10g中有两种:range-hash,range-list。

    注意顺序,根分区只能是range分区,子分区可以是hash分区或list分区。

    组合分区使用range方法分区,在每个子分区中使用hash方法进行再分区。

    组合分区比range分区更容易管理,充分使用了hash分区的并行优势。组合分区支持历史数据和条块数据两者。

    如添加新的RANGE分区,同时为DML操作提供更高层的并行性。

    创建组合分区时,需要指定如下内容:

    分区方法:range
    分区列
    标识分区边界的分区描述
    子分区方法:hash
    子分区列
    每个分区的子分区数量,或子分区的描述

    范围-哈希复合分区(range-hash)

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

      create table xiaogongjiang
        (
        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 (xgj_space01,xgj_space02,xgj_space03)
        (
        partition part_01 values less than(to_date(‘2006-01-01’,’yyyy-mm-dd’)),
        partition part_02 values less than(to_date(‘2016-01-01’,’yyyy-mm-dd’)),
        partition part_03 values less than(maxvalue)
        );

    范围-列表复合分区(range-list)

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

     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
        )
        )

    Oracle 11g-Interval Partitioning

    数据库管理员日常要做的一件重复而无聊的工作 比如每隔一天要生成新的24个分区,用以存储第二天的数据。而在11g中这项工作可以交由Oracle自动完成了,基于Range和List的Interval Partitioning分区类型登场。

    在 11g 里的 Interval 创建,这种方法对没有写全的分区会自动创建。 比如我
    这里只写了 1 月日期,如果插入的数据有其他月份的,会自动生成对应的分区。

    CREATE TABLE TB_INTERVAL 
    PARTITION BY RANGE (time_col) 
    INTERVAL(NUMTOYMINTERVAL(1, 'month')) 
    (PARTITION P0 VALUES LESS THAN (TO_DATE('1-1-2016', 'dd-mm-yyyy'))); 

    指定需要Oracle自动创建分区的间隔时间,上面这个例子是1个月,然后至少创建一个基本分区,上面这个例子是在2016-1-1之前的所有数据都在P0分区中,以后每个月的数据都会存放在Oracle自动创建的一个新分区中。

    select table_name,partition_name from user_tab_partitions where
    table_name='TB_INTERVAL';
    select count(*) from TB_INTERVAL partition (p1);

    创建按月分区的分区表

    1. 创建分区表
    /* Formatted on 2010/6/10 20:21:12 (QP5 v5.115.810.9015) */
    create table intervalpart (c1 number, c3 date)
    partition by range (c3)
    interval ( numtoyminterval (1, 'month') )
    (partition part1
    values less than (to_date ('01/12/2010', 'mm/dd/yyyy')),
    partition part2
    values less than (to_date ('02/12/2010', 'mm/dd/yyyy'))
    )

    注意: 如果在建 Interval 分区表是没有把所有的分区写完成,在插入相关数据后会自动生成分区
    2. 查看现在表的分区:

    SQL> select table_name,partition_name from user_tab_partitions where
    table_name='INTERVALPART';
    TABLE_NAME PARTITION_NAME
    ------------------------------ ------------------------------
    INTERVALPART PART1
    INTERVALPART PART2
    1. 插入测试数据:
    SQL> begin
    2 for i in 0 .. 11 loop
    3 insert into intervalpart
    values(i,add_months(to_date('2010-1-1','yyyy-mm-dd'),i));
    4 end loop ;
    5 commit;
    6 end;
    7 /

    PL/SQL 过程已成功完成。
    补充: add_months() 函数获取前一个月或者下一个月的月份, 参数中 负数 代
    表 往前, 正数 代表 往后。
    –上一个月

    select to_char(add_months(trunc(sysdate),-1),'yyyymm') from dual;

    –下一个月

    select to_char(add_months(trunc(sysdate),1),'yyyymm') from dual;
    1. 观察自动创建的分区:
    SQL> select table_name,partition_name from user_tab_partitions where
    table_name='INTERVALPART';
    TABLE_NAME PARTITION_NAME
    ------------------------------ ------------------------------
    INTERVALPART PART1
    INTERVALPART PART2
    INTERVALPART SYS_P22
    INTERVALPART SYS_P23
    INTERVALPART SYS_P24
    INTERVALPART SYS_P25
    INTERVALPART SYS_P26
    INTERVALPART SYS_P27
    INTERVALPART SYS_P28
    INTERVALPART SYS_P29
    INTERVALPART SYS_P30
    INTERVALPART SYS_P31

    已选择 12 行。
    5. 查看分区内容:

    SQL> select * from INTERVALPART;
    
    C1 C3
    --------- ----------
    1 2010-01-01
    0 2010-01-01
    1 2010-02-01
    2 2010-03-01
    3 2010-04-01
    4 2010-05-01
    5 2010-06-01
    6 2010-07-01
    7 2010-08-01
    8 2010-09-01
    9 2010-10-01
    10 2010-11-01
    11 2010-12-01
    已选择 13 行。
    SQL> select * from INTERVALPART partition(part1);
    C1 C3
    --------- ----------
    1 2010-01-01
    0 2010-01-01
    SQL> select * from INTERVALPART partition(part2);
    C1 C3
    --------- ----------
    1 2010-02-01

    创建一个以天为间隔的分区表

    1. 创建分区表:
    SQL> create table dave
    2 (
    3 id number,
    4 dt date
    5 )
    6 partition by range (dt)
    7 INTERVAL (NUMTODSINTERVAL(1,'day'))
    8 (
    9 partition p100101 values less than (to_date('2010-01-01','yyyy-mm-dd'))
    10 );
    1. 查看表分区:
    SQL> select table_name,partition_name from user_tab_partitions where table_name='DAVE';
    TABLE_NAME PARTITION_NAME
    ------------------------------ ------------------------------
    DAVE P100101
    1. 插入测试数据:
    SQL> begin
    2 for i in 1 .. 12 loop
    3 insert into dave values(i,trunc(to_date('2010-1-1','yyyy-mm-dd')+i));
    4 end loop;
    5 commit;
    6 end;
    7 /

    PL/SQL 过程已成功完成。
    4. 观察自动创建的分区:

    SQL> select table_name,partition_name from user_tab_partitions where
    table_name='DAVE';
    TABLE_NAME PARTITION_NAME
    ------------------------------ ------------------------------
    DAVE P100101
    DAVE SYS_P32
    DAVE SYS_P33
    DAVE SYS_P34
    DAVE SYS_P35
    DAVE SYS_P36
    DAVE SYS_P37
    DAVE SYS_P38
    DAVE SYS_P39
    DAVE SYS_P40
    DAVE SYS_P41
    DAVE SYS_P42
    DAVE SYS_P43

    已选择 13 行。
    5. 查看分区内容:

    SQL> select * from dave partition(SYS_P32);
    ID DT
    ---------- ----------
    1 2010-01-02
    SQL> select * from dave partition(SYS_P33);
    ID DT
    -------- ----------
    2 2010-01-03
    SQL> select * from dave partition(SYS_P34);
    ID DT
    -------- ----------
    3 2010-01-04
    SQL> select * from dave;
    ID DT
    -------- ----------
    1 2010-01-02
    2 2010-01-03
    3 2010-01-04
    4 2010-01-05
    5 2010-01-06
    6 2010-01-07
    7 2010-01-08
    8 2010-01-09
    9 2010-01-10
    10 2010-01-11
    11 2010-01-12
    12 2010-01-13
    已选择 12 行。

    System Partitioning

    系统分区,在这个新的类型中,我们不需要指定任何分区键,数据会进入哪个分区完全由应用程序决定,实际上也就是由SQL来决定,终于,我们在Insert语句中可以指定插入哪个分区了。
    假设我们创建了下面这张分区表,注意,没有指定任何分区键:

    CREATE TABLE systab (c1 integer, c2 integer) 
    PARTITION BY SYSTEM 
    ( 
    PARTITION p1 TABLESPACE tbs_1, 
    PARTITION p2 TABLESPACE tbs_2, 
    PARTITION p3 TABLESPACE tbs_3, 
    PARTITION p4 TABLESPACE tbs_4 
    ); 

    现在由SQL语句来指定插入哪个分区:
    – 数据插入p1分区

    INSERT INTO systab PARTITION (p1) VALUES (4,5); 

    – 数据插入第2个分区,也就是p2分区

    INSERT INTO systab PARTITION (2) VALUES (7,8); 

    – 为了实现绑定变量,用pno变量来代替实际分区号,以避免过度解析

    INSERT INTO systab PARTITION (:pno) VALUES (9,10); 

    由于System Partitioning的特殊性,所以很明显,这种类型的分区将不支持Partition Split操作,也不支持create table as select操作。

    12C 对表分区维护的增强

    Oracle Database 12c对表分区变化比较多,共分为下面几点

    1.在线移动分区:通过MOVE ONLINE关键字实现在线分区移动。移动过程中,对表和被移动的分区可以执行查询操作,

    DML语句以及分区的创建和维护操作。整个移动过程对用户来说是透明的。

    2.多个分区同时操作:可以对多个分区同时进行维护操作,如将一年的12个分区合并到一个新的分区中,或者将一个分区

    分成多个分区。可以通过FOR语句指定操作的每个语句,对于RANGE分区而言,也可以通过TO来指定处理分区的范围。

    多个分区操作自动并行完成。

    3.INTERVAL-REFERENCE分区:把11g的interval分区和reference分区结合,这样主表自动增加一个分区后,所有字表,

    孙子表·····重重孙子表上都会自动随着外界列数据增加,自动创建新的分区。

    4.TRUNCATE和EXCHANGE分区及子分区。五分是TRUNCATE还是EXCHANGE分区,在主表上执行,都可以级联的作用在字表,

    孙子吧·····重重孙子表上同时执行。对于TRUNCATE而言,所有表的TRUNACATE操作在同一个事务中,如果中途失败,
    会回滚到之前的状态。通过关键字CASCADE实现。

    5.异步全局索引维护:对于非常大的分区表而言,UPDATE GLOBAL INDEX不再是痛苦。Oracle可以实现了异步维护的

    功能,即使是几亿条的记录的全局索引,在分区维护操作,比如DROP或TRUNCATE后,仍然是VALID状态,索引不会失效,

    不过索引的状态是包含OBSOLETE数据,当维护操作完成,索引状态恢复。

    6.部分本地和全局索引:Oracle的所有可以在分区级别定义。无论全局索引还是本地所有都可以在分区表的部分分区

    上建立,其他分区上则没有所有。当通过所有列访问全表数据时,Oracle通过UNION ALL实现,一部分通过索引扫描,
    另一部分通过全分区扫描。这可以减少对历史数据的索引量,增强了灵活性。

    具体例子:
    1)添加多个新分区:

    在12c之前,一次只能添加一个新分区到一个已存在的分区表。在12c中只需要一条单独的ALTER TABLE ADD PARTITION

    命令就可以添加N个新分区。

     ALTER TABLE EMP_PART ADD PARTITION
     PARTITION P4 VALUES LESS THAN(35000)
     PARTITION P5 VALUES LESS THAN(40000)

    同样,只要MAXVALUE分区不存在,可以添加多个新分区到一个列表和系统分区表

    2)删除、截断多个分区/子分区
    通过在此之前,一次只能删除/截断一个分区。12c中通过ALTER TABLE table_name {TRUNCAT|DROP} PARTITIONS

     ALTER TABLE EMP_PART DROP PARTITIONS P4,P5;
     ALTER TABLE EMP_PART TRUNCATE PARTITIONS P4,P5;

    要保持索引更新,使用UPDATE INDEXES或UPDATE GLOBAL INDEXES语句。

     ALTER TABLE EMP_PART DROP PARTITIONS P4,P5 UPDATE GLOBAL INDEXES;
     ALTER TABLE EMP_PART TRUNCATE PARTITIONS P4,P5 UPDATE GLOBAL INDEXES;

    如果没有使用UPDATE GLOBAL INDEXES更新索引,也可以通过查询ORPHANED_ENTRIES字段找出是否有索引包含过期的条目

    3)将单个分区分割为多个新分区
    在此之前是无法单个命令完成这个操作的。

     ALTER TABLE EMP_PART SPLIT PARTITIONS p_max INTO
     (PARTITION P4 VALUES LESS THAN (30000),
     PARTITION P5 VALUES LESS THAN(40000),
     PARTITION P_MAX);

    4)将多个分区合并为一个分区

     ALTER TABLE EMP_PART MERGE PARTITIONS P2,P3,P4 INTO PARTITION P_MERGE;

    如果是连续分区,可以通过TO来

     ALTER TABLE EMP_PART MERGE PARTITIONS P2 TO P4 INTO PARTITION P_MERGE;

    普通表转分区表方法

    将普通表转换成分区表有 4 种方法:
    1. Export/import method
    2. Insert with a subquery method
    3. Partition exchange method
    4. DBMS_REDEFINITION

    详情参考
    How to Partition a Non-partitioned Table [ID 1070693.6]
    或者
    oracle将普通表改为分区表

    表分区的相关操作

    1.添加分区

    添加新的分区有 2 中情况:
    ( 1)原分区里边界是 maxvalue 或者 default。 这种情况下,我们需要把边界分区 drop 掉,加上新分区后,在添加上新的分区。 或者采用 split,对边界分区进行拆分。
    ( 2)没有边界分区的。 这种情况下,直接添加分区就可以了。

    以边界分区添加新分区示例:

    ( 1)分区表和索引的信息如下:

    SQL> create table custaddr
    2 (
    3 id varchar2(15 byte) not null,
    4 areacode varchar2(4 byte)
    5 )
    6 partition by list (areacode)
    7 (
    8 partition t_list556 values ('556') tablespace icd_service,
    9 partition p_other values (default)tablespace icd_service
    10 );

    表已创建。

    SQL> create index ix_custaddr_id on custaddr(id)
    2 local (
    3 partition t_list556 tablespace icd_service,
    4 partition p_other tablespace icd_service
    5 );

    索引已创建。

    ( 2)插入几条测试数据:

    SQL> insert into custaddr values('1','556');
    已创建 1 行。
    SQL> insert into custaddr values('2','551');
    已创建 1 行。
    SQL> insert into custaddr values('3','555');
    已创建 1 行。
    SQL> commit;
    提交完成。
    SQL> select * from custaddr;
    ID AREA
    --------------- ----
    1 556
    2 551
    3 555
    SQL> select * from custaddr partition(t_list556);
    ID AREA
    --------------- ----
    1 556
    SQL>

    ( 3)删除 default 分区

    sql> alter table custaddr drop partition p_other;
    表已更改。
    sql> select table_name,partition_name from user_tab_partitions where
    table_name='CUSTADDR';
    table_name partition_name
    ------------------------------ ------------------------------
    custaddr t_list556

    ( 4)添加新分区

    SQL> alter table custaddr add partition t_list551 values('551') tablespace icd_service;
    表已更改。
    SQL> select table_name,partition_name from user_tab_partitions where
    table_name='CUSTADDR';
    TABLE_NAME PARTITION_NAME
    ------------------------------ ------------------------------
    CUSTADDR T_LIST556
    CUSTADDR T_LIST551

    ( 5)添加 default 分区

    SQL> alter table custaddr add partition p_other values (default) tablespace
    icd_service;
    表已更改。
    SQL> select table_name,partition_name from user_tab_partitions where
    table_name='CUSTADDR';
    TABLE_NAME PARTITION_NAME
    ------------------------------ ------------------------------
    CUSTADDR T_LIST556
    CUSTADDR T_LIST551
    CUSTADDR P_OTHER

    ( 6)对于局部索引, oracle 会自动增加一个局部分区索引。验证一下:

    sql> select owner,index_name,table_name,partitioning_type from dba_part_indexes
    where index_name='ix_custaddr_id';
    owner index_name table_name
    
    ---------------------- ------------------------------ ------------------
    icd ix_custaddr_id custaddr
    sql> select index_owner,index_name,partition_name from dba_ind_partitions where
    index_name='ix_custaddr_id';
    index_owner index_name partition_name
    ------------------------------ ------------------------------ ------------------
    icd ix_custaddr_id p_other
    icd ix_custaddr_id t_list551
    icd ix_custaddr_id t_list556

    分区索引自动创建了。

    用 split方法示例

    sql> alter table custaddr split partition p_other values('552') into (partition t_list552
    tablespace icd_service, partition p_other tablespace icd_service);
    表已更改。

    注意values(‘552’),如果是 Range 类型的,使用 at, List 使用 Values。

    SQL> select table_name,partition_name from user_tab_partitions where
    table_name='CUSTADDR';
    TABLE_NAME PARTITION_NAME
    ------------------------------ ------------------------------
    CUSTADDR T_LIST556
    CUSTADDR T_LIST551
    CUSTADDR T_LIST552
    CUSTADDR P_OTHER
    SQL> select index_owner,index_name,partition_name from dba_ind_partitions
    where index_name='IX_CUSTADDR_ID';
    index_owner index_name partition_name
    ------------------------------ ------------------------------ ------------------
    icd ix_custaddr_id p_other
    icd ix_custaddr_id t_list551
    icd ix_custaddr_id t_list552
    icd ix_custaddr_id t_list556

    注意:分区表会自动维护局部分区索引。全局索引会失效,需要进行 rebuild。

    2.删除分区

      alter table T_TRACK drop partition p_2005_04;
    

    3.添加子分区

        alter table T_TRACK
            modify partition P_2005_01
            add subpartition P_2005_01_P1017 values('P1017');

    4.删除子分区

          alter table T_TRACK drop subpartition p_2005_01_p1017;

    5.截断一个分区表中的一个分区的数据:

      alter table sales3  truncate partition sp1
               --这种方式会使全局分区索引无效
      alter table sales3 truncate partition sp1 update indexes
              --这种方式全局分区索引不会无效

    说明:
    Truncate 相对 delete 操作很快,数据仓库中的大量数据的批量数据加载可能
    会有用到; 截断分区同样会自动维护局部分区索引,同时会使全局索引 unusable,需要重建

    6.截断分区表的子分区

      alter table comp truncate subpartition sub1

    7.截断带有约束的分区表

     a、禁用约束
              alter table sales disable constraint dname_sales1
            b、截断分区
              alter table sales truncate partitoin dec
            c、启用约束
              alter table sales enable constraint dname_sales1

    8.查看一个表是不是分区表

      select table_name,partitioned from user_tables;
            TABLE_NAME                     PAR
            ------------------------------ ---
            DEPT                           NO
            DEPT3                          YES

    9.将一个表的分区从一个表空间移动到另一个表空间

     a、查看分区在哪个表空间
              SELECT TABLE_OWNER,TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,
              SUBPARTITION_COUNT
              FROM DBA_TAB_PARTITIONS WHERE TABLE_OWNER='SCOTT';
    
            b、移动分区
              alter table sales move partiton sp1 tablespace tp;
    
            c、检查是否移动成功
              SELECT TABLE_OWNER,TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,
              SUBPARTITION_COUNT
              FROM DBA_TAB_PARTITIONS WHERE TABLE_OWNER='SCOTT';
    
            移动表空间后,要重建索引,否则索引会变得无效
            alter index xxx rebuild
    

    注意: 分区移动会自动维护局部分区索引, oracle 不会自动维护全局索引,所以需要我们重新 rebuild 分区索引,具体需要 rebuild 哪些索引,可以通过
    dba_part_indexes,dba_ind_partitions 去判断

    SQL> Select index_name,status From user_indexes Where
    table_name='CUSTADDR';
    INDEX_NAME STATUS
    ------------------------------ --------
    IX_CUSTADDR_ID N/A

    10.合并分区:

    相邻的分区可以 merge 为一个分区,新分区的下边界为原来边界值较低的分区,上边界为原来边界值较高的分区, 原先的局部索引相应也会合并,全局索引会失效,需要 rebuild。

     alter table sales3 merge partitons sp1,sp3 into partition sp3

    合并后的分区名,不能是边界值较低的那个

    11.与分区表相关的数据字典视图:

            DBA_TAB_PARTITIONS
            DBA_IND_PARTITIONS
            DBA_TAB_SUBPARTITIONS
            DBA_IND_SUBPARTITIONS
    
    展开全文
  • 详解Oracle partition分区表

    来源:http://www.cnblogs.com/pengineer/p/4393988.html
     随着表中行数的增多,管理和性能性能影响也将随之增加。备份将要花费更多时间,恢复也将 要花费更说的时间,对整个数据表的查询也将花费更多时间。通过把一个表中的行分为几个部分,可以减少大型表的管理和性能问题,以这种方式划分发表数据的方法称为对表的分区。分区表的优势:
    (1)改善查询性能:对分区对象的查询可以仅搜索自己关心的分区,提高检索速度;
    (2)方便数据管理:因为分区表的数据存储在多个部分中,所以按分区加载和删除数据比在大表中加载和删除数据更容易;
    (3)方便备份恢复:因为分区比被分区的表要小,所以针对分区的备份和恢复方法要比备份和恢复整个表的方法多。

    一、Oracle数据库提供对表或索引的分区方法有几种:
    1)范围分区
    2)列表分区
    3)散列分区(hash分区)
    4)复合分区(子分区)

    二、实例演示Oracle对表或索引的分区操作
    1,创建4个测试用的表空间,每个表空间作为一个独立分区(考虑到Oracle中分区映射的实现方式,建议将表中的分区数设置为2的乘方,以便使数据均匀分布)
    SYS>create tablespace partition1 datafile ‘/home/oracle/app/oradata/orcl/partition1.dbf’ size 20m;
    SYS>create tablespace partition2 datafile ‘/home/oracle/app/oradata/orcl/partition2.dbf’ size 20m;
    SYS>create tablespace partition3 datafile ‘/home/oracle/app/oradata/orcl/partition3.dbf’ size 20m;
    SYS>create tablespace partition4 datafile ‘/home/oracle/app/oradata/orcl/partition4.dbf’ size 20m;

     2,范围分区
     范围分区就是对数据表中的某个值的范围进行分区,根据某个值的范围,决定将该数据存储在哪个分区上。如根据序号分区,根据业务记录的创建日期进行分区等(联通每个月的账单记录就用的分区表存储)。
    
     需求描述:有一个物料交易表,表名:material_transactions。该表将来可能有千万级的数据记录数。要求在建该表的时候使用分区表。这时候我们可以使用序号分区三个区,每个区中预计存储三千万的数据,也可以使用日期分区,如每五年的数据存储在一个分区上。
    

    根据交易记录的序号分区建表:—-为了测试需要做以下修改:

      交易号小于2的记录存储在分区1上,大于等于2且小于4的交易号储存在分区2上,大于等于4且小于6的交易号储存在分区3上,大于等于6的交易号存储在分区4上。(不必为最后一个分区指定最大值,maxvalue关键字会告诉Oracle使用这个分区来存储在前面几个分区中不能储存的数据)。

     2.1,向分区表里面插入数据:
    

    2.2,查询表数据
    不指定分区:

    指定分区:

      2.3,更改表数据

     2.4,删除表数据
    

    3,散列分区(hash分区)
    除范围分区外,Oracle还支持散列分区。散列分区通过在分区键值上执行一个散列函数来说决定数据的物理位置。在范围分区中,分区键的连续值通常储存在相同的分区中。而在散列分区中,连续的分区键值不必储存在相同的分区中。散列分区把记录分布在比范围分区更多的分区上,这减少了I/O争用的可能性。

      还有一种定义hash分区的方式是:partition by hash(column) partition n store in (tbs1,,,tbsm)。表空间的数目不必等于分区的数目,即n不一定等于m,如果指定的分区数目比表空间的数目多,则分区将会以循环的方式分配到表空间中,一个表空间可以含有多个分区:

     4,列表分区
    列表分区告诉Oracle所有可能的值,并指定应该插入相应行的分区,它适用于表的数据量很大但是某一列的值只有少量几种。

    在列表分区中,可以使用关键字default来指定未列出的所有情况。(上述4个分区创建在一个表空间中)

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

    p1:sales_cost小于1
    p1sub1:sales_cost小于1,且status为ACTIVE
    p1sub2:sales_cost小于1,且status为INACTIVE
    p2:sales_cost大于等于1,小于3
    p2sub1:sales_cost大于等于1,小于3,且status为ACTIVE
    p2sub2:sales_cost大于等于1,小于3,且status为INACTIVE
    操作测试:

    补充:如果插入的值不在自定义的所有分区范围内,将由Oracle来分配。

    三,有关表分区的一些维护性操作:
    1、添加分区
    以下代码给SALES表添加了一个P3分区:
    ALTER TABLE SALES ADD PARTITION P3 VALUES LESS THAN(TO_DATE(‘2003-06-01’,’YYYY-MM-DD’));
    注意:以上添加的分区界限应该高于最后一个分区界限。

    以下代码给SALES表的P3分区添加了一个P3SUB1子分区
    ALTER TABLE SALES MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES(‘COMPLETE’);

    2、删除分区
    以下代码删除了P3表分区:
    ALTER TABLE SALES DROP PARTITION P3;
    在测试中遇到这样的情况。如果表创建了分区,如果要删除数据文件(表空间文件),则要先删除分区,然后才能删除数据文件(但是在删除数据文件时,必须要保留一个分区才能最终删除数据文件&表空间文件)。当然,也可以直接就删除表也行,刚所有的全删除,但是表空间文件还在。

    在以下代码删除了P4SUB1子分区:
    ALTER TABLE SALES DROP SUBPARTITION P4SUB1;
    注意:如果删除的分区是表中唯一的分区,那么此分区将不能被删除,要想删除此分区,必须先删除表。

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

    通过以下代码截断子分区:
    ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2;

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

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

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

    7、重命名表分区
    以下代码将P21更改为P2
    ALTER TABLE SALES RENAME PARTITION P21 TO P2;

    8、跨分区查询
    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)
    );

    9、与分区相关的表和视图:
    分区
    –查询表上有多少分区:SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME=’tableName’
    –显示表分区信息 显示数据库所有分区表的详细分区信息: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_TABLES where table_name=upper(‘dinya_test’)
    –显示当前用户可访问的所有分区表信息:select * from ALL_PART_TABLES
    –显示当前用户所有分区表的信息:select * from USER_PART_TABLES
    分区列
    –显示分区列 显示数据库所有分区表的分区列信息: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;

    注:分区根据具体情况选择。

    表分区有以下优点:
    1、数据查询:数据被存储到多个文件上,减少了I/O负载,查询速度提高。
    2、数据修剪:保存历史数据非常的理想。
    3、备份:将大表的数据分成多个文件,方便备份和恢复。
    4、并行性:可以同时向表中进行DML操作,并行性性能提高。

    ================================================

    四、分区索引:

    1、一般索引:create index index_name on table(col_name);
    2、Oracle 分区索引详解
    语法:Table Index
    CREATE [UNIQUE|BITMAP] INDEX [schema.]index_name
    ON [schema.]table_name [tbl_alias]
    (col [ASC | DESC]) index_clause index_attribs
    index_clauses:

    分以下两种情况
    1. Local Index
    就是索引信息的存放位置依赖于父表的Partition信息,换句话说创建这样的索引必须保证父表是Partition
    1.1 索引信息存放在父表的分区所在的表空间。但是仅可以创建在父表为HashTable或者composite分区表的。
    LOCAL STORE IN (tablespace)

    1.2 仅可以创建在父表为HashTable或者composite分区表的。并且指定的分区数目要与父表的分区数目要一致
    LOCAL STORE IN (tablespace) (PARTITION [partition [LOGGING|NOLOGGING] [TABLESPACE {tablespace|DEFAULT}] [PCTFREE int] [PCTUSED int] [INITRANS int] [MAXTRANS int] [STORAGE storage_clause] [STORE IN {tablespace_name|DEFAULT] [SUBPARTITION [subpartition [TABLESPACE tablespace]]]])

    1.3 索引信息存放在父表的分区所在的表空间,这种语法最简单,也是最常用的分区索引创建方式。
    Local

    1.4 并且指定的Partition 数目要与父表的Partition要一致
    LOCAL (PARTITION [partition
    [LOGGING|NOLOGGING]
    [TABLESPACE {tablespace|DEFAULT}]
    [PCTFREE int]
    [PCTUSED int]
    [INITRANS int]
    [MAXTRANS int]
    [STORAGE storage_clause]
    [STORE IN {tablespace_name|DEFAULT]
    [SUBPARTITION [subpartition [TABLESPACE tablespace]]]])
    Global Index

    索引信息的存放位置与父表的Partition信息完全不相干。甚至父表是不是分区表都无所谓的。语法如下:
    GLOBAL PARTITION BY RANGE (col_list)
    ( PARTITION partition VALUES LESS THAN (value_list)
    [LOGGING|NOLOGGING]
    [TABLESPACE {tablespace|DEFAULT}]
    [PCTFREE int]
    [PCTUSED int]
    [INITRANS int]
    [MAXTRANS int]
    [STORAGE storage_clause] )

    但是在这种情况下,如果父表是分区表,要删除父表的一个分区都必须要更新Global Index ,否则索引信息不正确
    ALTER TABLE TableName DROP PARTITION PartitionName Update Global Indexes

    –查询索引
    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 sale partition(p1) ; 截断p3分区 alter table sale truncate partition p3 update global indexes; 查看表的所有分区 select table_...
  • Oracle分区表及分区索引的创建

    万次阅读 多人点赞 2018-07-08 11:21:22
    关于分区表和分区索引(About Partitioned Tables and Indexes)对于10gR2而言,基本上可以分成几类:• Range(范围)分区• Hash(哈希)分区• List(列表)分区• 以及组合分区:Range-Hash,Range-List。 对于表而...
  • Oracle 分区表——Range分区

    千次阅读 2017-06-24 17:19:17
    Oracle 分区表——Range分区
  • Oracle中的分区表Oracle中的一个很好的特性,可以把大表划分成多个小表,从而提高对于该大表的SQL执行效率,而各个分区应用又是透明的。 分区表中的每个分区有独立的存储特性,包括表空间、PCT_FREE等。那分区表...
  • ORACLE分区表、分区索引ORACLE对于分区表方式其实就是将表分段存储,一般普通表格是一个段存储,而分区表会分成多个段,所以查找数据过程都是先定位根据查询条件定位分区范围,即数据在那个分区或那几个内部,然后在...
  • ORACLE分区表、分区索引
  • Oracle分区表操作

    2017-09-20 20:50:00
    Oracle提供的基本语法基本可以满足一般性修改,但是对于把普通堆表改为分区表,把索引组织表修改为堆表等操作就无法完成了。而且,对于被大量DML语句访问的表,幸运的是,Oracle从9i版本开始提供了在线重定义表功能...
  • oracle分区表

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

    2010-12-21 15:48:00
    ORACLE里如果遇到特别大的表,... 我们以一个2001年开始使用的留言版做例子讲述分区表创建和使用:   1 、以system 身份创建独立的表空间(大小可以根据数据量的多少而定) create tables
  • ORACLE分区表、分区索引ORACLE对于分区表方式其实就是将表分段存储,一般普通表格是一个段存储,而分区表会分成多个段,所以查找数据过程都是先定位根据查询条件定位分区范围,即数据在那个分区或那几个内部,然后在...
  • 深入学习Oracle分区表及分区索引

    千次阅读 2018-07-09 15:41:11
    转载于:http://www.blogjava.net/rabbit/archive/2013/01/08/393955.html深入学习Oracle分区表及分区索引 关于分区表和分区索引(About Partitioned Tables and Indexes)对于10gR2而言,基本上可以分成几类:• ...
  • Oracle数据库分区表原理学习

    千次阅读 2013-08-07 17:15:41
    分区表通过分区列的判断,把分区列不同的记录,放到不同的分区中。分区完全应用透明。 Oracle的表分区功能通过改善可管理性、性能和可用性,从而为各式应用程序带来了极大的好处。通常,分区可以使某些查询...
  • 分区表的讲解如何选择分区的类型... 下面分别说说分区表对每一项的具体影响, 性能: 这一般是分区的主要目的。分区将大表变成了小表,当where之后的条件体现分区字段的具体值时,避免了全表扫描。 易于管理: 对于...
  • oracle分区表、分区索引和全局索引部分总结: http://space.itpub.net/18953778/viewspace-557476 找不到数据对应分区会报: ora-14400插入的分区关键字未映射到任何分区 分区索引与全局索引的性能对比例子,前两个...
  • Oracle 分区表

    万次阅读 2010-10-07 17:51:00
    随着的不断增大,对于新纪录的增加、查找、删除等(DML)的维护也更加困难。对于数据库中的超大型,可通过把它的数据分成若干个 小,从而简化数据库的管理...对于每一个简化后的小,我们称为一个单个的分区

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 19,213
精华内容 7,685
关键字:

oracle对分区表创建视图