Wasn't there a whitepaper somewhere that said that it may be more efficient to use an index with NL, even if the entire table fits in a single block? A quick scan of my saved ORACLE-L messages didn't reveal anything.
Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA > -----Original Message----- > From: Mladen Gogala [mailto:[EMAIL PROTECTED] > Sent: Friday, October 31, 2003 11:25 AM > To: Multiple recipients of list ORACLE-L > Subject: Re: 9i on RHAS3 > > > Rich, I don't have AS 3.0, I'm using regular RH 9 and RH 8 > based worsktations, with gcc 3.2 > (gcc-gnat-3.2.2-5,gcc-3.2.2-5) and curiously enough, the > installation worked right out of > the box, with a quirk with linking context ("undefined > symbol"), but I was able to ignore the > error and proceed. Oracle works well, no complaints so far. > There is a thing that confuses me, > but it's generic. I tried on a Solaris8 box and the result > was the same (9.2.0.4). Here is > what confuses me. Here are two execution plans, for the same > query (autotrace on explain, timing on). > More expensive plan takes less time. Shouldn't it be the > other way round? > Elapsed: 00:00:00.00 > > Execution Plan > ---------------------------------------------------------- > 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=14 Bytes=77 > 0) > > 1 0 NESTED LOOPS (Cost=7 Card=14 Bytes=770) > 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'CP_ACTIONS' (Cost=2 Ca > rd=14 Bytes=588) > > 3 2 INDEX (RANGE SCAN) OF 'CP_ACTIONS_EFF_I' (NON-UNIQUE) > (Cost=2 Card=14) > > 4 1 TABLE ACCESS (BY INDEX ROWID) OF 'CHG_TKR' (Cost=2 Card= > 1 Bytes=13) > > 5 4 INDEX (UNIQUE SCAN) OF 'SYS_C004800' (UNIQUE) (Cost=1 > Card=1) > > > Elapsed: 00:00:00.01 > > Execution Plan > ---------------------------------------------------------- > 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=14 Bytes=77 > 0) > > 1 0 HASH JOIN (Cost=6 Card=14 Bytes=770) > 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'CP_ACTIONS' (Cost=2 Ca > rd=14 Bytes=588) > > 3 2 INDEX (RANGE SCAN) OF 'CP_ACTIONS_EFF_I' (NON-UNIQUE) > (Cost=2 Card=14) > > 4 1 TABLE ACCESS (FULL) OF 'CHG_TKR' (Cost=4 Card=1602 Bytes > =20826) > > > > > > Has anyone tried 9i on RHAS3 yet? Metalink 252217.1 and > the venerable > > Werner Puschitz's site http://www.puschitz.com have many > icky hacks that > > seem to have to be done, including temporarily dropping > gcc323 to the highly > > unstable and buggy v2.96 (even GNU says not to use it! -- > it's not even > > listed as a release on their website). > > > > I'm guessing that Mr. Puschitz isn't on this list? Looks > like he knows the > > Oracle install on RedHat quite well. > > > > How's about it, Mladen? I'm not willing to scrap my Gentoo > box to test it. > > :) > > > > > > Rich -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- 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).
