Catherine,
I am sure that Larry Elkins will forgive my taking the opportunity of being 7 hours
ahead of him (and 7 hours behind yourself) for singing the praise of the 'hash
anti-join' in his name. And anyway you could remind your senior DBA the existence of
NOT EXISTS, far better than
I would rewrite it with a where not exists But that is just me. I
would also not bother selecting anything in the subquery. Just a 1 or a 'x'
would do. Don't return stuff from the database that you don't want. All
you care is whether or not there is a record in the subquery not what it
Use 'exists' or 'not exists' only if you have index on col3 and col4 on
table_2 that can be used in the sub-query, else the query will be running
like a dog.
Raj
__
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN
CHAN Chor Ling Catherine (CSC) wrote:
Hi Gurus,
My senior DBA always tell us that the not in command sucks and we are all
encourage to use the select count(*). SQL A is greatly frowned upon and SQL
B will be the best.
SQL A :
SELECT col1,col2
FROM Table_1
WHERE (col1,col2) NOT
To: Multiple recipients of list ORACLE-L
Subject:Re: Follow-up :Long running SQL Problem?
CHAN Chor Ling Catherine (CSC) wrote:
Hi Gurus,
My senior DBA always tell us
Hi Gurus,
My senior DBA always tell us that the not in command sucks and we are all
encourage to use the select count(*). SQL A is greatly frowned upon and SQL
B will be the best.
SQL A :
SELECT col1,col2
FROM Table_1
WHERE (col1,col2) NOT IN (SELECT col3,col4
Why not code up a couple of SQL's and try it out?
I just tried a couple of examples that match your code, and the NOT IN
version was slightly faster, so I'm not frowning upon it. ;-)
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, April
]
sfaroult@orio cc:
le.com Subject: Re: Long-running SQL
Sent
Title: Long running SQL Problem?
Hello List
Is there anyone who can give me a solution to this problem.
It is a sql that runs forever and I eventually have to kill it, both tables are large 50 + rows.
Is there perhaps a quicker more effecient way of doing this.
SELECT col1,col2
FROM
/NL)
[EMAIL PROTECTED] Subject: Long running SQL Problem
Hi Denham,
Suggestion 1) Perhaps you may create an index for table_1 (col1,col2) and
table_2 (col3,col4)
Suggestion 2) Try
SELECT col1,col2
FROM Table_1
WHERE (0=(select count(*) from table_2 where
col3=col1 and
Should be better with
select col1, col2
from table_1
minus
select col3, col4
from table2
Iain Nicoll
-Original Message-
Sent: Wednesday, March 27, 2002 8:53 AM
To: Multiple recipients of list ORACLE-L
Hello List
Is there anyone who can give me a solution to this problem.
It
Title: Long running SQL Problem?
Hi
List
Thank
you to everyone who took the time to answer, I never realised that there could
be so many solutions :)
Rgds
Denham
-Original Message-From: Denham Eva
[mailto:[EMAIL PROTECTED]]Sent: Wednesday, March 27, 2002 10:53
AMTo
:
Subject: Long running SQL Problem?
03/27/2002
We have a statement that I feel takes too long to run in a nightly data
load.
The table it runs against has 386,000 records. It runs for about 10
seconds
on average. We're only loading about 50,000 records a night but this
statement
is running during the majority of the 9-hour load time.
)
[EMAIL PROTECTED] Subject: Long-running SQL
Fairfield Resorts, Inc.
954-935-4117
-Original Message-
From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]]
Sent: Wednesday, March 27, 2002 9:33 AM
To: Multiple recipients of list ORACLE-L
Subject: Long-running SQL
We have a statement that I feel takes too long to run
Cherie,
At least the leading column in your index doesn't have an index-killing
function on it in the Where clause - the NVL function on the other columns
makes Oracle not use them for hitting the index.
Since you're only interested in returning a single value, perhaps a stored
function that
Cherie,
Two things.
1. All the ORG_LEVEL_x_VALUE fields leads me to believe that the data isn't
normalized. What happens when someone needs to add ORG_LEVEL_9_VALUE?
2. I think all the NVLs make your index worthless. About the only functions an index
can work with are MIN and MAX. You might
: [EMAIL PROTECTED]
tt.af.milSubject: RE: Long-running SQL
],
field.com '[EMAIL PROTECTED]'
[EMAIL PROTECTED]
cc:
03/27/02 10:16 AM Subject: RE: Long-running SQL
],
field.com '[EMAIL PROTECTED]'
[EMAIL PROTECTED]
cc:
03/27/02 10:16 AM Subject: RE: Long-running
SQL
Hi Cherie,
do you know for sure it is using
]
TOUCH.COMcc:
Sent by: Subject: RE: Long-running SQL
[EMAIL PROTECTED
[SMTP:[EMAIL PROTECTED]]
Sent: Wednesday, March 27, 2002 12:15 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Long-running SQL
Cherie - This means that on the average the query is retrieving 366
values?
I had something similar on our data warehouse. Indexed retrievals
It is using the index. Range scans are not smart so to speak. In
Harrison's sql tuning book, he states that a range scan is not
intelligent.
Well, depends on the type of range scan. There are two kinds, bonded and
unbounded. Bounded is indeed intelligent and starts and stops at specific
PROTECTED]
TOUCH.COMcc:
Sent by: Subject: RE: Long-running SQL
[EMAIL PROTECTED]
m
03/27/02 11:15
AM
Please
PROTECTED]]
Sent: Wednesday, March 27, 2002 1:54 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Long-running SQL
It is using the index. Range scans are not smart so to speak. In
Harrison's sql tuning book, he states that a range scan is not
intelligent.
Well, depends
Um,
You want to double check that last bit? Shouldn't it say,
A _un_bounded range scan is caused by such actions like
Where range_id 100
Thom Dyson
Director of Information Services
Sybex, Inc.
On 3/27/02 10:53:41 AM, Freeman, Robert [EMAIL PROTECTED] wrote:
snip
Well, depends on the
it isn't my problem.
Lisa
-Original Message-
From: Freeman, Robert [SMTP:[EMAIL PROTECTED]]
Sent: Wednesday, March 27, 2002 1:54 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Long-running SQL
It is using the index. Range scans are not smart so to speak
Robert,
Thanks for your informative reply.
Cherie
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
[SMTP:[EMAIL PROTECTED]]
Sent: Wednesday, March 27, 2002 1:54 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Long-running SQL
It is using the index. Range scans are not smart so to speak. In
Harrison's sql tuning book, he states that a range scan is not
intelligent.
Well
[EMAIL PROTECTED] wrote:
We have a statement that I feel takes too long to run in a nightly data
load.
The table it runs against has 386,000 records. It runs for about 10
seconds
on average. We're only loading about 50,000 records a night but this
statement
is running during the
Cherie,
Are you calling this sql for every row in the load job?
If the answer = Yes
then my advice will be bulk loading in temporary table and joining the temp
table with your dim using a Hash/join to load the target table.
Do full table scan from the dim table for the join.
Regards,
Waleed
ORACLE-L Digest -- Volume 2002, Number 086
From: Denham Eva [EMAIL PROTECTED]
Date: Wed, 27 Mar 2002 10:58:23 +0200
Subject: Long running SQL Problem?
...
Is there anyone who can give me a solution to this problem.
get faster/more RAM, CPU, hard drives, etc?
It is a sql that runs
Subject: RE: Capturing long
running SQL
Sent by:
[EMAIL PROTECTED]
om
10/18/01 10:30
AM
Please respond
Subject: RE: Capturing long
running SQL
Sent by:
[EMAIL PROTECTED]
om
10/18/01 10:30
AM
Please respond
To: '[EMAIL PROTECTED]'
Cc: '[EMAIL PROTECTED]'
Subject: RE: Capturing long running SQL
If running 8i, you may want to check the view v$session_longops.
May be there is some useful info.
Regards,
- Kirti Deshpande
Verizon Information Services
http://www.superpages.com
-Original Message
$session_longops is also available on some select versions of
8.0.5.x
- Kirti
-Original Message-
From: Deshpande, Kirti
Sent: Tuesday, October 16, 2001 10:30 AM
To: '[EMAIL PROTECTED]'
Cc: '[EMAIL PROTECTED]'
Subject: RE: Capturing long running SQL
If running 8i, you may want to check the view
versions of
8.0.5.x
- Kirti
-Original Message-
From: Deshpande, Kirti
Sent: Tuesday, October 16, 2001 10:30 AM
To: '[EMAIL PROTECTED]'
Cc: '[EMAIL PROTECTED]'
Subject: RE: Capturing long running SQL
If running 8i, you may want to check the view v$session_longops.
May
-
From: Robertson Lee - lerobe [SMTP:[EMAIL PROTECTED]]
Sent: Thursday, October 18, 2001 10:30 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Capturing long running SQL
erm... whoops, you find the strangest things in your clipboard !
Correct pasting is as follows
]
lerobe@acxiom cc:
.co.uk Subject: RE: Capturing long running SQL
Sent
Robertson Lee - lerobe wrote:
Sorry, I passed it onto Kirti but didn't pass on to the list. I received
this from a guy called Paul, sorry Paul I don't know your surname but its
down to him. Initial tests show that it works but I need to test on a good
long running query.
They've got cars
To: '[EMAIL PROTECTED]'
Cc: '[EMAIL PROTECTED]'
Subject: RE: Capturing long running SQL
If running 8i, you may want to check the view v$session_longops.
May be there is some useful info.
Regards,
- Kirti Deshpande
Verizon Information Services
http://www.superpages.com
-Original Message
43 matches
Mail list logo
