Re: Ramming two SQL queries together.....

From: joel garry <joel-garry_at_home.com>
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.html

Received on Fri May 28 2010 - 18:58:17 CDT