11 Replies Latest reply: Jul 8, 2014 1:07 AM by Martin Preiss RSS

    Manual Partitioning - with check constraints for pruning

    user2282685

      I've got a client who won't pay for real partitioning, but has a single monster table on a data warehouse that needs to be broken into pieces for optimal performance of queries.

       

      What I would like to do is manually partition the table, create a union all join view that sits on top, then have oracle prune the execution plans of queries to allow me to only hit the tables I need to to fulfill the result set.

       


      -- Create table
      create table a_tbl
      (
        col1 varchar2(20),
        col2 varchar2(20),
        col3 varchar2(20),
        col4 varchar2(20)
      )
      tablespace AAA_DATA;

      -- Create/Recreate check constraints
      alter table a_tbl
        add constraint a_tbl_restrict
        check (col1='aaa');
       
      create table b_tbl
      (
        col1 varchar2(20),
        col2 varchar2(20),
        col3 varchar2(20),
        col4 varchar2(20)
      )
      tablespace AAA_DATA;

      -- Create/Recreate check constraints
      alter table b_tbl
        add constraint b_tbl_restrict
        check (col1='bbb');
       
      create table c_tbl
      (
        col1 varchar2(20),
        col2 varchar2(20),
        col3 varchar2(20),
        col4 varchar2(20)
      )
      tablespace AAA_DATA;

      -- Create/Recreate check constraints
      alter table c_tbl
        add constraint c_tbl_restrict
        check (col1='ccc');
       
      alter table A_TBL
        add constraint a_tbl_pk primary key (COL1, COL2)
        using index
        tablespace AAA_INDEX;

      alter table B_TBL
        add constraint b_tbl_pk primary key (COL1, COL2)
        using index
        tablespace AAA_INDEX;

      alter table C_TBL
        add constraint c_tbl_pk primary key (COL1, COL2)
        using index
        tablespace AAA_INDEX;
       
      create view abc_v as
      select * from a_tbl
      union all
      select * from b_tbl
      union all
      select * from c_tbl;

       

       

      When I execute a query against the view that should eliminate two of the tables, I don't see the pruning happening.

       

      select * from abc_v

      where col1 = 'aaa' and col2 < '100'

      order by col2

       

      SELECT STATEMENT, GOAL = ALL_ROWS   3 1 48

      SORT ORDER BY   3 1 48

        VIEW TAS ABC_V 2 1 48

         UNION-ALL    

          TABLE ACCESS BY INDEX ROWID TAS A_TBL 4 2 96

           INDEX RANGE SCAN TAS A_TBL_PK 3 2

          FILTER    

           TABLE ACCESS BY INDEX ROWID TAS B_TBL 2 1 48

            INDEX RANGE SCAN TAS B_TBL_PK 2 1

          FILTER    

           TABLE ACCESS BY INDEX ROWID TAS C_TBL 2 1 48

            INDEX RANGE SCAN TAS C_TBL_PK 2 1

       

       

      Am I doing something that just isn't possible without "true" partitioning?  Or did I set it up wrong in some way?

       

      Thanks!

       

      Cory Aston

        • 1. Re: Manual Partitioning - with check constraints for pruning
          Frank Kulash

          Hi, Cory,

           

          What did you expect to see in the execution plan, and why?  You're referencing all 3 tables in the query, so all 3 tables will be in the execution plan.

           

          If you can't use real partitioning, then your best bet might be to have one huge table, with indexes to simulate partitoning.  If you have separate tables, then you'll always be incurring some cost in UNIONs.

          • 2. Re: Manual Partitioning - with check constraints for pruning
            rp0428
            Am I doing something that just isn't possible without "true" partitioning?  Or did I set it up wrong in some way?

            You are doing it wrong.

             

            The technique is known as a 'partition view' and the best example I know is still the code/explanation provided by BillyVerreynne in this two year old thread.

            https://forums.oracle.com/forums/thread.jspa?threadID=2368537&tstart=0

            A better option would be a partitioned view. This feature has been introduced with Oracle 7.3 I think it was. It supports partition-like pruning and enables you to have a view on several tables, but only the relevant table being hit for the actual query.

             

            Here's an example on 11.2.0.2. Note how the CBO uses the filter condition NULL is not NULL to remove no-relevant tables from the query.

            • 3. Re: Manual Partitioning - with check constraints for pruning
              Martin Preiss

              Cory,

               

              the concept of partition views is quite ancient - among the top search results in google I get a link to the tuning guide for release 7.3.3: Managing Partition Views - I wasn't aware that these pages still exist. 7.3 was released in 1996.

               

              Since then Oracle has frequently declared that partition views are desupported - but they seem to work still. If I use your example I get the following execution plan:

              --------------------------------------------------------------------------------------------

              | Id  | Operation                       | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

              --------------------------------------------------------------------------------------------

              |   0 | SELECT STATEMENT                |          |     1 |    48 |     1 (100)| 00:00:01 |

              |   1 |  SORT ORDER BY                  |          |     1 |    48 |     1 (100)| 00:00:01 |

              |   2 |   VIEW                          | ABC_V    |     1 |    48 |     0   (0)| 00:00:01 |

              |   3 |    UNION-ALL                    |          |       |       |            |          |

              |   4 |     TABLE ACCESS BY INDEX ROWID | A_TBL    |     1 |    48 |     1   (0)| 00:00:01 |

              |*  5 |      INDEX RANGE SCAN           | A_TBL_PK |     1 |       |     1   (0)| 00:00:01 |

              |*  6 |     FILTER                      |          |       |       |            |          |

              |   7 |      TABLE ACCESS BY INDEX ROWID| B_TBL    |     1 |    48 |     1   (0)| 00:00:01 |

              |*  8 |       INDEX RANGE SCAN          | B_TBL_PK |     1 |       |     1   (0)| 00:00:01 |

              |*  9 |     FILTER                      |          |       |       |            |          |

              |  10 |      TABLE ACCESS BY INDEX ROWID| C_TBL    |     1 |    48 |     1   (0)| 00:00:01 |

              |* 11 |       INDEX RANGE SCAN          | C_TBL_PK |     1 |       |     1   (0)| 00:00:01 |

              --------------------------------------------------------------------------------------------

               

              Predicate Information (identified by operation id):

              ---------------------------------------------------

                 5 - access("COL1"='aaa' AND "COL2"<'100')

                 6 - filter(NULL IS NOT NULL)

                 8 - access("COL1"='aaa' AND "COL2"<'100')

                 9 - filter(NULL IS NOT NULL)

                11 - access("COL1"='aaa' AND "COL2"<'100')

              The most important part is (once more) the predicate section containing the filter predicates NULL IS NOT NULL that should always evaluate to FALSE. So I would say: works as advertised (once upon a time...)

               

              Regards

               

              Martin

              • 4. Re: Manual Partitioning - with check constraints for pruning
                user2282685

                Thanks for the help guys!

                 

                Maybe I just didn't look deeply enough - the explain plan was at a very high level.  I'll check and see if it is doing as Martin explained.

                • 5. Re: Manual Partitioning - with check constraints for pruning
                  Martin Preiss

                  to check the filtering you can use a plan with rowsource statistics:

                  insert into A_TBL(col1, col2)

                  select 'aaa', rownum from dual connect by level <= 1000;

                   

                  insert into B_TBL(col1, col2)

                  select 'bbb', rownum from dual connect by level <= 1000;

                   

                  insert into C_TBL(col1, col2)

                  select 'ccc', rownum from dual connect by level <= 1000;

                   

                  select /*+ gather_plan_statistics */ *

                    from abc_v

                  where col1 = 'aaa' and col2 < '100'

                  order by col2;

                   

                  select *

                    from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

                   

                  PLAN_TABLE_OUTPUT

                  ---------------------------------------------------------------------------------------------------------------------------------

                  SQL_ID  7yx9tn45td43k, child number 0

                  -------------------------------------

                  select /*+ gather_plan_statistics */ * from abc_v where col1 = 'aaa'

                  and col2 < '100' order by col2

                   

                  Plan hash value: 162231217

                  ---------------------------------------------------------------------------------------------------------------------------------

                  | Id  | Operation                       | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |

                  ---------------------------------------------------------------------------------------------------------------------------------

                  |   0 | SELECT STATEMENT                |          |      1 |        |      2 |00:00:00.01 |       3 |       |       |          |

                  |   1 |  SORT ORDER BY                  |          |      1 |      1 |      2 |00:00:00.01 |       3 |  2048 |  2048 | 2048  (0)|

                  |   2 |   VIEW                          | ABC_V    |      1 |      1 |      2 |00:00:00.01 |       3 |       |       |          |

                  |   3 |    UNION-ALL                    |          |      1 |        |      2 |00:00:00.01 |       3 |       |       |          |

                  |   4 |     TABLE ACCESS BY INDEX ROWID | A_TBL    |      1 |      2 |      2 |00:00:00.01 |       3 |       |       |          |

                  |*  5 |      INDEX RANGE SCAN           | A_TBL_PK |      1 |      2 |      2 |00:00:00.01 |       2 |       |       |          |

                  |*  6 |     FILTER                      |          |      1 |        |      0 |00:00:00.01 |       0 |       |       |          |

                  |   7 |      TABLE ACCESS BY INDEX ROWID| B_TBL    |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |

                  |*  8 |       INDEX RANGE SCAN          | B_TBL_PK |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |

                  |*  9 |     FILTER                      |          |      1 |        |      0 |00:00:00.01 |       0 |       |       |          |

                  |  10 |      TABLE ACCESS BY INDEX ROWID| C_TBL    |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |

                  |* 11 |       INDEX RANGE SCAN          | C_TBL_PK |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |

                  ---------------------------------------------------------------------------------------------------------------------------------

                   

                  Predicate Information (identified by operation id):

                  ---------------------------------------------------

                     5 - access("COL1"='aaa' AND "COL2"<'100')

                     6 - filter(NULL IS NOT NULL)

                     8 - access("COL1"='aaa' AND "COL2"<'100')

                     9 - filter(NULL IS NOT NULL)

                    11 - access("COL1"='aaa' AND "COL2"<'100')

                   

                  Note

                  -----

                     - dynamic sampling used for this statement (level=2)

                  As we can see steps 7 (and 8) and 10 (and 11) show 0 starts - because the optimizer knows that step 6 and 9 need no further evaluation.

                  • 6. Re: Manual Partitioning - with check constraints for pruning
                    user2282685

                    So normally with the explain plan you read up from the bottom layer of the tree.

                     

                    In this case:

                     

                    --------------------------------------------------------------------------------------------

                    | Id  | Operation                       | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

                    --------------------------------------------------------------------------------------------

                    |   0 | SELECT STATEMENT                |          |     1 |    48 |     1 (100)| 00:00:01 |

                    |   1 |  SORT ORDER BY                  |          |     1 |    48 |     1 (100)| 00:00:01 |

                    |   2 |   VIEW                          | ABC_V    |     1 |    48 |     0   (0)| 00:00:01 |

                    |   3 |    UNION-ALL                    |          |       |       |            |          |

                    |   4 |     TABLE ACCESS BY INDEX ROWID | A_TBL    |     1 |    48 |     1   (0)| 00:00:01 |

                    |*  5 |      INDEX RANGE SCAN           | A_TBL_PK |     1 |       |     1   (0)| 00:00:01 |

                    |*  6 |     FILTER                      |          |       |       |            |          |

                    |   7 |      TABLE ACCESS BY INDEX ROWID| B_TBL    |     1 |    48 |     1   (0)| 00:00:01 |

                    |*  8 |       INDEX RANGE SCAN          | B_TBL_PK |     1 |       |     1   (0)| 00:00:01 |

                    |*  9 |     FILTER                      |          |       |       |            |          |

                    |  10 |      TABLE ACCESS BY INDEX ROWID| C_TBL    |     1 |    48 |     1   (0)| 00:00:01 |

                    |* 11 |       INDEX RANGE SCAN          | C_TBL_PK |     1 |       |     1   (0)| 00:00:01 |

                    --------------------------------------------------------------------------------------------

                     

                    Predicate Information (identified by operation id):

                    ---------------------------------------------------

                       5 - access("COL1"='aaa' AND "COL2"<'100')

                       6 - filter(NULL IS NOT NULL)

                       8 - access("COL1"='aaa' AND "COL2"<'100')

                       9 - filter(NULL IS NOT NULL)

                      11 - access("COL1"='aaa' AND "COL2"<'100')

                     

                    I just want to verify that the filter in step 9 is happening first to avoid even hitting the B_TBL or C_TBL.  My guess is that the query rewrite is doing something like:

                     

                    select * from a_tbl

                    where col1 = 'aaa' and col2 < '100'

                    union all

                    select * from B_tbl

                    where col1 = 'aaa' and col2 < '100' and null is not null

                    union all

                    select * from C_tbl

                    where col1 = 'aaa' and col2 < '100' and null is not null;

                     

                    Just wanted to be sure my interpretation was correct.

                     

                    Thanks!

                     

                     

                    • 7. Re: Manual Partitioning - with check constraints for pruning
                      Martin Preiss

                      bringing the execution order of plans to a single sentence is not a simple task - but I think Jonathan Lewis' "first child first, recursive decent" is quite fitting: http://allthingsoracle.com/execution-plans-part-3-the-rule/. So I would say the execution starts with line 5 and then evaluates the filter in 6 and 9 - though the order is perhaps not so important in the given situation.

                      • 8. Re: Manual Partitioning - with check constraints for pruning
                        Martin Preiss

                        and I think on a logical level the optimizer does something similar to your rewritten query - but a CBO trace (event 10053) shows:

                        Final query after transformations:******* UNPARSED QUERY IS *******

                        SELECT "ABC_V"."COL1" "COL1","ABC_V"."COL2" "COL2","ABC_V"."COL3" "COL3","ABC_V"."COL4" "COL4"

                          FROM  ( (SELECT "A_TBL"."COL1" "COL1","A_TBL"."COL2" "COL2","A_TBL"."COL3" "COL3","A_TBL"."COL4" "COL4"

                                     FROM TEST."A_TBL" "A_TBL")

                                    UNION ALL

                                  (SELECT "B_TBL"."COL1" "COL1","B_TBL"."COL2" "COL2","B_TBL"."COL3" "COL3","B_TBL"."COL4" "COL4"

                                     FROM TEST."B_TBL" "B_TBL")

                                    UNION ALL

                                  (SELECT "C_TBL"."COL1" "COL1","C_TBL"."COL2" "COL2","C_TBL"."COL3" "COL3","C_TBL"."COL4" "COL4"

                                     FROM TEST."C_TBL" "C_TBL")) "ABC_V"

                        WHERE "ABC_V"."COL1"='aaa'

                           AND "ABC_V"."COL2"<'100'

                        ORDER BY NLSSORT("ABC_V"."COL2",'nls_sort=''GERMAN''')

                        So the rewrite does not include the constraint predicates - but as the plan with rowsource statistics shows: the engine knows enough to exclude the superfluous elements.

                        • 9. Re: Manual Partitioning - with check constraints for pruning
                          rp0428
                          So normally with the explain plan you read up from the bottom layer of the tree.

                          No - in general there IS NO 'bottom layer' since different branches can have different depths. Don't confuse the tree representation of an execution plan with the B*TREE that a heap index uses. That 'B' in btree means balanced - so all branches of the index have the same 'level'. That is NOT true for the tree representation of an execution plan.

                           

                          I just want to verify that the filter in step 9 is happening first to avoid even hitting the B_TBL or C_TBL.

                          No - that is the THIRD child - the first child is visited first. That first child may be the shallowest branch, the deepest branch or anywhere in between.

                           

                          Maria Colgan wrote a white paper called 'Explain the explain plan' that shows how the plan should be analyzed/read.

                          http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-explain-the-explain-plan-052011-393674.pdf

                           

                          The order is analogous to the order used by complex algebraic expressions: left to right taking nested parenthetical expressions into account.

                          a + (b + 7) * (c * (d - 2) / e) + 1

                          That expression has FOUR children at the same level

                          1. a

                          2. (b + 7)

                          3. (c * (d - 2) / e)

                          4. 1

                           

                          So '(b + 7)' will get evaluated before that complex and more deeply nested expression that follows it.

                           

                          Algebraic expressions typically include different operators (+, -, *, /). Each of those can have a different order of precendence that applies if they occur at the same level.

                           

                          But with execution plans the level of indentation already takes care of that for you.

                          • 10. Re: Manual Partitioning - with check constraints for pruning
                            rp0428
                            I think Jonathan Lewis' "first child first, recursive decent" is quite fitting: http://allthingsoracle.com/execution-plans-part-3-the-rule/.

                             

                            My own preference is to use the same methodology that is used for parenthesized algebraic expressions. Each set of parentheses indicates a new level of nesting.

                            So I would say the execution starts with line 5 and then evaluates the filter in 6 and 9 - though the order is perhaps not so important in the given situation.

                            Did you mean line 4? The three child elements are lines 4, 6 and 9.

                            • 11. Re: Manual Partitioning - with check constraints for pruning
                              Martin Preiss

                              in my understanding the execution starts with the index scan in step 5, followed by the table access with the aquirred rowids in step 4. Then follows the evaluation of the filter step child nodes in 6 and 9. But I agree that the child elements for the union all (step 3) are 4, 6 and 9.

                               

                              rp0428 wrote:

                               

                              My own preference is to use the same methodology that is used for parenthesized algebraic expressions. Each set of parentheses indicates a new level of nesting.

                              That's an interesting approach - never thought about that.