2 Replies Latest reply: Feb 14, 2014 9:07 AM by JohnWatson RSS

LIST PARTITION

c24f4c2e-540e-4982-b5e2-c4b8829e1636 Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points