ech.com> cc:
Sent by: Subject: RE: Life without a correlated
subquery
Thanks
Folks!
The
inline query, indeed, beat the correlated subquery.
Bambi.
-Original Message-From: Daniel Fink
[mailto:[EMAIL PROTECTED]Sent: Monday, November 10, 2003 11:34
AMTo: Multiple recipients of list ORACLE-LSubject: Re:
Life without a correlated subqueryIt
It is a little convoluted, but you can use an inline query. It is not a
correlated subquery, it may be more efficient, your mileage may vary, contents
under pressure...
Here is an example of the select using the old, reliable emp table that
I populated with duplicates.
SQL> l
1 select
I did a quick test with autotrace and here are the results. The bottom
line is that the inline query used 6 i/os, the subquery used 46. (I repeated
this test multiple times, same result).
1 select e1.rowid,
2 e1.empno,
3 e1.ename
4 from emp e1,
5 (select empno
s not null." Fine.
Standard correlated subquery.
delete from a
where rowid not in (select min(rowid)
where pid=a.pid
and X is not null)
and X is not null
Right?
So, my associate says "what are you doing? you're going to go through the
table every single time for each record?&q
pick to correlate or hash a correlated query (though we still
have the ability to un-correlate a correlated query in 8i).
Regards,
Larry G. Elkins
[EMAIL PROTECTED]
214.954.1781
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of
> BALA,PRAKASH (Non-H
8i).
Regards,
Larry G. Elkins
[EMAIL PROTECTED]
214.954.1781
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of
> BALA,PRAKASH (Non-HP-USA,ex1)
> Sent: Thursday, September 05, 2002 2:12 PM
> To: Multiple recipients of list ORACLE-L
>
> "BALA,PRAKASH (Non-HP-USA,ex1)" <[EMAIL PROTECTED]>
> Sent by: [EMAIL PROTECTED]
> 09/05/2002 12:11 PM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L
> <[EMAIL PROTECTED]>
> cc:
> Su
Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject:Correlated subquery performance in 8i & 9i
Learnt the following from Gaja's seminar last week. So just wanted to pass
this on:
Inline views works better than correlated subqueries in 8i.
ing a correlated
subquery.
HTH!
Prakash
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: BALA,PRAKASH (Non-HP-USA,ex1)
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing
IL PROTECTED]
f list
ORACLE-L <[EMAIL PROTECTED]>
Sent by: cc: (bcc: Jack van
Zanen/nlzanen1/External/MEY/NL)
[EMAIL PROTECTED] Subject: SQL Join
All...
I have a question about a query over which I've been racking my brain
for weeks, and I can't find a reference to adequately assist me.
Given a table of temperature sensors, and a table of readings from those
sensors, how might I grab the max (hence latest or current) temperature
reading f
nen/nlzanen1/External/MEY/NL)
Sent by: Subject: order by in
subquery workaround
[EMAIL PROTECTED]
11-07-2002 19:38
Please respond to
ORACLE-L
> Solaris 2.6
> O
Rob and Jack:
Found it. (165420.1)
Thanks for your help.
Barb
> --
> From: Robert Pegram[SMTP:[EMAIL PROTECTED]]
> Reply To: [EMAIL PROTECTED]
> Sent: Friday, July 12, 2002 6:48 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: or
.com>
> cc: (bcc: Jack van
> Zanen/nlzanen1/External/MEY/NL)
>
> Sent by:
> Subject: order by in subquer
ternal/MEY/NL)
Sent by: Subject: order by in subquery
workaround
[EM
errors in the "order by" clause. The
problem is that you can't do an "order by" on a subquery before version
8.1.something-or-other
I know there's some kind of work around for this. I had something to do
with a hint. Even after 3 cups of coffee, I can't remember
[EMAIL PROTECTED] & folks,
also, don't forget something along these lines:
squeal> whenever sqlerror exit sql.code ;
squeal> LOCK TABLE x in exclusive mode nowait ; /* piss off users */
squeal> whenever sqlerror continue ;
> ORACLE-L Digest -- Volume 2001, Number 332
>
;s what you mean. In
> some cases the optimizer can eliminate a sort step when using sub-queries
> instead of a join and other times it processes less data.
>
> Sometimes an IN subquery is faster, other times an EXISTS subquery is
> faster. The point is, you have to try the query all
less data.
Sometimes an IN subquery is faster, other times an EXISTS subquery is
faster. The point is, you have to try the query all 3 ways.
In this case, I suspect that a subquery will be much faster than the join.
(Because of the rownum = 1 restriction)
Kevin
-Original Message-
Sent: Tuesd
Why change it to subqueries?
The optimizer will just turn it back into a join anyway.
Jared
On Tuesday 29 May 2001 05:15, [EMAIL PROTECTED] wrote:
> Hallo you DBAs
>
> How can I write this sql query using subqueries?
>
> SELECT pbk.nielsenart.varunamn, pbk.nielsenart.strl,
> pbk.nielsenart.va
Why would you want to use a subquery? I can see no reason or it and
they do tend to slow things down.
Dick Goulet
-- Reply Separator --
Author: [EMAIL PROTECTED]
Date: 5/29/01 4:15 AM
Hallo you DBAs
How can I write this sql query using subqueries
Hallo you DBAs
How can I write this sql query using subqueries?
SELECT pbk.nielsenart.varunamn, pbk.nielsenart.strl, pbk.nielsenart.varutxt,
null, pbk.nielsenart.vgrp, rik2.hierarki_tekst.tekst, null,
pbk.sortiment_vgrp.sortiment, pbk.nielsenart.art_grp, pbk.nielsenart.art_ugrp, null
FROM pbk.n
t;_eliminate_common_subexpr = false"
>>
>> Which method does the list suggest, and what are the implications of each?
>>
>> Thanks,
>> Bruce Reardon
>> mailto:[EMAIL PROTECTED]
>>
>>
>>
>> Doc ID: Note:137430.1
>> res
c ID: Note:137430.1
>results
>Type: ALERT
>Status: PUBLISHED
> Content Type: TEXT/PLAIN
>Creation Date: 23-MAR-2001
>Last Revision Date: 28-MAR-2001
>
>
> Common Subquery Elimination in 8.1.7 causes incorrect results
> ~~
>
what are the implications of each?
>
>Thanks,
>Bruce Reardon
>mailto:[EMAIL PROTECTED]
>
>
>
>Doc ID: Note:137430.1
>results
>Type: ALERT
>Status: PUBLISHED
> Content Type: TEXT/PLAIN
>Creation Date: 23-MAR-2
te:137430.1
results
Type: ALERT
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 23-MAR-2001
Last Revision Date: 28-MAR-2001
Common Subquery Elimination in 8.1.7 causes incorrect results
~~
Versions Affected
~
Or
28 matches
Mail list logo
