Oracle List Partitioning knowledge

Application Scenario: Large data

--1), create a partition table

CREATE Table A

(Statis_date varchar2 (8),

ID number,

Name VARCHAR2 (50),

Salary number

)

Partition by list (statis_date)

(Partition day_20121130 values (' 20121130 '),

Partition day_20121201 values (' 20121201 '),

Partition day_20121202 values (' 20121202 '),

Partition day_20121203 values (' 20121203 '),

Partition day_20121204 values (' 20121204 ')

)

--2), inserting data

Insert into a (statis_date,id,name,salary) values (' 20121130 ', 1, ' a ', 1000);

Insert into a (statis_date,id,name,salary) values (' 20121201 ', 2, ' B ', 2000);

Insert into a (statis_date,id,name,salary) values (' 20121202 ', 3, ' C ', 3000);

Insert into a (statis_date,id,name,salary) values (' 20121203 ', 4, ' d ', 4000);

Insert into a (statis_date,id,name,salary) values (' 20121204 ', 6, ' F ', 6000);

Insert into a (statis_date,id,name,salary) values (' 20121205 ', 5, ' e ', 5000);

Commit

--3), query data

SELECT * from a where a.statis_date= ' 20121130 '--query data by partition

--4), deleting data

ALTER TABLE A TRUNCATE partition day_20121130;--delete data by partition

--5), increase zoning

ALTER TABLE A ADD partition day_20121204 values (' 20121204 ');

--), drop partition

ALTER TABLE A drop partition day_20121204

Append to SQL for table looping to increase partitioning:

Declare

J Number: = 1;

M VARCHAR2 (50): = ' 20130104 ';

Vv_sql varchar2 (100);

Begin

while (j<=1825) loop

Vv_sql: = ' ALTER TABLE Dw_cdr_unsuccessfulcall_day add partition Day_ ' | | m | | ' '||' Values (' | | m| | ') ';

Execute immediate vv_sql;

M: = To_char (to_date (' 20130104 ', ' YYYYMMD ') +j, ' YYYYMMDD ');

J:= j+1;

End Loop;

End

This column more highlights: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/