table partition的管理和使用

Range表分区：
----------
SQL> create table alan_test
  2  (
  3    transaction_id number primary key,
  4    item_id number(8) not null,
  5    item_description varchar2(300),
  6    transaction_date date not null
  7  )
  8  partition by range ( transaction_id )
  9  (
 10  partition alan_test01 values less than (30000000) tablespace alan_space01,
 11  partition alan_test02 values less than (60000000) tablespace alan_space02,
 12  partition alan_test03 values less than (maxvalue) tablespace alan_space03
 13  );

表已创建。

SQL> create table alan_test_date
  2  (
  3    transaction_id number primary key,
  4    item_id number(8) not null,
  5    item_description varchar2(300),
  6    transaction_date date not null
  7  )
  8  partition by range ( transaction_date )
  9  (
 10  partition alan_test_date01 values less than (to_date('2008-01-01','yyyy-mm-dd')) tablespace alan_space01,
 11  partition alan_test_date02 values less than (to_date('2010-01-01','yyyy-mm-dd')) tablespace alan_space02,
 12  partition alan_test_date03 values less than (maxvalue) tablespace alan_space03
 13  );

表已创建。

Hash表分区
---------
SQL> create table alan_test_hash
  2  (
  3    transaction_id number primary key,
  4    item_id number(8) not null,
  5    item_description varchar2(300),
  6    transaction_date date
  7  )
  8  partition by hash (transaction_id)
  9  (
 10  partition alan_test_hash01 tablespace alan_space01,
 11  partition alan_test_hash02 tablespace alan_space02,
 12  partition alan_test_hash03 tablespace alan_space03
 13  );

表已创建。

Composite表分区
-------------
SQL> create table alan_test_composite
  2  (
  3    transaction_id number primary key,
  4    item_id number(8) not null,
  5    item_description varchar2(300),
  6    transaction_date date
  7  )
  8  partition by range (transaction_date)
  9  subpartition by hash (transaction_id)
 10  subpartitions 3 store in (alan_space01,alan_space02,alan_space03)
 11  (
 12    partition alan_test_composite01 values less than (to_date('2008-01-01','yyyy-mm-dd')),
 13    partition alan_test_composite02 values less than (to_date('2010-01-01','yyyy-mm-dd')),
 14    partition alan_test_composite03 values less than (maxvalue)
 15  );

表已创建。

测试Range分区(based on Date)
------------------------------
SQL> select * from alan_test_date;

未选定行

SQL> insert into alan_test_date values (1,12,'BOOKS',to_date('2007-12-01','yyyy-mm-dd'));

已创建 1 行。

SQL> insert into alan_test_date values (2,12,'BOOKS',to_date('2007-12-20','yyyy-mm-dd'));

已创建 1 行。

SQL> insert into alan_test_date values (3,12,'BOOKS',to_date('2008-2-19','yyyy-mm-dd'));

已创建 1 行。

SQL> insert into alan_test_date values (4,12,'BOOKS',to_date('2008-04-09','yyyy-mm-dd'));

已创建 1 行。

SQL> insert into alan_test_date values (5,12,'BOOKS',to_date('2011-2-19','yyyy-mm-dd'));

已创建 1 行。

SQL> insert into alan_test_date values (6,12,'BOOKS',to_date('2011-4-9','yyyy-mm-dd'));

已创建 1 行。

查看对应段的分区：
SQL> select segment_name,partition_name,segment_type
  2  from user_segments
  3  where segment_name='ALAN_TEST_DATE';

SEGMENT_NAME                   PARTITION_NAME                 SEGMENT_TYPE
------------------------------ ------------------------------ ----------------
ALAN_TEST_DATE                 ALAN_TEST_DATE01               TABLE PARTITION
ALAN_TEST_DATE                 ALAN_TEST_DATE02               TABLE PARTITION
ALAN_TEST_DATE                 ALAN_TEST_DATE03               TABLE PARTITION

SQL> select * from alan_test_date partition(alan_test_date01);

TRANSACTION_ID    ITEM_ID ITEM_DESCRIPTION               TRANSACTION_DATE
-------------- ---------- ------------------------------ -------------------
             1         12 BOOKS                          2007-12-01 00:00:00
             2         12 BOOKS                          2007-12-20 00:00:00

SQL> select * from alan_test_date partition(alan_test_date02);

TRANSACTION_ID    ITEM_ID ITEM_DESCRIPTION               TRANSACTION_DATE
-------------- ---------- ------------------------------ -------------------
             3         12 BOOKS                          2008-02-19 00:00:00
             4         12 BOOKS                          2008-04-09 00:00:00

SQL> select * from alan_test_date partition(alan_test_date03);

TRANSACTION_ID    ITEM_ID ITEM_DESCRIPTION               TRANSACTION_DATE
-------------- ---------- ------------------------------ -------------------
             5         12 BOOKS                          2011-02-19 00:00:00
             6         12 BOOKS                          2011-04-09 00:00:00

我们可以清楚的看到6行数据，按照date的range分布到了3个不同的partition中.

更新分区：关键就是要指定更新的记录所指定的分区。
SQL> update alan_test_date partition(alan_test_date01)
  2  set item_description='DESK' where transaction_id=2;

已更新 1 行。

SQL> commit;

提交完成。

SQL> select * from alan_test_date partition(alan_test_date01);

TRANSACTION_ID    ITEM_ID ITEM_DESCRIPTION               TRANSACTION_DATE
-------------- ---------- ------------------------------ -------------------
             1         12 BOOKS                          2007-12-01 00:00:00
             2         12 DESK                           2007-12-20 00:00:00

那么表可以建立分区，同样的索引也可以建立在分区表上面，在分区表上一般可以建立两种索引：局部(本地)索引和全局索引

这里就先记录一下局部索引：
-------------------------
SQL> create index alan_test_date_t on alan_test_date (item_id) local;

索引已创建。

在不指定tablespace的情况下一般索引会根据表分区的设置来分配tablespace.
我们来看一下索引建立的结果：
SQL> select segment_name,partition_name,segment_type,tablespace_name
  2  from user_segments
  3  where segment_name='ALAN_TEST_DATE_T';

SEGMENT_NAME                   PARTITION_NAME                 SEGMENT_TYPE       TABLESPACE_NAME
------------------------------ ------------------------------ ------------------ ----------------
ALAN_TEST_DATE_T               ALAN_TEST_DATE01               INDEX PARTITION    ALAN_SPACE01
ALAN_TEST_DATE_T               ALAN_TEST_DATE02               INDEX PARTITION    ALAN_SPACE02
ALAN_TEST_DATE_T               ALAN_TEST_DATE03               INDEX PARTITION    ALAN_SPACE03

那么如果指定了tablespace，我们就创建了一个新的分区专门用来存储索引
SQL> create index alan_test_date_t on alan_test_date(item_id)
  2  local
  3  (
  4  partition idx_1 tablespace alan_space01,
  5  partition idx_2 tablespace alan_space02,
  6  partition idx_3 tablespace alan_space03
  7  );

索引已创建。

SQL> select segment_name,segment_type,partition_name,tablespace_name
  2  from user_segments
  3  where segment_name='ALAN_TEST_DATE_T';

SEGMENT_NAME                   SEGMENT_TYPE       PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------ ------------------------------ ------------------
ALAN_TEST_DATE_T               INDEX PARTITION    IDX_1                          ALAN_SPACE01
ALAN_TEST_DATE_T               INDEX PARTITION    IDX_2                          ALAN_SPACE02
ALAN_TEST_DATE_T               INDEX PARTITION    IDX_3                          ALAN_SPACE03

我们用索引来查询一下partition alan_test_date01的数据，并看一下执行计划
SQL> set autotrace traceonly
SQL> select * from alan_test_date partition(alan_test_date01)
  2  where item_id=12;

执行计划
----------------------------------------------------------
Plan hash value: 1015351968

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                  |     2 |    40 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE            |                  |     2 |    40 |     2   (0)| 00:00:01 |     1 |     1 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| ALAN_TEST_DATE   |     2 |    40 |     2   (0)| 00:00:01 |     1 |     1 |
|*  3 |    INDEX RANGE SCAN                | ALAN_TEST_DATE_T |     1 |       |     1   (0)| 00:00:01 |     1 |     1 |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("ITEM_ID"=12)

这里验证了确实走的是索引