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

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

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<1007155689.19363.0.nnrp-02.9e984b29_at_news.demon.co.uk>...
> Index fast full scan can only work if all the
> data required by the query is contained in
> the index. In your case, the index is only
> on one of the three columns in the query.
>
> --
> 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.0111301059.1b7a984c_at_posting.google.com>...
> >Hi,
> >
> >I want to improve an query by using Sql hints. The actual query is
> >complicated. I narrowed down the situation and found that the key is
> >this query:
> >
> >select id from blastresults where queryspid =18 and subjspid =18;
> >
> >it's explain plan is:
> >
> > ID PA OPERATION OPTIONS OBJECT_NAME POSIT COST
> >---------- -- ------------------ -------- ---------------- -----
> >--------
> > 0 SELECT STATEMENT 5441 5441
> > 1 0 TABLE ACCESS BY INDEX BLASTRESULTS 1 5441
> > 2 1 INDEX RANGE SC BLASTRESULTS_SSP 1 728
> >
> >
> >BLASTRESULTS_SSP is the index on column "subjspid". I created the
> >index by just using "normal" way: (
> >
> >I want to try parallel scan on index to see if I can improve the
> >speed.
> >
> >1. Oracle db version is 805 and on Sun Solaris box
> >2. There are 4 CPUs on the unix box
> >3. Optimizer_mode is Choose
> >4. FAST_FULL_SCAN_ENABLED = true (in init.ora)
> >5. All Tables have been analyzed (using "analyze table compute
> >statistics")
> >6. I created the index this way:
> >create INDEX MT.BLASTRESULTS_SSPID_INDEX on mt.blastresults (subjspid
> >)
> >TABLESPACE INDEXES STORAGE (initial 228712448 NEXT 22872064
> >pctincrease 0) nologging parallel (degree 2);
> >
> >If I put the hints like this , I got the same execution plan:
> >
> >select /*+ PARALLEL (blastresults,5) IND_FFS (blastresults
> >BLASTRESULTS_SSPID_INDEX) */
> >id from blastresults where queryspid =18 and subjspid =18;
> >
> > ID PA OPERATION OPTIONS OBJECT_NAME POSIT COST
> >---------- -- ------------------ -------- ---------------- -----
> >--------
> > 0 SELECT STATEMENT 5441 5441
> > 1 0 TABLE ACCESS BY INDEX BLASTRESULTS 1 5441
> > 2 1 INDEX RANGE SC BLASTRESULTS_SSP 1 728
> >
> >So my questions are: What should I do so oracle will use "parallel"
> >query option (using multiple cpus) to scan the index?
> >
> >TIA.
Received on Sat Dec 01 2001 - 09:35:26 CST