Re: Ramming two SQL queries together.....
Date: Fri, 28 May 2010 16:58:17 -0700 (PDT)
Message-ID: <29f56a27-a82f-428a-be24-5303df393dc8_at_h37g2000pra.googlegroups.com>
On May 28, 11:38 am, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On May 28, 1:23 pm, joel garry <joel-ga..._at_home.com> wrote:
>
> > <mr.frog.to...._at_googlemail.com> wrote:
> > > Thankyou both gentlemen. I appreciate the feedback you have given me.
> > > I am not an Oracle expert so I was not sure what the best approach
> > > was. I will set both up and see which runs faster (I am guessing the
> > > inline due to the way correlated subqueries run).
>
> > > I thank you both very much.
>
> > > Cheers
>
> > > The Frog
>
> > I'm wondering about how you think correlated subqueries run. Seehttp://download.oracle.com/docs/cd/B19306_01/server.102/b14200/querie......
>
> > If you haven't already, get Jonathan's book about the optimizer. See
> > this example: http://jonathanlewis.wordpress.com/2006/11/06/filter-subqueries/
>
> > jg
>
> Joel,
>
> I wonder if it is not just a terminology problem here. Essentially,
> what do you call it when a SELECT statement appears in a column
> position of another query. I think that Walt and The Frog were on the
> same page regarding what they are talking about.
>
> I think that the Oracle definition of a correlated subquery is a
> SELECT statement that appears in the WHERE clause of another SQL
> statement, and one or more columns from the parent SQL statement is
> directly related to one or more columns (or generated values) from the
That's right.
Sql Language Reference manual:
"Using Subqueries
A subquery answers multiple-part questions. For example, to determine who works in Taylor's department, you can first use a subquery to determine the department in which Taylor works. You can then answer the original question with the parent SELECT statement. A subquery in the FROM clause of a SELECT statement is also called an inline view. you can nest any number of subqueries in an inline view. A subquery in the WHERE clause of a SELECT statement is also called a nested subquery...Oracle performs a correlated subquery when a nested subquery references a column from a table referred to a parent statement any number of levels above the subquery."
> SELECT statement in the WHERE clause.http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/statem...
> "The following examples show the general syntax of a correlated
> subquery:
> SELECT select_list
> FROM table1 t_alias1
> WHERE expr operator
> (SELECT column_list
> FROM table2 t_alias2
> WHERE t_alias1.column
> operator t_alias2.column);"
>
> I think that there is a proper name for the case where a subquery
> appears in a column position of another query, but I can't think of
> that name at the moment.
"Scalar subquery," perhaps? I think the O docs are weak on this, though people seem to have picked up on it when it was introduced in 9i. It's an ANSI definition.
Tom has interesting observations:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1594885400346999596
>
> Here is possibly interesting test case:
> ---
>
> I think that The Frog is on the right track to test the performance of
> both approaches.
>
Thanks once again for demonstrating to us all how to think.
jg
-- _at_home.com is bogus. http://www.gennick.com/madness.htmlReceived on Fri May 28 2010 - 18:58:17 CDT