Search code examples
sqloracleperformanceplsqldataset

How does a with clause effect the overall query

I have a query that has a with clause like such,

select column1, column2,........., columnN
  from table1, table2, table3, ......, tableN
 where table2.uniqueIndex = table1.uniqueIndex
   and table3.uniqueIndex = table2.uniqueIndex
   and ...... tableN.uniqueIndex = tableN-1.uniqueIndex

Here in my query, N = 23. When running this alone I get a runtime of about 1 minute. This is because is the data is very large itself and there is no filtering clauses that I have applied. Now I want to apply a filter clause, say table1.primaryKey = xxxxxxx, When I add this to the query I get a runtime of 0.197 secs which is nice.

However if I wrap the above query into a WITH block and then select from the subquery and then apply my filter clause, I get a runtime of more than 3 minutes.

Does oracle return the whole dataset of the subquery before it adds my filter when using the WITH statement. Also I understand that oracle save WITH blocks as stored querys so as to not run it every time. However, the data in my tables is everchanging and large, so should I not be using the WITH block to fetch this data and then apply my filters (user inputs)?

Essentially my query is slow, but it should not be slow especially when matching the primary key of one of my most important data table. The other tables are anyways small configuration tables.

Just want to understand how the WITH statement actually changes the efficiency and what are the 'never do' rules when using WITH clause (and why?).

Any documentation/tutorials is appreciated.

Solution

  • As already suggested, post an EXPLAIN plan for both the "fast" version and the "slow" version of the query. And post the actual queries - there may be relevant detail there.

    As a blind guess... are you using the correct datatypes in both of your queries? If your column is VARCHAR2 but you are using a number (not a string) in your "fliter clause" predicate, then that could prevent use of the indexes you expect.

    As for your expectation of how Oracle handles the "WITH" clause - it may materialize it, or it may not. Oracle does things the way it wants to. The plans should shed some light on this.

    From what you described, the query using the WITH construct should perform just as well as the one that has the predicate imbedded in the query.