Re: non equi-joins

From: derek <dereksmi_at_gmail.com>
Date: 31 May 2005 21:51:05 -0700
Message-ID: <1117601465.817267.286130@g14g2000cwa.googlegroups.com>

Hi David,
 Sincere apologies for duplicate posting. Thanks a lot for helping me out!! However, there is another aspect of the execution trace which I could not understand. I have the trace output of the same query as earlier pasted below. When I checked the "time" values in the "Row source operation" of the execution plan, I find that the time value of the merge phase is much higher than the value in the sort aggregate. Does that mean that the "merge time" is the projected time had the merge operation been materialised instead of just being pipelined to be aggregated? Or am I missing some thing? Hope to hear from you soon.

best regards,
Nagender.

select /*+ ORDERED USE_MERGE (f1 f2)*/count(*) from
 test2 f2, test1 f1 where f1.a = f2.a

call count cpu elapsed disk query current

    rows
------- ------ -------- ---------- ---------- ---------- ----------

---

Parse        1      0.00       0.00          0          0          0
       0
Execute      1      0.00       0.00          0          0          0
       0
Fetch        2      0.04       0.05          0         32          0
       1

------- ------ -------- ---------- ---------- ---------- ----------

---

total        4      0.04       0.05          0         32          0
       1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 58

Rows Row Source Operation

-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=32 pr=0 pw=0 time=51752 us)

  36000 MERGE JOIN (cr=32 pr=0 pw=0 time=159758 us)    3000 SORT JOIN (cr=16 pr=0 pw=0 time=15583 us)    3000 TABLE ACCESS FULL TEST2 (cr=16 pr=0 pw=0 time=3179 us)   36000 SORT JOIN (cr=16 pr=0 pw=0 time=43848 us)    3000 TABLE ACCESS FULL TEST1 (cr=16 pr=0 pw=0 time=3068 us)

---

Received on Tue May 31 2005 - 23:51:05 CDT