Tom, Apologies if this has been covered already but isn't this a prime candidate for the sum(decode(????)) trick with perhaps instr used to do the match. I think you could take count of all records and subtract the 1 or 0 for each of the specific ones. The select part would look horrendous but overall I think it would be faster as it would allow you to drive off the phy_id which I assume would be indexed.
Iain Nicoll -----Original Message----- Sent: 06 December 2002 18:15 To: Multiple recipients of list ORACLE-L Tom, Actually it returns 1 record. Thanks Rick "Mercadante, Thomas F" To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> <[EMAIL PROTECTED] cc: ate.ny.us> Subject: RE: sql tuning help Sent by: [EMAIL PROTECTED] 12/06/2002 11:41 AM Please respond to ORACLE-L Rick, does this return *any* records at all? the only reason that I ask is that in the 'where' clause, it is saying: where p.phy_id = board_aaps.phy_id (+) and p.phy_id = board_aba.phy_id (+) and p.phy_id = board_abem.phy_id (+) and p.phy_id = board_abfp.phy_id (+) and p.phy_id = board_abim.phy_id (+) and p.phy_id = board_abp.phy_id (+) and p.phy_id = board_abr.phy_id (+) and p.phy_id = board_aobem.phy_id (+) and p.phy_id = board_aobfp.phy_id (+) and p.phy_id = board_aobim.phy_id (+) and p.phy_id = board_aobr.phy_id (+) and p.phy_id = board_other.phy_id (+) and p.phy_id = 1870; well, from my way of thinking, a single record from p.phy_id cannot be equal to all of the others at the same time. so no records should be returned at all. I think I would re-wright the entire mess using a set of UNIONS to accomplish the same thing. something like: select board_other.description strBrdNameOtherTHQuest ,decode(board_aaps.description, null,' ','X') ysnAAPSBoard ,decode(board_aba.description, null,' ','X') ysnABABoard ,decode(board_abem.description, null,' ','X') ysnABEMBoard ,decode(board_abfp.description, null,' ','X') ysnABFPoard ,decode(board_abim.description, null,' ','X') ysnABIMBoard ,decode(board_abp.description, null,' ','X') ysnABPBoard ,decode(board_abr.description, null,' ','X') ysnABRBoard ,decode(board_aobem.description, null,' ','X') ysnAOBEMBoard ,decode(board_aobfp.description, null,' ','X') ysnAOBFPBoard ,decode(board_aobim.description, null,' ','X') ysnAOBIMBAoard ,decode(board_aobr.description, null,' ','X') ysnAOBRBoard ,decode(board_other.description, null,' ','X') ysnOtherBoard from phy_boards pb, boards b, physicians p where p.phy_id = pb.phy_id and pb.board_id = b.board_id and (pb.expiration_date >= sysdate or pb.expiration_date is null) and b.description like 'AMERICAN ASSOCIATION OF PHYSICIAN SPECIALIST%' union select board_other.description strBrdNameOtherTHQuest ,decode(board_aaps.description, null,' ','X') ysnAAPSBoard ,decode(board_aba.description, null,' ','X') ysnABABoard ,decode(board_abem.description, null,' ','X') ysnABEMBoard ,decode(board_abfp.description, null,' ','X') ysnABFPoard ,decode(board_abim.description, null,' ','X') ysnABIMBoard ,decode(board_abp.description, null,' ','X') ysnABPBoard ,decode(board_abr.description, null,' ','X') ysnABRBoard ,decode(board_aobem.description, null,' ','X') ysnAOBEMBoard ,decode(board_aobfp.description, null,' ','X') ysnAOBFPBoard ,decode(board_aobim.description, null,' ','X') ysnAOBIMBAoard ,decode(board_aobr.description, null,' ','X') ysnAOBRBoard ,decode(board_other.description, null,' ','X') ysnOtherBoard from phy_boards pb, boards b, physicians p where p.phy_id = pb.phy_id and pb.board_id = b.board_id and (pb.expiration_date >= sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF ANESTHESIOLOGY%' etc. / Does this make sense? Hope this helps. Tom Mercadante Oracle Certified Professional -----Original Message----- Sent: Friday, December 06, 2002 7:54 AM To: Multiple recipients of list ORACLE-L Hi, Oracle 8.1.6 NT 4.0 I have a rather complex query a developer gave to me to try to improve performance. There are 3 tables used. All relevant columns used are indexed. The tables have been analyzed SQLWKS> select count(*) from physicians; COUNT(*) ---------- 340043 1 row selected. SQLWKS> select count(*) from boards; COUNT(*) ---------- 220 1 row selected. SQLWKS> select count(*) from phy_boards; COUNT(*) ---------- 450674 Below is the sql statement and explain plan. I see one FTS on 440,000+ records but cannot tell exactly what statement it is and how to resolve Any suggestions on how to optimize is appreciated. Thanks Rick select board_other.description strBrdNameOtherTHQuest ,decode(board_aaps.description, null,' ','X') ysnAAPSBoard ,decode(board_aba.description, null,' ','X') ysnABABoard ,decode(board_abem.description, null,' ','X') ysnABEMBoard ,decode(board_abfp.description, null,' ','X') ysnABFPoard ,decode(board_abim.description, null,' ','X') ysnABIMBoard ,decode(board_abp.description, null,' ','X') ysnABPBoard ,decode(board_abr.description, null,' ','X') ysnABRBoard ,decode(board_aobem.description, null,' ','X') ysnAOBEMBoard ,decode(board_aobfp.description, null,' ','X') ysnAOBFPBoard ,decode(board_aobim.description, null,' ','X') ysnAOBIMBAoard ,decode(board_aobr.description, null,' ','X') ysnAOBRBoard ,decode(board_other.description, null,' ','X') ysnOtherBoard from physicians p ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date >= sysdate or pb.expiration_date is null) and b.description like 'AMERICAN ASSOCIATION OF PHYSICIAN SPECIALIST%') board_aaps ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date >= sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF ANESTHESIOLOGY%') board_aba ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date >= sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF EMERGENCY MEDICINE%') board_abem ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date >= sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF FAMILY PRACTICE%') board_abfp ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date >= sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF INTERNAL MEDICINE%') board_abim ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date >= sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF PEDIATRICS%') board_abp ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date >= sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF RADIOLOGY%') board_abr ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date >= sysdate or pb.expiration_date is null) and b.description like 'AMERICAN OSTEOPATHIC BOARD OF EMERGENCY MEDICINE%') board_aobem ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date >= sysdate or pb.expiration_date is null) and b.description like 'AMERICAN OSTEOPATHIC BOARD OF FAMILY PHYSICIANS%') board_aobfp ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date >= sysdate or pb.expiration_date is null) and b.description like 'AMERICAN OSTEOPATHIC BOARD OF INTERNAL MEDICINE%') board_aobim ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date >= sysdate or pb.expiration_date is null) and b.description like 'AMERICAN OSTEOPATHIC BOARD OF RADIOLOGY%') board_aobr ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date >= sysdate or pb.expiration_date is null) and (b.description not like 'AMERICAN ASSOCIATION OF PHYSICIAN SPECIALIST%' and b.description not like 'AMERICAN BOARD OF ANESTHESIOLOGY%' and b.description not like 'AMERICAN BOARD OF EMERGENCY MEDICINE%' and b.description not like 'AMERICAN BOARD OF FAMILY PRACTICE%' and b.description not like 'AMERICAN BOARD OF INTERNAL MEDICINE%' and b.description not like 'AMERICAN BOARD OF PEDIATRICS%' and b.description not like 'AMERICAN BOARD OF RADIOLOGY%' and b.description not like 'AMERICAN OSTEOPATHIC BOARD OF EMERGENCY MEDICINE%' and b.description not like 'AMERICAN OSTEOPATHIC BOARD OF FAMILY PHYSICIANS%' and b.description not like 'AMERICAN OSTEOPATHIC BOARD OF INTERNAL MEDICINE%' and b.description not like 'AMERICAN OSTEOPATHIC BOARD OF RADIOLOGY%' and b.description not like 'NO BOARDS%' )) board_other where p.phy_id = board_aaps.phy_id (+) and p.phy_id = board_aba.phy_id (+) and p.phy_id = board_abem.phy_id (+) and p.phy_id = board_abfp.phy_id (+) and p.phy_id = board_abim.phy_id (+) and p.phy_id = board_abp.phy_id (+) and p.phy_id = board_abr.phy_id (+) and p.phy_id = board_aobem.phy_id (+) and p.phy_id = board_aobfp.phy_id (+) and p.phy_id = board_aobim.phy_id (+) and p.phy_id = board_aobr.phy_id (+) and p.phy_id = board_other.phy_id (+) and p.phy_id = 1870; Plan Table ---------------------------------------------------------------------------- ---- | Operation | Name | Rows | Bytes| Cost | Pstart| Pstop | ---------------------------------------------------------------------------- ---- | SELECT STATEMENT | | 2 | 2K| 88289 | | | | NESTED LOOPS OUTER | | 2 | 2K| 88289 | | | | NESTED LOOPS OUTER | | 1 | 1K| 28954 | | | | NESTED LOOPS OUTER | | 1 | 1K| 26322 | | | | NESTED LOOPS OUTER | | 1 | 1K| 23690 | | | | NESTED LOOPS OUTER | | 1 | 924 | 21058 | | | | NESTED LOOPS OUTER | | 1 | 809 | 18426 | | | | NESTED LOOPS OUTER | | 1 | 694 | 15794 | | | | NESTED LOOPS OUTER| | 1 | 579 | 13162 | | | | NESTED LOOPS OUTE| | 1 | 464 | 10530 | | | | NESTED LOOPS OUT| | 1 | 349 | 7898 | | | | NESTED LOOPS OU| | 1 | 234 | 5266 | | | | NESTED LOOPS O| | 1 | 119 | 2634 | | | | INDEX UNIQUE |PHY_PK | 1 | 4 | 2 | | | | VIEW | | 8K| 922K| | | | | SORT UNIQUE | | 8K| 649K| 2632 | | | | NESTED LOOP| | 8K| 649K| 2126 | | | | INDEX FAST|BOARD_NAM | 3 | 207 | 2 | | | | TABLE ACCE|PHY_BOARD | 443K| 5M| 708 | | | | INDEX RAN|PBRD_BOAR | 443K| | 22 | | | | VIEW | | 8K| 922K| | | | | SORT UNIQUE | | 8K| 649K| 2632 | | | | NESTED LOOPS| | 8K| 649K| 2126 | | | | INDEX FAST |BOARD_NAM | 3 | 207 | 2 | | | | TABLE ACCES|PHY_BOARD | 443K| 5M| 708 | | | | INDEX RANG|PBRD_BOAR | 443K| | 22 | | | | VIEW | | 8K| 922K| | | | | SORT UNIQUE | | 8K| 649K| 2632 | | | | NESTED LOOPS | | 8K| 649K| 2126 | | | | INDEX FAST F|BOARD_NAM | 3 | 207 | 2 | | | | TABLE ACCESS|PHY_BOARD | 443K| 5M| 708 | | | | INDEX RANGE|PBRD_BOAR | 443K| | 22 | | | | VIEW | | 8K| 922K| | | | | SORT UNIQUE | | 8K| 649K| 2632 | | | | NESTED LOOPS | | 8K| 649K| 2126 | | | | INDEX FAST FU|BOARD_NAM | 3 | 207 | 2 | | | | TABLE ACCESS |PHY_BOARD | 443K| 5M| 708 | | | | INDEX RANGE |PBRD_BOAR | 443K| | 22 | | | | VIEW | | 8K| 922K| | | | | SORT UNIQUE | | 8K| 649K| 2632 | | | | NESTED LOOPS | | 8K| 649K| 2126 | | | | INDEX FAST FUL|BOARD_NAM | 3 | 207 | 2 | | | | TABLE ACCESS B|PHY_BOARD | 443K| 5M| 708 | | | | INDEX RANGE S|PBRD_BOAR | 443K| | 22 | | | | VIEW | | 8K| 922K| | | | | SORT UNIQUE | | 8K| 649K| 2632 | | | | NESTED LOOPS | | 8K| 649K| 2126 | | | | INDEX FAST FULL|BOARD_NAM | 3 | 207 | 2 | | | | TABLE ACCESS BY|PHY_BOARD | 443K| 5M| 708 | | | | INDEX RANGE SC|PBRD_BOAR | 443K| | 22 | | | | VIEW | | 8K| 922K| | | | | SORT UNIQUE | | 8K| 649K| 2632 | | | | NESTED LOOPS | | 8K| 649K| 2126 | | | | INDEX FAST FULL |BOARD_NAM | 3 | 207 | 2 | | | | TABLE ACCESS BY |PHY_BOARD | 443K| 5M| 708 | | | | INDEX RANGE SCA|PBRD_BOAR | 443K| | 22 | | | | VIEW | | 8K| 922K| | | | | SORT UNIQUE | | 8K| 649K| 2632 | | | | NESTED LOOPS | | 8K| 649K| 2126 | | | | INDEX FAST FULL S|BOARD_NAM | 3 | 207 | 2 | | | | TABLE ACCESS BY I|PHY_BOARD | 443K| 5M| 708 | | | | INDEX RANGE SCAN|PBRD_BOAR | 443K| | 22 | | | | VIEW | | 8K| 922K| | | | | SORT UNIQUE | | 8K| 649K| 2632 | | | | NESTED LOOPS | | 8K| 649K| 2126 | | | | INDEX FAST FULL SC|BOARD_NAM | 3 | 207 | 2 | | | | TABLE ACCESS BY IN|PHY_BOARD | 443K| 5M| 708 | | | | INDEX RANGE SCAN |PBRD_BOAR | 443K| | 22 | | | | VIEW | | 8K| 922K| | | | | SORT UNIQUE | | 8K| 649K| 2632 | | | | NESTED LOOPS | | 8K| 649K| 2126 | | | | INDEX FAST FULL SCA|BOARD_NAM | 3 | 207 | 2 | | | | TABLE ACCESS BY IND|PHY_BOARD | 443K| 5M| 708 | | | | INDEX RANGE SCAN |PBRD_BOAR | 443K| | 22 | | | | VIEW | | 8K| 922K| | | | | SORT UNIQUE | | 8K| 649K| 2632 | | | | NESTED LOOPS | | 8K| 649K| 2126 | | | | INDEX FAST FULL SCAN|BOARD_NAM | 3 | 207 | 2 | | | | TABLE ACCESS BY INDE|PHY_BOARD | 443K| 5M| 708 | | | | INDEX RANGE SCAN |PBRD_BOAR | 443K| | 22 | | | | VIEW | | 443K| 48M| | | | | SORT UNIQUE | | 443K| 34M| 59335 | | | | HASH JOIN | | 443K| 34M| 3058 | | | | INDEX FAST FULL SCAN |BOARD_NAM | 190 | 12K| 2 | | | | TABLE ACCESS FULL |PHY_BOARD | 443K| 5M| 3008 | | | ---------------------------------------------------------------------------- ---- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- 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: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- 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: INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- 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: Nicoll, Iain INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- 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).