Partition Elimination

Database
Enthusiast

Partition Elimination

For many years now we have seen the optimizer NOT chose partition elimination for a query when that query qualifies on a date column that is the partitioning column, UNLESS we are qualifying on a hard coded date value like where columnname = '2013-03-26'.

We opened an incident a few versions back on 6.1 and were told it would work in the next release.  We are now on 13.10 and unfortunately, we still see this behavior. 

Has anyone experienced this and more importantly, has anyone been able to find a viable workaround to help the optimizer realize that the date column we are joining with is in fact partitioned?

Below are some examples of the issue and how we have been encouraged the optimizer to chose partition elimination.

Thanks for you insight!

                     Mike

EXAMPLE 1 Full table scan:

sel * from db02.stil

where SALES_DT in (

sel SCOPED_DT from ST02.SIMS)

EXPLAIN:

4) We execute the following steps in parallel.

1) We do an all-AMPs RETRIEVE step from

db02.stil by way of an all-rows scan

with no residual conditions into Spool 2 (all_amps)

(compressed columns allowed), which is built locally on the

AMPs. Then we do a SORT to order Spool 2 by the hash code of

(db02.stil.SALES_DT). The input

table will not be cached in memory, but it is eligible for

synchronized scanning. The result spool file will not be

cached in memory. The size of Spool 2 is estimated with high

confidence to be 11,572,473,457 rows (1,353,979,394,469

bytes). The estimated time for this step is 1 hour and 25

minutes.

EXAMPLE 2 partition elimination:

sel * from db02.stil

where SALES_DT in (

sel SCOPED_DT from ST02.SIMS

where scoped_dt between '2013-05-01' and '2013-05-22');

EXPLAIN:

4) We execute the following steps in parallel.

1) We do an all-AMPs RETRIEVE step from 4 partitions of

db02.stil with a condition of (

"(db02.stil.SALES_DT >= DATE

'2013-05-01') AND

(db02.stil.SALES_DT <= DATE

'2013-05-22')") into Spool 2 (all_amps) (compressed columns

allowed), which is built locally on the AMPs. Then we do a

SORT to order Spool 2 by the hash code of (

db02.stil.SALES_DT). The input

table will not be cached in memory, but it is eligible for

synchronized scanning. The result spool file will not be

cached in memory. The size of Spool 2 is estimated with high

confidence to be 492,179,705 rows (57,585,025,485 bytes).

The estimated time for this step is 3 minutes and 9 seconds.

EXAMPLE 3 partition elimination:

sel * from db02.stil

where SALES_DT in (

sel SCOPED_DT from ST02.SIMS

where scoped_dt > current_date - 30 );

EXPLAIN:

4)We execute the following steps in parallel.

1) We do an all-AMPs RETRIEVE step from 39 partitions of

db02.stil with a condition of (

"db02.stil.SALES_DT >= DATE

'2013-05-01'") into Spool 2 (all_amps) (compressed columns

allowed), which is built locally on the AMPs. Then we do a

SORT to order Spool 2 by the hash code of (

db02.stil.SALES_DT). The input

table will not be cached in memory, but it is eligible for

synchronized scanning. The result spool file will not be

cached in memory. The size of Spool 2 is estimated with high

confidence to be 673,992,605 rows (78,857,134,785 bytes).

The estimated time for this step is 4 minutes and 22 seconds.

Tags (1)
2 REPLIES
Junior Contributor

Re: Partition Elimination

The date conditions are simply pushed into the stil table like any other condition resulting in partition elimination based on hard-coded values.

I don't have access to a TD13.10 right now, but for your example 1i would usually expect an access to SIMS first, followed by a (product) join to stil "enyhanced by dynamic partition elimination".

What are the DDL and stats for the SIMS table?

Dieter

Enthusiast

Re: Partition Elimination

Hi,

I have a question. We have a table having column with timestamp data type. Partition is applied on the column by casting it to date data type. attached below.

Col1 TIMESTAMP(0))

PRIMARY INDEX ( Col2 )

PARTITION BY RANGE_N(CAST((Col1 ) AS DATE AT TIME ZONE INTERVAL '3:00' HOUR TO MINUTE ) BETWEEN DATE '2012-01-01' AND DATE '2014-12-31' EACH INTERVAL '1' DAY );

Do you think this casting of the partitioning column can have a performance issue in insertion and sel queries?

Your reponse will be appreciated