Re: Query Optimizer does not ignore constant condition?

h00_at_hotmail.com (hemal) wrote in message news:<16c83c3f.0308251108.6490ca2f_at_posting.google.com>...
> Oracle version is 9i.
>
> I don't know how to retrieve the execution plan, optimizer_mode and
> the information about when the tables were analyzed. Can you please
> tell me how?
>
> I spoke the DBA and he did say something about the optimizer used.
> According to him, the rule bases optimizer works better in such cases.
> "Queries involving OR conditions can be difficult for the cost-based
> optimizer to resolve efficiently. Sometimes (but not always) a
> histogram can help. Otherwise, FULL or USE_CONCAT and INDEX hints can
> be used to select the best execution plan if OR condition is
> mandatory"
>
> But, but....I would consider this particular problem to be, er,
> elementary. All these optimizer_mode and table statistics are required
> for deciding that ((1=0) or x) is equivalent to (x)? I am surprised.
>

Well, it may SEEM elementary, but how would you code the optimizer to recognize it? Remember that extra clause might be nearly anything: OR -1=0
OR 2+2=3
OR -1=1
OR 0=1/0
OR 'JULY 1 1970' = '07-01-70' In theory
<any expression evaluating to a constant>

• <any expression evaluating to another constant>

But back to your practical problem. It is a long shot, but try putting the constant expression at the other end of the where clause. IOW if now you use:

select * from tables
where a = b
and 0=1;

change it to be

select * from tables
where 0=1
and a = b;

I really don't know if that will help, but the optimizer does seem to check expressions last to first. If it gets a chance to check the real expressions first, MAYBE it will help.

Caveat THIS IS ONLY A GUESS, LONGSHOT, PIE-IN-THE-SKY, crack-pot idea.

Yes you may call me a crackpot if I'm wrong. And I fully expect to be wrong. 8^) Received on Tue Aug 26 2003 - 20:26:53 CEST