Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Sam.

Asked: April 05, 2017 - 5:40 pm UTC

Last updated: April 10, 2017 - 12:33 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Can you please provide an example for interval partition 7 days interval

and Connor said...

SQL> create table t1 ( x date )
  2  partition by range ( x )
  3  interval ( numtodsinterval(7,'DAY'))
  4  (
  5    partition p1 values less than ( date '2017-04-01' )
  6  );

Table created.

SQL>
SQL> insert into t1
  2  select sysdate-10+rownum
  3  from dual
  4  connect by level <= 20 ;

20 rows created.

SQL>
SQL> exec dbms_stats.gather_table_stats('','T1');

PL/SQL procedure successfully completed.

SQL>
SQL> select partition_name, num_rows
  2  from user_tab_partitions
  3  where table_name = 'T1';

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
P1                                      4
SYS_P725                                7
SYS_P726                                7
SYS_P727                                2

4 rows selected.



Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

extent

A reader, April 09, 2017 - 9:42 pm UTC

If I insert data with a later date will oracle create the partition automatically in the above example?
Connor McDonald
April 10, 2017 - 12:33 am UTC

Yes - as you can see from mine, the DDL started with only 1 partition, but as I inserted data, three more were created.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.