scratch my last email. I wasnt thinking straight. I was thinking of something else. Jerome is right. 'IN' is faster when the cost of the sub-query is small. Exists is faster when the cost of the result set is high.
sorry about that. my last post is incorrect. > > From: "Whittle Jerome Contr NCI" <[EMAIL PROTECTED]> > Date: 2003/06/06 Fri PM 03:10:24 EDT > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: RE: query run time vs IN list elements > > Guang, > > 1. Just because you created an index doesn't necessarily mean Oracle is using it > especially if using CBO and you haven't analyzed the table and index after the index > creation. Try using a hint. > > 2. If IN isn't meeting your needs, try converting it to an EXISTS statement. > > Jerry Whittle > ASIFICS DBA > NCI Information Systems Inc. > [EMAIL PROTECTED] > 618-622-4145 > > > -----Original Message----- > > From: gmei [SMTP:[EMAIL PROTECTED] > > > > Hi: > > > > Today I have something I don't fully understand. I have oracle 8173 on Sun > > Solaris. I have the following query that runs pretty fast when the number of > > elements in the "IN" list is small. But if I kept adding more "geneids" in > > the IN list, my query time increased dramatically. Now there is no index on > > any columns on the table. I got very similar results even if I created index > > on gene2disease2H.geneid. So this seem to suggest this situation has not to > > do with index. > > > > So my question is: why did I see the sigificant time increase when I only > > add one more geneid? > > > > TIA. > > > > Guang > > > > > <<< cut >>> > >Title: RE: query run time vs IN list elements
Guang,
1. Just because you created an index doesn't necessarily mean Oracle is using it especially if using CBO and you haven't analyzed the table and index after the index creation. Try using a hint.
2. If IN isn't meeting your needs, try converting it to an EXISTS statement.
Jerry Whittle
ASIFICS DBA
NCI Information Systems Inc.
[EMAIL PROTECTED]
618-622-4145
-----Original Message-----
From: gmei [SMTP:[EMAIL PROTECTED]
Hi:
Today I have something I don't fully understand. I have oracle 8173 on Sun
Solaris. I have the following query that runs pretty fast when the number of
elements in the "IN" list is small. But if I kept adding more "geneids" in
the IN list, my query time increased dramatically. Now there is no index on
any columns on the table. I got very similar results even if I created index
on gene2disease2H.geneid. So this seem to suggest this situation has not to
do with index.
So my question is: why did I see the sigificant time increase when I only
add one more geneid?
TIA.
Guang
<<< cut >>>
