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.

_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp

Oracle LazyDBA home page