Re: Vanishing table in

From: Jonathan Lewis <>
Date: Thu, 2 Feb 2012 18:29:51 -0000
Message-ID: <>

I'm not sure you're interpreting push_subq correctly.



        from    parent, child
        where    child.id_parent = parent_id
        and        parent.type = 'RARE'
        and        exists (
                        select null
                        from ref_p
                        where =

Assume that parent.type is indexed and 'RARE' is a value that makes the optimizer drive from the parent table.

We can see that it would make sense to get a parent row, run the subquery to check for existence, then acquire the child rows. The default (historical) Oracle position is to get a parent, join to child, and then run the subquery for each row in that result set. By pushing the subquery (down the parse tree) we make Oracle run the subquery as soon as it has got a row from the parent table.


Jonathan Lewis
Oracle Core (Apress 2011)

"Mladen Gogala" <> wrote in message 

| On Thu, 02 Feb 2012 14:00:22 +0000, Jonathan Lewis wrote:
| > You're thinking of the undocumented /*+ precompute_subquery */ hint.
| To tell the truth, that does look very similar to push_subq. With non-
| correlated subqueries, the result is probably very similar, in both
| cases. I don't think that it is possible to pre-compute a correlated
| subquery.
| --
Received on Thu Feb 02 2012 - 12:29:51 CST

Original text of this message