| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> sql tuning help
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
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: Rick_Cale_at_teamhealth.com 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: ListGuru_at_fatcity.com (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).Received on Fri Dec 06 2002 - 06:53:46 CST
 |
 |