Ramming two SQL queries together.....
mr.frog...@googlemail.com
I am wanting to ask some advice before attmepting this as I am afraid
I might tie up too much resource on our DWH - and that would tick off
the admins (understandably).
I have two SQL statements that provide me with data, both very similar
and based off the same tables (one uses only a subset of the tables).
One of the queries returns a block of data telling me how many times a
product is seen in a given group of stores, while the second tells me
how many stores are in the given group of stores. The end idea is to
have a calculated column that gives me a 'distribution' percentile -
take the number of times the product is seen in the given group and
divide it by the number fo stores in the given group (and multiply by
100 of course).
The problem I have is that I cannot create views (not allowed). This
leaves me with having to do this in a 'single step'. I would not
normally approach a problem this way, and not being an Oracle expert I
hoping that I can gain the benefit of others experiences in
approaching this. I am really hoping to avoid doing this as a two
stepper in MS Access or something.........
The SQL statements are as follows:
1/ The one with the products seen.....
select
V.VS_NAME as Channel,
T.NAME as Organisation,
VT.VS_TYP_NAME as Type,
CAST(P.VBE_EAN as VARCHAR(13)) as EAN,
COUNT(L.GELISTET) as Listed
from
MASTERGISD.VERTRIEBSSCHIENE V
join MASTERGISD.TRADE_ORG_MASTER T on V.MAIN_ORG = T.ORG_NBR
join MASTERGISD.VERTRIEBSSCH_TYP VT on VT.VS_TYP = V.VS_TYP
join MASTERGISD.LISTKOPF LK on LK.VS_NR = V.VS_NR
join MASTERGISD.LISTPROD L on L.HDRSYSID = LK.SYSID
join MASTERGISD.PRODUKT P on P.PRODUKT = L.PRODUKT
where P.SEGMENT = 'PETCARE'
group by V.VS_NAME, T.NAME, VT.VS_TYP_NAME, P.VBE_EAN
order by V.VS_NAME, T.NAME, VT.VS_TYP_NAME
________________________________________________________________
2/ The one with the count of the stores..............
select
V.VS_NAME as Store,
Count(V.VS_NAME) as Stores,
T.NAME as Organisation,
VT.VS_TYP_NAME as Type
from
MASTERGISD.VERTRIEBSSCHIENE V
join MASTERGISD.TRADE_ORG_MASTER T on V.MAIN_ORG = T.ORG_NBR
join MASTERGISD.VERTRIEBSSCH_TYP VT on VT.VS_TYP = V.VS_TYP
group by V.VS_NAME, T.NAME, VT.VS_TYP_NAME
order by V.VS_NAME, T.NAME, VT.VS_TYP_NAME
_____________________________________________________________________
The perfect end result would be to have all the fields in the first
query with simply an added field from the second with the storecount
(stores). Is this possible in a single step? I was thinking of using a
correlated subquery but I am concerned that this would take inordinate
amounts of time due to how correlated subqueries work (once for each
row......)
Any guidance would be greatly appreciated.
Cheers
The Frog
Charles Hooper
<mr.frog.to....@googlemail.com> wrote:
> Hi Everyone,
>
> I am wanting to ask some advice before attmepting this as I am afraid
> I might tie up too much resource on our DWH - and that would tick off
> the admins (understandably).
>
> I have two SQL statements that provide me with data, both very similar
> and based off the same tables (one uses only a subset of the tables).
> One of the queries returns a block of data telling me how many times a
> product is seen in a given group of stores, while the second tells me
> how many stores are in the given group of stores. The end idea is to
> have a calculated column that gives me a 'distribution' percentile -
> take the number of times the product is seen in the given group and
> divide it by the number fo stores in the given group (and multiply by
> 100 of course).
>
> The problem I have is that I cannot create views (not allowed). This
> leaves me with having to do this in a 'single step'. I would not
> normally approach a problem this way, and not being an Oracle expert I
> hoping that I can gain the benefit of others experiences in
> approaching this. I am really hoping to avoid doing this as a two
> stepper in MS Access or something.........
>
> The perfect end result would be to have all the fields in the first
> query with simply an added field from the second with the storecount
> (stores). Is this possible in a single step? I was thinking of using a
> correlated subquery but I am concerned that this would take inordinate
> amounts of time due to how correlated subqueries work (once for each
> row......)
>
> Any guidance would be greatly appreciated.
>
> Cheers
>
> The Frog
Inline views will probably be the solution for you. First, getting
rid of the ANSI syntax so that you can see what is happening. The
first SQL statement:
select
V.VS_NAME as Channel,
T.NAME as Organisation,
VT.VS_TYP_NAME as Type,
CAST(P.VBE_EAN as VARCHAR(13)) as EAN,
COUNT(L.GELISTET) as Listed
from
MASTERGISD.VERTRIEBSSCHIENE V,
MASTERGISD.TRADE_ORG_MASTER T,
MASTERGISD.VERTRIEBSSCH_TYP VT,
MASTERGISD.LISTKOPF LK,
MASTERGISD.LISTPROD L,
MASTERGISD.PRODUKT P
where
P.SEGMENT = 'PETCARE'
AND V.MAIN_ORG = T.ORG_NBR
AND VT.VS_TYP = V.VS_TYP
AND LK.VS_NR = V.VS_NR
AND L.HDRSYSID = LK.SYSID
AND P.PRODUKT = L.PRODUKT
group by
V.VS_NAME, T.NAME, VT.VS_TYP_NAME, P.VBE_EAN
order by
V.VS_NAME, T.NAME, VT.VS_TYP_NAME;
The second SQL statement:
select
V.VS_NAME as Store,
Count(V.VS_NAME) as Stores,
T.NAME as Organisation,
VT.VS_TYP_NAME as Type
from
MASTERGISD.VERTRIEBSSCHIENE V
MASTERGISD.TRADE_ORG_MASTER T
MASTERGISD.VERTRIEBSSCH_TYP VT
WHERE
V.MAIN_ORG = T.ORG_NBR
AND VT.VS_TYP = V.VS_TYP
group by
V.VS_NAME, T.NAME, VT.VS_TYP_NAME
order by
V.VS_NAME, T.NAME, VT.VS_TYP_NAME;
The combined SQL statement:
select
V.VS_NAME as Channel,
T.NAME as Organisation,
VT.VS_TYP_NAME as Type,
CAST(P.VBE_EAN as VARCHAR(13)) as EAN,
COUNT(L.GELISTET) as Listed,
VW.STORES
from
MASTERGISD.VERTRIEBSSCHIENE V,
MASTERGISD.TRADE_ORG_MASTER T,
MASTERGISD.VERTRIEBSSCH_TYP VT,
MASTERGISD.LISTKOPF LK,
MASTERGISD.LISTPROD L,
MASTERGISD.PRODUKT P,
(select
V.VS_NAME as Store,
Count(V.VS_NAME) as Stores,
T.NAME as Organisation,
VT.VS_TYP_NAME as Type
from
MASTERGISD.VERTRIEBSSCHIENE V
MASTERGISD.TRADE_ORG_MASTER T
MASTERGISD.VERTRIEBSSCH_TYP VT
WHERE
V.MAIN_ORG = T.ORG_NBR
AND VT.VS_TYP = V.VS_TYP
group by
V.VS_NAME, T.NAME, VT.VS_TYP_NAME) VW
where
P.SEGMENT = 'PETCARE'
AND V.MAIN_ORG = T.ORG_NBR
AND VT.VS_TYP = V.VS_TYP
AND LK.VS_NR = V.VS_NR
AND L.HDRSYSID = LK.SYSID
AND P.PRODUKT = L.PRODUKT
AND V.VS_NAME=VW.STORE
AND T.NAME=VW.ORGANISATION
group by
V.VS_NAME, T.NAME, VT.VS_TYP_NAME, P.VBE_EAN,
VW.STORES
order by
V.VS_NAME, T.NAME, VT.VS_TYP_NAME;
In the above SQL statement I used the second SQL statement just as if
it were another table simply by putting the SQL statement in the FROM
clause, wrapped in () and given an alias. You aliased a couple of the
columns in the SQL statement, and so I joined the first query to the
second using those column aliases (AND V.VS_NAME=VW.STORE AND
T.NAME=VW.ORGANISATION). Those are the basics of using inline views.
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.
Walt
> Hi Everyone,
> The perfect end result would be to have all the fields in the first
> query with simply an added field from the second with the storecount
> (stores). Is this possible in a single step?
Charles' example of an inline view should work for you.
You could also use a correlated subquery. Something like
select
V.VS_NAME as Channel,
T.NAME as Organisation,
VT.VS_TYP_NAME as Type,
CAST(P.VBE_EAN as VARCHAR(13)) as EAN,
COUNT(L.GELISTET) as Listed,
(
selectCount(V.VS_NAME)
from
/*tables */
WHERE
/*appropriate joins to outer query*/
) as storecount
from
MASTERGISD.VERTRIEBSSCHIENE V,
MASTERGISD.TRADE_ORG_MASTER T,
MASTERGISD.VERTRIEBSSCH_TYP VT,
MASTERGISD.LISTKOPF LK,
MASTERGISD.LISTPROD L,
MASTERGISD.PRODUKT P,
where
P.SEGMENT = 'PETCARE'
AND V.MAIN_ORG = T.ORG_NBR
AND VT.VS_TYP = V.VS_TYP
AND LK.VS_NR = V.VS_NR
AND L.HDRSYSID = LK.SYSID
AND P.PRODUKT = L.PRODUKT
AND V.VS_NAME=VW.STORE
AND T.NAME=VW.ORGANISATION
order by
V.VS_NAME, T.NAME, VT.VS_TYP_NAME;
(Note that the above won't work as written, but the
mr.frog...@googlemail.com
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
joel garry
I'm wondering about how you think correlated subqueries run. See
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries008.htm
http://jonathanlewis.wordpress.com/2007/03/08/transformation-and-optimisation/
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
--
@home.com is bogus.
http://www.networkworld.com/news/2010/052710-alleged-100m-scareware-sellers-facing.html
Charles Hooper
> <mr.frog.to....@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
>
>
> 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
SELECT statement in the WHERE clause.
http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/statements_10002.htm#i2066912
"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.
Here is possibly interesting test case:
CREATE TABLE T1 (
ID NUMBER,
DESCRIPTION VARCHAR2(80));
INSERT INTO T1
SELECT
CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),
'This is the long description for this number '||
TO_CHAR(CEIL(ABS(SIN(ROWNUM/9.9999)*10000)))
FROM
(SELECT
ROWNUM RN
FROM
DUAL
CONNECT BY
LEVEL<=1000),
(SELECT
ROWNUM RN
FROM
DUAL
CONNECT BY
LEVEL<=1000);
CREATE INDEX IND_T1 ON T1(ID);
CREATE TABLE T2 AS
SELECT
ROWNUM C1,
LPAD('A',100,'A') C2
FROM
DUAL
CONNECT BY
LEVEL<=10000;
CREATE TABLE T3 AS
SELECT
ROWNUM C1,
LPAD('A',100,'A') C2
FROM
DUAL
CONNECT BY
LEVEL<=10000;
CREATE TABLE T4 AS
SELECT
ROWNUM C1,
LPAD('A',100,'A') C2
FROM
DUAL
CONNECT BY
LEVEL<=10000;
CREATE INDEX IND_T4 ON T4(C1);
COMMIT;
EXEC
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE,METHOD_OPT=>'FOR
ALL COLUMNS SIZE 1')
EXEC
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T2',CASCADE=>TRUE)
EXEC
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T3',CASCADE=>TRUE)
EXEC
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T4',CASCADE=>TRUE)
There are just 1,000,000 rows in table T1.
Now, let's try a test script using the tables:
SET AUTOTRACE TRACEONLY EXPLAIN
SELECT
T1.ID,
T2.C1 T2_C1,
T3.C1 T3_C1,
T4.C1 T4_C1
FROM
T1,
T2,
T3,
T4
WHERE
T2.C1 BETWEEN 1 AND 200
AND T2.C1=T3.C1
AND T2.C1=T4.C1
AND T2.C1=T1.ID;
SELECT
T1.ID,
(SELECT
T2.C1
FROM
T2
WHERE
T1.ID=T2.C1) T2_C1,
(SELECT
T3.C1
FROM
T3
WHERE
T1.ID=T3.C1) T3_C1,
(SELECT
T4.C1
FROM
T4
WHERE
T1.ID=T4.C1) T4_C1
FROM
T1
WHERE
T1.ID BETWEEN 1 AND 200;
The first SQL statement directly joins the tables, while the second
places SELECT statements in column positions. The output (11.1.0.7):
SQL> SELECT
2 T1.ID,
3 T2.C1 T2_C1,
4 T3.C1 T3_C1,
5 T4.C1 T4_C1
6 FROM
7 T1,
8 T2,
9 T3,
10 T4
11 WHERE
12 T2.C1 BETWEEN 1 AND 200
13 AND T2.C1=T3.C1
14 AND T2.C1=T4.C1
15 AND T2.C1=T1.ID;
Execution Plan
----------------------------------------------------------
Plan hash value: 3780653648
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 457 | 7312 | 10041 (1)|
00:00:41 |
|* 1 | HASH JOIN | | 457 | 7312 | 10041 (1)|
00:00:41 |
|* 2 | HASH JOIN | | 198 | 2376 | 46 (5)|
00:00:01 |
|* 3 | HASH JOIN | | 199 | 1592 | 43 (3)|
00:00:01 |
|* 4 | TABLE ACCESS FULL| T2 | 200 | 800 | 21 (0)|
00:00:01 |
|* 5 | TABLE ACCESS FULL| T3 | 200 | 800 | 21 (0)|
00:00:01 |
|* 6 | INDEX RANGE SCAN | IND_T4 | 200 | 800 | 2 (0)|
00:00:01 |
|* 7 | TABLE ACCESS FULL | T1 | 20002 | 80008 | 9994 (1)|
00:00:41 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T2"."C1"="T1"."ID")
2 - access("T2"."C1"="T4"."C1")
3 - access("T2"."C1"="T3"."C1")
4 - filter("T2"."C1"<=200 AND "T2"."C1">=1)
5 - filter("T3"."C1"<=200 AND "T3"."C1">=1)
6 - access("T4"."C1">=1 AND "T4"."C1"<=200)
7 - filter("T1"."ID"<=200 AND "T1"."ID">=1)
SQL> SELECT
2 T1.ID,
3 (SELECT
4 T2.C1
5 FROM
6 T2
7 WHERE
8 T1.ID=T2.C1) T2_C1,
9 (SELECT
10 T3.C1
11 FROM
12 T3
13 WHERE
14 T1.ID=T3.C1) T3_C1,
15 (SELECT
16 T4.C1
17 FROM
18 T4
19 WHERE
20 T1.ID=T4.C1) T4_C1
21 FROM
22 T1
23 WHERE
24 T1.ID BETWEEN 1 AND 200;
Execution Plan
----------------------------------------------------------
Plan hash value: 2945978589
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20002 | 80008 | 9994 (1)|
00:00:41 |
|* 1 | TABLE ACCESS FULL| T2 | 1 | 4 | 21 (0)|
00:00:01 |
|* 2 | TABLE ACCESS FULL| T3 | 1 | 4 | 21 (0)|
00:00:01 |
|* 3 | INDEX RANGE SCAN | IND_T4 | 1 | 4 | 1 (0)|
00:00:01 |
|* 4 | TABLE ACCESS FULL| T1 | 20002 | 80008 | 9994 (1)|
00:00:41 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T2"."C1"=:B1)
2 - filter("T3"."C1"=:B1)
3 - access("T4"."C1"=:B1)
4 - filter("T1"."ID"<=200 AND "T1"."ID">=1)
If we were to actually run the SQL statements, we might find that the
first runs in about 15 seconds and the second in about 16 seconds,
both with the same number of physical reads. That is no fun, so let's
change the number 200 to 1200 to see what happens.
SET TIMING ON
SET AUTOTRACE TRACEONLY STATISTICS;
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH BUFFER_CACHE;
SET ARRAYSIZE 1000
SELECT
T1.ID,
T2.C1 T2_C1,
T3.C1 T3_C1,
T4.C1 T4_C1
FROM
T1,
T2,
T3,
T4
WHERE
T2.C1 BETWEEN 1 AND 1200
AND T2.C1=T3.C1
AND T2.C1=T4.C1
AND T2.C1=T1.ID;
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH BUFFER_CACHE;
SELECT
T1.ID,
(SELECT
T2.C1
FROM
T2
WHERE
T1.ID=T2.C1) T2_C1,
(SELECT
T3.C1
FROM
T3
WHERE
T1.ID=T3.C1) T3_C1,
(SELECT
T4.C1
FROM
T4
WHERE
T1.ID=T4.C1) T4_C1
FROM
T1
WHERE
T1.ID BETWEEN 1 AND 1200;
Here is the output:
SQL> SELECT
2 T1.ID,
3 T2.C1 T2_C1,
4 T3.C1 T3_C1,
5 T4.C1 T4_C1
6 FROM
7 T1,
8 T2,
9 T3,
10 T4
11 WHERE
12 T2.C1 BETWEEN 1 AND 1200
13 AND T2.C1=T3.C1
14 AND T2.C1=T4.C1
15 AND T2.C1=T1.ID;
76580 rows selected.
Elapsed: 00:00:15.96
Statistics
---------------------------------------------------
1 recursive calls
0 db block gets
83197 consistent gets
83110 physical reads
0 redo size
1288037 bytes sent via SQL*Net to client
1217 bytes received via SQL*Net from client
78 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
76580 rows processed
SQL> SELECT
2 T1.ID,
3 (SELECT
4 T2.C1
5 FROM
6 T2
7 WHERE
8 T1.ID=T2.C1) T2_C1,
9 (SELECT
10 T3.C1
11 FROM
12 T3
13 WHERE
14 T1.ID=T3.C1) T3_C1,
15 (SELECT
16 T4.C1
17 FROM
18 T4
19 WHERE
20 T1.ID=T4.C1) T4_C1
21 FROM
22 T1
23 WHERE
24 T1.ID BETWEEN 1 AND 1200;
76580 rows selected.
Elapsed: 00:01:40.09
Statistics
---------------------------------------------------
1 recursive calls
0 db block gets
10073639 consistent gets
83110 physical reads
0 redo size
1288037 bytes sent via SQL*Net to client
1217 bytes received via SQL*Net from client
78 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
76580 rows processed
The number of consistent gets jumps significantly for the second SQL
statement and so did the execution time (and CPU usage), compare with
the output for T1.ID between 1 and 200:
12732 rows selected.
Elapsed: 00:00:17.54
Statistics
---------------------------------------------------
0 recursive calls
0 db block gets
522390 consistent gets
83108 physical reads
0 redo size
196813 bytes sent via SQL*Net to client
513 bytes received via SQL*Net from client
14 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
12732 rows processed
---
I think that The Frog is on the right track to test the performance of
both approaches.
joel garry
> On May 28, 1:23 pm, joel garry <joel-ga...@home.com> wrote:
>
> > <mr.frog.to....@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
>
>
> > 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
--
@home.com is bogus.
Charles Hooper
(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.
>
>
>
>
> > 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.
John Hurley
> 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.
You are not bringing in the name of the leader of the guide ponys are
you? Not the New York subway bleeder?
Not sure why you are going in this direction here buddy ... it could
get ugly.
Charles Hooper
I was curious, so I spent good money on the book. For most of the
Oracle books that I have read in the last two years I have posted
detailed reviews of the books on Amazon. The reviews list what I
thought was good about the book, what I thought was great about the
book, errors/errata found in the book's chapters, as well as misc.
points about the book as it is read. Some of my reviews identified a
dozen or more errors in a book, which is a significant problem with
some of the Oracle books on the market. The error must make it
difficult for beginners to learn about Oracle Database.
How long will the review of the "Oracle Tuning: The Definitive
Reference Second Edition" book stretch? I am not sure, but it appears
that I could write about a 4 page review on the first 27 pages of the
first edition of this book - I sincerely hope that some/most of those
issues were addressed in the second edition. I want to make certain
that I obtain a good value from the $44 that I spent on the book. I
do not think that it too much to ask for accuracy from a published
book, especially when the book cost more than $40.
Some of the pages in the chapters that I co-authored in the "Expert
Oracle Practices" required more than 8 hours of research, typing,
verification, testing, proofing, re-testing, and formatting per page.
I believe that Raldolf spent roughly the same amount of time on the
pages that he wrote for the chapters. I suspect that many of the very
good books that are on the market for Oracle also required about the
same amount of time per page. Certainly, it can't be too much to ask
that other book authors put effort into testing and verification of
what they publish?
The author's website states about the the "Oracle Tuning: The
Definitive Reference Second Edition" book: "This is not a book for
beginners. Targeted at the senior Oracle DBA, this comprehensive book
gives you all of the knowledge you need to be successful in tuning
even the most complex Oracle database." So, I will be looking for the
portions of the book's contents that are comprehensive and targeted at
the senior Oracle DBA.
John Hurley
# I was curious, so I spent good money on the book. For most of the
Oracle books that I have read in the last two years I have posted
detailed reviews of the books on Amazon.
Curious ... like curious yellow?
Doing a little checking yup it sure looks like you are referring to a
book by "he who must not be named" ... the voldemort of index
rebuilding and golden bullets.
Hey if you want to go a wedding and do the chicken dance by all means
go ahead ... it may be a whole lot less fun than you imagine however.
joel garry
> Charles:
>
> # I was curious, so I spent good money on the book. For most of the
> Oracle books that I have read in the last two years I have posted
> detailed reviews of the books on Amazon.
>
> Curious ... like curious yellow?
LOL, I assume you weren't referring to http://blanu.net/curious_yellow.html
>
> Doing a little checking yup it sure looks like you are referring to a
> book by "he who must not be named" ... the voldemort of index
> rebuilding and golden bullets.
>
> Hey if you want to go a wedding and do the chicken dance by all means
> go ahead ... it may be a whole lot less fun than you imagine however.
You might want to dig further into Charles' web site and otn postings,
he provides a valuable service by going into detail on exactly what is
wrong with the golden showers of silver bullets, furthering the
tradition of some other people we respect, and being polite about it
too. As entertaining as the Cherry Sisters and other nastiness was,
it hurt the cause of scientific inquiry some, simply saying everything
someone writes is bad just because they wrote some obviously bad stuff
and are arrogant about it - well, I happen to agree with the
sentiment, but it's both bad PR and logically inconsistent. Even as
recently as this past month, oracle-l was spreading rumors about the
fellows business I wouldn't repeat here, which I can't help but think
comes from the bias engendered from all the previous controversies.
Of course, politeness and sarcasm are difficult to distinguish on the
internet, and that can be fun.
It doesn't have to be fun when the motivation runs deep: http://xkcd.com/386/
jg
--
@home.com is bogus. "Newsman Dennis Morgigno informed attendees of
the Golden Fleece awards banquet Thursday that the La Jolla Children’s
Pool seal controversy finally had been resolved and the seals are
gone. Then he ad-libbed: “Enjoy your roasted harbor seal” … While
accepting a Golden Fleece from the county Taxpayers Association for
San Diego city schools’ support of a parcel tax, board President
Richard Barrera announced that a new superintendent finally had been
selected: Joe Arpaio, the sheriff of Arizona’s Maricopa County (who
promotes himself as “America’s Toughest Sheriff”). " - Diane Bell