10 Replies Latest reply on Aug 10, 2015 4:55 PM by rp0428

    help on partitions

    user586

      Hi,

       

       

      I am unable to create composite list hash partition using CTAS. am getting the error as " missing or invalid option".

      could you please guide us to create partition using CTAS

       

       

      create table P_LIST_HASH

      partition by hash(USERID) PARTITIONS 4

          SUBPARTITION BY list (divindicator)

          (

                  partition hp1

                  (

                          subPARTITION hp1_p1 VALUES ( 0 ),

                          subPARTITION hp1_p2 VALUES ( 1 ),

                          subPARTITION hp1_p3 VALUES ( 2 )

                  ),

                 partition hp2

                 (

                         subPARTITION hp2_p1 VALUES ( 0 ),

                         subPARTITION hp2_p2 VALUES ( 1 ),

                         subPARTITION hp2_p3 VALUES ( 2 )

                 ),

                     partition hp3

                 (

                         subPARTITION hp3_p1 VALUES ( 0 ),

                         subPARTITION hp3_p2 VALUES ( 1 ),

                         subPARTITION hp3_p3 VALUES ( 2 )

                 )

        )

      as select * from GL_USERS;

       

      Thanks in advance

        • 1. Re: help on partitions
          sacoefrancis

          Try this:

          create table P_LIST_HASH

          partition by hash(USERID)

              SUBPARTITION BY list (divindicator)

              (

                      partition hp1

                      (

                              subPARTITION hp1_p1 VALUES ( 0 ),

                              subPARTITION hp1_p2 VALUES ( 1 ),

                              subPARTITION hp1_p3 VALUES ( 2 )

                      ),

                     partition hp2

                     (

                             subPARTITION hp2_p1 VALUES ( 0 ),

                             subPARTITION hp2_p2 VALUES ( 1 ),

                             subPARTITION hp2_p3 VALUES ( 2 )

                     ),

                         partition hp3

                     (

                             subPARTITION hp3_p1 VALUES ( 0 ),

                             subPARTITION hp3_p2 VALUES ( 1 ),

                             subPARTITION hp3_p3 VALUES ( 2 )

                     )

            )

          as select * from GL_USERS

          • 2. Re: help on partitions
            user586

            I tried to remove the partitions 4 then i execute it, we are getting the below error.

             

            In GL_USERS total 22M data exists.

             

            ORA-14400: inserted partition key does not map to any partition

             

             

            Thx,

            • 3. Re: help on partitions
              sacoefrancis

              Yor partitioning key is not mapped with values in GL_USERS..

               

              check the values for userid  and divindicator in GL_USERS table  and then create partition.. it should cover all the values the in the table..

               

              values in the table and the your  partition values not matching..

              • 4. Re: help on partitions
                Karthick2003

                I don't think oracle offers HASH-LIST composite partition. For a composite partition the parent partition should be RANGE or LIST and can't be HASH.

                 

                I have not checked the document yet, but you can verify the same in Oracle document.

                • 5. Re: help on partitions
                  Chris Hunt

                  Well, your original statement failed because you told it to create four partitions, but only listed three of them. And your statement is failing now because you've told it to subpartition based on divindicator being either 0, 1 or 2 and

                  (presumably) there are rows in your table where this column has some other value.

                  • 6. Re: help on partitions
                    Jonathan Lewis

                    Corrrect,

                     

                    list/hash is a valid option, but hash/list is not.

                    For the OP a google search on "oracle composite partition options" produced the following link near the top.  (The top link was a paid one for a company selling Office (no, not MS-Office) Partitioning - which rather missed the point).

                     

                    http://www.oracle.com/technetwork/database/options/partitioning/twp-partitioning-11gr2-2009-09-130569.pdf

                     

                    Regards

                    Jonathan Lewis

                     

                    P.S.  As a related topic, the OP might like to take note ofe the "subpartition template" clause which allows one to avoid writing a repetitive subpartition clause.

                    • 7. Re: help on partitions
                      user586

                      Hi Jonanthan,

                       

                        As per Tom's thread we can go for hash-list partitioning on 11g onwards.

                       

                      https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:6836903900346905170

                      • 8. Re: help on partitions
                        user586

                        Hi Chris,

                         

                             You are right. In db table GL_USERS, having another value divindicator 3, I added in the partition table and able to create hash-list partition successfully.

                         

                             Thanks for pointing the exact error.

                         

                        Thx

                        • 9. Re: Re: help on partitions
                          Jonathan Lewis

                          Thanks for the link.

                           

                          Even Tom makes mistakes very occasionally - but there's no arguing with a working example

                          I had a syntax error in the statement I wrote to confirm the document - and didn't notice it because I was expecting the statement to fail anyway.

                           

                          If you're not worried about naming, here's a (corrected) example using the template syntax:

                           

                          create table t1

                          (

                                  object_name,

                                  object_type,

                                  created

                          )

                          partition by hash(object_name)

                          subpartition by list(object_type)

                          subpartition template (

                                  subpartition sp1 values ('TABLE'),

                                  subpartition sp2 values ('INDEX'),

                                  subpartition sp3 values (default)

                          )

                          partitions 4

                          as

                          select  object_name, object_type, created

                          from    all_objects

                          ;

                           

                          It's a little quirk of the unnamed hash partitions that the sp1, sp2, sp3 that would normally be attached to the partition name also disappear and the subpartition names include sequence numbers.

                           

                          Regards

                          Jonathan Lewis

                          • 10. Re: help on partitions

                              I am unable to create composite list hash partition using CTAS.

                            Although you marked this correct I would like to know what use case you have where a hash-range partitioning scheme will actually be of any benefit?

                             

                            It can't be for data management/archiving since a hash parittioning will have a range of values.

                             

                            Can you provide info about your use case?

                             

                            Can you tell us WHY you have chosen hash-range partitioning? I would like to see some test results that show that scheme will actually benefit your use case.