RE: SQL Query tuning help

From: Carle, William T (Bill), ALCAS <wcarle_at_att.com>
Date: Tue, 10 Sep 2002 15:38:44 -0400
Message-Id: <22528.293376@fatcity.com>

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

 -----Original Message-----

From: 	DENNIS WILLIAMS [mailto:DWILLIAMS_at_LIFETOUCH.COM]=20
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 =3D am.lid

AND so.key_ =3D sa.so_key
AND am.active =3D 1
AND so.code =3D 11
AND sa.ret =3D 'SB'

ORDER BY am.name

Tables:

   am - 250,000 rows, 220,000 rows have active =3D 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 =3D 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 =3D 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      =20

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.=20
      create index test1 on am (lid, active);
      create index test2 on sa (so_key, code);

SELECT STATEMENT   Cost =3D 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
                             =20
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.

=20
Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com <mailto:dwilliams_at_lifetouch.com>



--=20

Please see the official ORACLE-L FAQ: http://www.orafaq.com


--=20

Author: DENNIS WILLIAMS
  INET: DWILLIAMS_at_LIFETOUCH.COM

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: ListGuru_at_fatcity.com (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

Received on Tue Sep 10 2002 - 14:38:44 CDT