Re: Performance issue on query doing smart scan

 *Actually the partition column I_DT is on TAB2 which is part of another with clause query but full scanning that table is not the problem here as we see from the sql monitor. It's the scanning of TAB1 which is partitioned on PART_DT is causing the issue. And we do have an explicit filter on this column for partition pruning, but anyway our requirement is to scan across all the ~180 partitions only.*

Given that you have a table TAB1, which you've included in a CTE that you've aliased TAB1, which then gets an alias of "a" in the main query; with an equally bad appalli1ng n1aming convention involving TAB2 it's not entirely surprising that I managed to cross over the two names when working through your problem. Do you really have such bad coding standards?

The point remains, however you said that you scanned all the partitions, and that the partitioning column was part_dt, and the SQL you sent us originally has the predicate "AND a.part_dt BETWEEN :1 AND :2". BETWEEN is a very bad option for a predicate on a partition key.A partition contains data that is strictly less than the upper boundary, and greater than or equal to the upper bound of the previous partitions, so the ideally range-based predicate should be of the form:

   part_dt >= {date1} and part_dt < {date2} If you do this than Oracle may be able to discard the predicate (i.e. not waste CPU testing it) if it can deduce that every row in every relevant partition is going to satisfy the predicate. This is particularly interesting for EXADATA with compressed tables, since it MAY mean (and I can't check it) that Oracle won't have to decompress the column to check the value, and this MIGHT have a noticeable benefit for some of the compression options in terms of CPU.

Index compression and table compression have nothing to do with each other. Also the "much longer running" of the query when forced through
(AANUM, TXID) isn't about the large number of distinct values of TXID, it's
about the relatively small number of distinct values of AANUM or, to be more accurate, the large number of rows for each value of AANUM that are now being acquired by single block access.

Your thoughts on compression do make sense as a general strategy for reducing storage space and increasing the probability of caching index leaf blocks. Since you have 173 million distinct value for AANUM in a table with 56 Billion rows then on average each value of AANUM has about 330 related rows and if this table were not partitioned you would automatically say that this is a good index for compression. BUT this is a local index on a table with 180 partitions, so each partition MIGHT only have one or two index entries for each value of AANUM in which case you probably wouldn't want to compress it; on the other hand perhaps the AANUM has values which correlate with time, so maybe any one value of AANUM appears in only about 10 partitions, in which case each partition would average 33 rows per AANUM and the index would be worth compressing.

Regarding index columns:

• WHERE a.aanum = stage.anum AND a.did = stage.did AND a.d_ind = 'Y' AND a.o_id LIKE CASE WHEN stage.al <http://stage.al> = 'XXXX' THEN stage.o_id ELSE a.o_id END AND a.part_dt BETWEEN :1 AND :2*
• WHERE TAB1.anum(+) = stage.anum AND TAB1.did(+) = stage.did AND TAB2.anum(+) = stage.anum AND TAB2.did(+) = stage.did AND TAB1.pnm(+) = stage.pnm AND TAB2.pnm(+) = stage.pnm*

I made two mistakes with my advice on (anum, did).

First, I forgot that the final where clause was against CTEs, so I missed the need to have d_ind and o_id in the index if you wanted to avoid visiting the table unnecessarily; secondly I missed the TAB1.pnm predicate that was in the middle of your TAB2 predicates in the final where clause - you'd need to have this in the index as well to avoid visiting the table. Again there's a detail to check in your coding standards - the three TAB1 predicates should have been together to avoid the risk of that mistake happening. (Just to add a positive note, I approve of the "next_table = current_table" arrangement of the predicates, some people would have written "stage.anum = TAB1.anum(+)")

Two more thought on indexing - a predicate like d_ind='Y' suggests the column is a yes/no column.
a) If almost all the data has d_ind='Y' then there may be no significant benefit in having that column in the index. If only a tiny fraction of the data has d_ind='Y' then if you can modify the query, you might be able to create a very small function-based index that would make this query much more efficient while adding very little to the workload. b) The list of relevant columns for the index has gone up to 5 for the "perfect" index - in no particular order: aanum, did, d_ind, o_id, pnm. If you know your data you may be able to decide that a combination of 4 or 3 of these columns is "good enough" to reduce the workload dramatically at query time while not adding too great a maintenance overhead.

Finally a generic warning:
*a.o_id LIKE CASE WHEN stage.al <http://stage.al> = 'XXXX' THEN stage.o_id ELSE a.o_id END *

This is a variant of a far commoner construct which is erroneously written as the model for "give me everything unless the user requests specific rows", viz:
*where colX = nvl(:bind_variable, colX)*
(See: https://jonathanlewis.wordpress.com/2007/01/09/conditional-sql/ )

The "LIKE" is sufficiently different that it's possible the predicate is doing exactly what it's supposed to do, but it's worth mentioning that if o_id is NULL then the predicate evaluates to false (even when stage.al = 'XXXX') and this may not be the intention.

Regards
Jonathan Lewis

--
http://www.freelists.org/webpage/oracle-l