Path: news.cambrium.nl!textnews.cambrium.nl!feeder1.cambriumusenet.nl!feeder3.cambriumusenet.nl!feed.tweaknews.nl!87.79.20.101.MISMATCH!newsreader4.netcologne.de!news.netcologne.de!weretis.net!feeder1.news.weretis.net!news.solani.org!.POSTED!not-for-mail From: Mladen Gogala Newsgroups: comp.databases.oracle.server Subject: Re: Vanishing table in 11.2.0.3 Date: Thu, 2 Feb 2012 22:36:01 +0000 (UTC) Organization: solani.org Lines: 34 Message-ID: References: Mime-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit X-Trace: solani.org 1328222161 5834 eJwFwYEBwCAIA7CXZJSK50DF/09YEk6jNhhEvHhavLnUVhzv7MscyQbl7yS+8mJEb1QLB/oBMDMRuQ== (2 Feb 2012 22:36:01 GMT) X-Complaints-To: abuse@news.solani.org NNTP-Posting-Date: Thu, 2 Feb 2012 22:36:01 +0000 (UTC) User-Agent: Pan/0.135 (Tomorrow I'll Wake Up and Scald Myself with Tea; Unknown) X-User-ID: eJwFwYERACEIA7CVUGn7jEM92X+ET3C4eJUEE4MZv6/ZEShvruo6W/ZrINLWvhjzUM6llH4hOxDP Cancel-Lock: sha1:PB740lBLZp8mwoc4yPsoKVw27pI= X-NNTP-Posting-Host: eJwFwYEBwCAIA7CXZkuLO2fA/P8EE9HLnWE5dHRA/5zctQf1TPtUmzFQMYE3kuT+Gloq5FwYWRCc Xref: news.cambrium.nl On Thu, 02 Feb 2012 18:29:51 +0000, Jonathan Lewis wrote: > I'm not sure you're interpreting push_subq correctly. > Well, Oracle documentation says that this hint will execute the query at the earliest possible moment. I must confess that I didn't distinguish between correlated and non-correlated queries, that is the reason why I was using push_subq. It seems that the application of that query is much more limited, usable only for the cases like the one you specified. There should be much more clearer explanation for push_subq, merge, push_pred and unnest. Here is what Oracle documentation (11R2) says about push_subq hint: ***************************************************************************** PUSH_SUBQ Hint The PUSH_SUBQ hint instructs the optimizer to evaluate nonmerged subqueries at the earliest possible step in the execution plan. Generally, subqueries that are not merged are executed as the last step in the execution plan. If the subquery is relatively inexpensive and reduces the number of rows significantly, then evaluating the subquery earlier can improve performance. This hint has no effect if the subquery is applied to a remote table or one that is joined using a merge join. ***************************************************************************** This doesn't mention parent-child queries and doesn't even require the subquery to be correlated. To tell the truth, yes, it did look like the precompute_subquery to me. Now that you have corrected me, I'll write a little test tonight. -- http://mgogala.freehostia.com