06 March, 2011

Cardinality Estimates in Dynamic Partition Pruning

Dynamic Partition Pruning occurs when the Optimizer cannot determine which Partition(s) of a table will a query have to execute against, although the query does not need to do a Full Table Scan of all the Partitions.
Typically this happens with queries that use Bind Variables. However, it can also occur with SubQueries against Partition Key Columns and where Dimensions are joined to a Fact.

What I want to present here is that in Dynamic Partition Pruning, Oracle cannot really present the expected Cardinality from the Partition search. Explain Plan does present a Cardinality but it cannot really associate the Cardinality with a Partition -- as it does not know which Partition will be searched. It is only at Runtime that the execution will select the correct Partition(s) based on the binds , values from subqueries or dimensions.

Here is an example :

Given these row counts in a Fact Table (Range Partitioned by Sale_Date) and three Dimension Tables :

SQL> select partition_name, num_rows
2 from user_tab_partitions
3 where table_name = 'SALES_FACT'
4 order by partition_position
5 /

PARTITION_NAME NUM_ROWS
------------------------------ ----------
SF_P_2007 249300
SF_P_2008 350698
SF_P_2009 398913
SF_P_2010 499673
SF_P_2011 101411

SQL>
SQL> select count(*)
2 from cust_dim
3 /

COUNT(*)
----------
50

SQL>
SQL> select count(*)
2 from prod_dim
3 /

COUNT(*)
----------
25

SQL>
SQL>
SQL> select count(*)
2 from date_dim
3 where date_month_year = 'JUL-2010'
4 /

COUNT(*)
----------
31

SQL>

So we have Sales Data for the 2007 to 2011 (2 months data in 2011). The number of Sales have generally been increasing, with 2010 having almost twice as many sales as in 2007. What is not apparent from row counts (but does happen in the real world) is that not all 50 Customers have been present through 2007 to 2011. Neither have all Products existed in 2007 as are present in 2011.

In fact (no pun intended !), I have constructed the data such that the year 2007 had only 20 Customers and 15 Products. The number of Customers and Products has increased over the years.
Is the Optimizer aware of this ? Can it be aware of this ?

Here is my first pair of test queries :

SQL> -- Query for Jul 2010, Customer 46, Product 11
SQL> explain plan for
2 select f.sale_qty, f.sale_price, f.discount_rate
3 from sales_fact f, cust_dim c, prod_dim p, date_dim d
4 where
5 f.cust_id=c.cust_id
6 and f.prod_id=p.prod_id
7 and f.sale_date=d.date_value
8 and c.cust_name = 'Customer_46'
9 and p.prod_name = 'Product_11'
10 and d.date_month_year = 'JUL-2010'
11 /

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4059568812

-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 26 | 2314 | 2964 (1)| 00:00:36 | | |
|* 1 | HASH JOIN | | 26 | 2314 | 2964 (1)| 00:00:36 | | |
| 2 | PART JOIN FILTER CREATE | :BF0000 | 30 | 1380 | 11 (0)| 00:00:01 | | |
| 3 | MERGE JOIN CARTESIAN | | 30 | 1380 | 11 (0)| 00:00:01 | | |
| 4 | MERGE JOIN CARTESIAN | | 1 | 29 | 6 (0)| 00:00:01 | | |
|* 5 | TABLE ACCESS FULL | CUST_DIM | 1 | 15 | 3 (0)| 00:00:01 | | |
| 6 | BUFFER SORT | | 1 | 14 | 3 (0)| 00:00:01 | | |
|* 7 | TABLE ACCESS FULL | PROD_DIM | 1 | 14 | 3 (0)| 00:00:01 | | |
| 8 | BUFFER SORT | | 30 | 510 | 8 (0)| 00:00:01 | | |
|* 9 | TABLE ACCESS FULL | DATE_DIM | 30 | 510 | 5 (0)| 00:00:01 | | |
| 10 | PARTITION RANGE JOIN-FILTER| | 1599K| 65M| 2948 (1)| 00:00:36 |:BF0000|:BF0000|
| 11 | TABLE ACCESS FULL | SALES_FACT | 1599K| 65M| 2948 (1)| 00:00:36 |:BF0000|:BF0000|
-----------------------------------------------------------------------------------------------------------

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

1 - access("F"."SALE_DATE"="D"."DATE_VALUE" AND "F"."CUST_ID"="C"."CUST_ID" AND
"F"."PROD_ID"="P"."PROD_ID")
5 - filter("C"."CUST_NAME"='Customer_46')
7 - filter("P"."PROD_NAME"='Product_11')
9 - filter("D"."DATE_MONTH_YEAR"='JUL-2010')

27 rows selected.

SQL>
SQL> select count(*) from
2 (
3 select f.sale_qty, f.sale_price, f.discount_rate
4 from sales_fact f, cust_dim c, prod_dim p, date_dim d
5 where
6 f.cust_id=c.cust_id
7 and f.prod_id=p.prod_id
8 and f.sale_date=d.date_value
9 and c.cust_name = 'Customer_46'
10 and p.prod_name = 'Product_11'
11 and d.date_month_year = 'JUL-2010'
12 )
13 /

COUNT(*)
----------
39

SQL>
SQL>
SQL>
SQL> -- Query for Jul 2007, Customer 46, Product 11
SQL> explain plan for
2 select f.sale_qty, f.sale_price, f.discount_rate
3 from sales_fact f, cust_dim c, prod_dim p, date_dim d
4 where
5 f.cust_id=c.cust_id
6 and f.prod_id=p.prod_id
7 and f.sale_date=d.date_value
8 and c.cust_name = 'Customer_46'
9 and p.prod_name = 'Product_11'
10 and d.date_month_year = 'JUL-2007'
11 /

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4059568812

-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 26 | 2314 | 2964 (1)| 00:00:36 | | |
|* 1 | HASH JOIN | | 26 | 2314 | 2964 (1)| 00:00:36 | | |
| 2 | PART JOIN FILTER CREATE | :BF0000 | 30 | 1380 | 11 (0)| 00:00:01 | | |
| 3 | MERGE JOIN CARTESIAN | | 30 | 1380 | 11 (0)| 00:00:01 | | |
| 4 | MERGE JOIN CARTESIAN | | 1 | 29 | 6 (0)| 00:00:01 | | |
|* 5 | TABLE ACCESS FULL | CUST_DIM | 1 | 15 | 3 (0)| 00:00:01 | | |
| 6 | BUFFER SORT | | 1 | 14 | 3 (0)| 00:00:01 | | |
|* 7 | TABLE ACCESS FULL | PROD_DIM | 1 | 14 | 3 (0)| 00:00:01 | | |
| 8 | BUFFER SORT | | 30 | 510 | 8 (0)| 00:00:01 | | |
|* 9 | TABLE ACCESS FULL | DATE_DIM | 30 | 510 | 5 (0)| 00:00:01 | | |
| 10 | PARTITION RANGE JOIN-FILTER| | 1599K| 65M| 2948 (1)| 00:00:36 |:BF0000|:BF0000|
| 11 | TABLE ACCESS FULL | SALES_FACT | 1599K| 65M| 2948 (1)| 00:00:36 |:BF0000|:BF0000|
-----------------------------------------------------------------------------------------------------------

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

1 - access("F"."SALE_DATE"="D"."DATE_VALUE" AND "F"."CUST_ID"="C"."CUST_ID" AND
"F"."PROD_ID"="P"."PROD_ID")
5 - filter("C"."CUST_NAME"='Customer_46')
7 - filter("P"."PROD_NAME"='Product_11')
9 - filter("D"."DATE_MONTH_YEAR"='JUL-2007')

27 rows selected.

SQL>
SQL> select count(*) from
2 (
3 select f.sale_qty, f.sale_price, f.discount_rate
4 from sales_fact f, cust_dim c, prod_dim p, date_dim d
5 where
6 f.cust_id=c.cust_id
7 and f.prod_id=p.prod_id
8 and f.sale_date=d.date_value
9 and c.cust_name = 'Customer_46'
10 and p.prod_name = 'Product_11'
11 and d.date_month_year = 'JUL-2007'
12 )
13 /

COUNT(*)
----------
0

SQL>

The Optimizer presented exactly the same estimated Row Counts for the combination of (Product_11, Customer_46) in Jul-2007 as it did for Jul-2010.
Yet, Customer_46 didn't even exist in Jul-2007. There were no sales to Customer_46 in the year 2007 at all.

Here is the next pair of test queries :

SQL> -- Query for Jan 2011, Customer 9, Product 25
SQL> explain plan for
2 select f.sale_qty, f.sale_price, f.discount_rate
3 from sales_fact f, cust_dim c, prod_dim p, date_dim d
4 where
5 f.cust_id=c.cust_id
6 and f.prod_id=p.prod_id
7 and f.sale_date=d.date_value
8 and c.cust_name = 'Customer_9'
9 and p.prod_name = 'Product_25'
10 and d.date_month_year = 'JAN-2011'
11 /

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4059568812

-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 26 | 2314 | 2964 (1)| 00:00:36 | | |
|* 1 | HASH JOIN | | 26 | 2314 | 2964 (1)| 00:00:36 | | |
| 2 | PART JOIN FILTER CREATE | :BF0000 | 30 | 1380 | 11 (0)| 00:00:01 | | |
| 3 | MERGE JOIN CARTESIAN | | 30 | 1380 | 11 (0)| 00:00:01 | | |
| 4 | MERGE JOIN CARTESIAN | | 1 | 29 | 6 (0)| 00:00:01 | | |
|* 5 | TABLE ACCESS FULL | CUST_DIM | 1 | 15 | 3 (0)| 00:00:01 | | |
| 6 | BUFFER SORT | | 1 | 14 | 3 (0)| 00:00:01 | | |
|* 7 | TABLE ACCESS FULL | PROD_DIM | 1 | 14 | 3 (0)| 00:00:01 | | |
| 8 | BUFFER SORT | | 30 | 510 | 8 (0)| 00:00:01 | | |
|* 9 | TABLE ACCESS FULL | DATE_DIM | 30 | 510 | 5 (0)| 00:00:01 | | |
| 10 | PARTITION RANGE JOIN-FILTER| | 1599K| 65M| 2948 (1)| 00:00:36 |:BF0000|:BF0000|
| 11 | TABLE ACCESS FULL | SALES_FACT | 1599K| 65M| 2948 (1)| 00:00:36 |:BF0000|:BF0000|
-----------------------------------------------------------------------------------------------------------

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

1 - access("F"."SALE_DATE"="D"."DATE_VALUE" AND "F"."CUST_ID"="C"."CUST_ID" AND
"F"."PROD_ID"="P"."PROD_ID")
5 - filter("C"."CUST_NAME"='Customer_9')
7 - filter("P"."PROD_NAME"='Product_25')
9 - filter("D"."DATE_MONTH_YEAR"='JAN-2011')

27 rows selected.

SQL>
SQL> select count(*) from
2 (
3 select f.sale_qty, f.sale_price, f.discount_rate
4 from sales_fact f, cust_dim c, prod_dim p, date_dim d
5 where
6 f.cust_id=c.cust_id
7 and f.prod_id=p.prod_id
8 and f.sale_date=d.date_value
9 and c.cust_name = 'Customer_9'
10 and p.prod_name = 'Product_25'
11 and d.date_month_year = 'JAN-2011'
12 )
13 /

COUNT(*)
----------
0

SQL>
SQL>
SQL>
SQL> -- Query for Jan 2008, Customer 9, Product 25
SQL> explain plan for
2 select f.sale_qty, f.sale_price, f.discount_rate
3 from sales_fact f, cust_dim c, prod_dim p, date_dim d
4 where
5 f.cust_id=c.cust_id
6 and f.prod_id=p.prod_id
7 and f.sale_date=d.date_value
8 and c.cust_name = 'Customer_9'
9 and p.prod_name = 'Product_25'
10 and d.date_month_year = 'JAN-2008'
11 /

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4059568812

-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 26 | 2314 | 2964 (1)| 00:00:36 | | |
|* 1 | HASH JOIN | | 26 | 2314 | 2964 (1)| 00:00:36 | | |
| 2 | PART JOIN FILTER CREATE | :BF0000 | 30 | 1380 | 11 (0)| 00:00:01 | | |
| 3 | MERGE JOIN CARTESIAN | | 30 | 1380 | 11 (0)| 00:00:01 | | |
| 4 | MERGE JOIN CARTESIAN | | 1 | 29 | 6 (0)| 00:00:01 | | |
|* 5 | TABLE ACCESS FULL | CUST_DIM | 1 | 15 | 3 (0)| 00:00:01 | | |
| 6 | BUFFER SORT | | 1 | 14 | 3 (0)| 00:00:01 | | |
|* 7 | TABLE ACCESS FULL | PROD_DIM | 1 | 14 | 3 (0)| 00:00:01 | | |
| 8 | BUFFER SORT | | 30 | 510 | 8 (0)| 00:00:01 | | |
|* 9 | TABLE ACCESS FULL | DATE_DIM | 30 | 510 | 5 (0)| 00:00:01 | | |
| 10 | PARTITION RANGE JOIN-FILTER| | 1599K| 65M| 2948 (1)| 00:00:36 |:BF0000|:BF0000|
| 11 | TABLE ACCESS FULL | SALES_FACT | 1599K| 65M| 2948 (1)| 00:00:36 |:BF0000|:BF0000|
-----------------------------------------------------------------------------------------------------------

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

1 - access("F"."SALE_DATE"="D"."DATE_VALUE" AND "F"."CUST_ID"="C"."CUST_ID" AND
"F"."PROD_ID"="P"."PROD_ID")
5 - filter("C"."CUST_NAME"='Customer_9')
7 - filter("P"."PROD_NAME"='Product_25')
9 - filter("D"."DATE_MONTH_YEAR"='JAN-2008')

27 rows selected.

SQL>
SQL> select count(*) from
2 (
3 select f.sale_qty, f.sale_price, f.discount_rate
4 from sales_fact f, cust_dim c, prod_dim p, date_dim d
5 where
6 f.cust_id=c.cust_id
7 and f.prod_id=p.prod_id
8 and f.sale_date=d.date_value
9 and c.cust_name = 'Customer_9'
10 and p.prod_name = 'Product_25'
11 and d.date_month_year = 'JAN-2008'
12 )
13 /

COUNT(*)
----------
0

SQL>

This was a test for the combination of (Product_25, Customer_9). Although Product_25 does exist in 2011, it did not exist as a Product in 2008. (Even in 2011, there were actually zero sales of tis product to Customer_9.)
Also, note how the Row Estimate for the JAN-2011 partition is as high as for any of the other three months presented above. This in spite of the fact that we know that 2011 sales are almost double those in 2007 (and 2011 are on track to exceed 2010). The optimizer presents the same row count for a month in 2007 as in 2011.

Generally, I advise people to not look at the Cost but at the Cardinality at each step of the Execution Plan and evaluate whether the Cardinality correctly matches the really expected row count, else it will have a signficant impact on the real total "cost" of the query. However, in the case of Dynamic Partition Pruning, I probably wouldn't look at the Cardinality for the Table Partition --- unless I suspect that the Table level statistics are wrong.

In the case of Dynamic Partition Pruning, the Optimizer computes expected Cardinality from Table level, not Partition level statistics.

Here are the actual row counts for those CUST_IDs and PROD_IDs in the SALES_FACT table :

MONTH_NA CUST_ID COUNT
-------- ---------- ----------
Jan-2008 9 1242
Jan-2011 9 1003
Jul-2007 9 1037
Jul-2010 9 873
Jan-2011 46 1052
Jul-2010 46 838

MONTH_NA PROD_ID COUNT
-------- ---------- ----------
Jan-2008 11 1442
Jan-2011 11 2149
Jul-2007 11 1412
Jul-2010 11 1741
Jan-2011 25 2048


which shows that CUST_ID=46 ("Customer_46") did not exist in Jul-2007 and PROD_ID=25 ("Product_25") did not exist in Jan-2008.

.
.
.

3 comments:

lsattle said...

Hemant,
I don't FULLY understand why you are focused on the cardinality estimate.
Are you implying that with bad estimates the partitions will not get pruned?
I ran across your post researching partitioning with star schemas and with Oracle OBIEE.
I need to get dynamic pruning working with queries that cannot be modified coming out of OBIEE.

lsattle said...

I don't need a response to my prior question.
I found more discussion at:
http://hemantoracledba.blogspot.com/2011/03/cardinality-estimates-in-dynamic.html

That is how I got to this posting.
Seems the bad cardinality is causing a bad plan.
This stuff all seems so hit or miss.
How do the big boys in data warehousing get their stuff to run right using query tools where you can't mess with the queries.

Thanks,

Hemant K Chitale said...

Partition Pruning does occur. But, without knowing *which* will be the target partition in advance, Oracle cannot estimate the Cardinality from the target partition (because it doesn't know which partition it will be). Oracle has to estimate Cardinality from *table* level statistics and "intrapolate" and estimate for a target partition.

"Getting it right" starts with getting the design (what partitioning scheme you will use) correct to match the expected (a) Query Patterns (b) Maintenance Requirements (e.g. to archive/purge partitions). Sometimes the two requirements may appear to be contradictory so you may have to choose one over the other. But you MUST predict the sort of queries expected.