Oracle Performance Tuning: Query Subfactoring
Query Subfactoring
Looking for Answers
Have you ever tried to analyze a complicated sql, but there were so many clauses or inline views, that you hardly knew where to start? Well, I've been saddled with that type of code many times. Fortunately, there is a better alternative. It's called "Query Subfactoring," and it's a great way to make code more manageable.
The "With" Syntax
Some folks call this the "With" syntax. Using this feature makes your code much easier to follow, and much easier to debug--both functionality-wise and performance-wise. Use of a lot of inline-views can make the code nearly impossible to understand.
Here's what it looks like--note that each "view" is enclosed within parentheses:
With View1 as (Select * from Table1 Where Col1 = 'ABC'),
View2 as (Select * from Table2 Where Col1 = 'DEF')
Select Count(*) from View1, View2 Where View1.Col1 = View2.Col1;
In the example above, I create just two "views," but you can do as many as you want. Using this approach, you will find it much easier to tune complicate sql. When this format, you can simply tune each part, piece by piece. It's as though you were creating separate tables.
So, for the above example, I would get the "View1" running smoothly, then "View2." When all the preliminary parts are running well, you can then concentrate on tuning the main body. Simple!
Celebrating in the secret DBA spa
Use the "Materialize" Hint
It's often helpful to use the MATERIALIZE hint in the preliminary views. This way, the optimizer won't try to change execution plans at the end, since the early views are already "materialized" behind the scenes. If you don't use that hint, then Oracle won't actually build-up the separate parts.
I have used the "With" method countless times. It's easy for developers to get the idea, and I have helped many of them use it. I bet you will find it equally useful.
Where old AWR Reports go
Query Subfactoring
