How to use partition table

What advantage does Partition table can provide? 

Partition table can provide data elimination.  With partition, although some data is not available only if it is no the data we want to access, we can access the table and get the result. It can provide high data availability.  For example:

 

--Create range partition;

create table emp_par_range(empno number,ename varchar2(20),deptno number)

partition by range(deptno)

(partition p1  values less than (20) tablespace ts1,

partition p2  values less than (MAXVALUE) tablespace ts2);

 

--insert operation

insert into emp_par_range partition(p1) select empno, ename, deptno from emp where deptno<20;

insert into emp_par_range partition(p2) select empno, ename, deptno from emp where deptno>=20;

 

alter tablespace ts2 offline;

 

--scenario I

18:05:50 scott@ORCL>select * from emp_par_range;(want to access data of p2,error)

ERROR:

ORA-00376: 此时无法读取文件 7

ORA-01110: 数据文件 7: 'D:/ORACLE/PRODUCT/10.2.0/DB_1/DATABASE/TS2.DBF'

 

--scenario II

18:08:59 scott@ORCL>select * from emp_par_range partition(p1);

     EMPNO ENAME    DEPTNO

---------- -------------------- ----------

      8369 SMITH 10

      7782 CLARK 10

      7839 KING 10

      7934 MILLER 10

18:09:01 scott@ORCL>set autotrace on explain

18:09:09 scott@ORCL>select * from emp_par_range where empno=7839;

     EMPNO ENAME    DEPTNO

---------- -------------------- ----------

      7839 KING 10

----------------------------------------------------------

Plan hash value: 2752748117

-----------------------------------------------------------------------------------------------------------------------

| Id  | Operation   | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

-----------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     1 |    12 |     1 (0)| 00:00:01 |       |       |

|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| EMP_PAR_RANGE    |     1 |    12 |     1 (0)| 00:00:01 | ROWID | ROWID |

|*  2 |   INDEX UNIQUE SCAN   | PK_EMP_PAR_RANGE |     1 |       |     0 (0)| 00:00:01 |       |       |

-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("EMPNO"=7839)

 

 

 

We can see: 

• Optimizer access index, 
• although the P2 partition unavailable, we still can access data from p1.  Data independency

--Scenario III

we try to run the same query using full table scan but index. -->has some problem with Hint.  should have error.

 

 

When to use Partition table?

When our table becomes dozens of GB or Hundreds of GB. We should consider using partition table. User only need to query a chunk of data instead of all data. 

 

OLAP

It is quite common to use partition table in Data warehouse project. Rule of thumb, choose prefixed  local partition index. 

 

OLTP

Compare to OLAP system, OLTP focuses on Data integrity, availability. So Global index or global partition index can be good choice. 

 

HOw to use partition table?

Type of Partition table

there are three types of partition, range, list and hash partition. you can also combine two of them to consist composite partition. 

In 10g and previous version, range + (list|hash) only supported

In 11g, (range|list) + (range| list | hash) supported. 

 

Let give several examples:

 

-->scenario I: range partition

create table emp_par_range(empno number,ename varchar2(20),deptno number)

partition by range(deptno)

(partition p1  values less than (20) tablespace ts1,

partition p2  values less than (MAXVALUE) tablespace ts2);

 

 

-->Scenario II: List partition:

create table emp_par_list(empno number, ename varchar2(20), deptno number)

partition by list(deptno)

(

partition p1 values(10,20,30),

partition p2 values(40,50,60),

partition p3 values(70,80,90)

);

 

 

--Scenario III: hash partition(simplest)

create table emp_par_hash(empno number, ename varchar2(20), deptno number)

partition by hash(deptno)

(

partition p1,

partition p2,

partition p3

);

 

--Scenario IV: Range + hash

 

create table emp_par_range_hash(empno number, ename varchar2(20), deptno number)

partition by range(deptno) 

subpartition by hash(ename)

subpartition template

(

subpartition A,

subpartition B,

subpartition C

)

(

partition p1 values less than (30),

partition p2 values less than (60),

partition p3 values less than (90),

partition p4 values less than (MAXVALUE)

)

 

Type of index(partition key on a)

 

prefixed local partition index;

>create index local_prefixed on partitioned_table (a,b) local; 

 

non-prefixed local partition index;

>create index local_nonprefixed on partitioned_table (b) local; 

 

global partition index:

>create index g_par_index on partitioned_table(b)

global 

partition by range (b)

(

partition p1 values less than(2),

partition p2 values less than(maxvalue)

);

 

 

Management Operation:

SuperLink: http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/partiti.htm#i1008028

Slide window:

--create table

CREATE TABLE partitioned

( timestamp date,

id int

)

PARTITION BY RANGE (timestamp)

(

PARTITION fy_1999 VALUES LESS THAN

( to_date('2000-01-01','yyyy-mm-dd')),

PARTITION fy_2000 VALUES LESS THAN

(to_date('2001-01-01','yyyy-mm-dd')),

 PARTITION the_rest VALUES LESS THAN

 ( maxvalue )

 );

 

 

 

insert into partitioned partition(fy_1999) select to_date('1999-12-31','yyyy-mm-dd')- mod(rownum,360), object_id from all_objects;

insert into partitioned partition(fy_2000) select to_date('2000-12-31','yyyy-mm-dd')- mod(rownum,360), object_id from all_objects;

 

create index l_id on partitioned(id) local;

create index g_timestamp on partitioned(timestamp) global;

 

create table fy_1999 ( timestamp date, id int );

create index fy_1999_idx on fy_1999(id);

create table fy_2001 ( timestamp date, id int );

insert into fy_2001 select to_date('2001-12-31','yyyy-mm-dd')- mod(rownum,360), object_id from all_objects;

create index fy_2001_idx on fy_2001(id) nologging;

 

--exchange partition fy_1999 with table fy_1999;

alter table partitioned exchange partition fy_1999 with table fy_1999 including indexes without validation;

 

--split partition

alter table partitioned split partition the_rest at(to_date('2001-12-31','yyyy-mm-dd')) into (partition fy_2001,partition the_rest);

alter table partitioned exchange partition fy_2001 with table fy_2001 including indexes without validation;

 

 

--after operation

select index_name , status from user_indexes;

--global index is not valid

alter index g_timestamp rebuild [online];

select index_name , status from user_indexes;

--all indexes are valid

 

 

Related Data Dict:

select * from dba_part_tables;

select * from dba_part_histograms;

select * from dba_part_indexes;

select * from dba_part_key_columns;

select * from dba_segments;

select * from dba_extents;