创建自动分区的例子

郑全

创建一张分区表sales_history_2014,以时间字段分区,2011,2012,2013,and 2014 对应到SAL1,SAL2,SAL3 and SAL4,超过2014年的数据,每个月一个分区:

create table sales_history_2014
           (PROD_ID number,
            CUST_ID number,
            TIME_ID date,
            CHANNEL_ID number,
            PROMO_ID number,
            QUANTITY_SOLD number(10,2))
           partition by range(time_id) interval (numtoyMinterval (1,'MONTH'))
             (partition sal1 values less than (to_date('2012-01-01','yyyy-mm-dd')),
              partition sal2 values less than (to_date('2013-01-01','yyyy-mm-dd')),
              partition sal3 values less than (to_date('2014-01-01','yyyy-mm-dd')),
              partition sal4 values less than (to_date('2015-01-01','yyyy-mm-dd'))
             );

郑全

未插入数据前:
SQL>  select partition_name from user_tab_partitions where table_name='SALES_HISTORY_2014';

PARTITION_NAME
--------------------------------------------------------------------------------
SAL4
SAL3
SAL2
SAL1

插入数据:

insert into sales_history_2014 select PROD_ID,CUST_ID,TIME_ID+7300,CHANNEL_ID,PROMO_ID,QUANTITY_SOLD from sh.sales;

验证分区:


SQL> select partition_name from user_tab_partitions where table_name='SALES_HISTORY_2014' order by 1;

PARTITION_NAME
--------------------------------------------------------------------------------
SAL1
SAL2
SAL3
SAL4
SYS_P241
SYS_P242
SYS_P243
SYS_P244
SYS_P245
SYS_P246
SYS_P247

PARTITION_NAME
--------------------------------------------------------------------------------
SYS_P248
SYS_P249
SYS_P250
SYS_P251
SYS_P252
SYS_P253
SYS_P254
SYS_P255
SYS_P256
SYS_P257
SYS_P258

PARTITION_NAME
--------------------------------------------------------------------------------
SYS_P259
SYS_P260
SYS_P261
SYS_P262
SYS_P263
SYS_P264
SYS_P265
SYS_P266
SYS_P267
SYS_P268
SYS_P269

PARTITION_NAME
--------------------------------------------------------------------------------
SYS_P270
SYS_P271
SYS_P272
SYS_P273
SYS_P274
SYS_P275
SYS_P276
SYS_P277
SYS_P278
SYS_P279
SYS_P280

PARTITION_NAME
--------------------------------------------------------------------------------
SYS_P281
SYS_P282
SYS_P283
SYS_P284
SYS_P285
SYS_P286
SYS_P287
SYS_P288
SYS_P289

53 rows selected.