Hi:
I have been trying for two days to see if I could optimize this query
without much success. One of the programs here calls this query many many
times and I hope I could make it run faster. It typically take about 1 sec
to get the result. I have tried using "exists" to replace "in" and the
result is not good. All the columns involved in the "where" clause have been
indexed. b1 and b2 are bind variables that are passed in.
----
select distinct observationlist.geneid, pval, score,
Decode(evidenceCode, 3000900, 'E', 3000902, 'P', 3000906),
proteomeRefID, Decode(ReferenceType, 'I', 'Y', 'N'), reftarget
from mt.dualblastresults, mt.seqtable querySeq, isi.observationlist,
isi.termobs
where subjID = :b1
and queryID = QuerySeq.AASeqID
and querySeq.use='Y'
and querySeq.geneID=observationlist.geneid
and curationStatus='E'
and evidenceCode in (3000900,3000902,3000906)
and observationlist.id=obsID
and target='GeneID'
and termobs.termid in (select termid from isi.arc
where arctype in (2999999,3000000)
start with termid = :b2
connect by prior termid=parenttermid)
order by mt.blast.pvaltonumber(pval) asc, score desc, geneid,
decode(proteomerefid, null, 0, 1) desc;
--
This query typically returns 10 or less rows. mt.dualblastresults is a view,
all others are tables. BTW, I need "distinct" and "order by" in the query.
Here is the explain plan and row counts in tables and their definition.
Anyone has any suggestions to make it run faster?
TIA.
Guang
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=715 Card=1 Bytes=124
)
1 0 SORT (ORDER BY) (Cost=715 Card=1 Bytes=124)
2 1 SORT (UNIQUE) (Cost=662 Card=1 Bytes=124)
3 2 NESTED LOOPS (Cost=609 Card=1 Bytes=124)
4 3 NESTED LOOPS (Cost=553 Card=1 Bytes=118)
5 4 NESTED LOOPS (Cost=550 Card=1 Bytes=106)
6 5 NESTED LOOPS (Cost=280 Card=30 Bytes=1830)
7 6 VIEW OF 'DUALBLASTRESULTS' (Cost=112 Card=168
Bytes=8232)
8 7 UNION-ALL
9 8 TABLE ACCESS (BY INDEX ROWID) OF 'BLASTRES
ULTS' (Cost=102 Card=118 Bytes=2360)
10 9 INDEX (RANGE SCAN) OF 'BLASTRESULTS_SUBJ
ID_INDEX' (NON-UNIQUE) (Cost=3 Card=118)
11 8 TABLE ACCESS (BY INDEX ROWID) OF 'BLASTRES
ULTS' (Cost=10 Card=50 Bytes=1000)
12 11 INDEX (RANGE SCAN) OF 'BLASTRESULTS_QUER
YID_INDEX' (NON-UNIQUE) (Cost=3 Card=50)
13 6 TABLE ACCESS (BY INDEX ROWID) OF 'SEQTABLE' (C
ost=1 Card=57344 Bytes=688128)
14 13 INDEX (UNIQUE SCAN) OF 'ST_ASI_UN' (UNIQUE)
15 5 TABLE ACCESS (BY INDEX ROWID) OF 'OBSERVATIONLIS
T' (Cost=9 Card=499 Bytes=22455)
16 15 INDEX (RANGE SCAN) OF 'OBSERVATIONLISTGENEID'
(NON-UNIQUE) (Cost=2 Card=499)
17 4 TABLE ACCESS (BY INDEX ROWID) OF 'TERMOBS' (Cost=3
Card=2388115 Bytes=28657380)
18 17 INDEX (RANGE SCAN) OF 'TERMOBSIDINDEX' (NON-UNIQ
UE) (Cost=2 Card=2388115)
19 3 VIEW OF 'VW_NSO_1' (Cost=56 Card=7 Bytes=42)
20 19 SORT (UNIQUE) (Cost=56 Card=7 Bytes=126)
21 20 FILTER
22 21 CONNECT BY
23 22 INDEX (RANGE SCAN) OF 'ARC_TERMID' (NON-UNIQ
UE) (Cost=1 Card=2 Bytes=12)
24 22 TABLE ACCESS (BY USER ROWID) OF 'ARC'
25 22 INDEX (RANGE SCAN) OF 'ARC_TYPETERMPARENT' (
UNIQUE) (Cost=3 Card=8 Bytes=144)
SQL> select count(*) from mt.dualblastresults;
COUNT(*)
----------
22332188
SQL> select count(*) from mt.seqtable ;
COUNT(*)
----------
373505
SQL> select count(*) from isi.observationlist;
COUNT(*)
----------
2290858
SQL> select count(*) from isi.termobs;
COUNT(*)
----------
2388115
SQL> select count(*) from isi.arc;
COUNT(*)
----------
207375
SQL> desc mt.dualblastresults
Name Null? Type
----------------------------------------- -------- -------------------
ID NUMBER
QUERYID NUMBER
SUBJID NUMBER
MATCHLEN NUMBER
IDENTITY NUMBER
POSITIVE NUMBER
GAP NUMBER
PVAL VARCHAR2(16)
SCORE NUMBER
QUERYSTART NUMBER
QUERYEND NUMBER
SUBJSTART NUMBER
SUBJEND NUMBER
CCOMMENT VARCHAR2(300)
BLASTDATE DATE
QFRAME NUMBER
SFRAME NUMBER
QUERYSPID NUMBER
SUBJSPID NUMBER
SQL> desc mt.seqtable ;
Name Null? Type
----------------------------------------- -------- --------------------
ID NOT NULL NUMBER
AASEQID NUMBER
DNASEQID NUMBER
GENEID NOT NULL NUMBER
USE CHAR(1)
ALTSPLICE VARCHAR2(128)
MUTANT VARCHAR2(128)
STRAIN VARCHAR2(128)
CDSSTRING VARCHAR2(2000)
VALID CHAR(1)
GENOMEPROJ CHAR(1)
CDNA CHAR(1)
PARTIALNTERM CHAR(1)
PARTIALCTERM CHAR(1)
TRANSID NUMBER
CCOMMENT VARCHAR2(300)
EST CHAR(1)
CLASS VARCHAR2(128)
SEQDATE DATE
CURID NUMBER
SQL> desc isi.observationlist;
Name Null? Type
----------------------------------------- -------- --------------------
ID NOT NULL NUMBER
GENEID NUMBER
CURATIONTYPE NUMBER
PROTEOMEREFID NUMBER
SOURCEID NUMBER
SOURCETABLE VARCHAR2(25)
DESTID NUMBER
DESTTABLE VARCHAR2(25)
DESTDATE DATE
REFERENCETYPE VARCHAR2(1)
EVIDENCECODE NUMBER
CURATORID NUMBER
EDITORID NUMBER
UPDATESTAMP DATE
CURATIONSTATUS VARCHAR2(1)
ORIGINALSTAMP DATE
NEXTOBS NUMBER
TARGET VARCHAR2(15)
REFTARGET VARCHAR2(15)
TOOL VARCHAR2(25)
OLDGENEID NUMBER
SQL> desc isi.termobs;
Name Null? Type
----------------------------------------- -------- -----------
OBSID NUMBER
TERMID NUMBER
CONTEXT NUMBER
SQL> desc isi.arc
Name Null? Type
----------------------------------------- -------- ---------------
TERMID NOT NULL NUMBER
PARENTTERMID NOT NULL NUMBER
OBSID NUMBER
ARCTYPE NOT NULL NUMBER
ARCID NUMBER
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: gmei
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).
