Question on using sql hints (PARALLEL and INDEX_FFS)

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 - 12:59:38 CST