Cost Of Subquery Pruned Partitioned Table Is The Same As Accessing All Partitions. (Doc ID 802367.1)

Last updated on APRIL 08, 2009

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.4
This problem can occur on any platform.

Symptoms

When selecting against a partitioned table, it does not make any difference how many of the partitions are eliminated by Subquery Pruning, the cost does not change. This means that is certain circumstances a better access path using Dimension Based Subquery Pruning is not selected.

For example:

1 Partition Accessed:

December 2008 -> January 2009 predicate on DIMENSION partition is PARTITIONED_TAB accessing 1 partition

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |   111 |  4551 |  1861K  (5)| 03:44:09 |       |       |
|   1 |  HASH GROUP BY              |                  |   111 |  4551 |  1861K  (5)| 03:44:09 |       |       |
|*  2 |   HASH JOIN                 |                  |  3275K|   128M|  1860K  (5)| 03:44:02 |       |       |
|   3 |    TABLE ACCESS FULL        | ANOTHER_TABLE    |    28 |   168 |     2   (0)| 00:00:01 |       |       |
|*  4 |    HASH JOIN                |                  |  3275K|   109M|  1860K  (5)| 03:44:02 |       |       |
|*  5 |     TABLE ACCESS FULL       | DIMENSION        |   274 |  3562 |    16   (0)| 00:00:01 |       |       |
|   6 |     PARTITION RANGE SUBQUERY|                  |    18M|   388M|  1859K  (5)| 03:43:58 |KEY(SQ)|KEY(SQ)|
|*  7 |      TABLE ACCESS FULL      | PARTITIONED_TAB  |    18M|   388M|  1859K  (5)| 03:43:58 |KEY(SQ)|KEY(SQ)|
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - filter("A13"."WEEK">=200812 AND "A13"."WEEK"<=200901)

12 Partitions Accessed:

January 2007 -> January 2009 predicate on DIMENSION partition is PARTITIONED_TAB accessing 24 partitions


----------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |   292 | 11972 |  1863K  (5)| 03:44:22 |       |       |
|   1 |  HASH GROUP BY              |                  |   292 | 11972 |  1863K  (5)| 03:44:22 |       |       |
|   2 |   HASH JOIN                 |                  |  8619K|   337M|  1860K  (5)| 03:44:03 |       |       |
|   3 |    TABLE ACCESS FULL        | ANOTHER_TABLE    |    28 |   168 |     2   (0)| 00:00:01 |       |       |
|   4 |    HASH JOIN                |                  |  8619K|   287M|  1860K  (5)| 03:44:02 |       |       |
|*  5 |     TABLE ACCESS FULL       | DIMENSION        |   722 |  9386 |    16   (0)| 00:00:01 |       |       |
|   6 |     PARTITION RANGE SUBQUERY|                  |    18M|   388M|  1859K  (5)| 03:43:58 |KEY(SQ)|KEY(SQ)|
|   7 |      TABLE ACCESS FULL      | PARTITIONED_TAB  |    18M|   388M|  1859K  (5)| 03:43:58 |KEY(SQ)|KEY(SQ)|
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - filter("A13"."WEEK">=200701 AND "A13"."WEEK"<=200901)


All Partition Accessed:
2006 -> 2009 predicate on dimension - All partitions.

-----------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                  |   447 | 18327 |  1864K  (5)| 03:44:33 |       |       |
|   1 |  HASH GROUP BY         |                  |   447 | 18327 |  1864K  (5)| 03:44:33 |       |       |
|*  2 |   HASH JOIN            |                  |    13M|   515M|  1860K  (5)| 03:44:04 |       |       |
|   3 |    TABLE ACCESS FULL   | ANOTHER_TABLE    |    28 |   168 |     2   (0)| 00:00:01 |       |       |
|*  4 |    HASH JOIN           |                  |    13M|   440M|  1860K  (5)| 03:44:02 |       |       |
|*  5 |     TABLE ACCESS FULL  | DIMENSION        |  1105 | 14365 |    16   (0)| 00:00:01 |       |       |
|   6 |     PARTITION RANGE ALL|                  |    18M|   388M|  1859K  (5)| 03:43:58 |     1 |    52 |
|*  7 |      TABLE ACCESS FULL | PARTITIONED_TAB  |    18M|   388M|  1859K  (5)| 03:43:58 |     1 |    52 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - filter("A13"."WEEK">=200601 AND "A13"."WEEK"<=200901)

Note that he cost of the access to 'PARTITONED_TAB' does not change in either case and is independent of how many partitions are actually accessed at run time.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms