6 Replies Latest reply: May 23, 2013 10:35 AM by 688337 RSS

    INTERVAL PARTITION (YYYYMM)  Number ?

    688337
      I have a table with the format
      CREATE TABLE TABLE_NAME
      (
      YR_MO    NUMBER,
      COL2      NUMBER, 
      COL3      VARCHAR2(100)
      PARTITION BY RANGE (YR_MO)
      INTERVAL (1)
      ( PARTITION P0 VALUES LESS THAN (200310) )
      )
      The data in the YR_MO columns is stored in YYYYMM format.
      It seems to be working great when the insert for 200310,200311
      but for 200312 the high Value is 200313 , which is not a valid Year and Month , where as it should be 200401
      becuase the next YYYYMM for 200312 will be 200401 .

      column datatype cannot be modified to Date

      How can I set the interval partition so that my high value always gets increased by 1 month .

      Thanks in advance.
        • 1. Re: INTERVAL PARTITION (YYYYMM)  Number ?
          Tubby
          user10586521 wrote:
          I have a table with the format
          CREATE TABLE TABLE_NAME
          (
          YR_MO    NUMBER,
          COL2      NUMBER, 
          COL3      VARCHAR2(100)
          PARTITION BY RANGE (YR_MO)
          INTERVAL (1)
          ( PARTITION P0 VALUES LESS THAN (200310) )
          )
          The data in the YR_MO columns is stored in YYYYMM format.
          It seems to be working great when the insert for 200310,200311
          but for 200312 the high Value is 200313 , which is not a valid Year and Month , where as it should be 200401
          becuase the next YYYYMM for 200312 will be 200401 .

          column datatype cannot be modified to Date

          How can I set the interval partition so that my high value always gets increased by 1 month .

          Thanks in advance.
          Not sure I see the confusing part here, you have a number column you've asked to have a new partition added for and you asked to have that number incremented by 1 ... How is Oracle supposed to know that you are actually dealing with a date when you refuse to tell it that?

          You say the data type "cannot" be modified, but I find that very hard to believe.

          You have a couple options as I see (others may have more for you).
          1) use the proper data type
          2) create your partitions manually ... Oracle cannot divine things
          3) hack it ... use a virtual column (to_date the number column) and partition on that ... this is just a whim of an idea, you'd need to do some serious investigations as to the ramifications of this given your queries (you could probably set up a view, etc... again ... just a hack, use the proper data type!) .

          Cheers,
          • 2. Re: INTERVAL PARTITION (YYYYMM)  Number ?
            688337
            Thank tubby

            Is there is a way we can use
            INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
            ( PARTITION P0 VALUES LESS THAN (TO_CHAR(TO_DATE('200310','YYYYMM'),'YYYYMM'))
            or something like that so that high value is always incrementing in YYYYMM format.

            I wish we can change the datatype of the YR_MO.

            Thanks again.
            • 3. Re: INTERVAL PARTITION (YYYYMM)  Number ?
              Greg Spall
              >
              VALUES LESS THAN (xxx) )
              >
              but for 200312 the high Value is 200313 ,
              I'm not seeing the issue.

              200312 is < 200313 ??

              Where else is going to store 200312? The threshold amounts are for values LESS THAN .. not LESS THAN OR EQUAL TO.

              So 200301 is stored in partition: 200302.
              200302 is stored in partition: 200303 ... etc.

              Try this:
              CREATE TABLE junk
              (
              YR_MO    NUMBER
              )
              PARTITION BY RANGE (YR_MO)
              INTERVAL (10)
              ( PARTITION P0 VALUES LESS THAN (10) )
              ;
              
              insert into junk values (13);
              insert into junk values (25);
              insert into junk values (30);
              insert into junk values (49);
              commit;
              
              set linesize 200
              select partition_name, high_value from dba_tab_partitions where table_name = 'JUNK'
              results
              PARTITION_NAME                 HIGH_VALUE                                        
              ------------------------------ --------------------------------------------------
              P0                             10                                                
              SYS_P349                       20                                                
              SYS_P350                       30                                                
              SYS_P351                       40                                                
              SYS_P352                       50                                                
              
              5 rows selected.
              and note item #30 .. where it's stored.
              select * from junk partition (SYS_P350);
                   YR_MO
              ----------
                      25
              1 row selected.
              
              select * from junk partition (SYS_P351);
                   YR_MO
              ----------
                      30
              1 row selected.
              so yeah .. it's working perfectly ...
              for numbers.

              If you want it to work properly for DATEs ... you need to use a date.
              • 4. Re: INTERVAL PARTITION (YYYYMM)  Number ?
                Tubby
                user10586521 wrote:
                Thank tubby

                Is there is a way we can use
                INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
                ( PARTITION P0 VALUES LESS THAN (TO_CHAR(TO_DATE('200310','YYYYMM'),'YYYYMM'))
                or something like that so that high value is always incrementing in YYYYMM format.

                I wish we can change the datatype of the YR_MO.

                Thanks again.
                What's wrong with creating the partitions manually? If someone is telling you "you can't use the proper data type" what's wrong with going back to them and saying "then you can't have interval partitioning"?

                Cheers,
                • 5. Re: INTERVAL PARTITION (YYYYMM)  Number ?
                  rp0428
                  >
                  Is there is a way we can use
                  INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
                  ( PARTITION P0 VALUES LESS THAN (TO_CHAR(TO_DATE('200310','YYYYMM'),'YYYYMM'))
                  or something like that so that high value is always incrementing in YYYYMM format.
                  >
                  Sure - just add a VIRTUAL column as Tubby previously suggested and partition on it.
                  CREATE TABLE TABLE_NAME
                  (
                  YR_MO    NUMBER,
                  COL2      NUMBER, 
                  COL3      VARCHAR2(100),
                  YR_MO_DATE DATE as (to_date(to_char(yr_mo), 'yyyymm')) VIRTUAL
                  )
                  PARTITION BY RANGE (YR_MO_DATE)
                  interval(NUMTOYMINTERVAL (1,'MONTH')) 
                  (
                   PARTITION P0 VALUES LESS THAN (TO_DATE('200310','YYYYMM'))
                  )
                  Your queries will need to use the virtual column 'YR_MO_DATE' in the where clauses in order to get partitioning pruning.

                  The virtual column is a metadata only change; no data is stored for that column. You will also need to ensure that your INSERT queries provide an actual column list since you can't provide a value for the VIRTUAL column.
                  • 6. Re: INTERVAL PARTITION (YYYYMM)  Number ?
                    688337
                    Thank you so much for all your suggestion.