8 Replies Latest reply: Aug 26, 2014 9:06 PM by Hemant K Chitale RSS

    Interval Partition creation!!!

    1467096

      I would like to create an interval partition on 11.2.0.4 database and do the following:

       

      1) Create interval partition

      2) Export data from other database

      3) Import data into the partition table.

       

      but while trying to create the partition, I see the following error.

       

      SQL> CREATE TABLE CUSTOMER

        2  (SNAPSHOT_DK                NUMBER(10)    NOT NULL,

        3   SNAPSHOT_DATE              DATE,

        4   CREATED_ON_DK              NUMBER(15),

        5   UPDATED_BY              NVARCHAR2(18) NOT NULL),

        6  )

        7  PARTITION BY RANGE (SNAPSHOT_DATE)

        8  INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'));

      DW_UPDATED_BY              NVARCHAR2(18) NOT NULL),

                                                         *

      ERROR at line 5:

      ORA-00922: missing or invalid option

       

      What i would like to know is: Once the partition is created, i can load the data and system will create the partitions as needed. but the data i am looking to load has last year data as well. I.E,  Jan 2013 to Present - August 2014. Question is, does oracle create last year monthly partitions as well with the above syntax? what's wrong with the above create table syntax?

       

      appreciate if you could please let me know, what am i doing wrong?

       

      Thanks again,

      Rekha.

        • 1. Re: Interval Partition creation!!!
          1512841

          Hi Rekha,

          First, Please check the syntax error. Also i believe you need to mention a base partition to start with. Since you know you will be loading the historic data, you can be cognizant enough to define the range accordingly.

           

          Refer the below sample script:

           

          CREATE TABLE CUSTOMER

              (SNAPSHOT_DK                NUMBER(10)    NOT NULL,

               SNAPSHOT_DATE              DATE,

               CREATED_ON_DK              NUMBER(15),

               UPDATED_BY              NVARCHAR2(18) NOT NULL

              )

              PARTITION BY RANGE (SNAPSHOT_DATE)

              INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))

              (PARTITION P_FIRST VALUES LESS THAN (TO_DATE('01/01/2013','DD/MM/YYYY')));

           

          Thanks,

          Prasanth V

          • 2. Re: Interval Partition creation!!!
            rp0428

            what's wrong with the above create table syntax?

            What's wrong is that you did NOT post the actual DDL you are using.

            SQL> CREATE TABLE CUSTOMER

              2  (SNAPSHOT_DK                NUMBER(10)    NOT NULL,

              3   SNAPSHOT_DATE              DATE,

              4   CREATED_ON_DK              NUMBER(15),

              5   UPDATED_BY              NVARCHAR2(18) NOT NULL),

              6  )

              7  PARTITION BY RANGE (SNAPSHOT_DATE)

              8  INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'));

            DW_UPDATED_BY              NVARCHAR2(18) NOT NULL),

            The column in the DDL is named 'UPDATED_BY'.

             

            You posted an exception for a column named 'DW_UPDATED_BY'.

             

            How is even possible to get an exception on a column that is NOT in the DDL?

            • 3. Re: Interval Partition creation!!!
              1467096

              Prasanth V,

               

              Thank you so much for posting the correct syntax. The onething i dont understand is:

               

              1) I see you creating a P_FIRST Partition with a date of 01/01/2013. I dont understand whats the purpose of this partition?

              2) I will be doing an export and import from other database to this newly created partition. The data i am bringing from other table has 7 years of data.

              3) Will the new data be loaded automatically by creating monthly partitions for each year? appreciated if you could please shed some light on this confusion.

               

              This new partition database is goign to be in a OLTP database and I also have lot of indexes to be created which i will do Global indexes. Is that good option?

               

              Thanks again and apprecaited if you could please clarify the above points.

               

              Rekha.

              • 4. Re: Interval Partition creation!!!
                Hemant K Chitale

                Interval Partitioning requires that at least the "first" partition be defined -- that is what Prasanth is suggesting.

                See http://docs.oracle.com/cd/E18283_01/server.112/e16541/part_admin001.htm#BAJHFFBE

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

                 

                Thereafter, Oracle can automatically create subsequent partitions.

                 

                 

                Hemant K Chitale

                • 5. Re: Interval Partition creation!!!
                  1512841

                  Correct. It requires you to define one partition which holds data less than the date you define.

                   

                  You can find the least date which you will be importing and then define the first partition (some logical name). Then Oracle will automatically create partitions with its own naming convention while importing data greater than the defined date.

                   

                  Prasanth V

                  • 6. Re: Interval Partition creation!!!
                    1467096


                    Prasanth V and Hemant K Chitale, Thank you so much for your assistance. I have one last question and would apprecaite if you could clarify this for me:

                    1) I have indexes on the table (not on the partition range column) but on other columsn.
                    2) should these indexes need to be created with global clause (This system is an OLTP system)
                    3) Any other thing to consider before creating indexes?


                    Thanks again for your continued assistance.

                    Rekha

                    • 7. Re: Interval Partition creation!!!
                      rp0428
                      Prasanth V and Hemant K Chitale, Thank you so much for your assistance. I have one last question and would apprecaite if you could clarify this for me:

                      1) I have indexes on the table (not on the partition range column) but on other columsn.
                      2) should these indexes need to be created with global clause (This system is an OLTP system)
                      3) Any other thing to consider before creating indexes?

                      Indexes are a solution to a problem. If you don't have a problem you don't need an index.

                       

                      If you DO need an index then whether a local or global index is appropriate depends on the PROBLEM you are trying to solve by adding the index.

                       

                      See the VLDB and partitioning guide for info about local/global indexes and how to choose an appropriate one.

                      Very Large Databases (VLDB)

                      • 8. Re: Interval Partition creation!!!
                        Hemant K Chitale

                        Indexes are defined to meet query patterns.  Without knowing how your data is accessed we cannot advice whether you need indexes or not and if you need indexes whether they should be global or local.

                         

                        Hemant K Chitale