 |
 |
Archives of the TeradataForum
Message Posted: Thu, 02 Dec 2004 @ 14:06:16 GMT
| Subj: | | Re: ("1=1") condition in the explain plan |
| |
| From: | | Victor Sokovin |
| | The thing that normally causes the optimizer to use a join condition of 1=1 is that you did not provide a proper join condition in your
query. Teradata cannot do a join without a join condition. If you do not provide one, it makes one up (1=1). The 1=1 is normally a Caresian
Product join because 1 is always equal to 1. Therefore, normally whenever you see this, go back to your SQL and finish writing all of the join
conditions. Whatever you do, do not run an SQL that has a product join with a condition of 1=1 unless you are trying to do a product join. A
product join can be controlled using non-join (residual) condition(s) in your WHERE. In the newer form of the syntax, this is a CROSS JOIN which
does not allow an ON comparison. | |
Sometimes reality is different, Michael, because the optimizer often rewrites our queries. The SQL submitted may have no Cartesian
product whatsoever but the optimizer still decides to use the product join on the spool files. Please refer to the excellent example provided by
Robert in an earlier posting. it should lead to the relevant part of the documentation.
In my experience, product joins occur *very* often in TD explain plans. I must admit some of the postings in this thread came as a surprise to
me. Some posters seem to have missed out on quite an important subject. It is never too late to learn new things, though, if the mind is still
open to them.
Regards,
Victor
| |
