Archives of the TeradataForum
Message Posted: Fri, 31 Jan 2003 @ 15:09:31 GMT
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.)
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).
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,
The constraint on RED "overrides?" the outer. I get what I would expect, just the RED items.
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.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|