Re: Vanishing table in 11.2.0.3

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

Consider

    select

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

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.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com
Oracle Core (Apress 2011)
http://www.apress.com/9781430239543


"Mladen Gogala" <gogala.mladen_at_gmail.com> wrote in message 
news:jgeik2$ve0$1_at_solani.org...


| 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.

|

|

|

| -- 

| http://mgogala.byethost5.com