RE: sql query optimization

Given the low elapsed time for each iteration of the query I wonder if the problem might be susceptible to either of the following approaches.

1. Calling the query less often. I'm guessing from the object names etc that this is some sort of scientific analysis program, and it may be that you are repeatedly calling the same logic with different binds when you could be doing some sort of batch processing.
2. Creating a Materialized view that will serve the query and could be stored if necessary in the keep pool.

Niall

> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
> Behalf Of gmei
> Sent: 10 June 2003 22:59
> To: Multiple recipients of list ORACLE-L
> Subject: 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
> 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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Niall Litchfield
  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).