5 Replies Latest reply: Dec 5, 2012 12:20 AM by user8941550 RSS

    Interval Partitioned to Range. Created Daily Partitions from Monthly Part.

    user8941550
      Hi,

      We have a huge table with millions of rows.
      I need to change it to a Range Partitioned.

      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'))

      Now as Table A is Interval Partitioned Monthly as shown above.
      WHEREAS Partition Key is DATE.

      So when I create the new Range Partitioned Table and use a script we already have to create partitions in advance. It uses the Partition Key as Date and creates Daily Partition.

      WHAT are the consequences if I apply daily partitions instead of the monthly ones on this table after converting it to a Range Partitioned table.
      Please suggest..
        • 1. Re: Interval Partitioned to Range. Created Daily Partitions from Monthly Part.
          rp0428
          >
          Now as Table A is Interval Partitioned Monthly as shown above.
          WHEREAS Partition Key is DATE.
          >
          No - that is NOT a monthly partitioned table example. That is a table partitioned by 30 day intervals. A monthly partitioned table would use the 'NUMTOYMINTERVAL' function: INTERVAL(NUMTOYMINTERVAL(1, 'MONTH').

          A daily interval would be: INTERVAL( NUMTODSINTERVAL(1,'DAY'))
          >
          So when I create the new Range Partitioned Table and use a script we already have to create partitions in advance. It uses the Partition Key as Date and creates Daily Partition.

          WHAT are the consequences if I apply daily partitions instead of the monthly ones on this table after converting it to a Range Partitioned table
          >
          Well the obvious answer is that you wind up with more partitions to manage.

          The main question is what is the problem you are trying to solve? Don't partition just to be partitioning. Do it to solve a problem.

          Do you have a table maintenance issue?
          Do you need to offload older data? How often? By year? by month?
          Do you have a performance issue?
          What filter predicates are normally used?
          Do the queries normally include the 'CreationDate' column?

          What indexes do you need have on the table? Will they be global or local? Global indexes complicate the maintenance operations; local indexes are better for that.

          Without more information about what goals you are trying to achieve it is hard to provide any specific advice.
          • 2. Re: Interval Partitioned to Range. Created Daily Partitions from Monthly Part.
            user8941550
            Thanks RP0428,

            I understand it's not a monthly partition.
            We are reverting to Range based partitioning instead of Interval based as we need to implement xml indexes.

            I think I need to create virtual column based partitioning for implementing 30 days partition as column is a date column. Any advice on that..

            Thanks..
            • 3. Re: Interval Partitioned to Range. Created Daily Partitions from Monthly Part.
              rp0428
              >
              I understand it's not a monthly partition.
              >
              You're the one that said it was a montly partition.
              >
              We are reverting to Range based partitioning instead of Interval based as we need to implement xml indexes.
              >
              Please explain that. Interval partitioning IS range partioning. And what does the need to implement xml lndexes have to do with it. Please provide an example of an 'xml index'.
              >
              I think I need to create virtual column based partitioning for implementing 30 days partition as column is a date column. Any advice on that..
              >
              No you don't - the code you posted shows that isn't true; that code IS partitioning at 30 day intervals on a date column.
              >
              Any advice
              >
              I gave you advice above and you didn't answer ANY of the six questions I ask you about what it is you are actually trying to do and why you think partitioning is even needed. What I ask and said above still applies before I, or anyone else can provide much specific help.
              >
              The main question is what is the problem you are trying to solve? Don't partition just to be partitioning. Do it to solve a problem.

              Do you have a table maintenance issue?
              Do you need to offload older data? How often? By year? by month?
              Do you have a performance issue?
              What filter predicates are normally used?
              Do the queries normally include the 'CreationDate' column?

              What indexes do you need have on the table? Will they be global or local? Global indexes complicate the maintenance operations; local indexes are better for that.

              Without more information about what goals you are trying to achieve it is hard to provide any specific advice.
              • 4. Re: Interval Partitioned to Range. Created Daily Partitions from Monthly Part.
                user8941550
                Hi rp0428 ,

                Thanks for the patience and reply.

                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??


                Now the Table A has Interval Partition and if I change it to Range Partititon(No Interval Partition). Then I need to replicate 30 days partition. I am unable to do that. Any suggestions..
                Thanks..