Good day to all, there is such a table

CREATE TABLE test ( ID NUMBER(*) PRIMARY KEY NOT NULL, NAME VARCHAR2(40) NOT NULL, TEXT VARCHAR2(4000), DATE_CREATE DATE DEFAULT sysdate NOT NULL )

you need to do automatic partitioning on it by the DATE_CREATE column, the interval is one day, so that the next day the new partition is automatically created and therefore it was filled in with data for that day, I heard that this implementation was added to 11g, I tried to figure it out, but it comes to partition ... values less than (..) immediately get lost. My first question, all comments on the question are welcome in order to avoid mistakes in the future.

    1 answer 1

    Something like this:

     create table test(id number, date_create date default sysdate not null) partition by range (date_create) INTERVAL(NUMTODSINTERVAL(1,'DAY')) (PARTITION FIRST VALUES LESS THAN (TO_DATE('2016-11-30 00:00:00', 'YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) );

    PS all days will be in the first partition (section) < 2016-11-30 00:00:00

    From the docks :

    You must specify at least one range partition using the PARTITION clause.

    UPDATE:

    instead of select * from tab partition(p1); You can use the following. a query that needs to read only one day partition and return records from it:

     select * from test where date_create between to_date('2016-11-29','yyyy-mm-dd') and to_date('2016-11-30','yyyy-mm-dd');
    • I had this option, I just don’t quite understand its implementation, it turns out that we are creating the initial partition for the current day from the beginning and then the automatic creation takes place? - D.Antov
    • one
      @ D.Antov, yes, Oracle "requires" that at least one "range partition" is present. Later partitions will be created automatically. - MaxU
    • This raises the question: do partitions create unnamed ones, how do you select a sample for them as a consequence? Standard select * from tab partition(p1) does not fit - D.Antov
    • one
      @ D.Antov, and you don’t trust "oracle partition pruning"? In sense if to specify in WHERE. condition, Oracle will not read those partitions that it does not need ... - MaxU
    • I would like to note that there is either a bug or a feature: if the partitioning column has the date 31.12.9999, then the error ORA-01841 may appear: (full) year must be between -4713 and +9999, and not be 0. Apparently, this is not your case, but you never know. We had to get rid of the interval automatic separation at all because of this. You can read how to get around jamescoyle.net/knowledge/… - hinotf