写分区表SQL的一个实用技巧
时间：2010-10-20 来源：gonglindi
在建分区表时，要建大量的表空间和长长的建表语句。用手工写的话，非常费时，有一个简易的方法。
例如，每天一个表空间，建一个月的表空间：
select 'CREATE TABLESPACE tbs_'
||t.d
||' DATAFILE ''d:/gis_tablespace/index/'
||t.d
||'.dbf'' SIZE 1M AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED;'
from
(
select to_char(to_date('2010-02-28','yyyy-mm-dd')+level,'yyyymmdd') d,
level l,rownum rn
from dual
connect by
level <= 30
) t
在PL/SQL执行后，可以得到下面的结果：
CREATE TABLESPACE tbs_20100301 DATAFILE 'd:/gis_tablespace/index/20100301.dbf' SIZE 1M AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED;
CREATE TABLESPACE tbs_20100302 DATAFILE 'd:/gis_tablespace/index/20100302.dbf' SIZE 1M AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED;
CREATE TABLESPACE tbs_20100303 DATAFILE 'd:/gis_tablespace/index/20100303.dbf' SIZE 1M AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED;
CREATE TABLESPACE tbs_20100304 DATAFILE 'd:/gis_tablespace/index/20100304.dbf' SIZE 1M AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED;
CREATE TABLESPACE tbs_20100305 DATAFILE 'd:/gis_tablespace/index/20100305.dbf' SIZE 1M AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED;
CREATE TABLESPACE tbs_20100306 DATAFILE 'd:/gis_tablespace/index/20100306.dbf' SIZE 1M AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED;
CREATE TABLESPACE tbs_20100307 DATAFILE 'd:/gis_tablespace/index/20100307.dbf' SIZE 1M AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED;
CREATE TABLESPACE tbs_20100308 DATAFILE 'd:/gis_tablespace/index/20100308.dbf' SIZE 1M AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED;
CREATE TABLESPACE tbs_20100309 DATAFILE 'd:/gis_tablespace/index/20100309.dbf' SIZE 1M AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED;
CREATE TABLESPACE tbs_20100310 DATAFILE 'd:/gis_tablespace/index/20100310.dbf' SIZE 1M AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED;
CREATE TABLESPACE tbs_20100311 DATAFILE 'd:/gis_tablespace/index/20100311.dbf' SIZE 1M AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED;
CREATE TABLESPACE tbs_20100312 DATAFILE 'd:/gis_tablespace/index/20100312.dbf' SIZE 1M AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED;
CREATE TABLESPACE tbs_20100313 DATAFILE 'd:/gis_tablespace/index/20100313.dbf' SIZE 1M AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED;
CREATE TABLESPACE tbs_20100314 DATAFILE 'd:/gis_tablespace/index/20100314.dbf' SIZE 1M AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED;
CREATE TABLESPACE tbs_20100315 DATAFILE 'd:/gis_tablespace/index/20100315.dbf' SIZE 1M AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED;
CREATE TABLESPACE tbs_20100316 DATAFILE 'd:/gis_tablespace/index/20100316.dbf' SIZE 1M AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED;
CREATE TABLESPACE tbs_20100317 DATAFILE 'd:/gis_tablespace/index/20100317.dbf' SIZE 1M AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED;
CREATE TABLESPACE tbs_20100318 DATAFILE 'd:/gis_tablespace/index/20100318.dbf' SIZE 1M AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED;
CREATE TABLESPACE tbs_20100319 DATAFILE 'd:/gis_tablespace/index/20100319.dbf' SIZE 1M AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED;
CREATE TABLESPACE tbs_20100320 DATAFILE 'd:/gis_tablespace/index/20100320.dbf' SIZE 1M AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED;
CREATE TABLESPACE tbs_20100321 DATAFILE 'd:/gis_tablespace/index/20100321.dbf' SIZE 1M AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED;
CREATE TABLESPACE tbs_20100322 DATAFILE 'd:/gis_tablespace/index/20100322.dbf' SIZE 1M AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED;
CREATE TABLESPACE tbs_20100323 DATAFILE 'd:/gis_tablespace/index/20100323.dbf' SIZE 1M AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED;
右键把这些SQL语句COPY出来执行，30个表空间就可以很快生成了。
如果要生成一年的表空间，只要把level <= 30 改为 level <= 365就可以了。
接着建表，每天一个分区。
我们发现，分区SQL中，有部分的SQL是有规律的。
同样使用上面的方面得到这部分的SQL语句。
select 'PARTITION p'
||t.d1
||' VALUES LESS THAN (TO_DATE('''
||t.d2
||''',''YYYYMMDD'')) tablespace tbs_'
||t.d1
||','
from
(
select to_char(to_date('2010-02-28','yyyy-mm-dd')+level,'yyyymmdd') d1,
to_char(to_date('2010-02-28','yyyy-mm-dd')+level+1,'yyyymmdd') d2,
level l,rownum rn
from dual
connect by
level <= 30
) t
在PL/SQL运行后，得到下面的结果：
PARTITION p20100301 VALUES LESS THAN (TO_DATE('20100302','YYYYMMDD')) tablespace tbs_20100301,
PARTITION p20100302 VALUES LESS THAN (TO_DATE('20100303','YYYYMMDD')) tablespace tbs_20100302,
PARTITION p20100303 VALUES LESS THAN (TO_DATE('20100304','YYYYMMDD')) tablespace tbs_20100303,
PARTITION p20100304 VALUES LESS THAN (TO_DATE('20100305','YYYYMMDD')) tablespace tbs_20100304,
PARTITION p20100305 VALUES LESS THAN (TO_DATE('20100306','YYYYMMDD')) tablespace tbs_20100305,
PARTITION p20100306 VALUES LESS THAN (TO_DATE('20100307','YYYYMMDD')) tablespace tbs_20100306,
PARTITION p20100307 VALUES LESS THAN (TO_DATE('20100308','YYYYMMDD')) tablespace tbs_20100307,
PARTITION p20100308 VALUES LESS THAN (TO_DATE('20100309','YYYYMMDD')) tablespace tbs_20100308,
PARTITION p20100309 VALUES LESS THAN (TO_DATE('20100310','YYYYMMDD')) tablespace tbs_20100309,
PARTITION p20100310 VALUES LESS THAN (TO_DATE('20100311','YYYYMMDD')) tablespace tbs_20100310,
PARTITION p20100311 VALUES LESS THAN (TO_DATE('20100312','YYYYMMDD')) tablespace tbs_20100311,
PARTITION p20100312 VALUES LESS THAN (TO_DATE('20100313','YYYYMMDD')) tablespace tbs_20100312,
PARTITION p20100313 VALUES LESS THAN (TO_DATE('20100314','YYYYMMDD')) tablespace tbs_20100313,
PARTITION p20100314 VALUES LESS THAN (TO_DATE('20100315','YYYYMMDD')) tablespace tbs_20100314,
PARTITION p20100315 VALUES LESS THAN (TO_DATE('20100316','YYYYMMDD')) tablespace tbs_20100315,
PARTITION p20100316 VALUES LESS THAN (TO_DATE('20100317','YYYYMMDD')) tablespace tbs_20100316,
PARTITION p20100317 VALUES LESS THAN (TO_DATE('20100318','YYYYMMDD')) tablespace tbs_20100317,
PARTITION p20100318 VALUES LESS THAN (TO_DATE('20100319','YYYYMMDD')) tablespace tbs_20100318,
PARTITION p20100319 VALUES LESS THAN (TO_DATE('20100320','YYYYMMDD')) tablespace tbs_20100319,
PARTITION p20100320 VALUES LESS THAN (TO_DATE('20100321','YYYYMMDD')) tablespace tbs_20100320,
PARTITION p20100321 VALUES LESS THAN (TO_DATE('20100322','YYYYMMDD')) tablespace tbs_20100321,
PARTITION p20100322 VALUES LESS THAN (TO_DATE('20100323','YYYYMMDD')) tablespace tbs_20100322,
PARTITION p20100323 VALUES LESS THAN (TO_DATE('20100324','YYYYMMDD')) tablespace tbs_20100323,
PARTITION p20100324 VALUES LESS THAN (TO_DATE('20100325','YYYYMMDD')) tablespace tbs_20100324,
PARTITION p20100325 VALUES LESS THAN (TO_DATE('20100326','YYYYMMDD')) tablespace tbs_20100325,
PARTITION p20100326 VALUES LESS THAN (TO_DATE('20100327','YYYYMMDD')) tablespace tbs_20100326,
PARTITION p20100327 VALUES LESS THAN (TO_DATE('20100328','YYYYMMDD')) tablespace tbs_20100327,
PARTITION p20100328 VALUES LESS THAN (TO_DATE('20100329','YYYYMMDD')) tablespace tbs_20100328,
PARTITION p20100329 VALUES LESS THAN (TO_DATE('20100330','YYYYMMDD')) tablespace tbs_20100329,
PARTITION p20100330 VALUES LESS THAN (TO_DATE('20100331','YYYYMMDD')) tablespace tbs_20100330,
COPY出来，改一下：
前面加上
CREATE TABLE "ADMIN"."TBPartion"
( "GTIME" TIMESTAMP (6),
"DETAIL" VARCHAR2(50)
"INSDATE" DATE default sysdate
)
PARTITION BY RANGE(GPSTIME)
(
后面加上
PARTITION p_max LESS THAN MAXVALUE);
然后，执行一下，搞定！
相关阅读 更多 +
