--表分区机制
--Oracle目前有9中对表分区的方法
--区间分区（range partitioning）：你可以指定某个分区的数据应该放在一起。比如时间戳一月二月存放在分区一分区二中。
--散列分区（hash partitioning）：数据库会在一个或多个列上应用一个散列函数，每行数据都按散列值放在相应的分区中。
--列表分区（list partitioning）：指定一个离散值集，来确定应当存储在一起的数据。例如指定STATUS列值在（'A','B'）中的行放在分区1中，值（'C','D'）放在分区2中。
--间隔分区（interval partitioning）：它与区间分区非常相似，区别在于数据库可以在数据到来时创建新的分区。
--引用分区（reference partitioning）：对于一对外键约束的父/子表，如果父表已分区，子表可以通过外键约束来继承父表的分区方式，子表上的这种分区方法称为引用分区。
--间隔引用分区（interval reference partitioning）：这种分区就是间隔分区和引用分区的组合。Oracle12c才有这种分区。
--虚拟列分区（virtual column partitioning）：这是区间分区、散列分区和列表分区的组合。通过组合分区我们可以先按照某种方式分区（区间、散列、列表），再按照另外某种方式对每个分区的数据划分子分区。
--系统分区（system partitioning）：应用来决定将数据写入哪一个分区。这种分区类型比较少见。
--区间分区
EODA@PROD1> CREATE TABLE range_example --创建一个使用RANGE_KEY_COLUMN分区的区间分区表，严格小于01-JAN-2014的数据放在part_1，严格小于01-JAN-2015的数据放在part_2
2 ( range_key_column date NOT NULL,
3 data varchar2(20)
4 )
5 PARTITION BY RANGE (range_key_column)
6 ( PARTITION part_1 VALUES LESS THAN
7 (to_date('01/01/2014','dd/mm/yyyy')),
8 PARTITION part_2 VALUES LESS THAN
9 (to_date('01/01/2015','dd/mm/yyyy'))
10 )
11 /
Table created.
EODA@PROD1>
EODA@PROD1> insert into range_example --插入数据
2 ( range_key_column, data )
3 values
4 ( to_date( '15-dec-2013 00:00:00',
5 'dd-mon-yyyy hh24:mi:ss' ),
6 'application data...' );
1 row created.
EODA@PROD1>
EODA@PROD1> insert into range_example
2 ( range_key_column, data )
3 values
4 ( to_date( '31-dec-2013 23:59:59',
5 'dd-mon-yyyy hh24:mi:ss' ),
6 'application data...' );
1 row created.
EODA@PROD1>
EODA@PROD1> insert into range_example
2 ( range_key_column, data )
3 values
4 ( to_date( '01-jan-2014 00:00:00',
5 'dd-mon-yyyy hh24:mi:ss' ),
6 'application data...' );
1 row created.
EODA@PROD1>
EODA@PROD1> insert into range_example
2 ( range_key_column, data )
3 values
4 ( to_date( '31-dec-2014 23:59:59',
5 'dd-mon-yyyy hh24:mi:ss' ),
6 'application data...' );
1 row created.
EODA@PROD1>
EODA@PROD1> -- should throw an error
EODA@PROD1> insert into range_example --尝试插入超过分区的值失败
2 ( range_key_column, data )
3 values
4 ( to_date( '01-jan-2015 00:00:00',
5 'dd-mon-yyyy hh24:mi:ss' ),
6 'application data...' );
insert into range_example
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition
EODA@PROD1>
EODA@PROD1> select to_char(range_key_column,'dd-mon-yyyy hh24:mi:ss') --查询part_1
2 from range_example partition (part_1);
TO_CHAR(RANGE_KEY_COLUMN,'DD-
-----------------------------
15-dec-2013 00:00:00
31-dec-2013 23:59:59
EODA@PROD1>
EODA@PROD1> select to_char(range_key_column,'dd-mon-yyyy hh24:mi:ss') --查询part_2
2 from range_example partition (part_2);
TO_CHAR(RANGE_KEY_COLUMN,'DD-
-----------------------------
01-jan-2014 00:00:00
31-dec-2014 23:59:59
EODA@PROD1>
EODA@PROD1> drop table range_example purge;
Table dropped.
EODA@PROD1>
EODA@PROD1> CREATE TABLE range_example --这样可以保证所有数据都能被插入
2 ( range_key_column date ,
3 data varchar2(20)
4 )
5 PARTITION BY RANGE (range_key_column)
6 ( PARTITION part_1 VALUES LESS THAN
7 (to_date('01/01/2014','dd/mm/yyyy')),
8 PARTITION part_2 VALUES LESS THAN
9 (to_date('01/01/2015','dd/mm/yyyy')),
10 PARTITION part_3 VALUES LESS THAN
11 (MAXVALUE)
12 )
13 /
Table created.
--散列分区
对于散列分区，Oracle会使用一个散列函数对每一条插入数据的分区列值计算出起散列值，以此确定数据应当放在N个分区中的哪一个分区中。
Oracle建议N是2一个幂，这样表中的数据才能最好的分布在所有的分区上。
表中的散列键值应当各不相同，这样数据才能均匀的分布在各个分区上。
EODA@PROD1> set echo on
EODA@PROD1>
EODA@PROD1> CREATE TABLE hash_example --创建两个分区的分区表
2 ( hash_key_column date,
3 data varchar2(20)
4 )
5 PARTITION BY HASH (hash_key_column)
6 ( partition part_1 tablespace p1,
7 partition part_2 tablespace p2
8 )
9 /
Table created.
EODA@PROD1>
EODA@PROD1> insert into hash_example
2 ( hash_key_column, data )
3 values
4 ( to_date( '25-jun-2014' ),
5 'application data...' );
1 row created.
EODA@PROD1>
EODA@PROD1> insert into hash_example
2 ( hash_key_column, data )
3 values
4 ( to_date( '27-feb-2015' ),
5 'application data...' );
1 row created.
EODA@PROD1>
EODA@PROD1> select 'part_1', hash_key_column from hash_example partition(part_1) union all
2 select 'part_2', hash_key_column from hash_example partition(part_2);
'PART_ HASH_KEY_
------ ---------
part_2 25-JUN-14
part_2 27-FEB-15
--散列分区数需要使用2的幂
--首先自动创建一个有N个分区的散列分区表，这个过程会构造一个动态查询，按分区获取其中的行数，再按分区显示行数，最后给出一个简单直方图。
EODA@PROD1> create or replace
2 procedure hash_proc
3 ( p_nhash in number,
4 p_cursor out sys_refcursor )
5 authid current_user
6 as
7 l_text long;
8 l_template long :=
9 'select $POS$ oc, ''p$POS$'' pname, count(*) cnt ' ||
10 'from t partition ( $PNAME$ ) union all ';
11 table_or_view_does_not_exist exception;
12 pragma exception_init( table_or_view_does_not_exist, -942 );
13 begin
14 begin
15 execute immediate 'drop table t';
16 exception when table_or_view_does_not_exist
17 then null;
18 end;
19
20 execute immediate '
21 CREATE TABLE t ( id )
22 partition by hash(id)
23 partitions ' || p_nhash || '
24 as
25 select rownum
26 from all_objects';
27
28 for x in ( select partition_name pname,
29 PARTITION_POSITION pos
30 from user_tab_partitions
31 where table_name = 'T'
32 order by partition_position )
33 loop
34 l_text := l_text ||
35 replace(
36 replace(l_template,
37 '$POS$', x.pos),
38 '$PNAME$', x.pname );
39 end loop;
40
41 open p_cursor for
42 'select pname, cnt,
43 substr( rpad(''*'',30*round( cnt/max(cnt)over(),2),''*''),1,30) hg
44 from (' || substr( l_text, 1, length(l_text)-11 ) || ')
45 order by oc';
46
47 end;
48 /
Procedure created.
EODA@PROD1>
EODA@PROD1> set lines 132
EODA@PROD1> column hg format a50
EODA@PROD1> variable x refcursor
EODA@PROD1> set autoprint on
EODA@PROD1> exec hash_proc( 4, :x ); --数据均匀分布在了4个分区中
PL/SQL procedure successfully completed.
PN CNT HG
-- ---------- --------------------------------------------------
p1 18208 *****************************
p2 18183 *****************************
p3 18415 ******************************
p4 18096 *****************************
EODA@PROD1> exec hash_proc( 5, :x ); --第一个和最后一个分区是中间三个的一半，数据没有均匀分区
PL/SQL procedure successfully completed.
PN CNT HG
-- ---------- --------------------------------------------------
p1 9178 ***************
p2 18185 *****************************
p3 18416 ******************************
p4 18096 *****************************
p5 9032 **************
EODA@PROD1> exec hash_proc( 6, :x ); --同五个分区的趋势一样
PL/SQL procedure successfully completed.
PN CNT HG
-- ---------- --------------------------------------------------
p1 9179 ***************
p2 9229 ***************
p3 18417 ******************************
p4 18098 *****************************
p5 9032 **************
p6 8958 **************
6 rows selected.
EODA@PROD1> exec hash_proc( 7, :x ); --同五个分区的趋势一样
PL/SQL procedure successfully completed.
PN CNT HG
-- ---------- --------------------------------------------------
p1 9179 ***************
p2 9231 ***************
p3 9205 ***************
p4 18099 ******************************
p5 9035 ***************
p6 8958 **************
p7 9213 ***************
7 rows selected.
EODA@PROD1> exec hash_proc( 8, :x ); --数据分布又变为均匀
PL/SQL procedure successfully completed.
PN CNT HG
-- ---------- --------------------------------------------------
p1 9181 *****************************
p2 9231 ******************************
p3 9206 ******************************
p4 9068 *****************************
p5 9036 *****************************
p6 8959 *****************************
p7 9213 ******************************
p8 9034 *****************************
8 rows selected.
--很明显得出结论！散列分区数要使用的2的幂
--列表分区
列表分区是Oracle9i的一个新特性，它为每个分区指定了一个离散值列表，每行数据根据其分区键值所属列表归到相应的分区中。
EODA@PROD1> create table list_example
2 ( state_cd varchar2(2),
3 data varchar2(20)
4 )
5 partition by list(state_cd)
6 ( partition part_1 values ( 'ME', 'NH', 'VT', 'MA' ), --列表分区
7 partition part_2 values ( 'CT', 'RI', 'NY' )
8 )
9 /
Table created.
EODA@PROD1>
EODA@PROD1> insert into list_example (state_cd, data)
2 values ('CT', 'application data...');
1 row created.
EODA@PROD1> insert into list_example (state_cd, data)
2 values ('MA', 'application data...');
1 row created.
EODA@PROD1> insert into list_example (state_cd, data)
2 values ('ME', 'application data...');
1 row created.
EODA@PROD1> insert into list_example (state_cd, data)
2 values ('NH', 'application data...');
1 row created.
EODA@PROD1> insert into list_example (state_cd, data)
2 values ('NY', 'application data...');
1 row created.
EODA@PROD1> insert into list_example (state_cd, data)
2 values ('RI', 'application data...');
1 row created.
EODA@PROD1> insert into list_example (state_cd, data)
2 values ('VT', 'application data...');
1 row created.
EODA@PROD1> select * from list_example partition (part_1);
ST DATA
-- --------------------
MA application data...
ME application data...
NH application data...
VT application data...
EODA@PROD1>
EODA@PROD1> 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
EODA@PROD1>
EODA@PROD1> alter table list_example --增加默认分区
2 add partition
3 part_3 values ( DEFAULT );
Table altered.
EODA@PROD1>
EODA@PROD1> insert into list_example values ( 'VA', 'data' ); --插入成功
1 row created.
--间隔分区
间隔分区就是以一个区间分区表为起点，不过他在定义中还有一个规则，让数据库知道如何增加分区。
EODA@PROD1> create table audit_trail --设置分区间隔为每个月一个分区，p0设置为过度分区，所有严格小于这个过度分区上限的数据都会被放到这个分区中。
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 )
11 /
Table created.
EODA@PROD1> column partition_name format a10
EODA@PROD1> column tablespace_name format a10
EODA@PROD1> column high_value format a31
EODA@PROD1> column interval format a30
EODA@PROD1>
EODA@PROD1> 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'
EODA@PROD1>
EODA@PROD1> insert into audit_trail (ts,data) values
2 ( to_timestamp('27-feb-2014','dd-mon-yyyy'), 'xx' ); --插入数据
1 row created.
EODA@PROD1>
EODA@PROD1> select a.partition_name, a.tablespace_name, a.high_value, --观察到自动创建了SYS_P71分区
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'
SYS_P71 USERS TIMESTAMP' 2014-03-01 00:00:00' NUMTOYMINTERVAL(1,'MONTH')
EODA@PROD1>
EODA@PROD1> column greater_than_eq_to format a32
EODA@PROD1> column strictly_less_than format a32
EODA@PROD1>
EODA@PROD1> select
2 TIMESTAMP' 2014-03-01 00:00:00'-NUMTOYMINTERVAL(1,'MONTH') greater_than_eq_to,
3 TIMESTAMP' 2014-03-01 00:00:00' strictly_less_than
4 from dual
5 /
GREATER_THAN_EQ_TO STRICTLY_LESS_THAN
-------------------------------- --------------------------------
01-FEB-14 12.00.00.000000000 AM 01-MAR-14 12.00.00.000000000 AM
EODA@PROD1>
EODA@PROD1> insert into audit_trail (ts,data) values
2 ( to_date('25-jun-2014','dd-mon-yyyy'), 'xx' );
1 row created.
EODA@PROD1>
EODA@PROD1> select a.partition_name, a.tablespace_name, a.high_value, --发现插入两行都存在了USERS表空间内
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'
SYS_P71 USERS TIMESTAMP' 2014-03-01 00:00:00' NUMTOYMINTERVAL(1,'MONTH')
SYS_P72 USERS TIMESTAMP' 2014-07-01 00:00:00' NUMTOYMINTERVAL(1,'MONTH')
EODA@PROD1>
EODA@PROD1> insert into audit_trail (ts,data) values --插入奇数月份
2 ( to_date('15-mar-2014','dd-mon-yyyy'), 'xx' );
1 row created.
EODA@PROD1>
EODA@PROD1> 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'
SYS_P71 USERS TIMESTAMP' 2014-03-01 00:00:00' NUMTOYMINTERVAL(1,'MONTH')
SYS_P73 EXAMPLE TIMESTAMP' 2014-04-01 00:00:00' NUMTOYMINTERVAL(1,'MONTH')
SYS_P72 USERS TIMESTAMP' 2014-07-01 00:00:00' NUMTOYMINTERVAL(1,'MONTH')
EODA@PROD1> --更改系统自动创建的表空间名字
EODA@PROD1> declare
2 l_str varchar2(4000);
3 begin
4 for x in ( select a.partition_name, a.tablespace_name, a.high_value
5 from user_tab_partitions a
6 where a.table_name = 'AUDIT_TRAIL'
7 and a.interval = 'YES'
8 and a.partition_name like 'SYS\_P%' escape '\' )
9 loop
10 execute immediate
11 'select to_char( ' || x.high_value ||
12 '-numtodsinterval(1,''second''), ''"PART_"yyyy_mm'' ) from dual'
13 into l_str;
14 execute immediate
15 'alter table audit_trail rename partition "' ||
16 x.partition_name || '" to "' || l_str || '"';
17 end loop;
18 end;
19 /
PL/SQL procedure successfully completed.
EODA@PROD1>
EODA@PROD1> set lines 132
EODA@PROD1> column partition_name format a20
EODA@PROD1>
EODA@PROD1> 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_NAME TABLESPACE HIGH_VALUE INTERVAL
-------------------- ---------- ------------------------------- ------------------------------
P0 USERS TIMESTAMP' 1900-01-01 00:00:00'
PART_2014_02 USERS TIMESTAMP' 2014-03-01 00:00:00' NUMTOYMINTERVAL(1,'MONTH')
PART_2014_03 EXAMPLE TIMESTAMP' 2014-04-01 00:00:00' NUMTOYMINTERVAL(1,'MONTH')
PART_2014_06 USERS TIMESTAMP' 2014-07-01 00:00:00' NUMTOYMINTERVAL(1,'MONTH')
--引用分区
引用分区是Oracle 11g R1以上版本的一个新特性，用于处理父/子对等分区的问题。
也就是说，我们可以某种方式对子表分区，使得子表的各个分区与父表相应的分区是一对一关系。
EODA@PROD1> 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_2014 VALUES LESS THAN (to_date('01-01-2015','dd-mm-yyyy')) ,
11 PARTITION part_2015 VALUES LESS THAN (to_date('01-01-2016','dd-mm-yyyy'))
12 )
13 /
Table created.
EODA@PROD1>
EODA@PROD1> insert into orders values
2 ( 1, to_date( '01-jun-2014', 'dd-mon-yyyy' ), 'xxx' );
1 row created.
EODA@PROD1>
EODA@PROD1> insert into orders values
2 ( 2, to_date( '01-jun-2015', 'dd-mon-yyyy' ), 'xxx' );
1 row created.
EODA@PROD1>
EODA@PROD1> 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 /
Table created.
EODA@PROD1>
EODA@PROD1> insert into order_line_items values ( 1, 1, 'yyy' );
1 row created.
EODA@PROD1>
EODA@PROD1> insert into order_line_items values ( 2, 1, 'yyy' );
1 row created.
EODA@PROD1>
EODA@PROD1> column table_name format a20
EODA@PROD1> column partition_name format a20
EODA@PROD1>
EODA@PROD1> 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_NAME
-------------------- --------------------
ORDERS PART_2014
ORDERS PART_2015
ORDER_LINE_ITEMS PART_2014
ORDER_LINE_ITEMS PART_2015
EODA@PROD1>
EODA@PROD1> alter table orders drop partition part_2014 update global indexes; --删除父表分区
Table altered.
EODA@PROD1>
EODA@PROD1> 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_NAME
-------------------- --------------------
ORDERS PART_2015
ORDER_LINE_ITEMS PART_2015
EODA@PROD1>
EODA@PROD1> alter table orders add partition
2 part_2016 values less than
3 (to_date( '01-01-2017', 'dd-mm-yyyy' ));
Table altered.
EODA@PROD1>
EODA@PROD1> 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_NAME
-------------------- --------------------
ORDERS PART_2015
ORDERS PART_2016
ORDER_LINE_ITEMS PART_2015
ORDER_LINE_ITEMS PART_2016
EODA@PROD1>
EODA@PROD1> select '2015', count(*) from order_line_items partition(part_2015)
2 union all
3 select '2016', count(*) from order_line_items partition(part_2016);
'201 COUNT(*)
---- ----------
2015 1
2016 0
EODA@PROD1>
EODA@PROD1> update orders set order_date = add_months(order_date,12); --对父表更新
1 row updated.
EODA@PROD1>
EODA@PROD1> select '2015', count(*) from order_line_items partition(part_2015)
2 union all
3 select '2016', count(*) from order_line_items partition(part_2016);
'201 COUNT(*)
---- ----------
2015 0
2016 1 --子表中的数据也异动了，这只有在父表定义为允许行移动才有效。
--间隔引用分区
--只有版本12c以后才有效
SQL> select banner from v$version; -12c
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
SQL> create table orders --创建间隔分区的父表
2 (order# number primary key,
3 order_date timestamp,
4 data varchar2(30))
5 PARTITION BY RANGE (order_date)
6 INTERVAL (numtoyminterval(1,'year'))
7 (PARTITION part_2014 VALUES LESS THAN (to_date('01-01-2015','dd-mm-yyyy')) ,
8 PARTITION part_2015 VALUES LESS THAN (to_date('01-01-2016','dd-mm-yyyy')));
Table created.
SQL>
SQL> create table order_line_items --创建引用分区的子表
2 ( order# number,
3 line# number,
4 data varchar2(30),
5 constraint c1_pk primary key(order#,line#),
6 constraint c1_fk_p foreign key(order#) references orders)
7 partition by reference(c1_fk_p);
Table created.
SQL>
SQL> insert into orders values (1, to_date('01-jun-2014', 'dd-mon-yyyy'), 'xxx');
1 row created.
SQL> insert into orders values (2, to_date('01-jun-2015', 'dd-mon-yyyy'), 'xxx');
1 row created.
SQL> insert into order_line_items values (1, 1, 'yyy');
1 row created.
SQL> insert into order_line_items values (2, 1, 'yyy');
1 row created.
SQL>
SQL> column table_name format a25
SQL> column partition_name format a20
SQL>
SQL> select table_name, partition_name from user_tab_partitions --查询已有分区
2 where table_name in ( 'ORDERS', 'ORDER_LINE_ITEMS' )
3 order by table_name, partition_name;
TABLE_NAME PARTITION_NAME
------------------------- --------------------
ORDERS PART_2014
ORDERS PART_2015
ORDER_LINE_ITEMS PART_2014
ORDER_LINE_ITEMS PART_2015
SQL>
SQL> insert into orders values (3, to_date( '01-jun-2016', 'dd-mon-yyyy' ), 'xxx'); --再插入不能插入已有分区的数据
1 row created.
SQL> insert into order_line_items values (3, 1, 'yyy');
1 row created.
SQL>
SQL> column partition_name format a10
SQL> column table_name format a16
SQL> column interval format a25
SQL> column high_value format a31
SQL>
SQL> select a.table_name, a.partition_name, a.high_value, --oracle自动创建新的分区成功
2 decode( a.interval, 'YES', b.interval ) interval
3 from user_tab_partitions a, user_part_tables b
4 where a.table_name IN ('ORDERS', 'ORDER_LINE_ITEMS')
5 and a.table_name = b.table_name
6 order by a.table_name;
TABLE_NAME PARTITION_ HIGH_VALUE
---------------- ---------- -------------------------------
INTERVAL
-------------------------
ORDERS PART_2014 TIMESTAMP' 2015-01-01 00:00:00'
ORDERS PART_2015 TIMESTAMP' 2016-01-01 00:00:00'
ORDERS SYS_P1133 TIMESTAMP' 2017-01-01 00:00:00'
NUMTOYMINTERVAL(1,'YEAR')
TABLE_NAME PARTITION_ HIGH_VALUE
---------------- ---------- -------------------------------
INTERVAL
-------------------------
ORDER_LINE_ITEMS PART_2014
ORDER_LINE_ITEMS PART_2015
ORDER_LINE_ITEMS SYS_P1133
YES
6 rows selected.
--虚拟列分区
我们可以通过虚拟列分区来基于某个SQL表达式对数据进行分区，当表中某列充斥着大量的业务值，并且我们只想以这列的某一部分来分区数据时，就可以使用。
EODA@PROD1> create table res(
2 reservation_code varchar2(30),
3 region as (substr(reservation_code,1,1)) --虚拟列
4 )
5 partition by list (region)
6 (partition p1 values('A'),
7 partition p2 values('B'),
8 partition p3 values('C'),
9 partition p4 values('D'));
Table created.
EODA@PROD1>
EODA@PROD1> create index r1 on res(reservation_code) local;
Index created.
EODA@PROD1>
EODA@PROD1> insert into res (reservation_code)
2 select chr(64+(round(dbms_random.value(1,4)))) || level
3 from dual connect by level < 100000;
99999 rows created.
EODA@PROD1>
EODA@PROD1> exec dbms_stats.gather_table_stats(user,'RES');
PL/SQL procedure successfully completed.
EODA@PROD1>
EODA@PROD1> explain plan for select count(*) from res where reservation_code='A';
Explained.
EODA@PROD1> select * from table(dbms_xplan.display(null, null, 'BASIC +PARTITION'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3813016412
-------------------------------------------------------
| Id | Operation | Name | Pstart| Pstop |
-------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | SORT AGGREGATE | | | |
| 2 | PARTITION LIST SINGLE| | 1 | 1 | --只扫描了一个分区
| 3 | INDEX RANGE SCAN | R1 | 1 | 1 |
-------------------------------------------------------
10 rows selected.
--组合分区
组合分区就是区间分区、散列分区以及列表分区的组合。
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/2014','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/2015','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 )
)
)
/
--参考来源《Oracle编程艺术深入理解
数据库
体系结构（第三版）》
本文详细介绍了Oracle数据库中的9种表分区方法，包括区间分区、散列分区、列表分区、间隔分区、引用分区、间隔引用分区、虚拟列分区、系统分区和组合分区。通过示例展示了如何创建和操作这些分区，以及它们在数据管理和查询效率中的作用。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
