Re: Re: Tuning this sql?

1. where geneid = 155928 use a bind variable here
2. the table that geneid, comes from, use an inline view. this will decrease the size of your result that you are joining.
3. best option is to attempt to make your joins smaller. Best way to do that is to try to spin them into inline views or possible sub-queries.

select name, category2gene.id cid
from diseasemodels,observationdetails, category2gene where geneid = 155928 and
diseasemodels.phenotypedetailsid = observationdetails.phenotypedetailsid and category2gene.id = category2geneid order by name asc;

>
> From: Guang Mei <gmei_at_incyte.com>
> Date: 2003/04/25 Fri AM 10:10:21 EDT
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Subject: Re: Tuning this sql?
>
> Hi:
>
> All the joined columns have already been indexed. All tables are analyzed
> also.
>
> Guang
>
> On Thu, 24 Apr 2003, chao_ping wrote:
>
> > Guang Mei,
> > 3 Table joins, hash join or nested loop join. If the result from geneid = :155928 does not fetch many rows, you can think of create index on joined columns and use nested loops.
> >
> >
> >
> > Regards
> > zhu chao
> > msn:chao_ping_at_163.com
> > www.cnoug.org(China Oracle User Group)
> >
> > ======= 2003-04-24 19:06:00 ,you wrote£º=======
> >
> > >Hi:
> > >
> > >The following sql is called a lot in an application. I am trying to see
> > >if there is any way to speed it up:
> > >
> > >select name, category2gene.id cid
> > > from diseasemodels,
> > > observationdetails, category2gene where geneid = 155928 and
> > > diseasemodels.phenotypedetailsid = observationdetails.phenotypedetailsid
> > > and category2gene.id = category2geneid order by name asc;
> > >
> > >
> > >
> > >The execution plan and "10046 trace name context forever, level 12" are:
> > >
> > >
> > >Execution Plan
> > >----------------------------------------------------------
> > > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=105 Card=4 Bytes=168
> > > )
> > >
> > > 1 0 SORT (ORDER BY) (Cost=105 Card=4 Bytes=168)
> > > 2 1 HASH JOIN (Cost=52 Card=4 Bytes=168)
> > > 3 2 TABLE ACCESS (BY INDEX ROWID) OF 'CATEGORY2GENE' (Cost
> > > =10 Card=11 Bytes=110)
> > >
> > > 4 3 INDEX (RANGE SCAN) OF 'CATEGORY2GENE_GENEID_INDEX' (
> > > NON-UNIQUE) (Cost=3 Card=11)
> > >
> > > 5 2 HASH JOIN (Cost=41 Card=11368 Bytes=363776)
> > > 6 5 TABLE ACCESS (FULL) OF 'DISEASEMODELS' (Cost=28 Card
> > > =4337 Bytes=99751)
> > >
> > > 7 5 TABLE ACCESS (FULL) OF 'OBSERVATIONDETAILS' (Cost=11
> > > Card=33015 Bytes=297135)
> > >
> > >
> > >
> > >=====================
> > >PARSING IN CURSOR #1 len=269 dep=0 uid=19 oct=3 lid=19 tim=2044352232
> > >hv=735974675 ad=
> > >'9a3a2f7c'
> > >select distinct name, category2gene.id cid
> > > from diseasemodels,
> > > observationdetails, category2gene where geneid = 155928 and
> > > diseasemodels.phenotypedetailsid = observationdetails.phenotypedetailsid
> > > and category2gene.id = category2geneid order by name asc
> > >END OF STMT
> > >PARSE #1:c=0,e=1,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=2044352232
> > >BINDS #1:
> > >EXEC #1:c=1,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=2044352232
> > >WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1650815232 p2=1 p3=0
> > >FETCH #1:c=11,e=11,p=0,cr=132,cu=8,mis=0,r=1,dep=0,og=4,tim=2044352243
> > >WAIT #1: nam='SQL*Net message from client' ela= 1 p1=1650815232 p2=1 p3=0
> > >WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1650815232 p2=1 p3=0
> > >FETCH #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=4,tim=2044352244
> > >WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1650815232 p2=1 p3=0
> > >WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1650815232 p2=1 p3=0
> > >FETCH #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=4,tim=2044352244
> > >WAIT #1: nam='SQL*Net message from client' ela= 1 p1=1650815232 p2=1 p3=0
> > >WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1650815232 p2=1 p3=0
> > >FETCH #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=12,dep=0,og=4,tim=2044352245
> > >*** 2003-04-24 21:54:46.069
> > >WAIT #1: nam='SQL*Net message from client' ela= 1257 p1=1650815232 p2=1
> > >p3=0
> > >STAT #1 id=1 cnt=43 pid=0 pos=0 obj=0 op='SORT UNIQUE '
> > >STAT #1 id=2 cnt=43 pid=1 pos=1 obj=0 op='HASH JOIN '
> > >STAT #1 id=3 cnt=90 pid=2 pos=1 obj=184472 op='TABLE ACCESS BY INDEX ROWID
> > >CATEGORY2GE
> > >NE '
> > >STAT #1 id=4 cnt=91 pid=3 pos=1 obj=185376 op='INDEX RANGE SCAN '
> > >STAT #1 id=5 cnt=12619 pid=2 pos=2 obj=0 op='HASH JOIN '
> > >STAT #1 id=6 cnt=4337 pid=5 pos=1 obj=184528 op='TABLE ACCESS FULL
> > >DISEASEMODELS '
> > >STAT #1 id=7 cnt=33015 pid=5 pos=2 obj=184773 op='TABLE ACCESS FULL
> > >OBSERVATIONDETAILS
> > > '
> > >=====================
> > >
> > >
> > >And here are the row counts for each table:
> > >
> > >MT_at_max-SQL> select count(*) from DISEASEMODELS;
> > >
> > > COUNT(*)
> > >----------
> > > 4337
> > >
> > >MT_at_max-SQL> select count(*) from OBSERVATIONDETAILS;
> > >
> > > COUNT(*)
> > >----------
> > > 33015
> > >
> > >MT_at_max-SQL> select count(*) from CATEGORY2GENE;
> > >
> > > COUNT(*)
> > >----------
> > > 801871
> > >
> > >
> > >Any suggestions?
> > >
> > >Thank you very much.
> > >
> > >Guang
> > >
> > >
> > >--
> > >Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > >--
> > >Author: Guang Mei
> > > INET: gmei_at_incyte.com
> > >
> > >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: ListGuru_at_fatcity.com (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: chao_ping
> > INET: chao_ping_at_vip.163.com
> >
> > 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: ListGuru_at_fatcity.com (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: Guang Mei
> INET: gmei_at_incyte.com
>
> 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: ListGuru_at_fatcity.com (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: <rgaffuri_at_cox.net
  INET: rgaffuri_at_cox.net

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: ListGuru_at_fatcity.com (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).