Re: Order of where clause matters!

Evan wrote in message <39355A95.4BCF74B0_at_cadvision.com>...
>Oracle 8 documentation says the same thing. However, considering the
>documentation misleading is being a bit courteous. Oracle's license (as does
>most software) has a clause warrenting that the software conforms to documented
>behaviour.
>
>In the past, Oracle has accepted bug reports and provided patches on the
>non-conforming operator precedence issue. Unfortunately, such conformance costs
>processing overhead, so they routinely slide back to the non-conforming state.
>Dba's prefer the hit and miss/workaround in this case rather than accept the
>penalty of processing overhead. Generally, its not too much of an issue.
>

 [snip]
>
>Basically, between the original poster's example and your example, Oracle
>demonstrates a distinct error (ie non-conformance to their documentated
>behaviour and lack of predictability and control). This can't be considered
>acceptable/normal. A bug report seems an appropriate course of action. Hope you
>(as a dba) or the dba looking after the original poster will take this on.

Gentle nudge: Why don't *you* report the "error" to Oracle? After all, you're the one who misinterpreted the documentation. Neither I nor Steve Haynes, the original poster, expect brackets to work in this way.

If you do, I guarantee that the most Oracle will do is amend the documentation, they won't change the optimiser. If they changed it as you suggest, millions of lines of SQL all over the world would "break" from a performance point of view, as the optimiser would be forced to process bracketted conditions differently to how it does now.

Using brackets to coerce the optimiser to process the statement in a particular way is not the way to go, as you wouldn't be able to separate your logic requirements from your efficiency requirements (ie. a particular logic requirement would force the optimiser to optimise in a particular way which may be sub-optimal.)

Oracle provides the HINT syntax so you can influence how the optimiser works if you want greater manual control over the optimiser. But the whole gist of optimiser development is to minimise the need for developers to worry about performance, and let them focus on business requirements. If developers don't want to chance letting the optimiser determine the best execution plan, they can use the /*+ RULE */ hint.

Dave.

--
If you reply to this posting by email, remove the "nospam" from my email
address first.