[11/26] partition table

interval partition table

orcl@SYS> create table order_date2

  2  (order# number, orderd date)

  3  partition by range(orderd)

  4

 create table order_date2

    (order# number

    ,orderd  date)

    partition by range(orderd) INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))

      (

       partition p200907 values less than(to_date('2015/01/01', 'yyyy/mm/dd'))dd'))

     , partition p200908 values less than(to_date('2015/02/01', 'yyyy/mm/dd'))dd'))

      , partition p200909 values less than(to_date('2015/03/01', 'yyyy/mm/dd'))dd'))

    , partition p200910 values less than(to_date('2015/04/01', 'yyyy/mm/dd'))dd'))

      , partition p200911 values less than(to_date('2015/05/01', 'yyyy/mm/dd'))dd'))

 11      );

Table created.

Elapsed: 00:00:00.31

orcl@SYS> desc order_date2;

 Name                                                                   Null?    Type

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

 ORDER#                                                                  NUMBER

 ORDERD                                                                  DATE

orcl@SYS> select * from order_date2;

no rows selected

Elapsed: 00:00:00.01

 insert into order_date2 values (1,to_date('2015/06/01', 'yyyy/mm/dd'));/mm/dd'));

1 row created.

Elapsed: 00:00:00.12

select table_name, PARTITION_NAME

    from user_tab_partitions

  3     where table_name='ORDER_DATE2';

TABLE_NAME                     PARTITION_NAME

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

ORDER_DATE2                    P200907

ORDER_DATE2                    P200908

ORDER_DATE2                    P200909

ORDER_DATE2                    P200910

ORDER_DATE2                    P200911

ORDER_DATE2                    SYS_P21

6 rows selected.

Elapsed: 00:00:00.14

orcl@SYS>

orcl@SYS>

orcl@SYS>  insert into order_date2 values (1,to_date('2015/07/01', 'yyyy/mm/dd'));

1 row created.

Elapsed: 00:00:00.05

select table_name, PARTITION_NAME

    from user_tab_partitions

  3     where table_name='ORDER_DATE2';

TABLE_NAME                     PARTITION_NAME

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

ORDER_DATE2                    P200907

ORDER_DATE2                    P200908

ORDER_DATE2                    P200909

ORDER_DATE2                    P200910

ORDER_DATE2                    P200911

ORDER_DATE2                    SYS_P21

ORDER_DATE2                    SYS_P22

7 rows selected.

Elapsed: 00:00:00.05

orcl@SYS>  insert into order_date2 values (1,to_date('2015/05/01', 'yyyy/mm/dd'));

1 row created.

Elapsed: 00:00:00.05

orcl@SYS> select * from order_date2;

    ORDER# ORDERD

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

         1 2015-05-01:00:00:00

         1 2015-06-01:00:00:00

         1 2015-07-01:00:00:00

Elapsed: 00:00:00.00

orcl@SYS>  insert into order_date2 values (1,to_date('2015/04/01', 'yyyy/mm/dd'));

1 row created.

Elapsed: 00:00:00.00

orcl@SYS>  insert into order_date2 values (1,to_date('2015/03/01', 'yyyy/mm/dd'));

1 row created.

Elapsed: 00:00:00.00

orcl@SYS>  insert into order_date2 values (1,to_date('2015/02/01', 'yyyy/mm/dd'));

1 row created.

Elapsed: 00:00:00.01

orcl@SYS>  insert into order_date2 values (1,to_date('2015/01/01', 'yyyy/mm/dd'));

1 row created.

Elapsed: 00:00:00.01

orcl@SYS> select * from order_date2;

    ORDER# ORDERD

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

         1 2015-01-01:00:00:00

         1 2015-02-01:00:00:00

         1 2015-03-01:00:00:00

         1 2015-04-01:00:00:00

         1 2015-05-01:00:00:00

         1 2015-06-01:00:00:00

         1 2015-07-01:00:00:00

7 rows selected.