Re: Question on using sql hints (PARALLEL and INDEX_FFS)

One thought - you haven't told us which version of Oracle you are using, and whether there are any init.ora parameters you have set which may be disabling the INDEX_FFS.

Once you have arranged for your query to be one that can be completed within the index, index_ffs() should be sufficient on its own to force that path if it isn't taken automatically - and about the only reason it won't be taken automatically is if the predicates make it cheaper to run a range scan.

One point I did overlook, however, is that the hint to make the query run in parallel is PARALLEL_INDEX(alias index), not
PARALLEL(alias).

Is it also possible that you simply mistyped the index_ffs hint. (I note that you quote it below as IND_FFS).

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.

G M wrote in message <50a5e6b6.0112010735.62fba313_at_posting.google.com>...


>I did the following test so that only one column is involved in SQL

>query:

>

>1. With index created "normally" (without parallel option)  and then

>get the explain plan for the following query:

>

>select subjspid  from  blastresults where  subjspid =18;

>

>

>        ID PA OPERATION          OPTIONS  OBJECT_NAME      POSIT COST

>---------- -- ------------------ -------- ---------------- -----

>--------

>         0    SELECT STATEMENT                             2909  2909

>         1 0  INDEX              RANGE SC BLASTRESULTS_SSP 1     2909

>

>

>2. drop INDEX MT.BLASTRESULTS_SSPID_INDEX;

>create INDEX MT.BLASTRESULTS_SSPID_INDEX on mt.blastresults (subjspid)

>TABLESPACE YPD STORAGE (initial 228712448 NEXT 22872064

>pctincrease 0) nologging parallel (degree 2);

>

>analyze index MT.BLASTRESULTS_SSPID_INDEX compute statistics;

>

>3. Get the explain plan for

>

>select /*+ PARALLEL (blastresults,2)  IND_FFS (blastresults

>BLASTRESULTS_SSPID_INDEX) */

>subjspid  from blastresults where  subjspid =18;

>

>

>        ID PA OPERATION          OPTIONS  OBJECT_NAME      POSIT COST

>---------- -- ------------------ -------- ---------------- -----

>--------

>         0    SELECT STATEMENT                             2909  2909

>         1 0  INDEX              RANGE SC BLASTRESULTS_SSP 1     2909

>

>

>This does not take any hints.

>

>Any comments?

>

>TIA

>

>GM