9 Replies Latest reply: Dec 6, 2012 9:24 AM by rp0428 RSS

    How to Implement 30 days Range Partitioning with Date column. Not Interval

    user8941550
      Hi,

      I am using the db:
      Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit

      Current table structure is:

      CREATE TABLE A
      (
      a NUMBER,
      CreationDate DATE
      )
      PARTITION BY RANGE (CreationDate)
      INTERVAL ( NUMTODSINTERVAL (30, 'DAY') )
      (PARTITION P_FIRST
      VALUES LESS THAN (TIMESTAMP ' 2001-01-01 00:00:00'))

      How can I define virtual column based partitioning with RANGE partitioning without using INTERVAL partitioning.
      And that is with Intervals of 30 days.

      For monthly I am trying as

      CREATE TABLE A
      (
      a NUMBER,
      CreationDate DATE,
      monthly_interval date as (to_char(CreationDate,'MM-YYYY')) VIRTUAL
      )
      PARTITION BY RANGE (monthly_interval)
      (
      partition p_AUG12 values less than (to_date('08-2012','mm-yyyy')),
      partition p_SEP12 values less than (to_date('09-2012','mm-yyyy')),
      partition p_OCT12 values less than (to_date('10-2012','mm-yyyy'))
      )
      Enable ROw Movement


      BUT CAN'T INSERT the data even for that:
      Insert into a (a, CreationDate)
      Values (1, '12-10-2012')
      Insert into a (a, CreationDate)
      Values (1, '12-10-2012')


      Please suggest..
        • 1. Re: How to Implement 30 days Range Partitioning with Date column. Not Interval
          rp0428
          Why are you posting a new thread when you already ask a related question here?
          Interval Partitioned to Range. Created Daily Partitions from Monthly Part.

          You should mark one thread ANSWERED and continue using the other one.

          Even if you do choose to keep both threads active you need to cross-link them and provide the links to the other thread so people can follow what you are doing.

          Your two threads are now very confusing as to what you are really trying to do.
          • 2. Re: How to Implement 30 days Range Partitioning with Date column. Not Interval
            user8941550
            Hi rp,

            Interval Partitioned to Range. Created Daily Partitions from Monthly Part. got complicated so I am posting here.

            Basically,


            I know Interval Partitioning is a kind of Range partitioning. But explicitly for Interval Partitioned tables XML Indexes are not allowed as discussed here:

            XMLIndexes on an Interval Partitioned Table??

            I can do monthly partitions as :
            CREATE TABLE A
            (
            a NUMBER,
            CreationDate DATE,
            monthly_interval varchar2(8) as (to_char(CreationDate,'MM-YYYY')) VIRTUAL
            )
            PARTITION BY RANGE (monthly_interval)
            (
            partition p_AUG12 values less than ('09-2012'),
            partition p_SEP12 values less than ('10-2012'),
            partition p_OCT12 values less than ('11-2012')
            ) Enable ROw Movement





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

            Insert into a (a, CreationDate)
            Values (1, '12-SEP-2012')

            Insert into a (a, CreationDate)
            Values (1, '14-SEP-2012')


            Select * from A partition (p_SEP12)


            Select * from A partition (p_AUG12)


            Select * from A partition (p_OCT12)



            Can we do it for 30 days partitions, instead of the monthly partitions. ANY suggestions..

            Thanks..
            • 3. Re: How to Implement 30 days Range Partitioning with Date column. Not Interval
              user8941550
              But I have used:
              monthly_interval as varchar and not date datatype. So basically partition '12-2012' becomes > '01-2013' which is not right.

              monthly_interval varchar2(8) as (to_char(CreationDate,'MM-YYYY')) VIRTUAL

              WHen I convert it to date as:

              CREATE TABLE A
              (
              a NUMBER,
              CreationDate DATE,
              monthly_interval date as (to_date(CreationDate,'MM-YYYY')) VIRTUAL
              )
              PARTITION BY RANGE (monthly_interval)
              (
              partition p_AUG12 values less than (to_date('09-2012','MM-YYYY')),
              partition p_SEP12 values less than (to_date('10-2012','MM-YYYY')),
              partition p_OCT12 values less than (to_date('11-2012','MM-YYYY'))
              ) Enable ROw Movement


              Insert into a (a, CreationDate)
              Values (1, '12-09-2012')

              I can't insert the data. I get the error message:
              ORA-01843: not a valid month
              Kindly suggest

              Thanks..
              • 4. Re: How to Implement 30 days Range Partitioning with Date column. Not Interval
                Manik
                Check your table script:

                It should be:
                CREATE TABLE A (
                   a NUMBER,
                   CreationDate DATE,
                   monthly_interval DATE
                         AS
                            (TO_DATE (
                                SUBSTR (CreationDate, INSTR (creationdate, '-', 1) + 1),
                                'MM-YYYY'))
                )
                PARTITION BY RANGE (monthly_interval)
                (
                partition p_AUG12 values less than (to_date('09-2012','MM-YYYY')),
                partition p_SEP12 values less than (to_date('10-2012','MM-YYYY')),
                partition p_OCT12 values less than (to_date('11-2012','MM-YYYY'))
                ) Enable ROw Movement
                Your insert query should be :
                INSERT INTO a (a, CreationDate)
                     VALUES (1, TO_DATE ('12-09-2012', 'DD-MM-YYYY'));
                Cheers,
                Manik.

                Edited by: Added modified table script as per the requirement.
                • 6. Re: How to Implement 30 days Range Partitioning with Date column. Not Interval
                  rp0428
                  >
                  Insert into a (a, CreationDate)
                  Values (1, '12-09-2012')

                  I can't insert the data. I get the error message:
                  ORA-01843: not a valid month
                  >
                  That's the price you pay when you rely in implicit conversion instead of using TO_DATE like you did in the table definition
                  Insert into a (a, CreationDate)
                  Values (1, to_date('12-09-2012', 'mm-dd-yyyy');
                  • 7. Re: How to Implement 30 days Range Partitioning with Date column. Not Interval
                    rp0428
                    >
                    I can do monthly partitions as :
                    CREATE TABLE A
                    (
                    a NUMBER,
                    CreationDate DATE,
                    monthly_interval varchar2(8) as (to_char(CreationDate,'MM-YYYY')) VIRTUAL
                    )
                    PARTITION BY RANGE (monthly_interval)
                    (
                    partition p_AUG12 values less than ('09-2012'),
                    partition p_SEP12 values less than ('10-2012'),
                    partition p_OCT12 values less than ('11-2012')
                    ) Enable ROw Movement

                    Can we do it for 30 days partitions, instead of the monthly partitions. ANY suggestions..
                    >
                    Yes - but you need your virtual column to create a value that can be interpreted as 30 days. You have month (MM) in your definition and months cannot be interpreted as 30 days. You could use 'DDD-YYYY' to get a value based on day and year and then change your partition definintions to use explicit 30 day differences. You will have to calculate those yourself.
                    VALUES LESS THAN ('001-2012')
                    VALUES LESS THAN ('031-2012')
                    VALUES LESS THAN ('061-2012')
                    VALUES LESS THAN ('091-2012')
                    . . .
                    Why do you simply have to use 30 days? Monthly is so much easier. You don't need a virtual column and determining the partition boundaries is a snap. You had better have a very good reason and actual need to use 30 day boundaries or you are just making things much more complex than they need to be.

                    With 30 day boundaries you can't maintain partitions by month, quarter or even year.
                    • 8. Re: How to Implement 30 days Range Partitioning with Date column. Not Interval
                      user8941550
                      Thanks for the explanation RP.
                      I got your point for 30 days not implementing logic. It's just that it's already there, even I don't agree with it unless a strong reason is there.

                      But even for Monthly I need to have a virtual column. Right?
                      Since I don't have a column with Month data but only date which is the partitioning key.

                      Thanks..
                      • 9. Re: How to Implement 30 days Range Partitioning with Date column. Not Interval
                        rp0428
                        >
                        But even for Monthly I need to have a virtual column. Right?
                        Since I don't have a column with Month data but only date which is the partitioning key.
                        >
                        Why would you? Just partition on the date column and manually specify the partitions. You were almost there in your first post
                        PARTITION BY RANGE (monthly_interval)
                        (
                        partition p_AUG12 values less than (to_date('08-2012','mm-yyyy')),
                        partition p_SEP12 values less than (to_date('09-2012','mm-yyyy')),
                        partition p_OCT12 values less than (to_date('10-2012','mm-yyyy'))
                        )
                        Except that code above has AUGUST as containing data LESS THAN AUGUST instead of LESS THAN SEPTEMBER.
                        And even though the day will implicitly be '01' I would specify it explicitly.
                        partition p_PRIOR_TO_2012 values less than (to_date('01-01-2012','dd-mm-yyyy')),
                        partition p_JAN_2012 values less than (to_date('02-01-2012','dd-mm-yyyy')),
                        partition p_FEB_2012 values less than (to_date('03-01-2012','dd-mm-yyyy')),
                        . . .