Oracle分区表

DBA小记 2020-10-27

3038

一、分区的引入

分区（partitioning）最早在Oracle 8.0中引入，这个过程是将一个表或索引物理地分解为多个更小、更可管理的部分。

对索引或表进行分区时可能发生3种情况：使用这些分区表的应用可能运行得更慢；可能运行得更快；有可能没有任何变化。

Partition（分区）一直是 Oracle 数据库引以为傲的一项技术，正是分区的存在让 Oracle 高效的处理海量数据成为可能，在 Oracle 11g 中，分区技术在易用性和可扩展性上再次得到了增强。

二、分区的作用

2.1调高可用性

可用性的提高源自于每个分区的独立性。

对象中一个分区的可用性（或不可用）并不意味着对象本身是不可用的。

优化器知道有这种分区机制，会相应地从查询计划中去除未引用的分区。

在一个大对象中如果一个分区不可用，你的查询可以消除这个分区而不予考虑，这样Oracle就能成功地处理这个查询。

如果查询必须涉及这个分区，那么查询报错
如果查询不涉及这个分区，那么可以正常查询别的分区

SQL> create tablespace p1 datafile '/app/oracle/oradata/pxdb/p1.dbf' size 100m autoextend on;
SQL> create tablespace p2 datafile '/app/oracle/oradata/pxdb/p2.dbf' size 100m autoextend on;
conn scott/tiger
create table emp_part
(empno int,
ename varchar2(20)
)
partition by hash(empno)
(partition part_1 tablespace p1,
partition part_2 tablespace p2);
SQL> insert into emp_part select empno,ename from emp;
SQL> commit;
SQL> select * from emp_part partition(part_1);
     EMPNO ENAME
---------- --------------------
      7369 SMITH
      7499 ALLEN
      7934 MILLER
      …… …… ……
8 rows selected.
select * from emp_part partition(part_2);
 EMPNO ENAME
---------- --------------------
      7521 WARD
      7566 JONES
      7788 SCOTT
      7844 TURNER
      7900 JAMES
      7902 FORD

数据的“摆放”有些随机。通过使用散列分区，我们让Oracle随机地（很可能均匀地）将数据分布到多个分区上。我们无法控制数据要分布到哪个分区上；Oracle会根据生成的散列键值来确定。

模拟一个分区故障。

SQL> alter tablespace p1 offline;  --dba权限
SQL> conn scott/tiger;
SQL> select * from emp_part;
select * from emp_part
              *
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/app/oracle/oradata/pxdb/p1.dbf'
SQL> select * from emp_part where empno='7844';
     EMPNO ENAME
---------- --------------------
      7844 TURNER
SQL> select * from emp_part where empno='7876';
select * from emp_part where empno='7876'
              *
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/app/oracle/oradata/pxdb/p1.dbf'

结论：对于分区表来说，一个分区不可用并不影响其他分区的正常使用。

2.2减少管理负担

例1：

假设数据库中有一个10GB的索引。如果需要重建这个索引，而该索引未分区，你就必须将整个10GB的索引作为一个工作单元来重建。尽管可以在线地重建索引，但是要完全重建完整的10GB索引，还是需要占用大量的资源吧，并对全表产生锁表操作。

如果将索引本身划分为10个1GB的分区，各个索引的重建也更快（可能是原来的10倍）。

你可能只需要重建全部聚集索引的10%，例如，只是“最新”的数据（活动数据）需要重组，而所有“较旧”的数据（相当静态）不受影响。

例2：

如果发现表中50%的行都是“移植”行，可能想进行修正。

建立一个分区表将有利于这个操作。

为了“修正”移植行，你往往必须重建对象，在这种情况下，就是要重建一个表。如果有一个100GB的表，就需要在一个非常大的“块”（chunk）上连续地使用ALTER TABLE MOVE来执行这个操作。

另一方面，如果你有25个分区，每个分区的大小为4GB，就可以一个接一个地重建各个分区。

对于一个未分区对象所能做的工作，分区对象中的单个分区几乎都能做到。

你甚至可能发现，移植行都集中在一个很小的分区子集中，因此，可以只重建一两个分区，而不是重建整个表。

2.3改善语句性能

分区最后一个总的（潜在）好处体现在改进语句（SELECT、INSERT、UPDATE、DELETE、MERGE）的性能方面。

2.3.1并行DML

修改数据库中数据的语句有可能会执行并行DML（parallel DML，PDML）。

采用PDML时，Oracle使用多个线程或进程来执行INSERT、UPDATE或DELETE，而不是执行一个串行进程。在一个有充足I/O带宽的多CPU主机上，对于大规模的DML操作，速度的提升可能相当显著。

在Oracle9i以前的版本中，PDML要求必须分区。如果你的表没有分区，在先前的版本中就不能并行地执行这些操作。

如果表确实已经分区，Oracle会根据对象所有的物理分区数为对象指定一个最大并行度。

从很大程度上讲，在Oracle9i及以后版本中这个限制已经放松，只有一个突出的例外；如果希望在一个表上执行PDML，而且这个表的一个LOB列上有一个位图索引，要并行执行操作就必须对这个表分区；另外并行度就限制为分区数。

不过，总的说来，使用PDML并不一定要求进行分区。

2.3.2查询性能

在只读查询（SELECT语句）的性能方面，分区对两类特殊操作起作用：

分区消除（partition elimination）：处理查询时不考虑某些数据分区。我们已经看到了一个分区消除的例子。
并行操作（parallel operation）：并行全表扫描和并行索引区间扫描就是这种操作的例子。

2.3.3 OLTP系统

事实上在一个OLTP系统中，查询已经有以下特点：即索引访问相当快，因此，分区不会让索引访问的速度有太大的提高（甚至根本没有任何提高）。

这并不是说要绝对避免在OLTP系统中使用分区；而只是说不要指望通过分区来提供大幅的性能提升。

尽管有效情况下分区能够改善查询的性能，但是这些情况在大多数OLTP应用中并不成立。

不过在OLTP系统中，你还是可以得到另外两个可能的好处：减轻管理负担以及有更高的可用性。

有分区的OLTP系统确实也有可能得到效率提示。例如，可以用分区来减少竞争，从而提高并发度。

至于并行操作，你可能不希望在一个OLTP系统中执行并行查询。你会慎用并行操作，而是交由DBA来完成重建、创建索引、收集统计信息等工作。

2.3.4数据仓库（OLAP）系统

处理许多查询时都要求执行一个全表扫描，但是最后却发现，一方面必须扫描数百万条记录，但另一方面其中大多数记录并不适用于我们的查询。如果使用一种明智的分区机制，就可以实现消除分区，这样在查询某个给定的数据时，就可以只对这个数据进行全面扫描。

这在所有可能的解决方案中是最佳的选择。

在一个数据仓库/决策支持环境中，会频繁地使用并行查询。因此，诸如并行索引区间扫描或并行快速全面索引扫描等操作对我们很有好处。

三、表分区机制

目前Oracle中有4种对表分区的方法：

（1）区间分区：通过数据范围进行分区。例如，时间戳在Jan-2005内的所有记录都存储在分区1中，时间戳在Feb-2005内的所有记录都存储在分区2中，依此类推。这可能是Oracle中最常用的分区机制。

（2）散列分区：这是指在一个列（或多个列）上应用一个散列函数，行会按这个散列值放在某个分区中。

（3）列表分区：指定一个离散值集，来确定应当存储在一起的数据。例如，可以指定STATUS列值在（’A’,’M’,’Z’）中的行放在分区1中，STATUS值在（‘D’,’P’,’Q’）中的行放在分区2中，依此类推。

（4）组合分区：这是区间分区和散列分区的一种组合，或者是区间分区与列表分区的组合。通过组合分区，你可以先对某些数据应用区间分区，再在区间中根据散列或列表来选择最后的分区。

3.1区间（RANGE）分区

SQL > CREATE TABLE range_example
( range_key_column date ,
data varchar2(20)
)
PARTITION BY RANGE (range_key_column)
( PARTITION part_1 VALUES LESS THAN
(to_date('01/01/2005','dd/mm/yyyy')),
PARTITION part_2 VALUES LESS THAN
(to_date('01/01/2006','dd/mm/yyyy')),
PARTITION part_3 VALUES LESS THAN
(MAXVALUE));

我们在范围分区的时候，要注意范围分区的值的情况。

向这个表插入一个行时，这一行肯定会放入三个分区中的某一个分区中，而不会再拒绝任何行，因为分区PART_3可以接受不能放在PART_1或PART_2中的任何RANG_KEY_COLUMN值（即使RANGE_KEY_COLUMN值为null，也会插入到这个新分区中）。

3.2散列（HASH）分区

对一个表执行散列分区（hash partitioning）时，Oracle会对分区键应用一个散列函数，以此确定数据应当放在N个分区中的哪一个分区中。Oracle建议N是2的一个幂（2、4、8、16等），从而得到最佳的总体分布。

散列分区设计为能使数据很好地分布在多个不同设备（磁盘）上，为表选择的散列键应当是惟一的一个列或一组列，或者至少有足够多的相异值，以便行能在多个分区上很好地（均匀地）分布。如果你选择一个只有4个相异值的列，并使用两个分区，那么最后可能把所有行都散列到同一个分区上，这就有悖于分区的最初目标！

SQL> CREATE TABLE hash_example
( hash_key_column date,
data varchar2(20)
)
PARTITION BY HASH (hash_key_column)
( partition part_1 tablespace p1,
partition part_2 tablespace p2
);

注意：

（1）如果使用散列分区，你将无从控制一行最终会放在哪个分区中。Oracle会应用散列函数，并根据散列的结果来确定行会放在哪里。

（2）如果改变散列分区的个数，数据会在所有分区中重新分布（向一个散列分区表增加或删除一个分区时，将导致所有数据都重写，因为现在每一行可能属于一个不同的分区）。

（3）分区数应该是2的幂

如果分区数是2的幂，那么分区将会均匀分布，如果不是2的幂，那么分区将会不均匀分布。我们显然希望得到均匀分布的分区。

3.3列表（LIST）分区

列表分区（list partitioning）是Oracle9i Release 1的一个新特性。它提供了这样一种功能，可以根据离散的值列表来指定一行位于哪个分区。

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

（1）如果我们想插入列表分区中未指定的一个值，Oracle会向客户应用返回一个合适的错误。

SQL > insert into list_example values ( 'VA', 'data' );
insert into list_example values ( 'VA', 'data' )
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition      
SQL > alter table list_example
add partition
part_3 values ( DEFAULT );
SQL > insert into list_example values ( 'VA', 'data' );
1 row created.

（2）关于DEFAULT的使用，有一点要注意：一旦列表分区表有一个DEFAULT分区，就不能再向这个表中增加更多的分区了。

此时必须删除DEFAULT分区，然后增加PART_4，再加回DEFAULT分区。

3.4组合分区

在组合分区中，顶层分区机制11g以前总是range分区，11g开始顶层分区可以使用list分区。

Oracle10g第二级分区机制可能是列表分区（range-list）或散列分区（range-hash）（在Oracle9i Release 1及以前的版本中，只支持散列子分区，而没有列表子分区）。11g后完全组合分区出现range-range,list-range,list-list,list-hash。

使用组合分区时，并没有分区段，而只有子分区段。

分区本身并没有段（这就类似于分区表没有段）。

数据物理的存储在子分区段上，分区成为一个逻辑容器，或者是一个指向实际子分区的容器。

3.4.1 range-hash

SQL > 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/2005','dd/mm/yyyy'))
(subpartition part_1_sub_1,
subpartition part_1_sub_2
),
PARTITION part_2
VALUES LESS THAN(to_date('01/01/2006','dd/mm/yyyy'))
(subpartition part_2_sub_1,
subpartition part_2_sub_2
));

在区间-散列组合分区中，Oracle首先会应用区间分区规则，得出数据属于哪个区间。然后再应用散列函数，来确定数据最后要放在哪个物理分区中。

3.4.2 range-list

SQL > 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/2005','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/2006','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 )
));

每个分区的子分区的数目可以是不一样的。

注意：

如果用于确定分区的列有修改会发生什么。需要考虑两种情况：

（1）修改不会导致使用一个不同的分区；行仍属于原来的分区。这在所有情况下都得到支持。

（2）修改会导致行跨分区移动。只有当表启用了行移动时才支持这种情况；否则，会产生一个错误。

（3）如果构建的系统会频繁修改分区键，而且这种修改会导致分区移动，这实在是一个糟糕的设计决策。

四、索引分区

索引与表类似，也可以分区。对索引进行分区有两种可能的方法：

（1）随表对索引完成相应的分区：这也称为局部分区索引（locally pertitioned index）。每个表分区都有一个索引分区，而且只索引该表分区。一个给定索引分区中的所有条目都指向一个表分区，表分区中的所有行都表示在一个索引分区中。

（2）按区间对索引分区：这也称为全局分区索引（globally partitioned index）。在此，索引按区间分区（或者在Oracle 10g中该可以按散列分区），一个索引分区可能指向任何（和所有）表分区。

4.1局部索引和分区消除行为

如果查询首先访问索引，它是否能消除分区完全取决于查询中的谓词。

SQL > CREATE TABLE partitioned_table
( a int,
b int,
data char(20)
)
PARTITION BY RANGE (a)
(
PARTITION part_1 VALUES LESS THAN(2) tablespace p1,
PARTITION part_2 VALUES LESS THAN(3) tablespace p2
);

小于2的值都在分区PART_1中，小于3的值则都在分区PART_2中。

创建一个局部前缀索引LOCAL_PREFIXED和一个局部非前缀索引LOCAL_NONPREFIXED。

SQL > create index local_prefixed on partitioned_table (a,b) local;
Index created.
SQL > create index local_nonprefixed on partitioned_table (b) local;
Index created.

非前缀索引在其定义中没有以A作为其前导列（最前列），这是这一点使之成为一个非前缀索引。

SQL> insert into partitioned_table
select mod(rownum-1,2)+1, rownum, 'x'
from all_objects;
48967 rows created.
SQL> begin
dbms_stats.gather_table_stats
( user,
'PARTITIONED_TABLE',
cascade=>TRUE );
end;
/
PL/SQL procedure successfully completed.

向一个分区中插入一些数据，并收集统计信息。

将表空间P2离线，其中包含用于表和索引的PART_2分区

SQL> alter tablespace p2 offline;
Tablespace altered.

表空间P2离线后，Oracle就无法访问这些特定的索引分区。这就好像是我们遭遇了“介质故障”，导致分区不可用。现在我们查询这个表，来看看不同的查询需要哪些索引分区。第一个查询编写为允许使用局部前缀索引：

查询例1：

SQL> select * from partitioned_table where a = 1 and b = 1;
A       B       DATA
----------    ----------     --------------------
1               1                x      
SQL> explain plan for select * from partitioned_table where a = 1 and b = 1;
SQL> select * from TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));

这个查询成功了，通过查看解释计划，可以看到这个查询为什么能成功。

使用LOCAL_PREFIXED的查询成功了。

优化器能消除LOCAL_PREFIXED的PART_2不予考虑，因为我们在查询中指定了A=1，而且在计划中可以清楚地看到PSTART和PSTOP都等于1.

局部索引的限制：

要能够实现消除分区，必须将分区键作为where条件

查询例2：

SQL> select * from partitioned_table where b = 1;
ERROR:
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: '/app/oracle/oradata/pxdb/p2.dbf'


SQL> delete from plan_table;
SQL> explain plan for select * from partitioned_table where b = 1;
SQL> select * from table(dbms_xplan.display);

没有实现消除分区，因此访问所有分区。

是否消除分区，关键看的是是否使用分区键，和前缀索引、非前缀索引没有关系。

要使用非前缀索引，必须使用一个允许分区消除的查询，这样效率才会提高。

查询例3：

SQL> drop index local_prefixed;--删除前缀索引
Index dropped.
SQL> delete from plan_table;
SQL> select * from partitioned_table where a = 1 and b = 1;
A             B       DATA
----------             ----------       --------------------
1             1       x
SQL> explain plan for select * from partitioned_table where a = 1 and b = 1;
SQL> select * from table(dbms_xplan.display);

结论：

非前缀索引也能实现消除分区。

如果不能实现分区消除，那么oracle将会对多个分区的多个分区索引进行扫描。

不必对非前缀索引退避三舍，也不要认为非前缀索引是主要的性能障碍。

重点是，要尽可能保证查询包含的谓词允许索引分区消除。

使用前缀局部索引可以保证这一点，使用非前缀索引则不能保证。

主要是因为前缀索引将分区键作为前导列。

4.2局部索引和唯一约束

为了保证唯一性（这包括UNIQUE约束或PRIMARY KEY约束），如果你想使用一个局部索引来保证这个约束，那么分区键必须包括在约束本身中。这是局部索引的最大限制。

Oracle只保证索引分区内部的唯一性，而不能跨分区。

这意味着不能一方面在一个TIMESTAMP字段上执行区间分区，而另一方面在ID上有一个主键（使用一个局部分区索引来保证）。

Oracle会利用全局索引来保证唯一性。

分区键建立分区以后，保证在不同的分区内，分区键一定是不同的。

如果建立主键的时候，包括分区键，那么可以保证分区键不同的时候，一定在不同的分区内，分区键相同的时候，一定在同一个分区内。

这样局部唯一性约束可以保证整个表的唯一性。

4.3全局索引

全局索引使用一种有别于底层表的机制进行分区。

表可以按一个TIMESTAMP（时间）列划分为10个分区，而这个表上的一个全局索引可以按REGION（地区）列划分为5个分区。

与局部索引不同，全局索引只有一类，这就是前缀全局索引（prefixed global index）。如果全局索引的索引键未从该索引的分区键开始，这是不允许的。这说明，不论用什么属性对索引分区，这些属性都必须是索引键的前几列。

CREATE TABLE partitioned
( timestamp date,
id int
)
PARTITION BY RANGE (timestamp)
(
PARTITION part_1 VALUES LESS THAN
( to_date('01-jan-2000','dd-mon-yyyy') ) ,
PARTITION part_2 VALUES LESS THAN
( to_date('01-jan-2001','dd-mon-yyyy') )
);

建立了一个分区表，分区键是timestamp。

SQL> create index partitioned_index
on partitioned(id)
GLOBAL
partition by range(id)
(
partition part_1 values less than(1000),
partition part_2 values less than (MAXVALUE)
);

建立了全局索引以后，对全局索引进行了分区。

我们在进行索引搜索的时候，首先根据where id=100，迅速的确定这个搜索落在哪个索引分区内，实现了索引分区的消减分区。

也就是说，我们只需要搜索一个索引树。

注意：

这个索引中使用了MAXVALUE。

MAXVALUE不仅可以用于索引中，还可以用于任何区间分区表中。它表示区间的“无限上界”。

在此前的所有例子中，我们都使用了区间的硬性上界（小于<< span="">某个值>的值）。不过，全局索引有一个需求，即最高分区（最后一个分区）必须有一个值为MAXVALUE的分区上界。这可以确保底层表中的所有行都能放在这个索引中。

SQL> alter table partitioned add constraint partitioned_pk primary key(id);

因为在id列上有一个全局索引，因此这个主键直接使用这个全局索引。

SQL> drop index partitioned_index;
drop index partitioned_index
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

这个表示我们建立了的索引被主键使用。

SQL> create index partitioned_index2
on partitioned(timestamp,id)
GLOBAL
partition by range(id)
(partition part_1 values less than(1000),
partition part_2 values less than (MAXVALUE));
partition by range(id)
*
ERROR at line 4:
ORA-14038: GLOBAL partitioned index must be prefixed

全局分区索引必须能够实现当进行where条件匹配的时候，首先能够消减分区，然后在一个分区内实现索引的搜索。

我们可以以timestamp进行分区，也可以以timestamp,id复合进行分区。

也就是必须使用索引的前导列进行分区。

create index partitioned_index2
on partitioned(timestamp)
GLOBAL
partition by range(timestamp)
(PARTITION part_1 VALUES
LESS THAN( to_date('01-jan-2000','dd-mon-yyyy') ) ,
PARTITION part_2 VALUES
LESS THAN( MAXVALUE)
); --可创建成功


create index partitioned_index3
on partitioned(timestamp,id)
GLOBAL ; --可创建成功

五、ORACLE 11g分区表特性

5.1引用分区表

使子表使用父表的分区条件进行分区，而无需在子表中额外添加用于分区的列，造成数据逆规范化，造成空间浪费等问题。

（1）创建主表

CREATE TABLE orders
(
order# NUMBER PRIMARY KEY,
order_date DATE NOT NULL,
data VARCHAR2 (30)
)
PARTITION BY RANGE (order_date)
(
PARTITION part_2016 VALUES LESS THAN (TO_DATE ('01-01-2017','dd-mm-yyyy')),
PARTITION part_2017 VALUES LESS THAN (TO_DATE ('01-01-2018','dd-mm-yyyy'))
);
SQL> insert into orders values ( 1, to_date( '01-01-2017', 'dd-mm-yyyy' ), 'xxx' );
SQL> insert into orders values ( 2, to_date( '01-01-2016', 'dd-mm-yyyy' ), 'yyy' );
SQL> commit;

（2）创建子表

create table order_line_items
( 
order#      number NOT NULL,
line#      number NOT NULL,
data      varchar2(30),
constraint c1_pk primary key(order#,line#),
constraint c1_fk_p foreign key(order#) references orders
)
enable row movement
partition by reference(c1_fk_p);
insert into order_line_items values  ( 1, 1, 'yyy' );
insert into order_line_items values  ( 2, 1, 'yyy' );
commit;
SELECT table_name, partition_name FROM user_tab_partitions WHERE table_name IN ('ORDERS', 'ORDER_LINE_ITEMS')
ORDER BY table_name, partition_name;
SQL> SELECT table_name, partition_name FROM user_tab_partitions WHERE table_name IN ('ORDERS', 'ORDER_LINE_ITEMS')
ORDER BY table_name, partition_name;  
TABLE_NAME                   PARTITION_NAME
------------------------------ ------------------------------
ORDERS                         PART_2016
ORDERS                         PART_2017
ORDER_LINE_ITEMS             PART_2016
ORDER_LINE_ITEMS             PART_2017

（3）删除父表partition，可以级联删除子表分区

SQL> alter table orders drop partition part_2016 update global indexes;
SQL> SELECT table_name, partition_name FROM user_tab_partitions WHERE table_name IN ('ORDERS', 'ORDER_LINE_ITEMS')
ORDER BY table_name, partition_name;  
TABLE_NAME                   PARTITION_NAME
------------------------------ ------------------------------
ORDERS                         PART_2017
ORDER_LINE_ITEMS             PART_2017

（4）添加父表partition，可以级联添加子表分区

alter table orders add partition part_2018 values less than (to_date( '01-01-2019', 'dd-mm-yyyy' ));
SELECT table_name, partition_name FROM user_tab_partitions WHERE table_name IN ('ORDERS', 'ORDER_LINE_ITEMS')
ORDER BY table_name, partition_name;  
TABLE_NAME                   PARTITION_NAME
------------------------------ ------------------------------
ORDERS                         PART_2017
ORDERS                         PART_2018
ORDER_LINE_ITEMS             PART_2017
ORDER_LINE_ITEMS             PART_2018

（5）删除子表partition，报错(truncate子表partition可行)

SQL> alter table order_line_items drop partition PART_2017 update global indexes;
alter table order_line_items drop partition PART_2017 update global indexes
                                            *
ERROR at line 1:
ORA-14255: table is not partitioned by Range, List, Composite Range or Composite List method

5.2间隔分区表

定义分区规则，当有符合条件的数据插入时自动创建分区。

可以使用alter命令将现有的区间分区表修改为间隔分区，也可以使用create创建一个间隔分区。

（1）创建间隔分区表

CREATE TABLE orders2
(
order# NUMBER PRIMARY KEY,
order_date DATE NOT NULL
)
PARTITION BY RANGE (order_date)
INTERVAL ( NUMTOYMINTERVAL (1, 'month') )
(PARTITION p201612 VALUES LESS THAN (TO_DATE ('01-01-2017', 'dd-mm-yyyy')));

- 插入数据

insert into orders2 values(1,TO_DATE ('01-12-2016', 'dd-mm-yyyy'));
insert into orders2 values(2,TO_DATE ('02-01-2017', 'dd-mm-yyyy'));
insert into orders2 values(3,TO_DATE ('02-02-2017', 'dd-mm-yyyy'));
commit;
SQL> SELECT table_name, partition_name
FROM user_tab_partitions
WHERE table_name IN ('ORDERS2')
ORDER BY table_name, partition_name;
TABLE_NAME                   PARTITION_NAME
------------------------------ ------------------------------
ORDERS2                    P201612
ORDERS2                    SYS_P61
ORDERS2                    SYS_P62

- 删除数据已生成的分区不变

- 将原有的range分区表变更为interval分区表

ALTER TABLE orders SET INTERVAL(1000000);
ALTER TABLE orders SET INTERVAL (NUMTODSINTERVAL(1,'DAY')); --NUMTODSINTERVAL常用的单位有 ('day','hour','minute','second')
ALTER TABLE orders SET INTERVAL (numtoyminterval(1,'month')); --numtoyminterval常用的单位有'year','month'

- 将interval分区表转为range分区表

ALTER TABLE orders3 SET INTERVAL();

5.3系统分区表

CREATE TABLE S_P_TEST
(
col1 number,
name varchar2(100)
)
PARTITION BY SYSTEM
(
PARTITION p1,
PARTITION p2,
PARTITION p3,
PARTITION p4
);

--插入时要指明分区

SQL> insert into S_P_TEST select object_id,object_name from dba_objects;
insert into S_P_TEST select object_id,object_name from dba_objects
            *
ERROR at line 1:
ORA-14701: partition-extended name or bind variable must be used for DMLs on tables partitioned by the System method
SQL> insert into S_P_TEST partition(p1) select object_id,object_name from dba_objects where object_type='TABLE';
SQL> insert into S_P_TEST partition(p2) select object_id,object_name from dba_objects where object_type='INDEX';
SQL> select count(1) from S_P_TEST;
SQL> select count(1) from S_P_TEST partition(p1);
SQL> select count(1) from S_P_TEST partition(p2);

--相同的数据可以插入到不同的分区中，这说明数据和分区没有任何关系

SQL> insert into S_P_TEST partition(p3) values(1,'aa');
SQL> insert into S_P_TEST partition(p4) values(1,'aa');
SQL> commit;
SQL> select * from S_P_TEST partition(p3);
SQL> select * from S_P_TEST partition(p4);

系统分区表的使用场景并不多，如果对传统的分区表数据的特性理解是：

数据和分区是有联系的；
分区就是把很多的表粘在一起管理。那系统分区表只有第二个特性。

5.4完整分区类型

（1）范围分区（range）

（2）哈希分区（hash）

（3）列表分区（list）

（4）范围－哈希复合分区（range-hash）

（5）范围－列表复合分区（range-list）

oracle11g 新增完全组合分区

range-range,list-range,list-list,list-hash

5.5虚拟列分区

以下内容来自https://www.cnblogs.com/moonandstar08/p/5100567.html

（1）只能在堆组织表（普通表）上创建虚拟列

（2）虚拟列不能是LOB或者RAW类型；

（3）虚拟列的值并不是真实存在的，只有用到时，才根据表达式计算出虚拟列的值，磁盘上并不存放。

（4）可把虚拟列当做分区关键字建立分区表，这是ORACLE 11g的另一新特性--虚拟列分区

创建带有虚拟列的分区表：

create table test(n1 number, c1 varchar2(80), n2 number generated always as (n1*0.8)) ---创建带有虚拟列的分区
   create table test1(n1 number,
                 c1 varchar2(80),
                 v1 varchar2(10) generated always as (substr(c1,1,1)))
   partition by list (v1)
    (partition v11 values('I'),
     partition v12 values('O'),
     partition v13 values('E'),
     partition v15 values(default)
    );

查询：

select * from test1 partition(v11);

oracle

文章转载自DBA小记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Oracle分区表

评论