September 13, 2010

Subquery Factoring (4)

I’ve written before about the effects of subquery factoring (common table expressions – or CTEs) on the optimizer, and the way that the optimizer can “lose” some strategies when you start factoring out subquery expressions. Here’s another example I came across quite recently. It involved a join of about 15 tables so I’ve only extracted a few lines from the SQL and resulting execution plans.

We start with the original query, which had factored out an aggregate subquery then used it in place of an inline view:

You can see that the optimizer has created a result set (VIEW) at line 41 by scanning the entire customer_communications table, for a total of about 800,000 rows, aggregating the data by order_id. This is not very efficient becauase (a) I happen to have a very useful index on the customer_communications table that contains all the data I need, and (b) there are just a few input rows where I need to find this max(comm_date).

Note, by the way, that we have an outer join into the aggregate view, so Oracle cannot use complex view merging (CVM) to avoid the need to create the result set – but it could, in principle, use join predicate pushdown (JPPD) to create a result once for each input value.

So now we change the code to copy the subquery back to the inline position:

Notice that the hash join outer has changed to a nested loop outer, and the full tablescan has changed to an optimal index-only access using the ideal (for this query) index range scan (min/max). Moving the inline view into a common table expression at the head of the SQL was enough to stop Oracle from investigating this option – even though the optimizer immediately moved it back inline.

So be careful the next time you think about using subquery factoring to make your queries look cleaner and simpler – it’s a good idea, and a nice piece of technology, but sometimes it will cause your execution plans to change for the worse.

Related

Whenever I’m handed a query to tune, I immediately remove CTE’s, hints, anything that’s not plain vanilla. See what the optimizer has for me… and with regular frequency that’s all the ‘fix’ that’s needed. Premature optimization is a pathology that affects a wide variety of programmers and universally affects those with a little knowledge and an overinflated self-assessment of that knowledge.

Mike,
Since most queries that use hints don’t use them properly, removing the hints to see what happens is a good idea; but I wouldn’t risk rewriting the CTEs until I’d worked the execution path Oracle should have taken and figured out why it didn’t take it.

If I have to sort out problematical SQL it’s not usually the type of SQL that can safely be rewritten without doing a lot of tests to check that the query is still logically the same – so a manual rewrite comes very low on the list of priorities.

I’ve found many times in the past that the optimizer can get “confused” by complex queries as this example was by the sound of things. Trial and error is usually a good way to resolve performance issues in SQL and as the previous comment from Mike said, don’t optimize prematurely.

Trial and error? This is an not a clever way to understand what’s going wrong. More importantly, it usually takes more time to resolve an issue (it doesn’t really matter whether it’s performance related or not). In this case supplied data doesn’t have enough evidence that one or another approach to the query execution is efficient or not. To get an idea of where the time is spent, it’s needed to collect row source execution statistics, without it you are almost blind in the analysis process. That data would allow you to make a decision about how efficient Oracle was able to handle your query. After that, depending on whether cardinality estimates for the first row source in the join are correct, you can make a well-considered conclusion.

OK “trial and error” was a poor choice of words. I probably should have said proposing a change based on observations (facts such as statistics, actual execution plan, knowledge of application and the database) and then testing the new approach to ensure that theory matches reality. I wasn’t implying blindly trying a new approach in the hope it will work and then trying some other random change if that doesn’t work in the hope that eventually you will hit on something that does work. For all the theory though you can’t be sure of anything until you’ve tested it.

The more complex the query, the more important it is to understand the data and what the query is trying to do, otherwise your trial and error could easily give you an execution plan that happens to work for a while and then requires you to go through the whole process again a little while later.

My note on “Cartesian Merge Join” is one of the most popular notes on the blog – https://jonathanlewis.wordpress.com/2010/04/20/veiwing-figures/ but after hitting a peak in the early days of migration to 10g its popularity has dropped off as poeple realise that the optimizer can produce them through transitive closure and they aren’t necessarily a bad thing.

ANSI is something that I’m not keen on. Although strategically it’s the sensible choice and has a couple of options that are not available to Oracle’s dialect, I find that if you need to force an execution path onto a query that’s written in ANSI it’s a lot harder to do because of the way that the optimizer transforms ANSI before optimising it.

What happens to the query if you add the /*+ INLINE */ hint to the query?

I must say that I see CTE’s as the most important addition to SQL in all the years I have been working with SQL.

The possibility to actual structure your query so other people have a chance to consume a complex query bit-by-bit is soooo important when more than one person works on code… (The optimizer must be smart enough to do the text substitution suggested by JL)

The CTE was already placed inline by the optimizer – so the /*+ INLINE */ hint would have had no effect.

I agree that CTEs can be very helpful in clarifying the structure of a query – and when they are used well it’s remarkable how much easier it is to understand the intent of a SQL statement.

Unfortunately I’ve also found cases where people use them in a totally unsuitable fashion, making their SQL much harder to read and virtually impossible to visualise. If you ever find a programmer who introduces a new (nested) CTE for every table that he adds to a query – get rid of him immediately.

Depending on the way the procedure works you could populate a global temporary table as it starts and use that in your queries; or you could define a materialized view for the common portion and start the procedure with a refresh of the MV.

Depending on how you use the 8 sys_refcursors you could rewrite the code into a single sys_refcursor and does a union all – then you could use subquery factoring.

If you’re opening 8 cursors, by the way,have you set your isolation so that they all open at the same SCN ? If not you could have 8 sets of results which are not consistent with each other.

It’s not related to subquery factoring, because Oracle fails to produce correct results without it (10.2.0.5 & 11.2.0.1). Looks like Oracle lost join predicate somewhere on the step of subquery unnesting:

subquery factoring applied to the V1 (let’s say V2)
———–
with X as
(select a complex query here)
select *
from t1, X
where
union all
select *
from t2, X
where

The report using the view V2 with a filter takes longer than the unconstrained one.
I guess, it is because it is loading all the data from the complex query into the temp table, then goes on with the rest of the query.
Is there a way to tell ORACLE to evaluate the filter first and then proceed to the temp table trans formation ?

You haven’t mentioned a version of Oracle, and you haven’t supplied any execution plans – although we might guess from what you’ve said that the subquery is being materialized.

I’d have to guess, from what you have said, that the optimizer was using a simple filter pushdown (FPD) when the views were inline, but wasn’t able to do the pushdown when the subquery materialized. This sounds very like an (unpublished) bug that Karen Morten mentioned to me some time ago.

If you want a workaround – try creating the subquery as a real ‘in-database’ view.

[…] visits the product_pricing table twice might be a very good idea – because there is a special “index (min/max) range scan” optimisation that works very well if (a) have the right indexes in place (b) use a nested loop and (c) only ask […]