WITH clause as inline view
KranthiNov 1 2010 — edited Nov 1 2010I m using oracle DB 11.2.0.2.0
I have a question regarding the usage of WITH clause, which is doing temp table transformation(Materializing) automatically.
I want the WITH clause query to operate as a inline view because materailizing the transaction table would kill the performance.
I m able to see the temp table transformation in the explain plan.
Do we have any hint to stop the Temp table transformation?
below is a sample example
I have multi union query (2 Queries),couple of the table are common across the union queries. the with query should not be materialized. it should operate as an inline common view.
WITH ORDERS AS
(SELECT h.order_number,
l.line_number,
l.line_id
from order_headers h, order_lines l)
SELECT ....
ORDERS, X,Y
WHERE ....
UNION ALL
SELECT ...
ORDERS,A, B,C
WHERE ....