精华内容
下载资源
问答
  • 2、子分区压缩 3、查看分区索引状态 4、重建对应分区赛索引 1、查看全部分区大小 查看ALM_FLOW的分区大小 t.BYTES/1024/1024(单位:M) SELECT t.segment_name,t.partition_name,t.BYTES/1024/1024 FROM USER_...

    文档结构如下:
    1、 查看全部分区大小
    2、子分区压缩
    3、查看分区索引状态
    4、重建对应分区赛索引
    5、分区的日常操作

    1、查看全部分区大小
    查看ALM_FLOW的分区大小 t.BYTES/1024/1024(单位:M)

    SELECT t.segment_name,t.partition_name,t.BYTES/1024/1024 FROM USER_SEGMENTS T WHERE T.segment_name='ALM_FLOW' ORDER BY T.partition_name
    

    在这里插入图片描述
    2、子分区压缩

    alter table ALM_FLOW move subpartition AF_P_201910_S1 compress parallel 8
    alter table ALM_FLOW move subpartition AF_P_201910_S2 compress parallel 8 
    alter table ALM_FLOW move subpartition AF_P_201910_S3 compress parallel 8 
    alter table ALM_FLOW move subpartition AF_P_201910_S4 compress parallel 8 
    alter table ALM_FLOW move subpartition AF_P_201910_S5 compress parallel 8 
    alter table ALM_FLOW move subpartition AF_P_201910_S6 compress parallel 8 
    alter table ALM_FLOW move subpartition AF_P_201910_S7 compress parallel 8 
    alter table ALM_FLOW move subpartition AF_P_201910_S8 compress parallel 8
    

    3、查看分区索引状态 b.subpartition_name,为子分区名称

    select b.index_name, b.partition_name, b.subpartition_name,b.status from user_ind_subpartitions b where b.index_name in ('IDX_ALM_FLOW','IDX_ALM_FLOW_TELLER','IDX_ALM_FLOW_TRAN_DT') and status<>'USABLE' order by 1,2
    

    在这里插入图片描述

    4、重建对应分区赛索引 IDX_ALM_FLOW(已经重建)

    alter index IDX_ALM_FLOW rebuild subpartition AF_P_201910_S5   parallel 8 online
    alter index IDX_ALM_FLOW_TELLER rebuild subpartition AF_P_201910_S5   parallel 8 online
    alter index IDX_ALM_FLOW_TRAN_DT rebuild subpartition AF_P_201910_S5   parallel 8 online
    

    5、分区的日常操作
    (1)查看数据库所有分区表

    select * from user_tables t where partitoned = 'YES';
    

    (2)查看a_tab表分区详情

    select * from user_tab_partitions t where table_name= 'a_tab';
    

    (3)a_tab表添加“TAB_P20_1”分区

    ALTER TABLE a_tab add PARTITION TAB_P20_1 values  less than('2021-03-31');
    

    (4)a_tab表删除“TAB_P20_1”分区

    ALTER TABLE a_tab drop PARTITION TAB_P20_1 ;
    

    (5)其他

    添加分区 – 在最后一个分区之后添加新分区
    
    ALTER TABLE SALES ADD PARTITION P4 VALUES LESS THAN (4000);
    删除分区 – 删除一个指定的分区,分区的数据也随之删除
    
    ALTER TABLE SALES DROP PARTITION P4;
    截断分区 – 删除指定分区中的所有记录
    
    ALTER TABLE SALES TRUNCATE PARTITION P3;
    合并分区 - 将范围分区或复合分区的两个相邻分区连接起来
    
    ALTER TABLE SALES MERGE PARTITIONS S1, S2 INTO PARTITION S2;
    拆分分区 - 将一个大分区中的记录拆分到两个分区中
    
    ALTER TABLE SALES SPLIT PARTITION P2 AT (1500)INTO (PARTITION P21, PARTITION P22);
    
    展开全文
  • oracle中把分区表做成压缩

    千次阅读 2018-03-21 16:55:46
    alter table HH_FTP_RST_RPT move partition P20180323 compress for oltp;为什么我在执行这句话的时候ORA-14257: 不能移动范围, 列表, 系统或散列分区以外的分区报这个错误了呢。...

    alter table HH_FTP_RST_RPT move partition P20180323 compress for oltp;

    为什么我在执行这句话的时候

    ORA-14257: 不能移动范围, 列表, 系统或散列分区以外的分区

    报这个错误了呢。

    展开全文
  • 原创Oracle作者:bestpaydata时间:2015-12-18 18:17:4443440 oracle分区表的常规操作导致对...oracle分区表目前已经很普遍的应用于我们的生产系统,但是在日常需要维护分区表的时候,一些对于分区表的基本操作的...

    原文地址:http://blog.itpub.net/30109892/viewspace-1874763/

    原创 Oracle 作者:bestpaydata 时间:2015-12-18 18:17:44  4344  0

    oracle分区表的常规操作导致对索引的影响

         oracle分区表目前已经很普遍的应用于我们的生产系统,但是在日常需要维护分区表的时候,一些对于分区表的基本操作的时候,我们难免会对分区表上的索引是否失效有些担心,那么今天我就带大家看下具体哪些操作会导致分区表上的索引失效。

    为了控制篇幅,本次实验只针对RANGE分区,其他两种分区请有兴趣的同学自行测试哦~~~

    1、分区表索引的分类

       熟悉分区表的人都应该知道,oracle分区表的索引类型分为两种,一种是Local索引又称本地索引,一种是Global索引也叫全局索引。本次实验不具体介绍这两种索引对分区表访问所产生的性能问题方面的差异,只介绍一些常用的DDL操作对分区表上的索引的一些影响。

    如果想要知道两种索引的性能差异,请期待下期的文章分享哦~~~~

    2、针对分区表做各种DDL操作,查看对索引的影响

    2.1创建分区表,并创建全局索引和分区索引

    create table tmp_test_range

    (

      id number,

      id_local number,

      name varchar2(30),

      int_date date,

      bz varchar2(20)

    )

    PARTITION BY RANGE(int_date)

    --interval(numtodsinterval (1,'DAY'))

    (

    partition P201511 values less than (to_date('20151201','yyyymmdd')),

    partition P20151201 values less than (to_date('20151202','yyyymmdd')),

    partition P20151203 values less than (to_date('20151203','yyyymmdd')),

    partition P20151204 values less than (to_date('20151204','yyyymmdd')),

    partition P20151205 values less than (to_date('20151205','yyyymmdd')),

    partition P20151206 values less than (to_date('20151206','yyyymmdd')),

    partition P20151207 values less than (to_date('20151207','yyyymmdd'))

    );

    向分区表中插入数据:

    declare

      v_date date := to_date('20151127', 'yyyy-mm-dd');

    begin

      for c in 1 .. 9 loop

        for d in 1 .. 100 loop

          insert into tmp_test_range values (c || d,c||d, '测试数据', v_date, 'BZ');

        end loop;

        v_date := v_date + 1;

      end loop;

      commit;

    end;

     

    创建索引:

    分别在ID, ID_LOCAL 和创建一个全局索引和分区索引

    create index I_TMP_TEST_RANGE_G on tmp_test_range(id) nologging;

    create index I_TMP_TEST_RANGE_L on tmp_test_range(id_local) nologging LOCAL;
    查看索引的状态:

    local索引 :

    Global索引:

    2.2  DDL操作对全局索引的影响:

    好了,前面的基础工作已经准备完毕,下面我们开始做一些DDL操作,看下对全局索引的影响。

    添加分区和对其中一个分区重命名:

    ALTER TABLE tmp_test_range ADD PARTITION  P20151208 values less than (to_date('20151208','yyyymmdd'));

     

    ALTER TABLE tmp_test_range RENAME PARTITION P20151207 TO P20151207_2;

    查看索引情况:

    Global

    Local 索引:

    总结:添加分区和对分区重新命名并不会导致Global和local索引失效。

    删除表中的分区:

    ALTER TABLE tmp_test_range DROP PARTITION  P20151208 ;

    清空其中一个分区中的数据:

    ALTER TABLE tmp_test_range TRUNCATE PARTITION P201511;

    查看索引情况:

    local索引会将被删除的分区上的local索引删除,不会影响到其他分区的索引。

    Global 索引:

    OK,经过上面的实验可能你很容易就能得出结论说,删除分区不会导致Global索引失效,其实不然,让我们看一种其他情况:

    查看某个分区的数据(P201511),看下图是存在数据的:

    现在对分区进行删除:

    alter table tmp_test_range drop partition P201511;

    或者:
    ALTER TABLE tmp_test_range TRUNCATE PARTITION P201518;

    查看Global索引:

    唉,还是失效了,所以在删除分区表中的分区的时候,一定要确认有没有数据存在。

    总结:

    删除分区表中的分区的时候 或者truncate 分区中的数据时,一定要确认分区中是否有数据存在,如果没有数据不会导致Global失效,反之则会导致Global索引失效。而对其他分区上的local索引都不会造成影响。

    合并分区:

    合并分区有两种方式,一种是维护索引的,一种是不维护索引。我们先来看不维护索引的。

    ALTER TABLE tmp_test_range MERGE PARTITIONS P201511,P20151201 INTO PARTITION P20151208;

    查看索引的情况:

    总结:Global索引,做合并分区操作的时候会导致Global索引失效,所以操作的时候一定要当心哦!!!

    local 索引不会维护合并后的分区,但是不会影响其他的分区,合并分区操作还会将原来被合并的分区删除。

    当然oracle 也提供了合并分区的时候维护索引的操作,当大家在线上操作的时候,请使用下面的语句对分区做合并。

    ALTER TABLE tmp_test_range MERGE PARTITIONS P201511,P20151201 INTO PARTITION P20151208 update  indexes ;

    这样就不会在做合并分区操作的时候,导致索引失效了。

    拆分分区:

    拆分分区同样也是有两种方式,一种是直接拆分并不维护索引,另一种是带维护索引的拆分方式。具体操作见下面的实验:

    我们先看维护索引的方式拆分:

    ALTER TABLE tmp_test_range SPLIT PARTITION P201511 at (date'2015-11-28')    INTO (PARTITION TAB_PARTOTION_05,PARTITIONTAB_PARTOTION_OTHERE) update indexes;

    查看索引的情况:

    果然使用update indexes的方式进行拆分Global索引和local索引都是正常的。那下面我们再来看下不维护索引的方式:

    ALTER TABLE tmp_test_range SPLIT PARTITION P201511 at (date'2015-11-28')

        INTO (PARTITION TAB_PARTOTION_05,PARTITION TAB_PARTOTION_OTHERE) ;

    还是没有令我们失望,使用这种直接拆分的方式两种索引都有失效的情况发生,

    local索引在新增的两个分区上的索引失效,Global索引还是不负众望的失效了。

    总结:

    对分区表进行拆分分区的时候如果不加参数 update indexes 会导致 新增分区上的local索引失效,Global索引失效。采用update  indexes 的方式这两种索引都不会失效。

    交换分区:

    交换分区同样也是有两种方式,一种是自动维护索引的,一种是直接交换分区不做维护索引操作,显然第二种方式会导致索引失效。具体见下面的实验(下面两个不要同时操作):

    ALTER TABLE tmp_test_range EXCHANGE PARTITION P201511 WITH TABLE tmp_test_range_2;

     

    ALTER TABLE tmp_test_range EXCHANGE PARTITION P201511  WITH TABLE tmp_test_range_2 update INDEXES;

     

    查看两个索引情况:

    果不其然,交换分区还是会导致Global索引失效,local索引还是被交换的分区上的索引失效。所以在做交换分区的时候,我们还是可以使用update indexes 来维护索引Global索引,但是对于local索引即使使用update  indexes 也会导致local索引失效,还是要再重新维护 。

    3、总结:

    好了,上面我们说了那么多,让我们总结一下,具体哪些操作会对分区表上的索引有一定的影响。

     两种索引都不会产生影响的操作:

    1.添加分区

    2.删除分区(分区中没有数据)

    3.对分区重命名

     

    会产生影响的:

    1.合并分区

    新增分区上的local索引失效

    Global索引失效

    PS:使用update indexes 的方式可以避免索引失效的发生(包括local索引和Global索引)。

    2、拆分分区

    拆分出来的分区上的local索引失效

    Global索引失效

    PS:使用update indexes 的方式可以避免索引失效的发生(包括local索引和Global索引)。

    3、分区交换

    被交换分区上的local索引失效

    Global索引失效

    PS:使用update indexes 的方式可以避免Global索引失效的发生,不能避免local索引失效。

    4、删除分区 或者truncate分区中的数据 时,如果被删除的分区上有数据存在,会导致Global索引失效。

     

    通过上面的实验我们可以看出对于分区表来说,且不论性能如何,就单单对方便数据管理来说,还是创建local索引更加方便对分区表中数据的管理。

    author:冯栋华

    展开全文
  • Oracle分区表及分区索引的创建

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

    关于分区表和分区索引(About Partitioned Tables and Indexes)对于10gR2而言,基本上可以分成几类:

    •          Range(范围)分区

    •          Hash(哈希)分区

    •          List(列表)分区

    •          以及组合分区:Range-Hash,Range-List。

      对于表而言(常规意义上的堆组织表),上述分区形式都可以应用(甚至可以对某个分区指定compress属性),只不过分区依赖列不能是lob,long之类数据类型,每个表的分区或子分区数的总数不能超过1023个。

      对于索引组织表,只能够支持普通分区方式,不支持组合分区,常规表的限制对于索引组织表同样有效,除此之外呢,还有一些其实的限制,比如要求索引组织表的分区依赖列必须是主键才可以等。

      注:本篇所有示例仅针对常规表,即堆组织表!

      对于索引,需要区分创建的是全局索引,或本地索引:

      l 全局索引(global index):即可以分区,也可以不分区。即可以建range分区,也可以建hash分区,即可建于分区表,又可创建于非分区表上,就是说,全局索引是完全独立的,因此它也需要我们更多的维护操作。

      l 本地索引(local index):其分区形式与表的分区完全相同,依赖列相同,存储属性也相同。对于本地索引,其索引分区的维护自动进行,就是说你add/drop/split/truncate表的分区时,本地索引会自动维护其索引分区。

    Oracle建议如果单个表超过2G就最好对其进行分区,对于大表创建分区的好处是显而易见的,这里不多论述why,而将重点放在when以及how。

    ORACLE对于分区表方式其实就是将表分段存储,一般普通表格是一个段存 储,而分区表会分成多个段,所以查找数据过程都是先定位根据查询条件定位分区范围,即数据在那个分区或那几个内部,然后在分区内部去查找数据,一个分区一 般保证四十多万条数据就比较正常了,但是分区表并非乱建立,而其维护性也相对较为复杂一点,而索引的创建也是有点讲究的,这些以下尽量阐述详细即可。

    range分区方式,也算是最常用的分区方式,其通过某字段或几个字段的组合的值,从小到大,按照指定的范围说明进行分区,我们在INSERT数据的时候就会存储到指定的分区中。

    List分区方式,一般是在range基础上做的二级分区较多,是一种列举方式进行分区,一般讲某些地区、状态或指定规则的编码等进行划分。

    Hash分区方式,它没有固定的规则,由ORACLE管理,只需要将值INSERT进去,ORACLE会自动去根据一套HASH算法去划分分区,只需要告诉ORACLE要分几个区即可。

    WHEN

      一、When使用Range分区

      Range分区呢是应用范围比较广的表分区方式,它是以列的值的范围来做为分区的划分条件,将记录存放到列值所在的range分区中,比如按照 时间划分,2008年1季度的数据放到a分区,08年2季度的数据放到b分区,因此在创建的时候呢,需要你指定基于的列,以及分区的范围值,如果某些记录 暂无法预测范围,可以创建maxvalue分区,所有不在指定范围内的记录都会被存储到maxvalue所在分区中,并且支持指定多列做为依赖列,后面在 讲how的时候会详细谈到。

      二、When使用Hash分区

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

      三、When使用List分区

      List分区与range分区和hash分区都有类似之处,该分区与range分区类似的是也需要你指定列的值,但这又不同与range分区的 范围式列值---其分区值必须明确指定,也不同与hash分区---通过明确指定分区值,你能控制记录存储在哪个分区。它的分区列只能有一个,而不能像 range或者hash分区那样同时指定多个列做为分区依赖列,不过呢,它的单个分区对应值可以是多个。

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

      四、When使用组合分区

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

      组合分区呢在10g中有两种:range-hash,range-list。注意顺序哟,根分区只能是range分区,子分区可以是hash分区或list分区。

    提示:11g在组合分区功能这块有所增强,又推出了range-range,list-range,list-list,list-hash, 这就相当于除hash外三种分区方式的笛卡尔形式都有了。为什么会没有hash做为根分区的组合分区形式呢,再仔细回味一下第二点,你一定能够想明 白~~。

    深入学习Oracle分区表及分区索引(2)

    一、如何创建

      如果想对某个表做分区,必须在创建表时就指定分区,我们可以对一个包含分区的表中的分区做修改,但不能直接将一个未分区的表修改成分区表(起码在10g是不行的,当然你可能会说,可以通过在线重定义的方式,但是这不是直接哟,这也是借助临时表间接实现的)。

      创建表或索引的语法就不说了,大家肯定比我还熟悉,而想在建表(索引)同时指定分区也非常容易,只需要把创建分区的子句放到";"前就行啦,同 时需要注意表的row movement属性,它用来控制是否允许修改列值所造成的记录移动至其它分区存储,有enable|disable两种状态,默认是disable row movement,当disable时,如果记录要被更新至其它分区,则更新语句会报错。

      下面分别演示不同分区方式的表和索引的创建:

    1、创建range分区

      语法如下,需要我们指定的有:

      l column:分区依赖列(如果是多个,以逗号分隔);

      l partition:分区名称;

      l values less than:后跟分区范围值(如果依赖列有多个,范围对应值也应是多个,中间以逗号分隔);

      l tablespace_clause:分区的存储属性,例如所在表空间等属性(可为空),默认继承基表所在表空间的属性。

    ① 创建一个标准的range分区表:

      JSSWEB> create table t_partition_range (id number,name varchar2(50))

        partition by range(id)(

        partition t_range_p1 values less than (10) tablespace tbspart01,

        partition t_range_p2 values less than (20) tablespace tbspart02,

        partition t_range_p3 values less than (30) tablespace tbspart03,

        partition t_range_pmax values less than (maxvalue) tablespace tbspart04

        );

      表已创建。

     要查询创建分区的信息,可以通过查询user_part_tables,user_tab_partitions两个数据字典(索引分区、组织分区等信息也有对应的数据字典,后续示例会逐步提及)。

      user_part_tables:记录分区的表的信息;

      user_tab_partitions:记录表的分区的信息。

      例如:

    JSSWEB> select table_name,partitioning_type,partition_count

            From user_part_tables where table_name='T_PARTITION_RANGE';

    JSSWEB> select partition_name,high_value,tablespace_name

           from user_tab_partitions where table_name='T_PARTITION_RANGE'

           order by partition_position;

     

    ② 创建global索引range分区:

      JSSWEB> create index idx_parti_range_id on t_partition_range(id)

      2 global partition by range(id)(

      3 partition i_range_p1 values less than (10) tablespace tbspart01,

      4 partition i_range_p2 values less than (40) tablespace tbspart02,

      5 partition i_range_pmax values less than (maxvalue) tablespace tbspart03);

      索引已创建。

      由上例可以看出,创建global索引的分区与创建表的分区语句格式完全相同,而且其分区形式与索引所在表的分区形式没有关联关系。

      注意:我们这里借助上面的表t_partition_range来演示创建range分区的global索引,并不表示range分区的表,只能创建range分区的global索引,只要你想,也可以为其创建hash分区的global索引。

      查询索引的分区信息可以通过user_part_indexes、user_ind_partitions两个数据字典:

    JSSWEB> select index_name, partitioning_type, partition_count

      2    From user_part_indexes

      3   where index_name = 'IDX_PARTI_RANGE_ID';

     

    ③ Local分区索引的创建最简单,例如:

      仍然借助t_partition_range表来创建索引

      --首先删除之前创建的global索引

      JSSWEB> drop index IDX_PARTI_RANGE_ID;

      索引已删除。

      JSSWEB> create index IDX_PARTI_RANGE_ID on T_PARTITION_RANGE(id) local;

      索引已创建。

      查询相关数据字典:

    JSSWEB> select index_name, partitioning_type, partition_count

      2    From user_part_indexes

      3   where index_name = 'IDX_PARTI_RANGE_ID';

     

    JSSWEB> select partition_name, high_value, tablespace_name

      2    from user_ind_partitions

      3   where index_name = 'IDX_PARTI_RANGE_ID'

      4   order by partition_position;

     

     可以看出,local索引的分区完全继承表的分区的属性,包括分区类型,分区的范围值即不需指定也不能更改,这就是前面说的:local索引的分区维护完全依赖于其索引所在表。

      不过呢分区名称,以及分区所在表空间等信息是可以自定义的,例如:

      SQL> create index IDX_PART_RANGE_ID ON T_PARTITION_RANGE(id) local (

      2 partition i_range_p1 tablespace tbspart01,

      3 partition i_range_p2 tablespace tbspart01,

      4 partition i_range_p3 tablespace tbspart02,

      5 partition i_range_pmax tablespace tbspart02

      6 );

      索引已创建。

    SQL> select index_name, partitioning_type, partition_count

      2   From user_part_indexes

      3  where index_name = 'IDX_PART_RANGE_ID';

    SQL> select partition_name, high_value, tablespace_name

      2   from user_ind_partitions

      3  where index_name = 'IDX_PART_RANGE_ID'

      4  order by partition_position;

     创建hash分区

      语法如下:[图:hash_partitioning.gif]

      

      语法看起来比range复杂,其实使用起来比range更简单,这里需要我们指定的有:

      l column:分区依赖列(支持多个,中间以逗号分隔);

      l partition:指定分区,有两种方式:

      n 直接指定分区名,分区所在表空间等信息

    n 只指定分区数量,和可供使用的表空间。

     

    2、创建hash分区

      JSSWEB> create table t_partition_hash (id number,name varchar2(50))

      2 partition by hash(id)(

      3 partition t_hash_p1 tablespace tbspart01,

      4 partition t_hash_p2 tablespace tbspart02,

      5 partition t_hash_p3 tablespace tbspart03);

      表已创建。

      要实现同样效果,你还可以这样:

      JSSWEB> create table t_partition_hash2 (id number,name varchar2(50))

      2 partition by hash(id)

      3 partitions 3 store in(tbspart01,tbspart02,tbspart03);

      表已创建。

     这就是上面说的,直接指定分区数量和可供使用的表空间。

      提示:这里分区数量和可供使用的表空间数量之间没有直接对应关系。分区数并不一定要等于表空间数。

    要查询表的分区信息,仍然是通过user_part_tables,user_tab_partitions两个数据字典,这里不再举例。

     

     

    ① Global索引hash分区

      Hash分区索引的子句与hash分区表的创建子句完全相同,例如:

      JSSWEB> create index idx_part_hash_id on t_partition_hash(id)

      2 global partition by hash(id)

      3 partitions 3 store in(tbspart01,tbspart02,tbspart03);

      索引已创建。

      查询索引的分区信息也仍是通过user_part_indexes、user_ind_partitions两个数据字典,不再举例。

    ② 创建Local索引

      在前面学习range分区时,我们已经对Local索引的特性做了非常清晰的概述,因此这里也不再举例,如有疑问,建议再仔细复习range分区的相关示例,如果还有疑问,当面问我好了:)

      综上:

      Ø 对于global索引分区而言,在10g中只能支持range分区和hash分区,因此后续示例中不会再提及。

      Ø 对于local索引分区而言,其分区形式完全依赖于索引所在表的分区形式,不管从创建语法还是理解难度均无技术含量,因此后续也不再提供示例。

      Ø 注意,在创建索引时如果不显式指定global或local,则默认是global。

      Ø 注意,在创建global索引时如果不显式指定分区子句,则默认不分区(废话)。

     

     

     

     

    3、分区应用:

    一般一张表超过2G的大小,ORACLE是推荐使用分区表的,分区一般都需要 创建索引,说到分区索引,就可以分为:全局索引、分区索引,即:global索引和local索引,前者为默认情况下在分区表上创建索引时的索引方式,并 不对索引进行分区(索引也是表结构,索引大了也需要分区,关于索引以后专门写点)而全局索引可修饰为分区索引,但是和local索引有所区别,前者的分区 方式完全按照自定义方式去创建,和表结构完全无关,所以对于分区表的全局索引有以下两幅网上常用的图解:

    3.1、对于分区表的不分区索引(这个有点绕,不过就是表分区,但其索引不分区):

     

    创建语法(直接创建即可):

    CREATE INDEX <index_name> ON <partition_table_name>(<column_name>);

     

     

     

    3.2、对于分区表的分区索引:

     

    创建语法为:

    CREATE INDEX INX_TAB_PARTITION_COL1 ON TABLE_PARTITION(COL1)

      GLOBAL PARTITION BY RANGE(COL1)(

             PARTITION IDX_P1 values less than (1000000),

             PARTITION IDX_P2 values less than (2000000),

             PARTITION IDX_P3 values less than (MAXVALUE)

      );

     

    3.3、LOCAL索引结构:

     

     

    创建语法为:

     CREATE INDEX INX_TAB_PARTITION_COL1 ON TABLE_PARTITION(COL1) LOCAL;

    也可按照分区表的的分区结构给与一一定义,索引的分区将得到重命名。

    分区上的位图索引只能为LOCAL索引,不能为GLOBAL全局索引。

     

    3.4、对比索引方式:

     

      一般使用LOCAL索引较为方便,而且维护代价较低,并且LOCAL索引是在分区的基础上去创建索引,类似于在一个子表内部去创建索引,这样开销主要是区 分分区上,很规范的管理起来,在OLAP系统中应用很广泛;而相对的GLOBAL索引是全局类型的索引,根据实际情况可以调整分区的类别,而并非按照分区 结构一一定义,相对维护代价较高一些,在OLTP环境用得相对较多,这里所谓OLTP和OLAP也是相对的,不是特殊的项目,没有绝对的划分概念,在应用 过程中依据实际情况而定,来提高整体的运行性能。

     

    4、常用操作:

    1、查询当前用户下有哪些是分区表:

    SELECT * FROM USER_PART_TABLES;

     

    2、查询当前用户下有哪些分区索引:

    SELECT * FROM USER_PART_INDEXES;

     

    3、查询当前用户下分区索引的分区信息:

    SELECT * FROM USER_IND_PARTITIONS T

    WHERE T.INDEX_NAME=?

     

    4、查询当前用户下分区表的分区信息:

    SELECT * FROM USER_TAB_PARTITIONS T

    WHERE T.TABLE_NAME=?;

     

    5、查询某分区下的数据量:

    SELECT COUNT(*) FROM TABLE_PARTITION PARTITION(TAB_PARTOTION_01);

     

    6、查询索引、表上在那些列上创建了分区:

    SELECT * FROM USER_PART_KEY_COLUMNS;

     

    7、查询某用户下二级分区的信息(只有创建了二级分区才有数据):

    SELECT * FROM USER_TAB_SUBPARTITIONS;

     

    5、维护操作:

    5.1、删除分区

        ALTER TABLE TABLE_PARTITION DROP PARTITION TAB_PARTOTION_03;

         如果是全局索引,因为全局索引的分区结构和表可以不一致,若不一致的情况下,会导致整个全局索引失效,在删除分区的时候,语句修改为:

         ALTER TABLE TABLE_PARTITION DROP PARTITION TAB_PARTOTION_03 UPDATE GLOBAL INDEXES;

     

    5.2、分区合并(从中间删除掉一个分区,或者两个分区需要合并后减少分区数量)

        合并分区和删除中间的RANGE有点像,但是合并分区是不会删除数据的,对于LIST、HASH分区也是和RANGE分区不一样的,其语法为:

    ALTER TABLE TABLE_PARTITION MERGE PARTITIONS    TAB_PARTOTION_01,TAB_PARTOTION_02 INTO PARTITION MERGED_PARTITION;

     

     

    5.3、分隔分区(一般分区从扩展分区从分隔)

    ALTER TABLE TABLE_PARTITION SPLIT PARTITION TAB_PARTOTION_OTHERE AT(2500000)

    INTO (PARTITION TAB_PARTOTION_05,PARTITION TAB_PARTOTION_OTHERE);

     

    5.4、创建新的分区(分区数据若不能提供范围,则插入时会报错,需要增加分区来扩大范围)

    一般有扩展分区的是都是用分隔的方式,若上述创建表时没有创建TAB_PARTOTION_OTHER分区时,在插入数据较大时(按照上述建立规则,超过1800000就应该创建新的分区来存储),就可以创建新的分区,如:

    为了试验,我们将扩展分区先删除掉再创建新的分区(因为ORACLE要求,分区的数据不允许重叠,即按照分区字段同样的数据不能同时存储在不同的分区中):

    ALTER TABLE TABLE_PARTITION DROP PARTITION TAB_PARTOTION_OTHER;

    ALTER TABLE TABLE_PARTITION ADD PARTITION TAB_PARTOTION_06 VALUES LESS THAN(2500000);

     

    在分区下创建新的子分区大致如下(RANGE分区,若为LIST或HASH分区,将创建方式修改为对应的方式即可):

    ALTER TABLE <table_name> MODIFY PARTITION <partition_name> ADD SUBPARTITION <user_define_subpartition_name> VALUES LESS THAN(....);

     

    5.5、修改分区名称(修改相关的属性信息)

    ALTER TABLE TABLE_PARTITION RENAME PARTITION MERGED_PARTITION TO MERGED_PARTITION02;

     

     

    5.6、交换分区(快速交换数据,其实是交换段名称指针)

      首先创建一个交换表,和原表结构相同,如果有数据,必须符合所交换对应分区的条件:

      CREATE TABLE TABLE_PARTITION_2

      AS SELECT * FROM TABLE_PARTITION WHERE 1=2;

      然后将第一个分区的数据交换出去:

      ALTER TABLE TABLE_PARTITION EXCHANGE PARTITION TAB_PARTOTION_01

      WITH TABLE TABLE_PARTITION_2 INCLUDING INDEXES;

      此时会发现第一个分区的数据和表TABLE_PARTITION_2做了瞬间交换,比TRUNCATE还要快,因为这个过程没有进行数据转存,只是段名称的修改过程,和实际的数据量没有关系。

     

      如果是子分区也可以与外部的表进行交换,只需要将关键字修改为:SUBPARTITION 即可。

     

    5.7、清空分区数据

     

       ALTER TABLE <table_name> TRUNCATE PARTITION <partition_name>;

       ALTER TABLE <table_name> TRUNCATE subpartition <subpartition_name>;

     

    6、磁盘碎片压缩

       对分区表的某分区进行磁盘压缩,当对分区内部数据进行了大量的UPDATE、DELETE操作后,一定时间需要进行磁盘压缩,否则在查询时,若通过FULL SCAN扫描数据,将会把空块也会扫描到,对表进行磁盘压缩需要进行行迁移操作,所以首先需要操作:

    ALTER TABLE <table_name> ENABLE ROW MOVEMENT ;

     

        对分区表的某分区压缩语法为:

    ALTER TABLE <table_name>

    modify partition <partition_name> shrink space;

       对普通表压缩:

    ALTER TABLE <table_name> shrink space;

      对于索引也需要进行压缩,索引也是表:

    ALTER INDEX <index_name> shrink space;

     

     

    7、分区表重新分析以及索引重新分析

      对表进行压缩后,需要对表和索引进行重新分析,对表进行重新分析,一般有两种方式:

      在ORACLE 10G以前,使用:

      BEGIN

         dbms_stats.gather_table_stats(USER,UPPER('<table_name>'));

      END;

     

      ORACLE 10G后,可以使用:

      ANALYZE TABLE <table_name> COMPUTE STATISTICS;

     

      索引重新分析,将上述两种方式分别修改一下,如第一种可以使用:gather_index_stats,而第二种修改为:ANALYZE INDEX即可,不过一般比较常用的是重新编译:

      对于分区表并进行了索引分区的情况,需要对每个分区的索引进行重新编译,这里以LOCAL索引为例子(其每个索引的分区和表分区结构相同,默认分区名称和表分区名称相同):

     ALTER INDEX <index_name> REBUILD PARTITION <partition_name>;

      对于全局索引,根据全局索引锁定义的分区名称修改即可,若没有分区,和普通单表索引重新编译方式相同:

     ALTER INDEX <index_name> REBUILD;

     

    1、关联对象重新编译

      上述对表、索引进行重新编译,尤其对表进行了压缩后会产生行迁移,这个过程可能会导致一些视图、过程对象的失效,此时要将其重新编译一次。

     

     

    2、扩展:HASH分区中,如果创建了新的分区,可以将其进行重新HASH分布:

    ALTER TABLE <table_name> COALESCA PARTITION

     

     

    8、回归总结:何时建分区,分区类别,索引,如何对应SQL

     

    1、创建时机

     

         上述已经说明,2G以上的表,ORACLE推荐创建分区。

         分区的方式根据实际情况而定,才能提高整体性能。

         分区的字段一定要是经常用以提取数据的字段,否则会在提取过程中导致遍历多个分区,这样比没有分区还要慢。

         分区字段要选择合适,数据较为均匀分布到各个分区,不要太多也不要太少,而且根据分区字段可以很快定位到分区范围。

         一般情况下,尽量然业务操作在同一个分区内部完成。

     

     

    2、分区类别

     

        分区主要有RANGE、LIST、HASH;

         RANGE通过值的范围分区,也是最常用的分区,这种分区注意在一种变长数字字符串中,很多人会导致认为是数字类型,而按照数字区分区,这样会分布十分不均匀的现象发生。

         LIST是列举方式进行分区,一般作为二级分区而存在(当然也可以自己分区,ORACLE 11G后在分区上也可以作为主分区而存在),在RANGE基础上,若数据需要继续分区,并且在RANGE基础上数据量较为固定,只是较大,可以按照一定规则进一步分区。

         HASH只指定分区个数,分区细节由ORACLE完成,增加HASH分区可以重新分布数据。

     

         注意:分区字段不能使用函数转换后在分区,如,将某数字字符串字段,先TO_NUMER(COL_NAME)后分区。

     

    3、索引类别

        大致分:GLOBAL索引和LOCAL索引,钱和可以分:GLOBAL不分区索引,和GLOBAL分区索引。

     

         GLOBAL不分区索引一般不太推荐,因为是用一颗大的索引树来映射一个表,这个过程,这样速度不见得比不分区快。

         GLOBAL分区索引,查找数据若通过要通过索引,是先定位了索引内部的分区,然后在这个分区索引中找到ROWID,然后回表提取数据。

         LOCAL索引是和分区的个数逐个对应的,可以说先定位分区表的分区也可以说先定位索引的分区,因为他们是一一对应的,找到对应分区后,分区内部索引数据集合。

     

    4、对应应用

         分区表、索引、分区索引,要利用其性能优势,最基本就是要提取数据时,要通过它首先将数据的范围缩小到一个即使做全盘扫描也不会太慢的情况。

          所以SQL一定要有分区上的这个字段的一个WHERE条件,将数据迅速定位到分区内部,而且尽量定位到一个分区里面(这个和创建分区的规则有关系)。

          建立分区本身不提要性能,要用好才可提高性能,在必要的RAC集群中,若存在多分区提取数据,适当采用并行提取可以提高提取的速度。

          对于索引部分,这里也只提到分区索引的创建方式以及常见索引的维护方式,对于索引原理理解后会更容易认识到提取数据时的技巧。

     

    9、实战

     

    分区表和一般表一样可以建立索引,分区表可以创建局部索引和全局索引。当分区中出现许多事务并且要保证所有分区中的数据记录的唯一性时采用全局索引。

    1 局部索引分区的建立:

    SQL> create index dinya_idx_t on dinya_test(item_id)

      2  local

      3  (

      4     partition idx_1 tablespace dinya_space01,

      5     partition idx_2 tablespace dinya_space02,

      6     partition idx_3 tablespace dinya_space03

      7  );

     

    Index created.

     

    SQL>

     

     

    看查询的执行计划,从下面的执行计划可以看出,系统已经使用了索引:

    SQL> select * from dinya_test partition(part_01) t where t.item_id=12;

     

    Execution Plan

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

       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=187)

       1    0   TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'DINYA_TEST' (Cost=

              2 Card=1 Bytes=187)

       2    1     INDEX (RANGE SCAN) OF 'DINYA_IDX_T' (NON-UNIQUE) (Cost=1

               Card=1)

    Statistics

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

              0  recursive calls

              0  db block gets

              4  consistent gets

              0  physical reads

              0  redo size

            334  bytes sent via SQL*Net to client

            309  bytes received via SQL*Net from client

              2  SQL*Net roundtrips to/from client

              1  sorts (memory)

              0  sorts (disk)

              2  rows processed

     

    SQL>

     

    2 全局索引分区的建立。

    全局索引建立时global 子句允许指定索引的范围值,这个范围值为索引字段的范围值:

    SQL> create index dinya_idx_t on dinya_test(item_id)

      2  global partition by range(item_id)

      3  (

      4     partition idx_1 values less than (1000) tablespace dinya_space01,

      5     partition idx_2 values less than (10000) tablespace dinya_space02,

      6     partition idx_3 values less than (maxvalue) tablespace dinya_space03

      7  );

     

    Index created.

     

    SQL>

     

     

    本例中对表的item_id字段建立索引分区,当然也可以不指定索引分区名直接对整个表建立索引,如:

    SQL> create index dinya_idx_t on dinya_test(item_id);

    Index created.

     

    SQL>

     

     

    同样的,对全局索引根据执行计划可以看出索引已经可以使用:

    SQL> select * from dinya_test t where t.item_id=12;

     

    Execution Plan

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

       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=3 Bytes=561)

       1    0   TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'DINYA_TEST' (Cost

              =2 Card=3 Bytes=561)

     

       2    1     INDEX (RANGE SCAN) OF 'DINYA_IDX_T' (NON-UNIQUE) (Cost=1

               Card=3)

    Statistics

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

              5  recursive calls

              0  db block gets

             10  consistent gets

              0  physical reads

              0  redo size

            420  bytes sent via SQL*Net to client

            309  bytes received via SQL*Net from client

              2  SQL*Net roundtrips to/from client

              3  sorts (memory)

              0  sorts (disk)

              5  rows processed

     

    SQL>

     

     

    其实分区的管理很简单,难点在于分区方式的选择和分区表上面索引的选择。  

    Global Index全局索引和Local Index局部索引,Global partitioned index和global nonpartitioned index,


    本文转自:http://www.blogjava.net/rabbit/archive/2013/01/08/393955.html

    展开全文
  • Oracle支持在空间(tablespace)、数据(table)和分区(Partition)级别的压缩,如果设置为空间级别,那么默认将该空间中的全部的都进行压缩压缩操作可以在数据单条插入、数据修改和数据批量导入时...
  • 1.表压缩与lob字段的压缩没有关系 2.表压缩是支持的.  在建立表与子分区时候每个表与子分区都带上compress选项即可  如果是insert 操作,那么必须是append hint才支持压缩,...
  • oracle分区表发展历史

    2019-12-05 14:54:49
    ◊在Oracle 8版本前,DBAs和Consultants 根据系统需要实现了"Home-Grown"(自产的/土生土长的)分区方法,他们借助UNION-ALL Views或Partition Views(7.3开始提供的分区视图,需要配合初始化参数PARTITION_VIEW_...
  • 表压缩是如何工作的在Orcle9i第2版中,表压缩特性通过删除在数据库表中发现的重复数据值来节省空间。压缩是在数据库的数据块级别上进行的。当确定一个表要被压缩后,数据库便在每一个数据库数据块中保留空间,以便...
  • 12.1 - 19c Oracle分区表的新特性

    千次阅读 2019-08-02 22:44:01
    12.1 Release Asynchronous Global Index Maintenance for DROP and TRUNCATE ...之前drop或truncate分区时,会使全局索引不可用,update indexes和update global indexes虽然可以维护索引的可用性,但是索引的维...
  • 简单ORACLE分区表、分区索引

    万次阅读 多人点赞 2010-03-31 14:03:00
    ORACLE对于分区表方式其实就是将表分段存储,一般普通表格是一个段存储,而分区表会分成多个段,所以查找数据过程都是先定位根据查询条件定位分区范围,即数据在那个分区或那几个内部,然后在分区内部去查找数据,一...
  • Oracle表压缩

    千次阅读 2018-12-27 12:09:57
    表压缩是如何工作的在Orcle9i第2版中,表压缩特性通过删除在数据库表中发现的重复数据值来节省空间。压缩是在数据库的数据块级别上进行的。当确定一个表要被压缩后,数据库便在每一个数据库数据块中保留空间,以便...
  • 深入学习Oracle分区表及分区索引   关于分区表和分区索引(About Partitioned Tables and Indexes)对于10gR2而言,基本上可以分成几类: ? Range(范围)分区 ? Hash(哈希)分区 ? List(列表)分区 ...
  • Oracle 12C下的表压缩

    千次阅读 2017-04-06 17:28:30
    oracle12C支持以下三种表压缩方法 ⑴基本表压缩:(Compress Base)压缩比率高,CPU开心最小,适用于直接路径插入操作。常用于DSS 注:这样类型的压缩,除了直接路径插入会压缩外,普通的插入要是让数据块达到pctfree...
  • Oracle 分区表创建

    2018-11-08 14:23:00
    - 字段范围分区 range `Oracle 8` - 哈希分区 hash `Oracle 8i` - 组合分区 range-hash,range-list ... `Oracle 9i` - 列表分区 list `Oracle 9i` - 间隔分区 interval `Oracle 11g` - 系统分区 System `Oracle...
  • ORACLE分区表、分区索引ORACLE对于分区表方式其实就是将表分段存储,一般普通表格是一个段存储,而分区表会分成多个段,所以查找数据过程都是 先定位根据查询条件定位分区范围,即数据在那个分区或那几个内部,然后...
  • Oracle压缩黑科技(一)--基础表压缩

    千次阅读 2017-12-21 16:34:23
    在关于Oracle压缩的这一系列文章中,我们会研究下传统Oracle数据库系统的各类压缩方式,这意味着该系列文章的目录结构大概是:1. 基础表压缩 2. OLTP表压缩 3. 索引压缩。但是,不讨论Exadat
  • ORACLE 表压缩

    千次阅读 2016-07-08 17:04:13
    Oracle支持在表空间(tablespace)、数据表(table)和分区(Partition)...Oracle支持下面几种表压缩方式: 其中,OLTP压缩可以在数据单条插入、数据修改、数据批量导入的时候进行压缩,其它三种只能在批量导入的时
  • oracle11g表压缩

    千次阅读 2017-10-23 15:55:02
    在数据量变的很大的情况下,可以考虑表压缩,减少磁盘的存储,减少buffer cache的消耗,加快查询的速度,压缩需要在数据加载和dml的时候消耗一些cpu.表压缩对于应用是透明的。你可以在表空间,表或分区级别指定压缩...
  • 3.4 分区表压缩 3 3.5 索引压缩 4 3.6 数据压缩方法 4 4、如何正确压缩数据 5 4.1 压缩数据方法 5 4.2 CTAS数据压缩 5 4.3 insert /*+ append */压缩 6 4.4 alter table …move compress压缩 7 4.5 alter table … ...
  • 关于oracle 压缩表

    2019-05-31 18:16:22
    压缩表本身的语句相当简单,总的来讲分为两种类型,一种普通表的压缩,一种是分区表压缩。相关的语法如下: --建立普通表 create table table1 ( ...... ) compress; --建立分区表 create table t....
  • Oracle表压缩

    2019-06-23 19:39:00
    Oracle支持在空间(tablespace)、数据(table)和分区(Partition)级别的压缩,如果设置为空间级别,那么默认将该空间中的全部的都进行压缩压缩操作可以在数据单条插入、数据修改和数据批量导入时发生...
  • ORACLE分区表、分区索引ORACLE对于分区表方式其实就是将表分段存储,一般普通表格是一个段存储,而分区表会分成多个段,所以查找数据过程都是先定位根据查询条件定位分区范围,即数据在那个分区或那几个内部,然后在...
  • ORACLE分区表、分区索引ORACLE对于分区表方式其实就是将表分段存储,一般普通表格是一个段存储,而分区表会分成多个段,所以查找数据过程都是先定位根据查询条件定位分区范围,即数据在那个分区或那几个内部,然后在...
  • Oracle 8.5 开始支持分区表/索引, 首先提供 范围 分区(range) 1)使Oracle成为了第一个支持物理分区的RDBMS供应商 2)Oracle 8 仅支持表和索引级的分区( Clustered tables/indexes and snapshots are not ...
  • Oracle 表压缩(Table Compression)介绍 1、官方文档说法: As your database grows in size, consider using table ...
  • ORACLE分区表、分区索引ORACLE对于分区表方式其实就是将表分段存储,一般普通表格是一个段存储,而分区表会分成多个段,所以查找数据过程都是先定位根据查询条件定位分区范围,即数据在那个分区或那几个内部,然后...
  • Oracle 表压缩(Table Compression)技术介绍

    万次阅读 2015-08-05 00:37:00
    Oracle 表压缩(Table Compression)介绍1、官方文档说法: As your database grows in size, consider using table compression. Compression saves disk space, reduces memory use in the database buffer cache, ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 17,341
精华内容 6,936
关键字:

oracle分区表压缩