Solved - Re: [thelist] Oracle "in" question & owed tip

Luther, Ron Ron.Luther at hp.com
Wed Nov 20 15:47:01 CST 2002


Hi Gang,

The problem was that I had a 7 table join in Oracle where one of the
where clauses needed to be changed from testing for one value to
testing for either one of two possible values.  Using an
"IN('value 1', 'value 2')" ruined the performance. [Not that it
was exactly 'stellar' before.  Running for one value was executing
in 4.5 to 5.5 minutes (depending on load on the DB box I guess).]

Anyway, I moved it to a UNION:

Select foo from tablez where stuff and field_z = 'value 1'
union
Select foo from tablez where stuff and field_z = 'value 2'

It timed out on me.  ;-(

Then my nice neighborhood dba suggested adding "hints":

Select /*+rule*/1, foo from tablez where stuff and field_z = 'value 1'
union
Select /*+rule*/1, foo from tablez where stuff and field_z = 'value 2'

Bingo!

Not only did the first run gave me what I was looking for, (and an
extra 600k in output), but it ran in about 16 seconds flat.  Very nice!

I suppose I should go back and try the hints with an "IN" clause - but
the heck with it - it's working now and I don't wanna jinx it.

<tip author="RonL." type="Oracle UNION">

Tried a Union in Oracle and ran into the "ORA-0933:SQL command
not properly ended" error message?

Oracle doesn't like more than one 'Order By' clause:

Select foo from tables where fielda='value 1'
ORDER BY 15, 16, 2, 13, 14
Union
Select foo from tables where fielda='value 2'
ORDER BY 15, 16, 2, 13, 14

is bad,

Select foo from tables where fielda='value 1'
Union
Select foo from tables where fielda='value 2'
ORDER BY 15, 16, 2, 13, 14

is good.

</tip>


Thanks,

RonL.
(Who really needs to read up more on these 'hint' thingies!)


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

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

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



More information about the thelist mailing list