10053 trace picking join path with higher cost

From: Keith Brunsting <kbrunsting_at_email.com>
Date: 12 Jun 2003 08:14:46 -0700
Message-ID: <2ff9103c.0306120714.761967f6_at_posting.google.com>


I'm testing a simple query, 2 tables joined, with criteria on one of them. I want it to use nested loop because its faster and in this case it is picking that join, but the cost showing up in the trace is higher for the nested loop than the hash join, so why is it picking NL?

We're using Oracle 9.0.1

Here's a snippet from the trace file:

Join order[1]: CLM [ C] CLM_CHECK [CC]
Now joining: CLM_CHECK [CC] *******
NL Join
  Outer table: cost: 3302 cdn: 39591 rcz: 15 resp: 3302   Inner table: CLM_CHECK
    Access path: tsc Resc: 6680
    Join: Resc: 264471182 Resp: 264471182 OPTIMIZER PERCENT INDEX CACHING = 90
  Access path: index (scan)

      Index: CLM_CHECK_PK
  TABLE: CLM_CHECK
      RSC_CPU: 0 RSC_IO: 2
  IX_SEL: 7.5893e-08 TB_SEL: 7.5893e-08     Join: resc: 34975 resp: 34975
Join cardinality: 39592 = outer (39591) * inner (13176912) * sel (7.5893e-08) [flag=0]
  Best NL cost: 34976 resp: 34975

HA Join
  Outer table:
    resc: 3302 cdn: 39591 rcz: 15 deg: 1 resp: 3302   Inner table: CLM_CHECK
    resc: 6680 cdn: 13176912 rcz: 19 deg: 1 resp: 6680   Hash join one ptn Resc: 312 Deg: 1

      hash_area: 977 buildfrag: 978 probefrag: 24932   ppasses: 2
  Hash join Resc: 10294 Resp: 10294
Join result: cost: 34976 cdn: 39592 rcz: 34 Best so far: TABLE#: 0 CST: 3302 CDN: 39591 BYTES: 593865
Best so far: TABLE#: 1 CST: 34976 CDN: 39592 BYTES: 1346128

I have a query that is very similiar but the cardinality of accessing the first table is higher and in that case it is picked hash join. Received on Thu Jun 12 2003 - 17:14:46 CEST

Original text of this message