Archives of the TeradataForum

Message Posted: Fri, 04 Jun 2004 @ 15:55:39 GMT


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

Subj:		Re: Order of predicates

From:		Dieter Noeth

Martin O'Brien wrote:

Does the order of predicates matter when coding SQL in Teradata ? ( assuming no indexes satisfy the predicates ).

In other databases that I work with placing the most restrictive predicates first is better if you are 'anding' predicates and placing the least restrictive predicates first is better if you using 'Or'. By coding in this manner, the amount of comparisons is reduced ( and therefore CPU).

This is true, if the optimizer uses conditions in the order you define it, left to right. Most users don't care about that, but it might be a huge difference in CPU time.

Is Teradata's optimiser intelligent enough to re-order predicates no matter which order they are written in ?

IMHO No.

Some time ago i did run some checks with statistics and the order was always the same.

I don't know if any DBMS has an optimizer which will re-order those conditions if it knows about selectivity (from statistics).

For some time i believed that Teradata does it always right to left, but this is wrong. Maybe only the Teradata developers know it right ;- )

Just run some Explains:

     select * from tpcd100m.lineitem
     where l_partkey = 17000 AND l_linestatus = 'O';

           "(tpcd100m.lineitem.L_LINESTATUS = 'O') AND
           (tpcd100m.lineitem.L_PARTKEY = 17000)") into Spool 1 (group_amps),


     select * from tpcd100m.lineitem
     where l_partkey = 17000 OR l_linestatus = 'O';

           "(tpcd100m.lineitem.L_PARTKEY = 17000) OR
           (tpcd100m.lineitem.L_LINESTATUS = 'O')") into Spool 1 (group_amps),


     select count(*) from tpcd100m.lineitem
     where l_partkey = 17000 OR l_linestatus = 'O';

           "(tpcd100m.lineitem.L_PARTKEY = 17000) OR
           (tpcd100m.lineitem.L_LINESTATUS = 'O')").  Aggregate Intermediate


     select count(*) from tpcd100m.lineitem
     where l_partkey = 17000 AND l_linestatus = 'O';

           "(tpcd100m.lineitem.L_PARTKEY = 17000) AND
           (tpcd100m.lineitem.L_LINESTATUS = 'O')").  Aggregate Intermediate

So the rules might be:

If it's an aggregation or if it's an ORed condition -> left to right

If it's ANDed -> right to left

But you never know, so i always check Explain to see if it's an efficient order.

Dieter


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

Attachments

Library

Quick Reference