Oracle 表分区

最新推荐文章于 2024-10-30 10:59:51 发布

cuiti2625

最新推荐文章于 2024-10-30 10:59:51 发布

阅读量77

点赞数

文章标签：数据库

查看表分区 user_tab_partitions

--范围分区
JOHN@ ora10g> create table range_example
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/2010','dd/mm/yyyy')),
8   partition part_2 values less than
9     (to_date('01/01/2011','dd/mm/yyyy'))
10 );

JOHN@ ora10g> insert into range_example values(
2 to_date('01/02/2010','dd/mm/yyyy'),
3 'this is a test!');

JOHN@ ora10g> select * from range_example;

RANGE_KEY_CO DATA
------------ --------------------
01-FEB-10 this is a test!

JOHN@ ora10g> select * from range_example partition (part_1);

no rows selected

JOHN@ ora10g> select * from range_example partition (part_2);

RANGE_KEY_CO DATA
------------ --------------------
01-FEB-10 this is a test!

JOHN@ ora10g> select TABLE_NAME, PARTITION_NAME from user_tab_partitions;
TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
RANGE_EXAMPLE                  PART_1
RANGE_EXAMPLE                  PART_2

对于超过范围的可以用maxvalue，其他数据就会放到part_3分区
JOHN@ ora10g> 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/2010', 'dd/mm/yyyy')),
8    partition part_2 values less than
9      (to_date('01/01/2011', 'dd/mm/yyyy')),
10    partition part_3 values less than
11      (maxvalue)
12 );

--散列分区
JOHN@ ora10g> 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 users,
7 partition part_2 tablespace example
8 );

散列分区数建议是2的幂，这样数据的分布会比较均匀。

--列表分区
JOHN@ ora10g> 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    partition part_3 values (default)
9 );
不符合分区1和分区2的数据会放到分区3。

--间隔分区（11g新特性）
JOHN@ ora10g> create table audit_trail
2 ( ts timestamp,
3 data varchar2(30)
4 )
5 partition by rang(ts)
6 interval (numtoyminterval(1,'month'))
7 store in (users, example)
8 (partition p0 values less than (to_date('01-01-1900','dd-mm-yyyy'))
9 );
会自动根据数据增加分区。

--引用分区（11g新特性）
--父表orders
JOHN@ ora10g> create table orders
2 ( order# number primary key,
3    order_date date,
4    data varchar2(30)
5 )
6 enable row movement
7 partition by range (order_date)
8 ( partition part_2009 values less than (to_date('01-01-2010','dd-mm-yyyy')),
9    partition part_2010 values less than (to_date('01-01-2011','dd-mm-yyyy'))
10 );

--子表order_line_tiems
JOHN@ ora10g> 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 )
8 enable row movement
9 partition by reference(c1_fk_p);

11gr2暂时不支持引用分区和间隔分区结合使用。

--组合分区
组合分区支持的版本
         区间  列表     散列
区间  11gr1  9ir2     9ir1
列表  11gr1  11gr1  11gr1
散列  11gr2  11gr2  11gr2
先横向再纵向

JOHN@ ora10g> create table composite_example
2 ( range_key_column date,
3    hash_key_column int,
4    data varchar2(20)
5 )
6 partition by range (range_key_column)
7 subpartition by hash(hash_key_column) subpartition 2
8 ( partition part_1
9      values less than(to_date('01/01/2008','dd/mm/yyyy'))
10      ( subpartition part_1_sub_1,
11        sbupartition part_1_sub_2
12      ),
13    partition part_2
14      values less than(to_date('01/01/2011','dd/mm/yyyy'))
15      ( subpartition part_2_sub_1,
16        subpartition part_2_sub_2
17     )
18 );