11 Replies Latest reply: May 3, 2013 2:08 PM by 849776 RSS

    range partiotion using interval partitioning

    849776
      Hi all,

      I am trying to create a partitioned table so that a number (which date converted to number ) partition is created on inserting a new row for release_date column.
      But please note that release_date column is having number data type (as per design) and people want to create an interval based partition on this.

      Any work around for this?

      They want data type NOT to be altered.

      create table product(
      prod_id number,
      prod_code varchar2(3),
      release_date number)
      partition by range(release_date)
      interval(NUMTOYMINTERVAL (1,'MONTH'))
      (partition p0 values less than (20120101))


      Thanks in advance
        • 1. Re: range partiotion using interval partitioning
          rp0428
          >
          I am trying to create a partitioned table so that a number (which date converted to number ) partition is created on inserting a new row for release_date column.
          But please note that release_date column is having number data type (as per design) and people want to create an interval based partition on this.
          >
          You can't use interval partitioning on the NUMBER column but you can add a VIRTUAL column based on it that uses DATE datatype.
          create table product( 
          prod_id number, 
          prod_code varchar2(3), 
          release_date number,
          rel_date DATE as (to_date(to_char(release_date), 'yyyymmdd')) VIRTUAL
          )
          partition by range(rel_date)
          interval(NUMTOYMINTERVAL (1,'MONTH')) 
          (
             partition p0 values less than (to_date('20120101', 'yyyymmdd')) 
          )
          The virtual column is a metadata only column (i.e. no data is stored for it).

          NOTE: you will need to modify your queries to use the 'REL_DATE' column in order to get partition pruning:
          insert into product (prod_id, prod_code, release_date) values (1,'abc', 20110502)
          
          insert into product (prod_id, prod_code, release_date) values (1,'abc', 20120502)
          
          -- this query does NOT prune
          select * from product where release_date < 20120101
          
          |   0 | SELECT STATEMENT    |         |     1 |    38 |     4   (0)| 00:00:01 |       |       |
          
          |   1 |  PARTITION RANGE ALL|         |     1 |    38 |     4   (0)| 00:00:01 |    1 |1048575|
          
          |*  2 |   TABLE ACCESS FULL | PRODUCT |     1 |    38 |     4   (0)| 00:00:01 |    1 |1048575|
          
          -- this query DOES prune
          select * from product where rel_date < to_date('20120101', 'yyyymmdd')
          
          |   0 | SELECT STATEMENT       |         |     1 |    38 |     3   (0)| 00:00:01 |       |       |
          
          |   1 |  PARTITION RANGE SINGLE|         |     1 |    38 |     3   (0)| 00:00:01 |     1 |     1 |
          
          |*  2 |   TABLE ACCESS FULL    | PRODUCT |     1 |    38 |     3   (0)| 00:00:01 |     1 |     1 |
          • 2. Re: range partiotion using interval partitioning
            849776
            thanks for ur replay
            but i have to use release_date column in where clause.so that i can use partition prunning.i cannot use virtual column .Is there any other way to get the desired result

            Thanks for ur time in advance
            • 3. Re: range partiotion using interval partitioning
              rp0428
              >
              but i have to use release_date column in where clause.so that i can use partition prunning.i cannot use virtual column .Is there any other way to get the desired result
              >
              No - not with INTERVAL partitioning

              Use RANGE partitioning on your column
              PARTITION BY RANGE (release_date)
              (
              partition p_prior_to_2012 less than (20120101),
              partition p_2012 values less than (20130101),
              partition p_2013 values less than (20140101),
              partition p_2014 values less than (20150101)
              )
              Although you can use INTERVAL partition with NUMBER columns there is no way to express the number of days without using formulas to account for leap years.

              See the VLDB and Partitioning Guide
              http://docs.oracle.com/cd/E18283_01/server.112/e16541/part_admin001.htm#BAJHFFBE
              >
              For interval partitioning, the partitioning key can only be a single column name from the table and it must be of NUMBER or DATE type. The optional STORE IN clause lets you specify one or more tablespaces into which the database stores interval partition data using a round-robin algorithm for subsequently created interval partitions.
              • 4. Re: range partiotion using interval partitioning
                849776
                thanks for your reply.

                Can we alter table and add reference partition to the existing table or exchange reference partition
                • 5. Re: range partiotion using interval partitioning
                  Solomon Yakobson
                  846773 wrote:
                  but i have to use release_date column in where clause.so that i can use partition prunning.i cannot use virtual column .Is there any other way to get the desired result
                  That's what happens when you store dates as numbers or strings. Change column release_date data type to DATE and you'll be able to use interval partitioning.

                  SY.
                  • 6. Re: range partiotion using interval partitioning
                    849776
                    Thanks for your reply

                    Can we alter table and add subpartition to the existing partition table (range partition) .

                    Thanks in advance
                    • 7. Re: range partiotion using interval partitioning
                      rp0428
                      I have NO IDEA what it is you are now asking.

                      First you say this:
                      >
                      Can we alter table and add reference partition to the existing table or exchange reference partition
                      >
                      And now this:
                      >
                      Can we alter table and add subpartition to the existing partition table (range partition) .
                      >
                      Care to start over and tell us what it is now that you want to do?

                      An apple is not an orange. A standard table is NOT a partitioned table.

                      If you want to create a partitioned table I showed you how to do it.

                      A non-partitioned table stores ALL of its data (except possible for LOBs) in a single segment.

                      A partitioned table stores ALL of its data in a separate segment for each partition; the table itself has no data.

                      A subpartitioned table stores ALL of its data in a separate segment for each subpartition; neithe the table itself, nor any of the partitions have any data.

                      You can easily use EXCHANGE PARTITION to move the data from a standard table to a partitioned table that has ONE partition if that partition is designed to hold ALL of the data based on the partitioning key.

                      But you can't reuse the existing segment of a regular table for a partitioned table because the partitioned table needs to split the data among the partitions.

                      You can use DBMS_REDEFINITION to redefine the existing table to a partitioned table. That performs the operation on-line but the operation being performed still creates a new partitioned table and splits up the data.

                      Offline you can use CTAS to create the partitioned table and split the data in one operation.
                      • 8. Re: range partiotion using interval partitioning
                        849776
                        Thanks for your reply.

                        one last question:

                        Can we add multiple columns in partition by reference clause?
                        • 9. Re: range partiotion using interval partitioning
                          rp0428
                          You haven't answered my questions yet.
                          • 10. Re: range partiotion using interval partitioning
                            Solomon Yakobson
                            846773 wrote:
                            Can we add multiple columns in partition by reference clause?
                            Reference partitioning enables the partitioning of two tables that are related to one another by referential constraints. The partitioning key is resolved through an existing parent-child relationship, enforced by enabled and active primary key and foreign key constraints. Therefore you can't add/subtract any columns to it.

                            SY.
                            • 11. Re: range partiotion using interval partitioning
                              849776
                              Thanks solomon :)

                              can you please explain which (local and global) index is performs better in reference partiton(I googled my question,but i am getting different answers)?

                              Thanks