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 PM To: Multiple recipients of list ORACLE-L 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 > 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]> Dennis, I note that your select list is only made of columns from am. Your entry points are so.code and sa.ret, the second one being the most selective. I don't think that on such a volume a nested loop would be any better than a hash join between the two, so this part of the Oracle plan needs no change. However, a nested loop is probably what you need with am. I would try things such as SELECT am.lid, am.name FROM am WHERE am.lid in (SELECT so.lid from so, sa WHERE so.key_ = sa.so_key AND so.code = 11 AND sa.ret = 'SB') AND am.active = 1 ORDER BY am.name which may give the same plan as your first example; if this is the case, perhaps that SELECT am.lid, am.name FROM (SELECT so.lid from so, sa WHERE so.key_ = sa.so_key AND so.code = 11 AND sa.ret = 'SB') x, am WHERE am.lid = x.lid AND am.active = 1 ORDER BY am.name will give a better result. If it still doesn't, try the ORDERED hint after the first SELECT. If it still doesn't, add USE_NL(am) after ORDERED but I'd rather avoid it. Thinking while I'm typing, perhaps that all you need is a FIRST_ROWS hint. -- HTH, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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: 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).
