RE: SQL Query tuning help

From: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Date: Tue, 10 Sep 2002 15:09:07 -0500
Message-Id: <22528.293384@fatcity.com>

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 check, yes, I analyzed the tables.

Jared - RET has 281 values, pretty evenly distributed

Cary - Query returns 185 rows.

Bill - Thanks for the suggestions and insights.

Stephane - Good notice that only am values are used. Guess that is why Oracle accessed the data blocks anyway with my new indexes. Duh. Good = ideas.

Jeff - Thanks for the "Mickey Mouse" tag. I may need that in the = future.
Previously this data was on an old mainframe and the business itself = was
restricted by the inflexibility. My gut reaction was that they overcompensated.=20

Thanks everyone for the wonderful ideas. I was just given a hot = project, so
it may be a day or two before I get a chance to explore all of them, = but
I'll let you know.

=A0

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

=A0

-----Original Message-----
From: DENNIS WILLIAMS=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 Received on Tue Sep 10 2002 - 15:09:07 CDT