Results 1 to 1 of 1
  1. #1
    Join Date
    Nov 2009
    Posts
    1

    Unanswered: Problem: Partition pruning a fact table with partitioned time dimension.

    Hello All

    I'm looking for a little help understanding the Oracle optimizer's partition pruning decisions for a fact/dim scenario that I'm testing. My situation is this:

    1) The fact table is RANGE partitioned on the time surrogate key (actual_time_sk) which equates to calendar years. It currently has 10 partitions (2000 - 2009). It is also HASH subpartitoned (8 subpartitions) on another dimension's surrogate key.

    2) The existing time dim is NOT partitioned and has an index on calendar_year.

    3) I have created a new copy of the time dim which I have LIST partitioned on calendar_year (VARCHAR2).

    The query is:

    Code:
    SELECT SUM(no_dollars) FROM fact_table fct, dim_time tim WHERE fct.actual_time_sk = tim.time_sk AND tim.calendar_year = '2007' ;


    When I use the existing time dim, the explain plan is:
    Code:
    --------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                     | Name                     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    --------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT              |                          |     1 |    16 | 20805   (4)| 00:04:10 |       |       |
    |   1 |  SORT AGGREGATE               |                          |     1 |    16 |            |          |       |       |
    |*  2 |   HASH JOIN                   |                          |   917K|    14M| 20805   (4)| 00:04:10 |       |       |
    |   3 |    TABLE ACCESS BY INDEX ROWID| DIM_TIME                 |   385 |  3850 |    31   (0)| 00:00:01 |       |       |
    |*  4 |     INDEX RANGE SCAN          | IDX_TIME_YEAR            |   390 |       |     2   (0)| 00:00:01 |       |       |
    |   5 |    PARTITION RANGE SUBQUERY   |                          |  6439K|    36M| 20698   (3)| 00:04:09 |KEY(SQ)|KEY(SQ)|
    |   6 |     PARTITION HASH ALL        |                          |  6439K|    36M| 20698   (3)| 00:04:09 |     1 |     8 |
    |   7 |      TABLE ACCESS FULL        | FACT_TABLE               |  6439K|    36M| 20698   (3)| 00:04:09 |   KEY |   KEY |
    --------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("FCT"."ACTUAL_TIME_SK"="TIM"."TIME_SK")
       4 - access("TIM"."CALENDAR_YEAR"='2007')
    
    20 rows selected
    When I use the new time dim, the explain plan is:
    Code:
    --------------------------------------------------------------------------------------------------------------------
    | Id  | Operation               | Name                     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    --------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT        |                          |     1 |    16 | 20778   (4)| 00:04:10 |       |       |
    |   1 |  SORT AGGREGATE         |                          |     1 |    16 |            |          |       |       |
    |*  2 |   HASH JOIN             |                          |   869K|    13M| 20778   (4)| 00:04:10 |       |       |
    |   3 |    PARTITION LIST SINGLE|                          |   365 |  3650 |     4   (0)| 00:00:01 |    58 |    58 |
    |   4 |     TABLE ACCESS FULL   | DIM_TIME                 |   365 |  3650 |     4   (0)| 00:00:01 |    58 |    58 |
    |   5 |    PARTITION RANGE ALL  |                          |  6439K|    36M| 20698   (3)| 00:04:09 |     1 |    10 |
    |   6 |     PARTITION HASH ALL  |                          |  6439K|    36M| 20698   (3)| 00:04:09 |     1 |     8 |
    |   7 |      TABLE ACCESS FULL  | FACT_TABLE               |  6439K|    36M| 20698   (3)| 00:04:09 |     1 |    80 |
    --------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("FCT"."ACTUAL_TIME_SK"="TIM"."TIME_SK")
    
    19 rows selected
    In the first scenario, the time dimension is accessed by the calendar_year index and the fact table's range partition is determined by PARTITION RANGE SUBQUERY (i.e. partition pruning IS happening on the fact table).

    In the second scenario, the time dimension is accesed by partition yet the fact table is accessed by PARTITION RANGE ALL (i.e. partition pruning is NOT happening on the fact table).

    If anyone could shed some light on why the second scenario avoids partition pruning the fact table, it would be greatly appreciated.

    BTW, we are running 10gR2.

    Thanks
    Ryk
    Last edited by budzyr; 11-19-09 at 19:00.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •