[thelist] Oracle "in" question & owed tip

[rudy]

>>  "Account_ID = '1234567890'" to
>>  "Account_ID IN ('1234567890', '2345678901')".
>>  It's killing the performance.
>
> Is there an index on the Account_ID column ?
> Maybe thats the reason for the poor performance

nice guess, ashok

but think about it, if there were no index on that column, then how can the
database possibly find only one value efficiently?  without a index it's
gotta be doing some kind of scan, right?  and a scan is the worst
performing search, yet the query when searching for one value is fine, but
when searching for two, it barfs

jerry, IN isn't like LIKE at all    ;o)

[aside: there's a good example of why i capitalize some words]

... but your point is correct, some sql constructs perform badly by their
very nature

LIKE '%xxxx[%]' is bad because every row has to be looked at, which is not
the case with a finite IN (xxx...) condition

ron, an IN (xxx...) condition probably generates a temp table, which then
has to be merged against other tables, i.e. the intermediate result sets
that the database is building from those 7 tables you mentioned

whereas with an equality condition for a single value, it has a different
internal lookup method

the optimizer would not set up a loop to repetitively use the single value
lookup method, so if you give it more than one value to test, it prefers to
merge tables, even if it's only two values

i guess i should also state that all of the above starting right after
"ron,..." is total conjecture on my part

oracle guys that can tune a seven-table join from solid experience, rather
than my vague notions, deserve the big bucks they are paid


rudy