Re: With Clause

Comments embedded.
On Nov 5, 4:05 am, raja <dextersu..._at_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 Received on Wed Nov 05 2008 - 08:31:34 CST