RE: Re[2]: to_number question

From: Poras, Henry R. <Henry_Poras_at_dfci.harvard.edu>
Date: Fri, 16 Jul 2004 10:54:50 -0400
Message-ID: <0D3AAB332E29BA4F9D8BE559F244A914558BE6@phsexch27.mgh.harvard.edu>

Jonathan,

Funny, but I reach the opposite conclusion from the same starting point. This behavior of the optimizer will totally free us from considering what the optimizer does. We just need to phrase the query correctly. If we ask the right question (that's the tricky part) and make no assumptions about the physical execution of that query, we need not consider the physical access path at all.

Henry

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Jonathan Gennick Sent: Friday, July 16, 2004 9:32 AM
To: Stephen.Lee_at_DTAG.Com
Cc: oracle-l_at_freelists.org
Subject: Re[2]: to_number question

JG>* Related to the above, it would now seem that in order to
JG>write a statement containing a subquery, that you must be
JG>aware of, and think about, how that statement's execution is
JG>optimized. Yet databases are supposed to free us from
JG>worrying about optimization and physical access and the
JG>like.

Best regards,

Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:jonathan@gennick.com

Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://five.pairlist.net/mailman/listinfo/oracle-article, or send email to Oracle-article-request_at_gennick.com and include the word "subscribe" in either the subject or body.

Friday, July 16, 2004, 3:59:11 AM, Stephen.Lee_at_DTAG.Com (Stephen.Lee_at_DTAG.Com) wrote:
>> -----Original Message-----
>> Actually the subquery gets converted to a sql that has two predicates
>> grouped by "AND" (similar to yours).

SLDC> Well OK.  I've kept my mouth shut so far, and because I was getting some
SLDC> good info, I did not argue with the accusations that I "don't understand".
SLDC> But I think it's time to clarify some things.

SLDC> I DO understand 100%, and (if I may be so bold as to speak for others) those
SLDC> who have questioned the so-called logic understood 100%.

SLDC> Yes.  I'm very well aware that my SQL was essentially getting broken into
SLDC> two predicates and THEN the un-guaranteed order in the evaluation of
SLDC> predicates gets applied.  The people who argued that this should not be

the

SLDC> case understood it too.  The comments were an expression of disbelief (not
SLDC> lack of understanding) that the specifications concerning subqueries would
SLDC> be so loose and open ended as to allow this level of unpredictability.
SLDC> Clearly, that is the case.  But that doesn't mean we can't bitch about it.
SLDC> And bitching about it doesn't mean we don't understand it.  You bitch

about
SLDC> taxes, don't you?

SLDC> ----------------------------------------------------------------
SLDC> Please see the official ORACLE-L FAQ: http://www.orafaq.com
SLDC> ----------------------------------------------------------------
SLDC> To unsubscribe send email to:  oracle-l-request_at_freelists.org
SLDC> put 'unsubscribe' in the subject line.
SLDC> --
SLDC> Archives are at http://www.freelists.org/archives/oracle-l/

SLDC> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

SLDC> -----------------------------------------------------------------

----------------------------------------------------------------

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri Jul 16 2004 - 09:51:04 CDT