| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> sql query optimization
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
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(*)
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 servicesto: [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). Received on Tue Jun 10 2003 - 16:39:39 CDT
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
 |
 |