Re: Query optimization.

From: Antoine BRUNEL <antoinebrunel/yahoo.fr>
Date: Sun, 8 Aug 2004 04:19:56 +0200
Message-ID: <41158dce$0$20878$79c14f64@nan-newsreader-05.noos.net>

Hi from Paris

as you can see, the main differences between both plans is the use of "hash joins" when specifiying IN predicate....

in this case, I would try:

- to test with the "USE_NL" hint
- to test with the "FIRST_ROWS" hint
- if this can be done, to disable hash joins (with hash_join_enabled =

false) -> be careful !!!

Cdlt

"Kurta" <submit_at_galleus.com> a écrit dans le message de news:efcb1994.0408060743.10c1a6ac_at_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 Sat Aug 07 2004 - 21:19:56 CDT