转载内容
摘录部分我的笔记的中doc,和大家一起感受Oracle 11g在分区方面的增强--System Partitioning
系统分区的特点
●系统分区与其他分区相比,一个最根本的区别就是不需要指定分区KEY值
●数据会进入哪个分区由应用程序决定,实际上也就是由SQL来决定,在Insert语句中可以指定插入哪个分区
●因为System Partitioning分区表没有分区字段,所以一般分区表的performance benefits对System Partitioning分区表不再适用(包括分区表智能裁剪和智能联接)
●对于按“系统”方法进行分区的表, 必须对 DML 使用分区扩展名或绑定变量
●记录放在哪个分区与记录没有任何关系,同样的记录可以存放到不同的分区中 ●The following operations are supported for system partitioned tables: Partition maintenance operations and other DDLs (See exceptions below) Creation of local indexes. Creation of local bitmapped indexes. Creation of global indexes. All DML operations. ●insert 操作支持三种写法
INSERT with partition extended syntax: insert into <base_table> partition (<partition_name|dataobj_to_partition(table_name,:data_object_id)>)
其中data_object_id通过dba_objects视图中获取
参考文档:<Oracle Database Data Cartridge Developer's Guide> 11g Realses 1(11.1) Part Number B28425-01
●Because of the peculiar requirements of system partitioning, the following operations are not supported for system partitioning: Unique local indexes are not supported because they require a partitioning key. CREATE TABLE AS SELECT(不能通过CTAS创建System Partitioning表) Since there is no partitioning method, it is not possible to distribute rows to partitions. Instead the user should first create the table and then insert rows into each partition. INSERT INTO <tabname> AS <subquery> SPLIT PARTITION operations
说明
或许我们对Oracle提供的这个新特性的实际用途使用很少,但不难看出这确实是一个非常人性化的变革,所以我们也不得不认真、仔细的认识并研究他。或许将来我们有一个这样的需求:
假设有一个无法使用任何逻辑方法进行分区的表,结果将是一个巨大的、单一的表。不难想象这将导致需要扩展的索引维护和其他操作问题的出现。于是,开发那边可以提出一个这样的要求:他们可以通过某种智能算法写入分区,通过应用来控制特定的记录写入到某个分区,而对DBA来说,就只需要定义一个system partitiion table即可。
示例一:System Partitioning初体验
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 Connected as study SQL> create table sys_part_test 2 ( 3 region number(4), 4 recdate date 5 ) 6 partition by system 7 ( 8 partition p1, 9 partition p2, 10 partition p3, 11 partition p4 12 ); Table created SQL> insert into sys_part_test partition (p1) values(531,sysdate); 1 row inserted SQL> insert into sys_part_test partition (p2) values(532,sysdate); 1 row inserted SQL> insert into sys_part_test partition (p3) values(533,sysdate); 1 row inserted SQL> insert into sys_part_test partition (p4) values(534,sysdate); 1 row inserted SQL> commit; Commit complete SQL> select * from sys_part_test; REGION RECDATE ------ ----------- 531 2007-12-14 532 2007-12-14 533 2007-12-14 534 2007-12-14 SQL> insert into sys_part_test partition (p1) select * from sys_part_test partition (p4); 1 row inserted SQL> commit; Commit complete SQL> select * from sys_part_test partition (p1); REGION RECDATE ------ ----------- 531 2007-12-14 534 2007-12-14 SQL> select * from sys_part_test partition (p2); REGION RECDATE ------ ----------- 532 2007-12-14 SQL> select * from sys_part_test partition (p3); REGION RECDATE ------ ----------- 533 2007-12-14 SQL> select * from sys_part_test partition (p4); REGION RECDATE ------ ----------- 534 2007-12-14 SQL> select *from sys_part_test; REGION RECDATE ---------- ------------------- 531 2007-12-14 17:21:13 534 2007-12-14 17:21:32 532 2007-12-14 17:21:20 533 2007-12-14 17:21:26 534 2007-12-14 17:21:32 SQL> update sys_part_test set recdate=sysdate where region=533; 已更新 1 行。 SQL> select *from sys_part_test; REGION RECDATE ---------- ------------------- 531 2007-12-14 17:21:13 534 2007-12-14 17:21:32 532 2007-12-14 17:21:20 533 2007-12-14 17:27:19 534 2007-12-14 17:21:32 SQL> delete from sys_part_test partition(p4); 已删除 1 行。 SQL> select *from sys_part_test; REGION RECDATE ---------- ------------------- 531 2007-12-14 17:21:13 534 2007-12-14 17:21:32 532 2007-12-14 17:21:20 533 2007-12-14 17:27:19 SQL>
示例二:System Partitioning Insert三种方法体验
D:>sqlplus study/study@ora11g SQL*Plus: Release 11.1.0.6.0 - Production on 星期六 12月 15 10:41:13 2007 Copyright (c) 1982, 2007, Oracle. All rights reserved. 连接到: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> create table sys_part_test 2 ( 3 region number(4), 4 recdate date 5 ) 6 partition by system 7 ( 8 partition p1, 9 partition p2, 10 partition p3, 11 partition p4 12 ); 表已创建。 SQL> insert into sys_part_test partition (p1) values (531,sysdate); --方法一,指定partition_name 已创建 1 行。 SQL> commit; 提交完成。 SQL> select * from sys_part_test; REGION RECDATE ---------- ------------------- 531 2007-12-15 10:41:43 SQL> select object_name,subobject_name,object_id,data_object_id,object_type 2 from user_objects 3 where object_name='SYS_PART_TEST'; OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE -------------- ----------------- ----------- -------------- --------------- SYS_PART_TEST 69632 TABLE SYS_PART_TEST P1 69633 69633 TABLE PARTITION SYS_PART_TEST P2 69634 69634 TABLE PARTITION SYS_PART_TEST P3 69635 69635 TABLE PARTITION SYS_PART_TEST P4 69636 69636 TABLE PARTITION SQL> insert into sys_part_test partition(dataobj_to_partition(sys_part_test,69634)) values (532,sysdate); 已创建 1 行。 --方法二,通过data object id SQL> commit; 提交完成。 SQL> select *from sys_part_test; REGION RECDATE ---------- ------------------- 531 2007-12-15 10:41:43 532 2007-12-15 10:43:44 SQL> select *from sys_part_test partition (p2); REGION RECDATE ---------- ------------------- 532 2007-12-15 10:43:44 SQL> var p_obj number; SQL> exec :p_obj:=69635 PL/SQL 过程已成功完成。 --如下为方法三,通过data object id但使用bind var SQL> insert into sys_part_test partition(dataobj_to_partition(sys_part_test,:p_obj)) values (533,sysdate); 已创建 1 行。 SQL> insert into sys_part_test partition(dataobj_to_partition(sys_part_test,:p_obj)) values (533,sysdate); 已创建 1 行。 SQL> commit; 提交完成。 SQL> select * from sys_part_test; REGION RECDATE ---------- ------------------- 531 2007-12-15 10:41:43 532 2007-12-15 10:43:44 533 2007-12-15 10:45:19 533 2007-12-15 10:45:21 SQL> select * from sys_part_test partition (p3); REGION RECDATE ---------- ------------------- 533 2007-12-15 10:45:19 533 2007-12-15 10:45:21 SQL> select * from sys_part_test partition (p2); REGION RECDATE ---------- ------------------- 532 2007-12-15 10:43:44 SQL> select * from sys_part_test partition (p1); REGION RECDATE ---------- ------------------- 531 2007-12-15 10:41:43 SQL> ...