I am experiencing efficiency issues when using derived tables in queries. I can resolve this by breaking up the existing query into smaller queries but I would like to know technical reasons why derived tables would experience efficiency issues. Typical format: -
FROM (SELECT *
WHERE COLUMN2 = 'TEXT1'
ON T1.COLUMN1 = T2.COLUMN2
AND T1.COLUMN3 = T2.COLUMN3
Consider the primary index of your derived table ( first column of Table1 because of Sel *)
Is it the same as the primary index of Table1?
A derived table does not have a primary index. It is a spool file and it's distribution will be chosen by the optimizer based on future elements of the plan - eg the join columns.
In in this case where the derived table is a simple query on a table, the optimizer is going to rewrite it completely into a simple inner join.
As noted above, need the explain to understand what is happening with the query plan.
Glass - The example I provided was just to indicate the format, I wasn't trying to infer any meaning by the column names.
ToddAWalter - Thanks for the feedback