Derived table efficiency

Database
Enthusiast

Derived table efficiency

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: -

SELECT  T1.COLUMN1
FROM (SELECT *
FROM DATABASE1.TABLE1
WHERE COLUMN2 = 'TEXT1'
) T1
INNER JOIN
DATABASE1.TABLE2 T2
ON T1.COLUMN1 = T2.COLUMN2
AND T1.COLUMN3 = T2.COLUMN3
;

4 REPLIES
Enthusiast

Re: Derived table efficiency

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?

Rglass

Senior Supporter

Re: Derived table efficiency

Are your stats correct? Are stats defined? Can you share the explain?

Teradata Employee

Re: Derived table efficiency

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. 

Enthusiast

Re: Derived table efficiency

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