Re: sql tuning help

My first thought would be that the plan shows a full table scan for phy_boards for each in-line view. This is almost certainly due to the 'pb.expiration_Date is null' condition in the where clause of each in-line view. Since Oracle does not store NULLs in an index (except for bitmaps), that condition requires a full table scan.

-Mark

On Fri, 2002-12-06 at 07:53, Rick_Cale_at_teamhealth.com wrote:
> 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 | |
> |
> --------------------------------------------------------------------------------
>
>

-- 
--
Mark J. Bobak
Oracle DBA
mark_at_bobak.net
"It is not enough to have a good mind.  The main thing is to use it
well."
 						-- Rene Descartes
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mark J. Bobak
  INET: mark_at_bobak.net

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).