Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!in.100proofnews.com!in.100proofnews.com!cycny01.gnilink.net!cyclone1.gnilink.net!spamkiller.gnilink.net!nwrddc02.gnilink.net.POSTED!53ab2750!not-for-mail From: "Anurag Varma" Newsgroups: comp.databases.oracle.server References: <894b11eb.0308080802.65850337@posting.google.com> <0TPYa.268$aE1.186@nwrddc01.gnilink.net> <3f33dc13$1_1@news.estpak.ee> Subject: Re: how to avoid fast full scan of index? Lines: 107 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2800.1106 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 Message-ID: <6nYYa.914$dm.638@nwrddc02.gnilink.net> Date: Sat, 09 Aug 2003 01:45:38 GMT NNTP-Posting-Host: 151.198.147.137 X-Complaints-To: abuse@verizon.net X-Trace: nwrddc02.gnilink.net 1060393538 151.198.147.137 (Fri, 08 Aug 2003 21:45:38 EDT) NNTP-Posting-Date: Fri, 08 Aug 2003 21:45:38 EDT Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:240158 In fact let me show you a working example: Following is cut pasted from sqlplus (oracle version 9.2.0.3): prod_local table has non-unique index on prd_id product table has a unique index on prd_id Both tables analyzed. The query is just to show fast full scan disabling ... (I know it can be written better) SQL> select pl.prd_id from product p, prod_local pl 2 where p.prd_id = pl.prd_id 3 and pl.prd_id between 10 and 100000 4 / Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=25 Card=40133 Bytes=401330) 1 0 NESTED LOOPS (Cost=25 Card=40133 Bytes=401330) 2 1 INDEX (FAST FULL SCAN) OF 'PROD_LOCAL_PK' (UNIQUE) (Cost=25 Card=42971 Bytes=214855) 3 1 INDEX (UNIQUE SCAN) OF 'PRODUCT_PK' (UNIQUE) SQL> select /*+ index(pl) */ pl.prd_id from product p, prod_local pl 2 where p.prd_id = pl.prd_id 3 and pl.prd_id between 10 and 100000 4 / Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=144 Card=40133 Bytes=401330) 1 0 NESTED LOOPS (Cost=144 Card=40133 Bytes=401330) 2 1 INDEX (RANGE SCAN) OF 'PROD_LOCAL_PK' (UNIQUE) (Cost=144 Card=42971 Bytes=214855) 3 1 INDEX (UNIQUE SCAN) OF 'PRODUCT_PK' (UNIQUE) ? Now tell me why after I put in the hint .. even though the cost was higher .. it took the hint and did *not* do a FFS? Anurag > "Tanel Poder" wrote in message > news:3f33dc13$1_1@news.estpak.ee... > > > select /*+ index(g) */ ..... > > > > > > IMHO: it will avoid the fast full scan. Since it will override the > > > index_ffs(g) clause. > > > > No, it will just tell CBO to use an index access path (fast full scan or > > skip scan for example). > > There is an event: "10120 CBO Disable index fast full scan", but it didn't > > work in my test env (9.2.0.1 W2k). > > > > Before I start speculating about optimizer_* parameters, it would be > helpful > > to have both execution plans and your db version information. Also make > > sure, that your second join SQL is correct, not missing any predicates > etc.. > > > > Tanel. > > > > > > > > Try that. > > > > > > Anurag > > > > > > > > > "oofoof" wrote in message > > > news:894b11eb.0308080802.65850337@posting.google.com... > > > > I have a large query generated by a tool that joins tables > > > > A-B-C-D-E-F-G-H-I > > > > I modified the query to produce the same results by joining tables: > > > > A-F-G-H-I > > > > > > > > A composite index is used by Oracle on table G in both queries, but in > > > > the first case, Oracle uses an index range scan on that index while in > > the > > > > second case, Oracle uses a fast full scan on the same index. So, > instead > > > of > > > > the second query running faster (joins fewer tables), it runs slower. > > > > The query selects only a small subset of entries in the index and the > > > index > > > > has the 2 columns required to satisfy the query (no table access is > > > necessary). > > > > What can I do to avoid the fast full scan? I could not find a hint > that > > > > accomplished it. Will building a histogram on the index help? > > > > > > > > > > > >