I think the problem (without really getting into how they coded this) is the
nested-nested loops.
try this hint:
/*+ no_merge use_hash(board_aaps) use_hash(board_aba) use_hash(board_abem)
use_hash(board_abfp) use_hash(board_abim) use_hash(board_abp)
use_hash(board_abr) use_hash(board_aobem) use_hash(board_aobfp)
use_hash(board_aobim) use_hash(board_aobr) use_hash(board_other) */
HTH
Waleed
-----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: Khedr, Waleed
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).
