7 Replies Latest reply: Dec 6, 2012 1:18 AM by Hesh RSS

    Partition purning

    Hesh
      hi,

      I have partitioned my table using one column, this is column is not directly used for filter, but along with combination an other. When I have generated the explain plan I got following
                                                                                                                                                                                      
      ------------------------------------------------------------------------------------------------------------------------------                                                                                                                                                                               
      | Id  | Operation                    | Name                          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |                                                                                                                                                                               
      ------------------------------------------------------------------------------------------------------------------------------                                                                                                                                                                               
      |   0 | SELECT STATEMENT             |                               |  1228 |   370K|   107K  (2)| 00:21:30 |       |       |                                                                                                                                                                               
      |   1 |  NESTED LOOPS                |                               |  1228 |   370K|   107K  (2)| 00:21:30 |       |       |                                                                                                                                                                               
      |   2 |   TABLE ACCESS BY INDEX ROWID| DWH_TIME_D                    |     1 |   191 |     2   (0)| 00:00:01 |       |       |                                                                                                                                                                               
      |*  3 |    INDEX UNIQUE SCAN         | DWH_TIME_D_DIMENSION_KEY_PK_1 |     1 |       |     1   (0)| 00:00:01 |       |       |                                                                                                                                                                               
      |   4 |   PARTITION RANGE ITERATOR   |                               |  1228 |   141K|   107K  (2)| 00:21:30 |   KEY |   KEY |                                                                                                                                                                               
      |*  5 |    TABLE ACCESS FULL         | DWH_SALES_FACTS_F             |  1228 |   141K|   107K  (2)| 00:21:30 |   KEY |   KEY |                                                                                                                                                                               
      ------------------------------------------------------------------------------------------------------------------------------ 
      Predicate Information (identified by operation id):                                                                                                                                                                                                                                                          
      ---------------------------------------------------                                                                                                                                                                                                                                                          
                                                                                                                                                                                                                                                                                                                   
         3 - access("DWH_TIME_D"."DIMENSION_KEY"=89636)                                                                                                                                                                                                                                                            
         5 - filter("DWH_TIME_D"=89636 AND "DWH_SALES_FACTS_F"."YW"="DWH_TIME_D"."WEEK_YEAR_CY")                                                                                                                                                                                                                   
      my tables looks like following
      TAB1 : Is Range partitioned by YEAR_WEEK
      
      ID     SALES_VAL     YEAR_WEEK     TIME_D
      1     1000          201101          10001
      2     2000          201101          10001
      3     3000          201201          11002
      4     2000          201201          11002
      
      TAB2:
      
      TIME_D     YEAR_WEEK
      10001     201101
      10002     201201
      my query looks like following, not directly filtered by partitioned column.
      SELECT * FROM TAB1, TAB2
      WHERE TAB1.TIME_D=TAB2.TIME_D
      AND TAB1.YEAR_WEEK=TAB2.YEAR_WEEK
      AND TAB1.TIME_D=10001;
      Is my partition is functioning correctly? because the 'KEY' should represent number '2', how can I know that.

      Thanks,
      Hesh

      Edited by: Hesh on Dec 4, 2012 10:00 AM
        • 1. Re: Partition purning
          Max Seleznev
          The KEY means its value would be determined at execution time and is unknown at parse time. The optimizer does not know yet what partition range would correspond to the join condition.
          Also you condition
          >
          WHERE TAB1.TIME_D=TAB2.TIME_D
          ...
          AND TAB1.TIME_D=10001;
          >
          has been rewritten as
          WHERE TAB2.TIME_D=10001
          ...
          based on the plan. It makes sense since TAB2 seems to be a much smaller table.
          • 2. Re: Partition purning
            rp0428
            >
            Is my partition is functioning correctly? because the 'KEY' should represent number '2', how can I know that.
            >
            Yes - it is.

            Why do you think 'KEY' should represent number '2'? Key will represent the partition number that is actually used at runtime. That might be '2' or it might be '37'. It all depends on how many partitions there are and what partition '201101' represents.

            And why did you post a plan that uses tables DWH_TIME_D and DWH_SALES_FACTS_F but a query that uses TAB1, TAB2 and different column names? All that does is confuse everything and make it harder to understand what is going on.
            • 3. Re: Partition purning
              Hesh
              Thanks Max and rp0428 for your valuable reposes,

              @Max: where did you find query rewriting information from the plan?

              @rp0428 : I did change the table names as original tables have more columns and thought it will add some complexity, sorry if it creates confusion. The other thing about number '2' is, its shows this values in the plan instead of KEY when you give 'yw=201230' in the join condition. Now I can understand that this happened due to optimizer know the partition during the compile time itself.

              Is there anyway to know the KEY value during the run time? Just to check how many partitions get read during the run-time.

              -Hesh
              • 4. Re: Partition purning
                Max Seleznev
                >
                where did you find query rewriting information from the plan?
                >

                The first step of your execution plan is access of the dimensional table by PK index that yields 1 record.
                3 - access("DWH_TIME_D"."DIMENSION_KEY"=89636)
                that is equivalent to
                WHERE TAB2.TIME_D=10001
                that does not explicitly exist in your query, but could be derived from the existing conditions.

                Edited by: Max Seleznev on Dec 5, 2012 9:50 AM
                • 5. Re: Partition purning
                  Martin Preiss
                  in 11g and with the diagnostic and tuning pack licenced you can get some processing information in the sql monitor or using row source execution statistics (Maria Colgan explains both strateqies in her blog: https://blogs.oracle.com/optimizer/entry/how_do_i_know_if). But the plan only contains the partition number if the value is known when the query is optimized. If the FTS operations take some time you could take a look at v$session_longops to see some progress information.

                  Regards

                  Martin

                  P.S.: for interval partitioned tables the key pstart und pstop are without much information even if the range is known at the time of optimization (showing always 1 and 1048575).

                  Edited by: mpreiss on Dec 5, 2012 5:52 PM
                  • 6. Re: Partition purning
                    Randolf Geist
                    Hesh wrote:
                    Is there anyway to know the KEY value during the run time? Just to check how many partitions get read during the run-time.
                    You can track the partition pruning at runtime by setting event 10128 in your session, which writes the information to a trace file and a special table that you need to create upfront.

                    For more information, see here:

                    http://www.juliandyke.com/Diagnostics/Events/EventReference.html#10128

                    http://cbohl.blogspot.de/2006/10/verify-that-partition-pruning-works.html

                    Hope this helps,
                    Randolf
                    • 7. Re: Partition purning
                      Hesh
                      Thanks Max, very useful observation and learned one more new thing today.

                      Its always pleasure to here a word from Randolf, and very useful stuff he mentioned. I will generate the trace event and post the results.

                      Thank you all for your valuable responses.

                      Regards
                      Hesh