Unnesting table expressions, any hints?

From: Aakash <a_bordia_at_hotmail.com>
Date: 20 Nov 2002 10:05:09 -0800
Message-ID: <63d1d68c.0211201005.311ae05f@posting.google.com>

Hi, I have a simple performance question. One of our customers is writing the following 2 SQL queries which are equivalent. The time for execution is drastically different though:

1)
select * from

(select b1.c1,b2.c2 from t11 b1 left outer join t12 b2 on
b1.c1=b2.c1 ) a0(c1,c2)

       left outer join


       (select b1.c1,b2.c2 from t13 b1 left outer join t14 b2  on

b1.c1=b2.c1 ) a1(c1,c2)
on a0.c1=a1.c1;

2)
SELECT A0."C1", A1."C2", A2."C1", A3."C2" FROM "T11"
   A0, "T12" A1, "T13" A2, "T14"
   A3
WHERE (A0."C1" = A1."C1"(+) ) AND (A2."C1" = A3."C1"(+) )    AND (A0."C1" = A2."C1"(+)) The performance of 2 is much better than performance of 1. (of the order of huge magnitudes)

Is there a way to give hint to the server that 1 should actually be unnested to 2? We cannot issue 2 directly since no.1 is being issued by a tool.

Thanks
Aakash Received on Wed Nov 20 2002 - 12:05:09 CST