Re: subselect results in poor performance

From: Benny <benny.derous_at_gmail.com>
Date: 17 Jan 2007 08:10:28 -0800
Message-ID: <1169050227.813791.96510@a75g2000cwd.googlegroups.com>

This is 9207 and dbms_stats is always used. In this case even with columns stats on indexed columns skewonly. Stats are fresh.

select vw_rrp00010.*,rrp0.tb_rrp00115.*
from rrp0.mv_rrp00010 vw_rrp00010, rrp0.tb_rrp00115 where vw_rrp00010.load_dt = tb_rrp00115.load_dt and vw_rrp00010.load_type_cd = tb_rrp00115.load_type_cd and vw_rrp00010.fuse_cty_id = tb_rrp00115.fuse_cty_id and (vw_rrp00010.iso2_cty_cd = tb_rrp00115.iso2_cty_cd or ' ' = tb_rrp00115.iso2_cty_cd)
and tb_rrp00115.load_dt = (select load_dt from rrp0.mv_rrp00104 where '1' = vsn_cd and 'D' = load_type_cd)

| 0 | SELECT STATEMENT | | 64674 |

10M|       |  2891 |       |       |


|*  1 |  HASH JOIN              |                       | 64674 |

10M|  7104K|  2891 |       |       |


|   2 |   PARTITION RANGE SINGLE|                       |       |

|       |       |   KEY |   KEY |

|*  3 |    TABLE ACCESS FULL    | TB_RRP00115           | 48811 |

6530K|       |  1290 |   KEY |   KEY |


|*  4 |     INDEX RANGE SCAN    | IDX__MV_RRP00104_OPT  |     2 |    18

|       |     1 |       |       |

|   5 |   PARTITION RANGE ALL   |                       |       |

|       |       |     1 |    35 |

|   6 |    TABLE ACCESS FULL    | MV_RRP00010           |  1100K|

33M|       |   754 |     1 |    35 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

1 - access("VW_RRP00010"."LOAD_DT"="TB_RRP00115"."LOAD_DT" AND

        "VW_RRP00010"."LOAD_TYPE_CD"="TB_RRP00115"."LOAD_TYPE_CD" AND
            "VW_RRP00010"."FUSE_CTY_ID"="TB_RRP00115"."FUSE_CTY_ID")

       filter("VW_RRP00010"."ISO2_CTY_CD"="TB_RRP00115"."ISO2_CTY_CD"

OR "TB_RRP00115"."ISO2_CTY_CD"=' ') 3 - filter("TB_RRP00115"."LOAD_DT"= (SELECT /*+ */ "MV_RRP00104"."LOAD_DT" FROM "RRP0"."MV_RRP00104" "MV_RRP00104" WHERE "MV_RRP00104"."LOAD_TYPE_CD"='D' AND "MV_RRP00104"."VSN_CD"='1')) 4 - access("MV_RRP00104"."VSN_CD"='1' AND "MV_RRP00104"."LOAD_TYPE_CD"='D') Note: cpu costing is off Received on Wed Jan 17 2007 - 10:10:28 CST