The acess path knows about DUAL and that it will return only 1 row. Create any dual table under another user and you will see that it returns all rows.
Anjo. "Khedr, Waleed" wrote: > Do you know of any reason that keeps Oracle implementing dual the way they > have now and its effect on all the other PL/SQL functions? > > Also I saw something on the Metalink where some one inserted many records in > dual and when selecting count(*) from dual it returns the right count but > when selecting any expression from dual it returns it only once. > > Also completely agree with: > > >If your application is using dual "like there is no tomorrow" > >then there is almost certainly something wrong with > >your application design or code which is a much > >more significant threat to performance - both through > >bottlenecks and wasted CPU. > > Thanks > > Waleed > > -----Original Message----- > Sent: Wednesday, April 24, 2002 11:59 AM > To: Multiple recipients of list ORACLE-L > > I think there is a completely different level at which > to view this issue. > > If your application is using dual "like there is no tomorrow" > then there is almost certainly something wrong with > your application design or code which is a much > more significant threat to performance - both through > bottlenecks and wasted CPU. > > Sure, it's cute to play with replacing DUAL with a > view called DUAL on x$dual - or playing slightly > more safely by recreating DUAL as an IOT, but > if you are hammering DUAL, it probably won't be the > CBC latch on dual's bucket that is the problem. > > BTW - counter-example for anyone thinking of > using a view. > > If your developers decide that they will get Oracle > to do all the arithmetic to avoid problems of IEEE > rounding or some such issue. and have millions of > lines like: > select 2.4 * 5.1 from dual; > select 18.7 / 2.1 from dual; > select 1 + 1 from dual; > > You will really kill the system, because every time > you hard-parse a statement containing a view, Oracle > re-executes a recursive query like: > select text from view$ where rowid = ... > > (Believe it - it has been done). > > Jonathan Lewis > http://www.jlcomp.demon.co.uk > > Author of: > Practical Oracle 8i: Building Efficient Databases > > Next Seminar - Australia - July/August > http://www.jlcomp.demon.co.uk/seminar.html > > Host to The Co-Operative Oracle Users' FAQ > http://www.jlcomp.demon.co.uk/faq/ind_faq.html > > |I think the issue of using SYS.DUAL vs. X$DUAL is much > |beyond just "response time". It is more related to > |"easing a potential bottleneck" in your database, in a > |production environment supporting multiple sessions. > |Given that it takes 5 LIOs (upto Oracle8i) and 3 LIOs > |(in Oracle9i for every access to SYS.DUAL, the issue > |then boils down to the contention for the "cache > |buffers chains" latch to access blocks in the database > |buffer cache. So just because it is only 5(3) LIOs, > |that does not make it OK. > | > |If your application is using SYS.DUAL "like there is > |no tomorrow", the cache buffers chains latch becomes > |your single point of contention. This is true, even if > |you have _DB_BLOCK_HASH_BUCKETS set to a value higher > |than its default. > | > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Jonathan Lewis > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Khedr, Waleed > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).