Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 31 Jan 2003 @ 15:09:31 GMT


     
  <Prev Next>   <<First <Prev Next> Last>>  


Subj:   Re: Query optimization: difference between inner join and derived table?
 
From:   John Grace

Andrew has a good point and this issue has some interesting implications.

We work with several RDBMS's and have noticed that moving constraints from the Where clause to the Join clause can have significant performance implication. More troubling, the same SQL can produce significantly different results on different RDBMS's.

This is a "rash generalization" from experience with several RDBMS's, Teradata, DB2, SQL Server and Oracle.

It seems a Filter or Constraint in the Where Clause will be applied when the table in first accessed. So if I ask for one row from the small table, one row is retrieved. (Very fast.) The Large Table is joined to the one row table in memory. (Again very fast.)

SELECT ...
FROM table1
INNER JOIN table2
ON table1.rid=table2.rid
Where table2.rid = "RED"


If I move the Constraint to the From clause, the entire small table is read (maybe slow) and the constraint is applied DURING the join to large table. (maybe slow).

SELECT ...
FROM table1
INNER JOIN table2
ON table1.rid=table2.rid and table2.rid = "RED"


To make this example a little more interesting, try coupling these queries with outer joins. The timing of the filter and Outer join can have performance implications and produce different results. For example,

SELECT ...
FROM table1
Left outer JOIN table2
ON table1.rid=table2.rid and table2.rid = "RED"
(all rows from the table1)


The constraint on RED "overrides?" the outer. I get what I would expect, just the RED items.

SELECT ...
FROM table1
Left outer JOIN table2
ON table1.rid=table2.rid
Where table2.rid = "RED"

The constraint is applied first, then the left outer is performed. I get ALL rows from table1 joined to the subset of "RED" items from table2! We have seen some optimizers turn off the outer and perform an inner when a constraint is detected in this manner. It matters what side on an outer the constraint is on.

Now, try nesting the outers and add some constraints.

SELECT ...
 FROM table1 Left Outer JOIN
  (
    table2 Left Outer JOIN
    (
      table3 Left Outer JOIN table4
        ON table3.rid=table4.rid
    )
    ON table2.rid=table3.rid
  )
 ON table1.rid=table2.rid
 Where table3.rid = "Chevy"

All the Left outers, Left of the constraint should switch to inners. I want only "Chevy".

We have found that explicitly switching all LEFT outers, LEFT of the constraint to inners is a safer approach along with a big performance booster. (just the opposite with Right outers)

We have also found that it makes sense, for performance reasons, to split up constraints on some tables, placing some constraints in the Where and some in the From. (Hit a specific index in the first pass.)

As I said earlier, this is a hodge-podge of issues we had getting one partner's 3NF, ERP apps working on multiple RDBMS's. Teradata is pretty good with these issues and some others are just now supporting SQL92. This may or may not apply to your specific situation, but you may find it interesting.

John Grace
Geppetto's Workshop



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023