Message-Id: <22528.293384@fatcity.com> From: DENNIS WILLIAMS Date: Tue, 10 Sep 2002 15:09:07 -0500 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 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@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@lifetouch.com --=20 Please see the official ORACLE-L FAQ: http://www.orafaq.com --=20 Author: DENNIS WILLIAMS INET: DWILLIAMS@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@fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L