Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: sql tuning help

RE: sql tuning help

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Fri, 06 Dec 2002 07:29:56 -0800
Message-ID: <F001.00514632.20021206072956@fatcity.com> 





Something like this might help ...



SELECT p.phy_id


      ,CASE WHEN (b.description LIKE 'AMERICAN ASSOCIATION OF PHYSICIAN

SPECIALIST%' AND cnt > 0 )  THEN 'X' ELSE ' ' END


      ,CASE WHEN (b.description LIKE 'AMERICAN BOARD OF ANESTHESIOLOGY%' AND

cnt > 0 ) THEN 'X' ELSE ' ' END


      ,CASE WHEN (b.description LIKE 'AMERICAN BOARD OF EMERGENCY MEDICINE%'

AND cnt > 0 ) THEN 'X' ELSE ' ' END


      ,CASE WHEN (b.description LIKE 'AMERICAN BOARD OF FAMILY PRACTICE%'

AND cnt > 0 ) THEN 'X' ELSE ' ' END


      ,CASE WHEN (b.description LIKE 'AMERICAN BOARD OF INTERNAL MEDICINE%'

AND cnt > 0 ) THEN 'X' ELSE ' ' END


      ,CASE WHEN (b.description LIKE 'AMERICAN BOARD OF PEDIATRICS%'  AND

cnt > 0 ) THEN 'X' ELSE ' ' END


      ,CASE WHEN (b.description LIKE 'AMERICAN BOARD OF RADIOLOGY%'  AND cnt

> 0 ) THEN 'X' ELSE ' ' END



      ,CASE WHEN (b.description LIKE 'AMERICAN OSTEOPATHIC BOARD OF

EMERGENCY MEDICINE%'  AND cnt > 0 ) THEN 'X' ELSE ' ' END


      ,CASE WHEN (b.description LIKE 'AMERICAN OSTEOPATHIC BOARD OF FAMILY

PHYSICIANS%'  AND cnt > 0 ) THEN 'X' ELSE ' ' END


      ,CASE WHEN (b.description LIKE 'AMERICAN OSTEOPATHIC BOARD OF INTERNAL

MEDICINE%'  AND cnt > 0 ) THEN 'X' ELSE ' ' END


      ,CASE WHEN (b.description LIKE 'AMERICAN OSTEOPATHIC BOARD OF

RADIOLOGY%' AND cnt > 0 ) THEN 'X' ELSE ' ' END


      ,CASE WHEN (b.description LIKE 'NO BOARDS%' ) THEN 'X' ELSE ' ' END

FROM(



SELECT p.phy_id


      ,b.description

      ,pb.COUNT(board_id) cnt



  FROM physicians p


      ,phy_boards pb

      ,boards b



 WHERE p.phy_id = 1870


   AND NVL(pb_expiration_date, SYSDATE+1) >= SYSDATE
 GROUP BY p.phy_id, b.description


)


/



I haven't tested this ... buit should be pretty close.
Raj




Rajendra Jamadagni		MIS, ESPN Inc.


Rajendra dot Jamadagni at ESPN dot com


Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.



QOTD: Any clod can have facts, but having an opinion is an art!




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








-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jamadagni, Rajendra
  INET: Rajendra.Jamadagni_at_espn.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 - 09:29:56 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US