Query optimization.

From: Kurta <submit_at_galleus.com>
Date: 6 Aug 2004 08:43:27 -0700
Message-ID: <efcb1994.0408060743.10c1a6ac@posting.google.com>

I have a simple query with lots of inner and outer joins.

Is there a way to determine what columns in what tables should be indexed? Currently the foreign keys are indexed, and columns that are in the WHERE and ORDER BY clauses in the T_MDCLCLM table.

With IN in the WHERE clause:
WHERE
MCLM_ID IN (46504, 55468, 28487, 72037, 72060, 13452, 44502, 44556, 44984, 49386, 49781)
it runs sloooooow.

If I change it to
WHERE
MCLM_ID = 46504
it runs fast using the indexes.

See execution plans at the end. Any clues?

SELECT
  MCLM_ID, MCLM_C4C_ID, MCLM_CLM_ID, MCLM_FRSTDT_OF_SRVC, MCLM_LAS_DT_OF_SRVC,
  MCLM_AMNTCRGD, MCLM_AMNTPAID, MCLM_AMNTALWD, MCLM_AMNTDDCT, MCLM_AMNTCPAY,
  MBUR_MSTR.MBUR_COB,

  ICD9DX1.IC9D_ICD9DGESCODE, ICD9DX1.IC9D_LONGDSCN,
  ICD9DX2.IC9D_ICD9DGESCODE, ICD9DX2.IC9D_LONGDSCN,
  ICD9DX3.IC9D_ICD9DGESCODE, ICD9DX3.IC9D_LONGDSCN,

HTCD.HTCD_HCCSCPT_CODE, HTCD.HTCD_CPT_DSCN,

  MCLM_PLCEOF_SRVCCODE, SENG_MSTR.USR_FRSTNAM, SENG_MSTR.USR_MDLENAM,
  SENG_MSTR.USR_LAS_NAM, MCLM_SENGPVDR, RFNG_MSTR.USR_FRSTNAM,
  RFNG_MSTR.USR_MDLENAM, RFNG_MSTR.USR_LAS_NAM, MCLM_RFNGPVDR

FROM
  T_MDCLCLM
INNER JOIN
  T_USR MBUR ON MCLM_PAT_ID = MBUR.MBUR_ID LEFT OUTER JOIN
  T_SRVC HTCD ON MCLM_CPT4CODE = HTCD.HTCD_ID LEFT OUTER JOIN
  T_USR SENG ON MCLM_SENGPVDR = SENG.PRV_ID LEFT OUTER JOIN
(SELECT IC9D_ID, IC9D_ICD9DGESCODE, IC9D_LONGDSCN FROM T_DGIS)
ICD9DX1 ON MCLM_ICD9DX1 = ICD9DX1.IC9D_ID LEFT OUTER JOIN
(SELECT IC9D_ID, IC9D_ICD9DGESCODE, IC9D_LONGDSCN FROM T_DGIS)
ICD9DX2 ON MCLM_ICD9DX2 = ICD9DX2.IC9D_ID LEFT OUTER JOIN
(SELECT IC9D_ID, IC9D_ICD9DGESCODE, IC9D_LONGDSCN FROM T_DGIS)
ICD9DX3 ON MCLM_ICD9DX3 = ICD9DX3.IC9D_ID LEFT OUTER JOIN
  T_USR RFNG ON MCLM_RFNGPVDR = RFNG.PRV_ID LEFT OUTER JOIN
(SELECT MBUR_COB, USR_ID FROM T_USR) MBUR_MSTR ON
NVL(MBUR.USR_MRGEMSTR, MBUR.USR_ID) = MBUR_MSTR.USR_ID LEFT OUTER JOIN
  T_USR SENG_MSTR ON NVL(SENG.USR_MRGEMSTR, SENG.USR_ID) = SENG_MSTR.USR_ID LEFT OUTER JOIN
  T_USR RFNG_MSTR ON NVL(RFNG.USR_MRGEMSTR, RFNG.USR_ID) = RFNG_MSTR.USR_ID WHERE
  MCLM_ID IN (46504, 55468, 28487, 72037, 72060, 13452, 44502, 44556, 44984, 49386, 49781)
ORDER BY
  MCLM_FRSTDT_OF_SRVC DESC, MCLM_ID ASC Execution plan with IN:
SELECT STATEMENT Optimizer=CHOOSE (Cost=13083881442 Card=1093269573632 Bytes=2851247048032260)
SORT (ORDER BY) (Cost=13083881442 Card=1093269573632 Bytes=2851247048032260)
  HASH JOIN (OUTER) (Cost=97928308 Card=1093269573632 Bytes=2851247048032260)

HASH JOIN (OUTER) (Cost=1120621 Card=2565300423 Bytes=5995107088551)

HASH JOIN (OUTER) (Cost=13593 Card=34716692 Bytes=71724685672)

     HASH JOIN (Cost=484 Card=469828 Bytes=843341260)
      TABLE ACCESS (FULL) OF T_DGIS (Cost=16 Card=30222 Bytes=8190162)
      HASH JOIN (OUTER) (Cost=189 Card=6358 Bytes=9689592)
       HASH JOIN (OUTER) (Cost=45 Card=6358 Bytes=7146392)
        HASH JOIN (OUTER) (Cost=33 Card=549 Bytes=595665)
         HASH JOIN (OUTER) (Cost=15 Card=549 Bytes=376065)
          HASH JOIN (OUTER) (Cost=10 Card=47 Bytes=30362)
           NESTED LOOPS (OUTER) (Cost=5 Card=47 Bytes=28811)
            INLIST ITERATOR
             TABLE ACCESS (BY INDEX ROWID) OF T_MDCLCLM (Cost=1 Card=4
Bytes=2296)
              INDEX (RANGE SCAN) OF PK_T_MDCLCLM (UNIQUE) (Cost=2
Card=2)
            TABLE ACCESS (BY INDEX ROWID) OF T_USR (Cost=1 Card=12
Bytes=468)
             INDEX (UNIQUE SCAN) OF UK_MBUR (UNIQUE)
           TABLE ACCESS (FULL) OF T_USR (Cost=4 Card=4738
Bytes=156354)
          TABLE ACCESS (FULL) OF T_USR (Cost=4 Card=4738 Bytes=184782)
         TABLE ACCESS (FULL) OF T_USR (Cost=4 Card=4738 Bytes=1895200)
        TABLE ACCESS (FULL) OF T_USR (Cost=4 Card=4738 Bytes=184782)
       TABLE ACCESS (FULL) OF T_USR (Cost=4 Card=4738 Bytes=1895200)
     TABLE ACCESS (FULL) OF T_DGIS (Cost=16 Card=30222 Bytes=8190162)

TABLE ACCESS (FULL) OF T_DGIS (Cost=16 Card=30222 Bytes=8190162) TABLE ACCESS (FULL) OF T_SRVC (Cost=84 Card=174306 Bytes=47236926)

Execution plan with =:
SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=1 Bytes=2601) SORT (ORDER BY) (Cost=14 Card=1 Bytes=2601) NESTED LOOPS (OUTER) (Cost=14 Card=1 Bytes=2601) NESTED LOOPS (OUTER) (Cost=13 Card=1 Bytes=2575) NESTED LOOPS (OUTER) (Cost=12 Card=1 Bytes=2175)

     NESTED LOOPS (Cost=11 Card=1 Bytes=1775)
      NESTED LOOPS (OUTER) (Cost=10 Card=1 Bytes=1504)
       NESTED LOOPS (OUTER) (Cost=9 Card=1 Bytes=1233)
        NESTED LOOPS (OUTER) (Cost=8 Card=1 Bytes=962)
         NESTED LOOPS (OUTER) (Cost=7 Card=1 Bytes=691)
          NESTED LOOPS (OUTER) (Cost=6 Card=1 Bytes=652)
           NESTED LOOPS (OUTER) (Cost=5 Card=1 Bytes=613)
            TABLE ACCESS (BY INDEX ROWID) OF T_MDCLCLM (Cost=1 Card=4
Bytes=2296)
             INDEX (RANGE SCAN) OF PK_T_MDCLCLM (UNIQUE) (Cost=1
Card=2)
            TABLE ACCESS (BY INDEX ROWID) OF T_USR (Cost=1 Card=12
Bytes=468)
             INDEX (UNIQUE SCAN) OF UK_PRV (UNIQUE)
           TABLE ACCESS (BY INDEX ROWID) OF T_USR (Cost=1 Card=12
Bytes=468)
            INDEX (UNIQUE SCAN) OF UK_PRV (UNIQUE)
          TABLE ACCESS (BY INDEX ROWID) OF T_USR (Cost=1 Card=12
Bytes=468)
           INDEX (UNIQUE SCAN) OF UK_MBUR (UNIQUE)
         TABLE ACCESS (BY INDEX ROWID) OF T_DGIS (Cost=1 Card=74
Bytes=20054)
          INDEX (UNIQUE SCAN) OF UK_IC9D (UNIQUE)
        TABLE ACCESS (BY INDEX ROWID) OF T_DGIS (Cost=1 Card=74
Bytes=20054)
         INDEX (UNIQUE SCAN) OF UK_IC9D (UNIQUE)
       TABLE ACCESS (BY INDEX ROWID) OF T_SRVC (Cost=1 Card=426
Bytes=115446)
        INDEX (UNIQUE SCAN) OF UK_HTCD (UNIQUE)
      TABLE ACCESS (BY INDEX ROWID) OF T_DGIS (Cost=1 Card=74
Bytes=20054)
       INDEX (UNIQUE SCAN) OF UK_IC9D (UNIQUE)
     TABLE ACCESS (BY INDEX ROWID) OF T_USR (Cost=1 Card=1 Bytes=400)
      INDEX (UNIQUE SCAN) OF PK_T_USR (UNIQUE)
    TABLE ACCESS (BY INDEX ROWID) OF T_USR (Cost=1 Card=1 Bytes=400)
     INDEX (UNIQUE SCAN) OF PK_T_USR (UNIQUE)

TABLE ACCESS (BY INDEX ROWID) OF T_USR (Cost=1 Card=1 Bytes=26) INDEX (UNIQUE SCAN) OF PK_T_USR (UNIQUE) Received on Fri Aug 06 2004 - 10:43:27 CDT