2 Replies Latest reply on Feb 14, 2014 5:07 PM by JohnWatson

    LIST PARTITION

    c24f4c2e-540e-4982-b5e2-c4b8829e1636

      If we partition by list partition method we can partition one table by either method

       

      Will there be impact on SELECT query if we combine multiple values and make single partition or if we create partition for all value?

       

       

      CREATE TABLE WF_USER_QUEUE (...)

           PARTITION BY LIST (WF_PROC_STEP_INST_STATUS_CODE)

           (

           PARTITION status_actives VALUES ('ID','IP','UL','RO','HD','FD','HL','HS','AC'),

           PARTITION status_co_hi VALUES ('CO'),

           PARTITION status_null VALUES (NULL),

           PARTITION status_unknown VALUES (DEFAULT)

           );

       

      CREATE TABLE WF_USER_QUEUE (...)

           PARTITION BY LIST (WF_PROC_STEP_INST_STATUS_CODE)

           (

           PARTITION status_id VALUES ('ID'),

           PARTITION status_ip VALUES ('IP'),

           PARTITION status_r VALUES ('HD'),

           PARTITION status_a VALUES ('FD'),

           PARTITION status_j VALUES ('HL'),

           PARTITION status_e VALUES ('HS'),

           PARTITION status_e VALUES ('AC'),

           PARTITION status_co_hi VALUES ('CO'),

           PARTITION status_null VALUES (NULL),

           PARTITION status_unknown VALUES (DEFAULT)

           );

        • 1. Re: LIST PARTITION
          rp0428

          Will there be impact on SELECT query if we combine multiple values and make single partition or if we create partition for all value?

          Well I certainly hope so, don't you? After all improving performance is one of the main reasons partitioning was introduced.

           

          So 'if we create partition for all value' there is ONE partition. So how is that any different from an unpartitioned table? It isn't.

          PARTITION status_actives VALUES ('ID','IP','UL','RO','HD','FD','HL','HS','AC'), 

               PARTITION status_co_hi VALUES ('CO'),

          So will it make a difference if you put ID/IP/UL/RO/HD/FD/HL/HS/AC into partitions of their own?

           

          Who knows - it depends on how many rows have each of those values. If only one row has each value it won't make any difference at all.

          • 2. Re: LIST PARTITION
            JohnWatson

            It may be more complicated than this. You are looking only at the table partitioning. What about the index partitioning strategy? For example, performance of local non-prefixed indexes may degrade as you add more partitions. Global indexes should not.