Thanks. If I add the no.optimize keyword, it changes all joins to an "outer cartesian join". So that's even worse ... (I haven't tried actually running it :-)
If a LEFT JOIN and LEFT OUTER JOIN are the same thing, that adds another little oddity to the mix :-) Why is the optimiser using both terminologies when the input uses just one? :-) Not that that's important unless it gives IBM's engineers a clue ... Cheers, Wol -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ray Wurlod Sent: 21 July 2008 00:26 To: [email protected] Subject: RE: [U2] Strange SQL query optimisation ... LEFT JOIN and LEFT OUTER JOIN are the same animal. A left outer join must yield every row from the left input and (a) any matching rows from the right input and (b) nulls for any row that does not exist in the right input. To achieve this from the right input, the optimizer has chosen to use a full table scan. Why? I don't know. There is a "30% rule" that applies when deciding when to use an index, but there is no index involved here. What happens (as reported by EXPLAIN) if you specify NO.OPTIMIZE for each query? ------- u2-users mailing list [email protected] To unsubscribe please visit http://listserver.u2ug.org/
