Re: Ramming two SQL queries together.....
Date: Sat, 29 May 2010 06:42:01 -0700 (PDT)
Message-ID: <344adb3a-36b5-4c2d-a827-4d973204ad2d_at_f13g2000vbm.googlegroups.com>
On May 28, 7:58 pm, joel garry <joel-ga..._at_home.com> wrote: (snip)
> > 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:1...
>
>
>
> > 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
Joel,
Thanks for finding the definition of that term - I have seen it countless times, but have trouble remembering the term when needed.
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/expressions013.htm "A scalar subquery expression is a subquery that returns exactly one column value from one row. The value of the scalar subquery expression is the value of the select list item of the subquery. If the subquery returns 0 rows, then the value of the scalar subquery expression is NULL. If the subquery returns more than one row, then Oracle returns an error."
With the definition from Oracle's documentation, a scalar subquery can
also appear in a WHERE clause, if the subquery returns a single row
and a single column:
SELECT
*
FROM
T1
WHERE
C1=(
SELECT
MAX(C1)
FROM
T2
WHERE
STATE='CONFUSED');
This might be what was leading me to forget the term when the subquery
appears in a column position.
The AskTom article does a nice job of explaining why someone might or might not want to use a *scalar* query in a column position in a SQL statement.
Incidentally, I have a copy of Tom Kyte's new book on pre-order and a copy of "Oracle Tuning: The Definitive Reference Second Edition" on pre-order (the first of that author's books I have ordered - I was curious after bumping into the author several times on the OTN forums). I will probably post detailed reviews of both books. I hope that the second edition of the second book does not show me a "Top 5 Timed Events" report with a value indicating the number of waits for the CPU, or other odd problems like average single block read times of 2.75 seconds, while the average multi-block read time is 0.127 seconds like can be observed in a Google Books view of page 16 of the first edition. Last year I mentioned to the author of this book some of my concerns (on the OTN forums) about several pages of the first edition, viewable through Google books, and I am curious to see if corrections were made for the second edition.
Charles Hooper
Co-author of "Expert Oracle Practices: Oracle Database Administration
from the Oak Table"
http://hoopercharles.wordpress.com/
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Sat May 29 2010 - 08:42:01 CDT