Thank you Stephane! Your final idea of FIRST_ROWS as a winner! Oracle
slapped the data back in just a second. Thanks everyone for the ideas to
try.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] mailto:[EMAIL PROTECTED]
-Original Message-
Sent: Tuesday, September 10, 2002 3:42
Dennis,
If you use the ordered hint and have sa then so then am and also hint to use
the index on sa(ret) then I think that would be about the best as you'd be
starting with the best filter ie 1.3m/281 giving less than 5000 on average
(assuming ret is indexed). I don't know if you'd have to
DENNIS WILLIAMS wrote:
I am trying to tune a SQL query on Oracle 8.1.6. I have tried several
optimizations, but
so far have made no improvements. I would appreciate any suggestions.
SELECT am.lid, am.name
FROM am, so, sa
WHERE so.lid = am.lid
AND so.key_ = sa.so_key
AND am.active = 1
Dennis,
What is the distribution of sa.ret?
I didn't see it included in an index.
Jared
DENNIS WILLIAMS [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
09/10/2002 12:18 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Dennis,
You're better off not having an index on the AM table. With 220,000 out of 250,000
rows having the same value, an index will do you more harm than good. You're not much
better off on the SO table with only 12 different values out of 1.3 million. The final
table SA has 281
Thanks everyone for your wonderful suggestions. And thanks for leaving the
hey stupid off your reply header :-)
Rachel - Thanks for the bitmapped idea. These tables don't change often, so
that may be a good alternative.
Iain - Thanks so much for the detailed suggestions.
Rick - Good sanity
Just in case anyone out there is interested, we use the term Mickey
Mouse schema to refer to a very specific design tactic. We're *not*
using the term's slang meaning of unimportant or uninspired.
(...Which always seemed odd to me, because MM is a really strong, high
quality brand.)
Subject: RE: SQL Query tuning help
Thanks everyone for your wonderful suggestions. And thanks
for leaving the
hey stupid off your reply header :-)
Rachel - Thanks for the bitmapped idea. These tables don't
change often, so
that may be a good alternative.
Iain - Thanks so much