]
Sent: Friday, June 06, 2003 2:14 PM
To: [EMAIL PROTECTED]
Cc: [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
an 'in' is just an 'or' so as soon as one value in your in list is found it has
accomplished its task. so if there are alot of possibilities to choose from oracle has
to search less records to find one that it wants.
From: gmei [EMAIL PROTECTED]
Date: 2003/06/06 Fri PM 02:09:44 EDT
To:
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
Title: RE: query run time vs IN list elements
Hi:
1. I
should have mentioned that I analyzed the index after creating the index, also I
looked at the explain table in both situations:
Execution
Plan--
0 SELECT STATEMENT Optimizer
[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
ok i think i figured out what is going on. sorry for all the emails. long day...
Look at your query. You are including a count and a group by. Both are costly
operations. As you increase the values in 'IN' you are increasing the size of the
resultset.
COUNT and group by need to work harder
Subject: Re: query run time vs IN list elements
ok i think i figured out what is going on. sorry for all the
emails. long day...
Look at your query. You are including a count and a group by.
Both are costly operations. As you increase the values in
'IN' you are increasing the size