当前位置:Gxlcms > 数据库问题 > Oracle分区表

Oracle分区表

时间:2021-07-01 10:21:17 帮助过:12人阅读

oracle,不支持,要用企业版(收费功能)

---Partitioning    FALSE

 

2 分区机制(10gr2)

Range( 范围 ) 分区

Hash( 哈希 ) 分区

List( 列表 ) 分区

以及组合分区: Range- Hash, Range-L ist 。

 2.1 range分区

A When 使用 Range 分区

Range 分区呢是应用范围比较广的表分区方式,它是以列的值的范围来做为分区的划分条件,将记录存放

到列值所在的 range 分区中,比如按照时间划分, 2008 年 1 季度的数据放到 a 分区, 08 年 2 季度的数据放到 b分区,因此在创建的时候呢,需要你指定基于的列,以及分区的范围值,如果某些记录暂无法预测范围,可以创建 maxvalue 分区,所有不在指定范围内的记录都会被存储到 maxvalue 所在分区中,并且支持指定多列做为依赖列。

   Partition by range(id,id2,。。)

            partition part1 values less than ()--tablespace tablelspacename

           partition part2 values less than ()--可以指定表空间属性

partition part3 values less than (maxvalue))

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

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

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

  CREATE TABLE range_example

   ( range_key_column date NOT NULL, 

     data             varchar2(20) 

    ) 

    PARTITION BY RANGE (range_key_column) 

    ( PARTITION part_1 VALUES LESS THAN 

           (to_date(‘01/01/2010‘,‘dd/mm/yyyy‘)), 

      PARTITION part_2 VALUES LESS THAN 

           (to_date(‘01/01/2011‘,‘dd/mm/yyyy‘)) )

 

select table_name,partitioning_type,partition_count from user_part_tables  where table_name=‘RANGE_EXAMPLE‘;

   RANGE_EXAMPLE RANGE 2

   select table_name,  partition_name,high_value,tablespace_name  from user_tab_partitions

   where table_name=‘RANGE_EXAMPLE‘ order by partition_name

RANGE_EXAMPLE PART_1 (WIDEMEMO) USERS

RANGE_EXAMPLE PART_2 (WIDEMEMO) USERS

 插入一些数据,如果插入的内容不能严格小于定义的值,则会报错,

 定义一个  PARTITION part_3 VALUES LESS THAN 

        (MAXVALUE),则大于定义的值都会存储在这个分区

  insert into range_example

   values(to_date(‘12/01/2010‘,‘dd/mm/yyyy‘),‘data1‘)

 

select * from range_example partition (part_1)

显示在各自分区的内容

select * from range_example partition (part_2)

b 创建global的range索引

   create index idx_range_example on range_example(range_key_column)

        global PARTITION BY RANGE (range_key_column) 

         (PARTITION part_idx_1 VALUES LESS THAN 

           (to_date(‘01/01/2010‘,‘dd/mm/yyyy‘)), 

      PARTITION part_idx_2 VALUES LESS THAN 

           (to_date(‘01/01/2011‘,‘dd/mm/yyyy‘)) ,

       PARTITION part_idx_3 VALUES LESS THAN 

        (MAXVALUE))

  这个索引中使用了(MAXVALUE)) ,全局索引有个需求(最后一个分区)必须有一个值为maxvalue的分区上界,

  Global索引与表的分区形式没有关系。

  查看索引

    select index_name,partitioning_type,partition_count,locality from user_part_indexes

         where index_name=‘IDX_RANGE_EXAMPLE‘

   IDX_RANGE_EXAMPLE RANGE 3 GLOBAL

     select partition_name,high_value,tablespace_name from user_ind_partitions 

         where index_name=‘IDX_RANGE_EXAMPLE‘

  PART_IDX_1 (WIDEMEMO) USERS

PART_IDX_2 (WIDEMEMO) USERS

PART_IDX_3 (WIDEMEMO) USERS

C 创建local的索引

   create index idx_local_example on range_example(range_key_column)local

 

 select index_name,partitioning_type,partition_count,locality from user_part_indexes

         where index_name=‘IDX_LOCAL_EXAMPLE‘

IDX_LOCAL_EXAMPLE RANGE 3 LOCAL

Local的分区属性完全继承了表的分区属性

 Example

   CREATE TABLE partitioned_table

   ( a int,  b int,  data char(20) 

    ) 

    PARTITION BY RANGE (a) 

    (  PARTITION part_1 VALUES LESS THAN(2),

    PARTITION part_2 VALUES LESS THAN(3))

 创建2个local索引

  create index local_prefixed on partitioned_table (a,b) local; 

    带有b的非前缀

    create index local_nonprefixed on partitioned_table (b) local;

  收集统计信息

      begin 

      dbms_stats.gather_table_stats 

       ( user, 

        ‘PARTITIONED_TABLE‘, 

         cascade=>TRUE ); 

   end;

    select * from partitioned_table where a = 1 and b = 1;

 经常有这种查询,则创建a,b的local索引

2.2 hash分区

A When  使用 Hash 分区

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

Hash分区,partition  by hash(column)(partition :直接指定分区名,分区所在表空间信息

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

 创建hash分区

  CREATE TABLE hash_example

    ( hash_key_column   date, 

      data              varchar2(20) 

   ) 

    PARTITION BY HASH (hash_key_column) 

    ( partition part_1 , 

      partition part_2 )

 第二种方式 指定分区数量

  CREATE TABLE hash_example2

    ( hash_key_column   date, 

      data              varchar2(20) 

   ) 

    PARTITION BY HASH (hash_key_column) 

    partitions 2 store in (users)

  分区数跟使用的表空间数没有关系

B  创建global的hash索引

   create index idx_hash_exapple on hash_example2(hash_key_column)

    global partition by hash(hash_key_column)

    partitions 3 store in (users)

   

c local索引

  create index idx_local_hash on hash_example2(hash_key_column)local

 

  select partition_name,high_value,tablespace_name from user_ind_partitions

         where index_name=‘IDX_LOCAL_HASH‘

 SYS_P21 (WideMemo) USERS

SYS_P22 (WideMemo) USERS

 

小结:

对于global索引而言,10g中只支持range分区和hash分区,

  对于local索引,其分区形式完全依赖于表的分区形式,

  在创建索引时不显示指定global或local,默认是global

  在创建global分区时不显示指定分区,则默认不分区。

3 list分区

A When  使用 List 分区

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

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

  List分区:partition by list(id)(partition part1 values(‘xx2’,’xx3’),partition part2 values(‘xx4’,’xx1’),partition part_default values(default))

   create table list_example

    ( state_cd   varchar2(2), 

      data       varchar2(20)  ) 

    partition by list(state_cd) 

    ( partition part_1 values ( ‘ME‘, ‘NH‘, ‘VT‘, ‘MA‘ ), 

      partition part_2 values ( ‘CT‘, ‘RI‘, ‘NY‘ ) ,

      partition part_default values(default)

    )

 

4 组合分区

A Range_hash

 CREATE TABLE composite_example

   ( range_key_column   date, 

      hash_key_column    int, 

      data               varchar2(20)  ) 

   PARTITION BY RANGE (range_key_column) 

   subpartition by hash(hash_key_column) subpartitions 2 

    ( 

    PARTITION part_1

        VALUES LESS THAN(to_date(‘01/01/2008‘,‘dd/mm/yyyy‘)) 

        (subpartition part_1_sub_1, 

         subpartition part_1_sub_2   ), 

   PARTITION part_2

       VALUES LESS THAN(to_date(‘01/01/2011‘,‘dd/mm/yyyy‘)) 

       (subpartition part_2_sub_1, 

        subpartition part_2_sub_2   ) )

  oracle会先根据range的区间确定将数据属于哪个空间,然后再应用hash函数来确定数据最后要放的物理位置中。

  以下 为每一个range分区创建4个hash子分区

   create table t_partition_rh (id number,name varchar2(50))

 partition by range(id) subpartition by hash(name)

 subpartitions 4 store in (tbspart01, tbspart02, tbspart03,tbspart04)(

 partition t_r_p1 values less than (10) tablespace tbspart01,

 partition t_r_p2 values less than (20) tablespace tbspart02,

 partition t_r_p3 values less than (30) tablespace tbspart03,

 partition t_r_pd values less than (maxvalue) tablespace tbspart04);

 也可以只为某一个range创建多个hash分区

   create table t_partition_rh (id number,name varchar2(50))

 partition by range(id) subpartition by hash(name)(

 partition t_r_p1 values less than (10) tablespace tbspart01,

 partition t_r_p2 values less than (20) tablespace tbspart02,

 partition t_r_p3 values less than (30) tablespace tbspart03(

subpartitions 3 store in (tbspart01, tbspart02, tbspart03),

 partition t_r_pd values less than (maxvalue) tablespace tbspart04);

 

A range_list分区

 

CREATE TABLE composite_range_list_example

   ( range_key_column   date, 

     code_key_column    int, 

     data               varchar2(20)   ) 

   PARTITION BY RANGE (range_key_column) 

   subpartition by list(code_key_column) 

   ( 

  PARTITION part_1

     VALUES LESS THAN(to_date(‘01/01/2008‘,‘dd/mm/yyyy‘)) 

  (subpartition part_1_sub_1 values( 1, 3, 5, 7 ), 

        subpartition part_1_sub_2 values( 2, 4, 6, 8 ) ), 

   PARTITION part_2

       VALUES LESS THAN(to_date(‘01/01/2011‘,‘dd/mm/yyyy‘)) 

       (subpartition part_2_sub_1 values ( 1, 3 ), 

        subpartition part_2_sub_2 values ( 5, 7 ), 

        subpartition part_2_sub_3 values ( 2, 4, 6, 8 )))

 

5 间隔分区(interval partitioning----11gr1

 以一个分区表为“起点”,在定义的过程中增加一个规则(间隔),使db知道将来如何增加分区

 不需要预先为db创建分区,而是在插入数据时自动创建分区,间隔(必须是number或interval类型的值),

ops$tkyte%ORA11GR2> create table audit_trail

  2  ( ts    timestamp,

  3    data  varchar2(30)

  4  )

  5  partition by range(ts)

  6  interval (numtoyminterval(1,‘month‘)) ---存储个月的分区

  7  store in (users, example )

  8  (partition p0 values less than

  9   (to_date(‘01-01-1900‘,‘dd-mm-yyyy‘))

 10  )

ops$tkyte%ORA11GR2> select a.partition_name, a.tablespace_name, a.high_value,

  2         decode( a.interval, ‘YES‘, b.interval ) interval

  3    from user_tab_partitions a, user_part_tables b

  4   where a.table_name = ‘AUDIT_TRAIL‘

  5     and a.table_name = b.table_name

  6   order by a.partition_position;

 

PARTITION_ TABLESPACE HIGH_VALUE                      INTERVAL

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

P0         USERS      TIMESTAMP‘ 1900-01-01 00:00:00‘

ops$tkyte%ORA11GR2> insert into audit_trail (ts,data) values

2 ( to_timestamp(‘27-feb-2010‘,‘dd-mon-yyyy‘), ‘xx‘ );

3 ----新增加一条数据

PARTITION_ TABLESPACE HIGH_VALUE                      INTERVAL

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

P0         USERS      TIMESTAMP‘ 1900-01-01 00:00:00‘

SYS_P659   USERS      TIMESTAMP‘ 2010-03-01 00:00:00‘ NUMTOYMINTERVAL(1,‘MONTH‘)

数据回滚,分区任然创立,ddl开启一个新的事务。

 

6引用分区reference partitioning 11g

以某种方式对子表进行分区,使得各个子分区分别跟父表分区存在一一对应的关系

11g之前需要使用逆规范,父表的数据复制到子表】

ops$tkyte%ORA11GR2> create table orders

  2  (

  3    order#      number primary key,

  4    order_date  date,

  5    data       varchar2(30)

  6  )

  7  enable row movement

  8  PARTITION BY RANGE (order_date)

  9  (

 10    PARTITION part_2009 VALUES LESS THAN (to_date(‘01-01-2010‘,‘dd-mm-yyyy‘)) ,

 11    PARTITION part_2010 VALUES LESS THAN (to_date(‘01-01-2011‘,‘dd-mm-yyyy‘))

 12  )

 13  /

--ORA-00439: feature not enabled: Partitioning

select * from v$option;--标准版的oracle,不支持,要用企业版(收费功能)

---Partitioning    FALSE

ops$tkyte%ORA11GR2> insert into orders values

  2  ( 1, to_date( ‘01-jun-2009‘, ‘dd-mon-yyyy‘ ), ‘xxx‘ );

1 row created.

 

ops$tkyte%ORA11GR2> insert into orders values

  2  ( 2, to_date( ‘01-jun-2010‘, ‘dd-mon-yyyy‘ ), ‘xxx‘ );

1 row created.

ops$tkyte%ORA11GR2> create table order_line_items

  2  (

  3    order#      number,

  4    line#       number,

  5    order_date  date, -- manually copied from ORDERS!

  6    data       varchar2(30),

  7    constraint c1_pk primary key(order#,line#),

  8    constraint c1_fk_p foreign key(order#) references orders

  9  )

 10  enable row movement

 11  PARTITION BY RANGE (order_date)

 12  (

 13    PARTITION part_2009 VALUES LESS THAN (to_date(‘01-01-2010‘,‘dd-mm-yyyy‘)) ,

 14    PARTITION part_2010 VALUES LESS THAN (to_date(‘01-01-2011‘,‘dd-mm-yyyy‘))

 15  )

 16  /

Table created.

ops$tkyte%ORA11GR2> insert into order_line_items values

  2  ( 1, 1, to_date( ‘01-jun-2009‘, ‘dd-mon-yyyy‘ ), ‘yyy‘ );

1 row created.

 

ops$tkyte%ORA11GR2> insert into order_line_items values

  2  ( 2, 1, to_date( ‘01-jun-2010‘, ‘dd-mon-yyyy‘ ), ‘yyy‘ );

1 row created.

11g

ops$tkyte%ORA11GR2> truncate table orders;

Table truncated.

 

ops$tkyte%ORA11GR2> insert into orders values

  2  ( 1, to_date( ‘01-jun-2009‘, ‘dd-mon-yyyy‘ ), ‘xxx‘ );

1 row created.

 

ops$tkyte%ORA11GR2> insert into orders values

  2  ( 2, to_date( ‘01-jun-2010‘, ‘dd-mon-yyyy‘ ), ‘xxx‘ );

1 row created

and create a new child table

 

ops$tkyte%ORA11GR2> create table order_line_items

  2  (

  3    order#      number,

  4    line#       number,

  5    data       varchar2(30),

  6    constraint c1_pk primary key(order#,line#),

  7    constraint c1_fk_p foreign key(order#) references orders

  8  )

  9  enable row movement

 10  partition by reference(c1_fk_p)

 11  /

ops$tkyte%ORA11GR2> insert into order_line_items values

  2  ( 1, 1, ‘yyy‘ );

1 row created.

 

ops$tkyte%ORA11GR2> insert into order_line_items values

  2  ( 2, 1, ‘yyy‘ );

11gr1需要将外键定义为not null

--两个表的结构相同

ops$tkyte%ORA11GR2> select table_name, partition_name

  2    from user_tab_partitions

  3   where table_name in ( ‘ORDERS‘, ‘ORDER_LINE_ITEMS‘ )

  4   order by table_name, partition_name

  5  /

 

TABLE_NAME                     PARTITION_NA

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

ORDERS                         PART_2009

ORDERS                         PART_2010

ORDER_LINE_ITEMS               PART_2009

ORDER_LINE_ITEMS               PART_2010

3 管理分区

   增加分区,收缩分区,删除分区,交换分区,合并分区,修改默认属性,修改分区当前属性,list分区增加值,list分区删除值,移动分区,重命名分区,拆分分区,截断分区,

另外 local 索引前头我们多次提到了,其维护会在 oracle 操作表分区的时候自动进行,需要注意的是 global索引,当 global 索引所在表执行 alter table 涉及下列操作时,会导至该索引失效:

? ADD PARTITION | SUBPARTITION

? COALESCE PARTITION | SUBPARTITION

? DROP PARTITION | SUBPARTITION

? EXCHANGE PARTITION | SUBPARTITION

? MERGE PARTITION | SUBPARTITION

? MOVE PARTITION | SUBPARTITION

? SPLIT PARTITION | SUBPARTITION

? TRUNCATE PARTITION | SUBPARTITION

因此,建议用户在执行上述操作 sql 语句后附加 update indexes 子句 oracle 即会自动维护全局索引,当然 需要注意这中间有一个平衡,你要平衡操作 ddl 的时间和重建索引哪个时间更少,以决定是否需要附加 update indxes 子句。

3.1 增加表分区

  增加表分区使用于所有的形式,需要注意在list,range分区中,所要增加的分区必须大于当前的最大分区,如果当前分区中存在default,maxvalue时,必须删除default,maxvalue分区,不然add partition会报错,

   alter table range_example add  PARTITION part_3 VALUES LESS THAN (MAXVALUE)

 

注意:

1 、 对于 hash 分区,当你执行 add partition 操作的时候, oracle 会自动选择一个分区,并重新分配部分记录到新建的分区,这也意味着有可能带来一些 IO 操作。

2 、 执行 alter table 时未指定 update indexes 子句:

如果是 range/list 分区,其 local 索引和 global 索引不会受影响 ;

如果是 hash 分区,新加分区及有数据移动的分区的 local 索引和 glocal 索引会被置为 unuseable ,需要重新编译。

3 、 复合分区完全适用上述所述规则。

3.2 收缩分区表

 Coalesce partition,仅使用于hash分区或者有hash子分区,执行之后会自动收缩当前的表分区,

  alter table tbname coalesce partitions

  执行一次,原来5,后4,后3,至少有一个分区。收缩的只是分区,不是数据。

 

3.3 删除分区

   删除分区:alter table tablename drop partition(partition_name)

   删除子分区:alter table tablename drop subpartition (partition_name)

   hash分区hash子分区外,都支持这项操作

      alter table range_example drop partition part_2

  删除分区会把分区内的数据一同删除

 

3.4 交换表分区(迁移)

  就是迁移数据,从分区表迁移到非分区表。

      alter table tbname1 exchange partition/subpartition ptname with table tbname2;

 交换的2个表结构必须一致,除非加with validation字句

 非分区表交换到分区表,非分区表的数据必须符合分区表,除非附加 with validation字句

 Global索引涉及到的迁移都会失效

3.5合并表分区

   合并两个分区为1个,适用除hash之外的分区表。

  alter table tbname merge partitions/subpartitions pt1,pt2 into partition/subpartition pt3;

 合并的2个分区的范围必须连续。合并后数据不会消失,合并时可以指定新的熟悉

3.6 修改list分区的值——add values

   Alter table tablename modify partition/subpartition partition_name  add values(v1,v2.。)

      alter table LIST_EXAMPLE MODIFY partition PART_1 add values(‘YH‘)

    alter table  LIST_EXAMPLE modify (STATE_CD varchar2(4))—error

   分区后的字段类型不能修改??、

唯一的限制就是新加的值不存在任何分区中,并且当前表也不能存在任何新加入值的记录,特别是当创建了default分区后,进行其他分区新加值时,先检查其default分区是否有相同的值在分区中。

   新增的值不会影响原来的记录,所以不会影响索引

3.7 修改list分区的值——drop values

   Alter table tablename modify partition/subpartition partition_name  drop values(v1,v2.。)

alter table LIST_EXAMPLE MODIFY partition PART_1 drop values(‘YH‘)

   删除分区值时,必须确认表中没有满足删除值的记录。要删除的分区必须在分区定义中。

3.8 拆分分区表 split partition

   发现某个分区过大,可以进行拆分,

     For range partition : alter table tbname split partition ptname at (value) into (partition newpt1 tbs_clause,partition newpt2 tbs_clause);

For list partition : alter table tbname split partition ptname values (v1,v2...vn) into (partition newpt1 tbs_clause,partition newpt2 tbs_clause);

  alter table  RANGE_EXAMPLE split partition PART_1  at (TO_DATE(‘ 2009-01-01 00:00:00‘, ‘SYYYY-MM-DD HH24:MI:SS‘)) into

  (partition PART_1,partition PART_2)

 

alter table  RANGE_EXAMPLE split partition PART_2  at (TO_DATE(‘ 2009-09-01 00:00:00‘, ‘SYYYY-MM-DD HH24:MI:SS‘)) into

  (partition PART_2,partition PART_22)

 可以在中间范围进行拆分,拆分后的数据自动放到新的分区中。

Split partition不能用于hash分区或有hash子分区

在执行split partition时没有指定update indexes,都会造成local跟global索引的失效,执行完之后,建议检查一下数据字典,看索引的状态。

3.9 截断表分区truncate partition

  Truncate partition就像truncate table一样,用来删除数据,如果该表有外键,ddl的truncate,要么使用delete或者先disable外键,

 Alter table tablename truncate partition/subpartition

3.10 移动表分区

Move partition 可以修改分区所在的表空间。与move table操作类似,可以降低行迁移等

 Alter table tablename move partition/subpartition ptname

 alter table RANGE_EXAMPLE move partition PART_2 tablespace user1

move partition 操作时会锁定表,索引失效问题

3.11 重命名表分区

   就是改名,改表名

   Alter table tablename rename partition to new_partition

  alter table RANGE_EXAMPLE rename partition PART_22 to PART_23

3.12 修改表分区的默认属性

  修改后对以前的属性没影响,只是后面添加的分区不显示指定分区属性,就会用新的默认属性

Alter table tablename modify default attributes tablespace new

修改表属性list,range,hash

 Alter table tablename modify defult attributes for partition  pname tablespace new

 

3.13 自动扩展分区

11g以前,

该程序可以做为一个Oracle的JOB执行在每月的28日前执行(考虑2月28天的原因),自动为该用户下的分区表增加分区.[@more@]

create or replace procedure guan_add_partition
/*
/*为一个用户下所有分区表自动增加分区.分区的列为date类型,分区名类似:p200706.
/*create by David
*/
as
v_table_name varchar2(50);
v_partition_name varchar2(50);
v_month char(6);
v_add_month_1 char(6);
v_sql_string varchar2(2000);
v_add_month varchar2(20);
cursor cur_part is select distinct u.table_name,max(p.partition_name) max_part_name from user_tables u,user_tab_partitions p
where u.table_name=p.table_name and u.partitioned = ‘YES‘
group by u.table_name;
Begin
select to_char(sysdate,‘yyyymm‘) into v_month from dual;
select to_char(add_months(sysdate,1),‘yyyymm‘) into v_add_month_1 from dual;
select to_char(add_months(trunc(sysdate,‘mm‘),2),‘yyyy-mm-dd‘) into v_add_month from dual;
open cur_part;
loop
fetch cur_part into v_table_name,v_partition_name;
exit when cur_part%notfound;
if to_number(substr(v_partition_name,2)) <=to_number(substr(v_month,1)) then

v_sql_string :=‘alter table ‘||v_table_name||‘ add partition p‘||v_add_month_1||
‘ VALUES LESS THAN ( to_date(‘‘‘||v_add_month||‘‘‘,‘‘yyyy-mm-dd‘‘) ) tablespace users‘;
execute immediate v_sql_string;
else
null;
end if;
end loop; 
close cur_part;
end;

11g之后

create table t_range (id number not null PRIMARY KEY, test_date date)

partition by range (test_date) interval (numtoyMinterval (1,‘MONTH‘))

(

partition p_2015_07_01 values less than (to_date(‘2015-07-01‘, ‘yyyy-mm-dd‘))

)

 

select partition_name from user_tab_partitions where table_name=‘T_RANGE‘;

 

insert /*+append */ into t_range select rownum+10,

to_date(to_char(sysdate , ‘J‘) +

trunc(dbms_random.value(0, 80)),

‘J‘)

from dual

connect by rownum <= 10

根据年: INTERVAL(NUMTOYMINTERVAL(1,‘YEAR‘))

根据月: INTERVAL(NUMTOYMINTERVAL(1,‘MONTH‘))

根据天: INTERVAL(NUMTODSINTERVAL(1,‘DAY‘))

根据时分秒: NUMTODSINTERVAL( n, { ‘DAY‘|‘HOUR‘|‘MINUTE‘|‘SECOND‘})

 

4 分区索引的管理

 4.1 增加索引分区

Alter index idxname add partition ptname tablespacename

Add partition只能用于hash的global索引,无法新增local索引。因为local索引由表来维护

alter index IDX_LOCAL_HASH add partition idx_local_hash3 tablespace users

 

 4.2 删除索引分区

 Drop index partition只能操作global的range分区

Alter index indname drop partition ptname

Global索引必须有一个maxvalue分区,该分区无法删除

 

alter index IDX_LOCAL_EXAMPLE drop partition PART_22—local就无法删除

 

 4.3 重新编译分区索引

  Global索引只支持range,local索引无限制

  alter index IDX_LOCAL_EXAMPLE rebuild partition PART_2

 

  select * from user_ind_partitions where index_name=‘IDX_LOCAL_EXAMPLE‘ 状态为unusable

 

 4.4 重命名分区索引

  Alter index idxname rename partition p_name1 to p_name2

Global索引只支持range,local索引无限制

 

4.5 分拆索引分区

只能操作global的range

Alter index idxname split partition ptname at(value) into(partition pt1 tbsclause,partition pt2 tbsclause)

 

5 行移动

  修改分区的内容会导致使用不同的分区

  修改会导致行跨分区移动

 Example

   Range分区

 insert into range_example 

    ( range_key_column, data ) 

   values 

    ( to_date( ‘15-dec-2009 00:00:00‘, 

             ‘dd-mon-yyyy hh24:mi:ss‘ ), 

    ‘application data‘ );

 

   update RANGE_EXAMPLE set range_key_column=range_key_column+2000 

    where range_key_column=to_date( ‘15-dec-2009 00:00:00‘,  ‘dd-mon-yyyy hh24:mi:ss‘ )

update后的值变成了另外分区的值

这时如果不知道行移动,就会报错

alter table RANGE_EXAMPLE enable row movement

这样指定过后,就会更新到新的分区,同样此时的rowid会发生变化

Oracle分区表

标签:开启   RoCE   varchar2   显示   dual   value   3.2   time   需求   

人气教程排行