All Forums Database
firstj 8 posts Joined 05/08
20 Nov 2015
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
        ;

 

 

 

Glass 225 posts Joined 04/10
20 Nov 2015

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
 
 

ulrich 816 posts Joined 09/09
20 Nov 2015

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

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

ToddAWalter 316 posts Joined 10/11
22 Nov 2015

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. 

firstj 8 posts Joined 05/08
14 Dec 2015

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

You must sign in to leave a comment.