With Clause
raja
I had a look at the usage of WITH Clause, where a repeated subquery
can be replaced using WITH clause which reduces the complexity of the
query.
Apart from this, i was not able to find anything regarding WITH
Clause.
Please reply to my doubts on WITH Clause :
1. Apart from subquery replacement, what are the other usages of WITH
Clause ?
2. Will the performance increase if we are using WITH Clause, instead
of a subquery ?
3. What is the difference between WITH Clause and correlated
subquery ?
Any other information regarding WITH Clause, please send them too.
Thanks in Advance.
With Regards,
Raja.
steph
a with-subquery can be referenced multiple times in a select-
statement. not so a subquery defined in the from-clause.
ddf
On Nov 5, 4:05 am, raja <dextersu...@gmail.com> wrote:
> Hi,
>
> I had a look at the usage of WITH Clause, where a repeated subquery
> can be replaced using WITH clause which reduces the complexity of the
> query.
> Apart from this, i was not able to find anything regarding WITH
> Clause.
>
> Please reply to my doubts on WITH Clause :
> 1. Apart from subquery replacement, what are the other usages of WITH
> Clause ?
That's it. That is the intent of the WITH clause.
> 2. Will the performance increase if we are using WITH Clause, instead
> of a subquery ?
It can. Oracle will either materialize the factored subquery and
access the temporary table it creates or it will substitute, inline,
the text for the factored subquery wherever it appears in the
subsequent query. Normally, without hinting, Oracle will (at least in
10g) materialize the subquery and treat it like a table.
> 3. What is the difference between WITH Clause and correlated
> subquery ?
>
Subqueries in a WITH clause can call another factored subquery as a
table, but they're not correlated. As you know a correlated subquery
would look like this:
select myid, mydata
from mytable
where myref in (select mynewref from mynewtable where mynewid = myid);
whereas a factored subquery would look like:
with mynewstuff as (
select mynewid,mynewref
from mynewtable
)
select myid, mydata
from mytable, mynewstuff
where mynewid = myid
and myref = mynewref;
which is a join, not a correlated subquery.
> Any other information regarding WITH Clause, please send them too.
>
I suggest you purchase "Cost-Based Oracle Fundamentals", by Jonathan
Lewis.
> Thanks in Advance.
>
> With Regards,
> Raja.
David Fitzjarrell
joel garry
A few SECONDS of google:
http://www.psoug.org/reference/with.html
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:524736300346304074
http://www.oracle.com/technology/products/oracle9i/daily/oct10.html
http://www.singlequery.com/2006/12/using-the-oracle-with-clause/
et cetera.
jg
--
@home.com is bogus.
"The Best Support is no Service Request." - Chris Wartticki
Palooka
>
> A few SECONDS of google:
>
> http://www.psoug.org/reference/with.html
> http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:524736300346304074
> http://www.oracle.com/technology/products/oracle9i/daily/oct10.html
> http://www.singlequery.com/2006/12/using-the-oracle-with-clause/
>
> et cetera.
>
his work for nothing?
Palooka
Palooka
Oh, damn. I sound like Sybrand now.
Palooka
raja
> Palooka wrote:
> > joel garry wrote:
>
> >> A few SECONDS of google:
>
> >>http://www.psoug.org/reference/with.html
>
> >>http://www.oracle.com/technology/products/oracle9i/daily/oct10.html
> >>http://www.singlequery.com/2006/12/using-the-oracle-with-clause/
>
> >> et cetera.
>
> > Why should he waste precious seconds on Google, when people here will do
> > his work for nothing?
>
> Oh, damn. I sound like Sybrand now.
>
>
> - Show quoted text -
Hi,
Thanks for ur responses.
I will check these links and then if i have doubts, i will get back to
you.
Should we use /*+ MATERIALIZE */ ( materialize hint ) even in 10g ?
With Regards,
Raja.
jan....@gmail.com
Ad 1 - I found WITH to be a workaround for what I think is Oracle BUG
with some bit complex FULL OUTER JOIN statements that wouldn't work as
subqueries but worked as expected with WITH Clause.
Regards,
Jan
On Nov 5, 11:05 am, raja <dextersu...@gmail.com> wrote:
> Hi,
>
> I had a look at the usage of WITHClause, where a repeated subquery
> can be replaced using WITHclausewhich reduces the complexity of the
> query.
> Apart from this, i was not able to find anything regarding WITHClause.
>
> Please reply to my doubts on WITHClause:
> 1. Apart from subquery replacement, what are the other usages of WITHClause?
> 2. Will the performance increase if we are using WITHClause, instead
> of a subquery ?
> 3. What is the difference between WITHClauseand correlated
> subquery ?
>
> Any other information regarding WITHClause, please send them too.
joel garry
> On Nov 6, 3:01 am, Palooka <nob...@nowhere.com> wrote:
>
>
>
>
>
> > Palooka wrote:
> > > joel garry wrote:
>
> > >> A few SECONDS of google:
>
> > >>http://www.psoug.org/reference/with.html
> > >>http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5...
>
> > >>http://www.oracle.com/technology/products/oracle9i/daily/oct10.html
> > >>http://www.singlequery.com/2006/12/using-the-oracle-with-clause/
>
> > >> et cetera.
>
> > > Why should he waste precious seconds on Google, when people here will do
> > > his work for nothing?
>
> > Oh, damn. I sound like Sybrand now.
metoo. Funny how that happens. (And seriously, that's why I don't
castigate him when he's grouchy, even if at times it seems always).
>
> > Palooka- Hide quoted text -
>
> > - Show quoted text -
>
> Hi,
>
> Thanks for ur responses.
> I will check these links and then if i have doubts, i will get back to
> you.
>
> Should we use /*+ MATERIALIZE */ ( materialize hint ) even in 10g ?
>
> With Regards,
> Raja.
See rule 1:
http://jonathanlewis.wordpress.com/2008/05/02/rules-for-hinting/
Response to Jan:
Agree, but then again, if things are changing because of bugs, they're
changing more because of bug fixes plus new functionality plus
behavioral changes plus all the things that can change plans
dynamically, including changes that affect explicit plan stability.
So using hints is making more work in the long run, unless they are
absolutely necessary.
All that said, one just has to look to see the Oracle built-in stuff
uses hints. I'm not convinced the argument "that's ok because they
know what is going on" is a good argument. Like gather_stats_job:
select /*+ no_parallel(t) no_parallel_index(t) dbms_stats
cursor_sharing_exact use_weak_name_resl dynamic_sampling(0)
no_monitoring */ count(*) from "<obscured by joel>"."<obscured by
joel>" sample block ( .1756888761,1) t
The hints may be ok, I wouldn't know - google for the arguments about
whether one should collect system stats, too. But the default
sampling turns plan evaluation into a guess, which may affect any
access to that user table (which happens to be a biggee, affecting end
of month processing windows).
But maybe it's a really _good_ guess.
jg
--
@home.com is bogus.
http://ask.slashdot.org/article.pl?no_d2=1&sid=08/11/05/2027234
William Robertson
> Should we use /*+ MATERIALIZE */ ( materialize hint ) even in 10g ?
Only if you need the set materialised. If you don't it's just extra I/
O.
raja
I am using oracle 10g.
I have query is repeating more than once in that in the materialized
view.
Is it possible to use WITH Clause even in Materialized View ?
With Regards,
Raja.
DA Morgan
Did you try it? If not why not?
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damo...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org