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 different out of 1.3 million. I see why the optimizer chose a table
scan. It has to look through most of the table anyway. I would try it with an index of
each of your join fields plus a separate index on the ret field of the SA table. I
wouldn't even try to index any other fields on the AM or SO tables. Actually, 16
second response time didn't sound too bad to me considering the tables you described.
Bill Carle
AT&T
Database Administrator
816-995-3922
[EMAIL PROTECTED]
-----Original Message-----
Sent: Tuesday, September 10, 2002 2:19 PM
To: Multiple recipients of list ORACLE-L
Subject: SQL Query tuning help
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
AND so.code = 11
AND sa.ret = 'SB'
ORDER BY am.name
Tables:
am - 250,000 rows, 220,000 rows have active = 1, the others are 0.
so - 1.3 million rows, lid has 250,000 distinct values, key_ is unique,
code has 12 values, evenly distributed.
sa - 1.3 million rows, ret has 281 values, fairly evenly distributed.
so_key is pretty unique.
Now, you'll probably say there is essentially a 1-1 relationship between so
and sa. You are right, but the developer insists this flexibility is
essential.
The query executes in 16 seconds and returns 185 rows. This is felt to be
too slow for an online lookup screen.
explain plan results:
SELECT STATEMENT Cost = 2955
SORT ORDER BY
HASH JOIN
HASH JOIN
TABLE ACCESS FULL SA
TABLE ACCESS FULL SO
TABLE ACCESS FULL AM
Here is what I've tried so far:
Using hints to force Oracle to use indexes.
Query Plan
----------------------------------------------------------------------------
----
SELECT STATEMENT Cost = 62031
SORT AGGREGATE
NESTED LOOPS
HASH JOIN
TABLE ACCESS BY INDEX ROWID SA
INDEX FULL SCAN SO_KEY3
TABLE ACCESS BY INDEX ROWID SO
INDEX RANGE SCAN PRG_CODE3
TABLE ACCESS BY INDEX ROWID AM
INDEX UNIQUE SCAN LID6
Timing result 25 minutes
Next I tried creating new indexes that combine both the accessing column as
well as the retrieved column, thinking that Oracle could get the result from
the index block and not need to retrieve the data block.
create index test1 on am (lid, active);
create index test2 on sa (so_key, code);
SELECT STATEMENT Cost = 2951
SORT AGGREGATE
HASH JOIN
HASH JOIN
INDEX FULL SCAN TEST2
TABLE ACCESS FULL SO
TABLE ACCESS BY INDEX ROWID AM
INDEX RANGE SCAN TEST1
Hinting so Oracle will use the new indexes, for one table Oracle uses the
index only and for the other table, Oracle hits both the index and table
itself. Response time is slightly longer than the original query. At this
point I'm fresh out of ideas, so any ideas would be appreciated. Thanks.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: DENNIS WILLIAMS
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Carle, William T (Bill), ALCAS
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
