Hi:
I found that a query worked quite well on Oracle 8173 is running very slow
on Oracle 9i.
I doubled check init paramters and they are the same. The table involved
has about 20M rows. The tables has been analyzed in both cases. Is there
any thing I should look or set in 9i so that query can run as fast as 8i?
Better yet, what could be the reason that this query is using "BITMAP"
instead of regular index range scan? TIA.
Guang
PS: The query is
SELECT queryid, subjid, 100.0*identity/matchlen pct from blastresults
where ((subjspid in (456,789) and queryid = 123)
or (queryspid in (456,789) and subjid = 123))
and (identity/matchlen >= .200 or positive/matchlen >= .400)
order by blast.pvalToNumber(pval) asc, score desc;
-- 9i:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6987 Card=1 Bytes=42
)
1 0 SORT (ORDER BY) (Cost=6987 Card=1 Bytes=42)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'BLASTRESULTS' (Cost=69
85 Card=1 Bytes=42)
3 2 BITMAP CONVERSION (TO ROWIDS)
4 3 BITMAP OR
5 4 BITMAP AND
6 5 BITMAP CONVERSION (FROM ROWIDS)
7 6 INDEX (RANGE SCAN) OF 'BLASTRESULTS_QUERYID_IN
DEX' (NON-UNIQUE) (Cost=3)
8 5 BITMAP OR
9 8 BITMAP CONVERSION (FROM ROWIDS)
10 9 INDEX (RANGE SCAN) OF 'BLASTRESULTS_SSPID_IN
DEX' (NON-UNIQUE) (Cost=1528)
11 8 BITMAP CONVERSION (FROM ROWIDS)
12 11 INDEX (RANGE SCAN) OF 'BLASTRESULTS_SSPID_IN
DEX' (NON-UNIQUE) (Cost=1528)
13 4 BITMAP AND
14 13 BITMAP CONVERSION (FROM ROWIDS)
15 14 INDEX (RANGE SCAN) OF 'BLASTRESULTS_SUBJID_IND
EX' (NON-UNIQUE) (Cost=3)
16 13 BITMAP OR
17 16 BITMAP CONVERSION (FROM ROWIDS)
18 17 INDEX (RANGE SCAN) OF 'BLASTRESULTS_QSPID_IN
DEX' (NON-UNIQUE) (Cost=1282)
19 16 BITMAP CONVERSION (FROM ROWIDS)
20 19 INDEX (RANGE SCAN) OF 'BLASTRESULTS_QSPID_IN
DEX' (NON-UNIQUE) (Cost=1282)
-- 8i:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=184 Card=2 Bytes=84)
1 0 SORT (ORDER BY) (Cost=184 Card=2 Bytes=84)
2 1 CONCATENATION
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'BLASTRESULTS' (Cost=
11 Card=1 Bytes=42)
4 3 INDEX (RANGE SCAN) OF 'BLASTRESULTS_SUBJID_INDEX' (N
ON-UNIQUE) (Cost=3 Card=1)
5 2 TABLE ACCESS (BY INDEX ROWID) OF 'BLASTRESULTS' (Cost=
11 Card=1 Bytes=42)
6 5 INDEX (RANGE SCAN) OF 'BLASTRESULTS_QUERYID_INDEX' (
NON-UNIQUE) (Cost=3 Card=1)
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Guang Mei
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
