[thelist] Oracle "in" question & owed tip

[Luther, Ron]

Hi Rudy,

Thanks!  That helps a lot towards understanding the issue.

<analogy>
I used to spend quite a bit of time working with the xBase R&R
report writer.  If you ran a lot of data through a complex
query it would take a while to run.  One of the nice features [1]
however was a status line that would count through the records
you were reading, then count through the temp files it was
building, then 'count down' through merging the temp files
before displaying the data.

I hadn't thought of db engines working in a similar manner, but
I guess it makes sense.
</analogy>

Ok, so "field IN ('value 1', 'value 2')" isn't effective for me
for this project.

I'll give "(field = 'value 1') OR (field = 'value 2')" a whirl
when that machine frees up from other projects.

Maybe I'll give "Select blah WHERE field = 'value 1' UNION
Select blah WHERE field = 'value 2'" a whirl if the OR doesn't
help.

If these don't give me any traction, I'll get with my IM support
guys and see if they can 'collapse' some of these joins into a
view or a separate table for me. Maybe that will give me enough
speed to be able to 'spend' some on getting the "IN" to work.


Meanwhile, I'll go beat up the S&M {Sales & Marketing} folks
about restructuring their hierarchy to let me use a single value.

Thanks!


RonL.
[1] Another nice feature was the ability to run a selected
range of records ... say 500000 through 505000.  Which was
*very* helpful if you understood the structure of the file
and needed to test for a 'rare occurrence' that you knew
came into play near the end of the file. I miss that feature.



-----Original Message-----
From: rudy [mailto:r937 at interlog.com]

... 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