| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: A Cure for Madness
in case this message arrives twice, my apologies. I removed some quoting
this time ;-)
I agree Niyi -- just to add my two cents:
The SQL language is missing/lacking functionality to implement this
properly.
in relational algebra, "(R WHERE P) WHERE Q" is logically equivalent with "R
WHERE P AND Q" which is also equivalent with the other two permutations.
Obviously, the example that started this thread shows that SQL doesn't have corresponding syntax -- because apparently there are cases where these transformations are *not* allowed.
See bug 2235753 for an interesting similar case (unfortunately most, if not all, of the bug text is hidden I believe) and I think the only "workaround" in Oracle is to use hints like NO_MERGE or ORDERED_PREDICATES.
I don't think we would like the Oracle optimizer to stop considering subquery flattening, view merging, and predicate pushing in all "possibly suspect" cases, right?
Kind regards,
Lex.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Niyi
Sent: Wednesday, September 01, 2004 23:28
Subject: Re: Re[2]: A Cure for Madness
I personally think Oracle's to_number is working as it should, throwing an exception if you try to convert an invalid number, not SILENTLY converting it to null. The fundamental issue here is with predicate ordering determining the result of a query. I don't know the solution to this issue, but I know that to_number is just one of the scenarios that can run into it.
--- To unsubscribe - mailto:oracle-l-request_at_freelists.org&subject=unsubscribe To read recent messages - http://freelists.org/archives/oracle-l/09-2004Received on Thu Sep 02 2004 - 08:38:21 CDT
 |
 |