RE: Plea for Query Tuning Help

It is more than cosmetic as it can be referenced multiple times and the query is only run once and materialized, then processed as a table in the main query

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Polarski, Bernard
Sent: Wednesday, September 13, 2006 1:33 AM To: Mark.Bobak_at_il.proquest.com; oracle-l_at_freelists.org Subject: RE: Plea for Query Tuning Help

 I always had the impression that the SQL construction 'with <name>' is only a cosmetic addition to SQL to avoid the inlining of views into the main SQL text and in order to keep the logic of the SQL more evident.

Am I wrong or it has any influence on optimizer ?

B. Polarski

-----Original Message-----

From: Bobak, Mark [mailto:Mark.Bobak_at_il.proquest.com] Sent: Tuesday, 12 September, 2006 10:50 PM To: charlottejanehammond_at_yahoo.com; oracle-l_at_freelists.org Subject: RE: Plea for Query Tuning Help

One more idea:

with subq as (select id from my_table where rownum=1) SELECT /*+ ordered use_nl(nv) */ nv.* FROM subq sq, NASTY_VIEW nv where sq.id = nv.id;

Hope that helps,

-Mark

-
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Wed Sep 13 2006 - 02:04:52 CDT