11 Replies Latest reply on Aug 19, 2016 7:02 AM by mariakatosvich

    Unable to use hit sub-partition on month column

    3242062

      Hi,

       

      Due to a requirement, I've range partitioned a 20 GB table on a date field DATE_1 (which will have a retention of 30 days), and sub-partitioned on another date field DATE_2 (which will have last 12 months' records).

      I've created a virtual column MONTH_2 to extract month (number) from DATE_2 on the table. The LIST sub-partitioning was done on this MONTH_2 column.

      Thus we will have 12 sub-partitions on each of the 30 partitions.

      Each sub-partition can contain max. up to 5 GB data.

       

      Note: We have an inbuilt partition manager package that does not support multi-column partitioning, so I avoided it. Also, the data within the two DATE fields are not in sync., thus when I tried using a RANGE-RANGE partition-sub-partitioning, the INSERT operations failed miserably.

       

      We've created simple view over the above table. The MONTH_2 field is exposed for reference. The view selects latest DATE_1 value. Thus SELECT * FROM <<view>>; gives PARTITION RANGE SINGLE as table is partitioned on DATE_1 field.

       

      Now, from our JAVA web-application, we have the facility to drill (click) on DATE_2 value and open another window with details.

      The PROBLEM is while clicking on DATE_2 field, we're not able to hit the sub-partition as its on month value instead of the date.

      Thus, the plan shows sub-PARTITION LIST ALL, while I expect it to show sub-PARTITION LIST SINGLE.

       

      My QUESTION is to help me on writing a query to trick the compiler on selecting sub-PARTITION LIST SINGLE instead of ALL.

      Also, if we CANNOT hit the sub-partition, is there any way we can better the performance by using INDEXES or PARALLELISM?

       

      Please help..

       

      -Pelom

        • 2. Re: Unable to use hit sub-partition on month column
          AndrewSayer

          If both partitioning and subpartitioning keys are based on the same column why not just partition by it with a finer range? You should easily be able to use interval partitioning by months, or manually do the partitioning if you so wish.

          • 3. Re: Unable to use hit sub-partition on month column
            3242062

            @Andrew - partition key is DATE1, sub-partition is on DATE2. Let me paste the sample DDL ...

             

            --***********************************************
            --TABLE CREATION STATEMENT
            --***********************************************
            CREATE TABLE M_DTX
            (
            R_ID NUMBER
            (3),
            R_AMT NUMBER
            (5),
            DATE1 DATE
            ,
            DATE2 DATE
            ,
            VC NUMBER
            (2) GENERATE ALWAYS AS (EXTRACT(MONTH FROM DATE2))
            )
            PARTITION BY RANGE (DATE1)
            SUBPARTITION
            BY LIST (VC)
            SUBPARTITION TEMPLATE
            (
            SUBPARTITION M1
            VALUES (1),
            SUBPARTITION M2
            VALUES (2),
            SUBPARTITION M3
            VALUES (3),
            SUBPARTITION M4
            VALUES (4),
            SUBPARTITION M5
            VALUES (5),
            SUBPARTITION M6
            VALUES (6),
            SUBPARTITION M7
            VALUES (7),
            SUBPARTITION M8
            VALUES (8),
            SUBPARTITION M9
            VALUES (9),
            SUBPARTITION M10
            VALUES (10),
            SUBPARTITION M11
            VALUES (11),
            SUBPARTITION M12
            VALUES (12)
            TABLESPACE M_DATA
            )
            (
            PARTITION M_DTX_2015060100 
            VALUES LESS THAN (
            TO_DATE
            (' 2015-06-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
            ) SEGMENT CREATION DEFERRED
            PCTFREE
            10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
            STORAGE
            ( INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
            PCTINCREASE
            0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
            ) TABLESPACE M_DATA);

            --******************************************
            --VIEW ON TOP OF M_DTX:
            --******************************************
            CREATE OR REPLACE VIEW v_dtx AS
            SELECT r_id, TRUNC(date2) date2_dd, vc, SUM(r_amt) amt
            FROM m_dtx WHERE date1 = TRUNC(sysdate)
            GROUP BY r_id, TRUNC(date2), vc;

            --******************************************
            --QUERY FIRED FROM WEB-APPLICATION (AFTER CLICKING ON date2_DD):
            --******************************************
            SELECT * FROM m_dtx WHERE date1 = trunc(sysdate) AND date2 = ''date2_dd'';
            --this is where its bypassing the sub-partition as I could not substitute month or VC ...

            • 4. Re: Unable to use hit sub-partition on month column

              The PROBLEM is while clicking on DATE_2 field, we're not able to hit the sub-partition as its on month value instead of the date.

              Thus, the plan shows sub-PARTITION LIST ALL, while I expect it to show sub-PARTITION LIST SINGLE.

              Your 'expectations' are wrong.

               

              The plan will only show 'single' when the optimizer can determine 'statically' (i.e. without examining any data) what subpartition the data will be in.

               

              Any given DATE_2 value can represent a row that is in ANY of the 12 subpartitions. One row with that DATE_2 value might be in subpartition 1 the next in subpartition 7. It all depends on the month of the DATE_2 value.

              • 5. Re: Re: Unable to use hit sub-partition on month column
                AndrewSayer

                My mistake, sorry.

                 

                If you wish for the optimizer to know which subpartitions it has to access then you'll have to filter on that column. Either push the virtual column to the view so that it can be selected or appropriately use a column which you do filter on as the subpartition key.

                 

                explain plan for
                SELECT * FROM m_dtx WHERE date1 = trunc(sysdate) AND date2 = to_date(:x,'dd/mm/yyyy') and v2 = EXTRACT(MONTH FROM to_date(:x,'dd/mm/yyyy'));
                
                PLAN_TABLE_OUTPUT
                _________________________________________________________________________________________________________
                Plan hash value: 938710559
                
                
                ------------------------------------------------------------------------------------------------
                | Id  | Operation              | Name  | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
                ------------------------------------------------------------------------------------------------
                |  0 | SELECT STATEMENT      |      |    1 |    57 |    2  (0)| 00:00:01 |      |      |
                |  1 |  PARTITION RANGE SINGLE|      |    1 |    57 |    2  (0)| 00:00:01 |  KEY |  KEY |
                |  2 |  PARTITION LIST SINGLE|      |    1 |    57 |    2  (0)| 00:00:01 |  KEY |  KEY |
                |*  3 |    TABLE ACCESS FULL  | M_DTX |    1 |    57 |    2  (0)| 00:00:01 |  KEY |  KEY |
                ------------------------------------------------------------------------------------------------
                
                
                Predicate Information (identified by operation id):
                ---------------------------------------------------
                
                
                  3 - filter("DATE2"=TO_DATE(:X,'dd/mm/yyyy') AND "DATE1"=TRUNC(SYSDATE@!))
                
                
                Note
                -----
                  - dynamic statistics used: dynamic sampling (level=2)
                

                 

                Why can't you fix your "inbuilt partition manager package" so that you are able to have range sub partitions?

                • 6. Re: Unable to use hit sub-partition on month column
                  AndrewSayer

                  It seems I've been sucked into the trap of assuming partitioning was the correct strategy. How much data do you expect exists for a particular value of date_2? Its possible an index would work well for you instead of further partitioning. If it was locally partitioned then both date filters can be evaluated via the index. You may not even need the index,     consider if the aggregation query will cache blocks that you could benefit from anyway (depends on whether direct reads were used)

                  • 7. Re: Unable to use hit sub-partition on month column
                    Jonathan Lewis

                    rp0428,

                     

                    Any given DATE_2 value can represent a row that is in ANY of the 12 subpartitions. One row with that DATE_2 value might be in subpartition 1 the next in subpartition 7. It all depends on the month of the DATE_2 value.

                     

                    Technically not true because vc is defined as "extract(month from date_2)" so date_2 determines the subpartition.

                     

                    My first thought was that the optimizer could (in principle) manage to do (sub)partition elimination for this one because of transitive closure between constraints and predicates (see https://jonathanlewis.wordpress.com/2008/12/03/predicate-problems/ )

                     

                    We could add a not null declaration to date_2 and the following check constraint to the table, then keep our fingers crossed:

                        alter table m_dtx add constraint md_ck_vc check (vc = extract(month from date2));

                     

                     

                    In theory the optimizer might be coded to check that there is a constraint associating the partitioning column with an incoming predicate and create a new predicate, viz;

                    date_2 = date'01-Jun-2016' and check constraint => vc = extract(month from date'01-jun-2016') and date_2 = date'01-Jun-2016'.

                     

                    I've just checked it on 12.1.0.2 - and it doesn't work. In fact it's worse than that because if you add the constraint you can't do "insert (list of columns) as select" because check constraint fails.

                     

                    Regards

                    Jonathan Lewis

                    • 8. Re: Unable to use hit sub-partition on month column
                      AndrewSayer

                      Jonathan, I also considered transitive closure as an option . Maybe it would work  if the virtual column used a user defined deterministic function . I imagine there are rules about what could be applied to carry out the transitive closure, can it even work with functions or are simple expressions only supported?

                      • 9. Re: Unable to use hit sub-partition on month column
                        Pavan Kumar

                        I was thinking for some functional index -- as it will be having less cardinality since twelve months -- only month is extracted a bitmap, function like >0 (just to get use of index)

                         

                        - Pavan Kumar N

                        • 10. Re: Unable to use hit sub-partition on month column
                          AndrewSayer

                          You just resurrected a post that has been dead for a month to add some information about your specific solution on a mysql database. OP is using  Oracle Database and should just rewrite the SQL to reference the virtual column being subpartitioned by.

                           

                          What you described with adding the month column is pretty much what OP did, but OP didn't require the space to store the extra column (it's virtual) . The only remaining thing to do is to reference the virtual column in the queries , something that looked possible via transitive closure.

                           

                          No idea why you said it's not wise to name the subpartitions?

                          The queries probably shouldn't specify the partition names.

                          • 11. Re: Unable to use hit sub-partition on month column
                            mariakatosvich

                            You should easily be able to use interval partitioning by months, or manually do the partitioning if you so wish.