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 Fri Nov 30 2001 - 15:29:47 CST
