How Oracle execute a select on a partitioned table with a date range criteria

Question

currently we have a working query that selects from a large partitioned table for processing. The table is partitioned by daily range, with 1 day of data in 1 partition.

The query is:

SELECT /*+parallel(auto)*/ a.*
          FROM TBL_EXCLUDED a
          WHERE (not exists(
                select 1 from C_HISTORY history 
                inner join C_MATRIX_EXT ext on  ext.from_type = (CASE WHEN UPPER(history.TYPE_CD)<>'B' THEN 'C' ELSE 'B' END) and ext.from_channel = history.channel
                where history.mid= a.mid
                and ext.to_channel = a.channel and ext.to_type = (CASE WHEN UPPER(a.TYPE_CD)<>'B' THEN 'C' ELSE 'B' END)
                and history.channel_sent_dt>=TRUNC (SYSDATE-ext.duration+1)
                and history.channel_sent_dt<=TRUNC (SYSDATE)+1
                )   )

The explain plan is a bit long so i'll only show the part with the partition. Note that the PARTITION_START is 1 and PARTITION_STOP is KEY

Now if i change the criteria to search from a fixed date, rather than deriving it from sysdate, the cost will change:

SELECT /*+parallel(auto)*/ a.*
          FROM TBL_EXCLUDED a
          WHERE (not exists(
                select 1 from C_HISTORY history 
                inner join C_MATRIX_EXT ext on  ext.from_type = (CASE WHEN UPPER(history.TYPE_CD)<>'B' THEN 'C' ELSE 'B' END) and ext.from_channel = history.channel
                where history.mid= a.mid
                and ext.to_channel = a.channel and ext.to_type = (CASE WHEN UPPER(a.TYPE_CD)<>'B' THEN 'C' ELSE 'B' END)
                and history.channel_sent_dt>=TRUNC ( TO_DATE('10-09-2015','dd-mm-yy')  )
                and history.channel_sent_dt<=TRUNC ( TO_DATE('17-09-2015','dd-mm-yy')  )+1
                )   )

The explain now showing far lesser cost:

Will the actual execution differs from this plan? Also, although in the query with sysdate the cost is high, will the actual execution be far lesser since it should be able to select from the targeted partitions only?

And finally, will there be actual benefit in using the 2nd way of querying on range partitioned table rather than the 1st query?

Appreciate all the response and guidance, thank you.

David Aldridge · Accepted Answer · 2015-09-17T09:58:25.423

The explanation is pretty simple -- Oracle does not know until execution time what the value of sysdate is, so it uses "KEY" to indicate that this will be evaluated at execution time. At execution time the cost will be the same as entering a fixed date.

However, in the first query,

history.channel_sent_dt>=TRUNC (SYSDATE-ext.duration+1)

... the optimiser has inferred that it has no way of pruning the lower limit on partitions to be scanned, since it depends on join data that is not known at parse time -- it varies according to the data in another table. Hence the lower partition bound is set to 1.

Possibly you could optimise this by using dynamic SQL to calculate the actual lower bound on the partitions, but if the lower bound is actually going to be 1 in practice, then there would be no point.

Edit: If you know that the maximum value of ext.duration is going to be 7, then you can try to help the optimiser with:

and history.channel_sent_dt>=TRUNC (SYSDATE-ext.duration+1)
and history.channel_sent_dt>=TRUNC (SYSDATE-7+1)
and history.channel_sent_dt<=TRUNC (SYSDATE)+1

Be interested to hear if this works.

it shouldn't be 1 in actual execution as well. the query purpose is to select the last '7' days ( 7 is a mutable value) records, and hence the partitioned involved should be the latest partition, + the 7 behind. Shouldn't need to look from the start. — ipohfly, Sep 17 '15 at 09:49
I should add that this does not look like the sort of query that I would be looking for table partition pruning on. It would probably optimise better if it relied on index-based access methods. — David Aldridge, Sep 17 '15 at 10:14
thanks. will try to explore further and see how it could be optimized. there are some index created on the table but so far none of them is useful in this case.. — ipohfly, Sep 17 '15 at 10:33

score 0 · Answer 2 · answered Sep 17 '15 at 13:58

Two small additional point to the complete answer from David

1) If you duration is low and "without peeks"

 select max(duration) from C_MATRIX_EXT ext

You may generalize instead of

 and history.channel_sent_dt>=TRUNC (SYSDATE-7+1)

by adding

 and history.channel_sent_dt>=TRUNC (SYSDATE- (select max(duration) -1 from C_MATRIX_EXT))

You will see again KEY - KEY range, but with the same effect as with sysdate hard limit.

2) As the numer of records in the inner table is low (82 estimated), you may profit of NESTED LOOP join. You will need the outer table (partitined table) to be accessible by index. The advantage is, that only the correct range of partition (the range valid for the current row) is used.

How Oracle execute a select on a partitioned table with a date range criteria

2 Answers2