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
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
Hi,
You could try the NOT EXIST flavour. It should be able to use indexes than
Jack
Denham Eva
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:
Try something like
select a.col1||a.col2, b.col3||b.col4 from table1 a, table2 b
where a.col1||a.col2 = b.col3||b.col4 (+))
WHERE b.col3||b.col4 IS NULL;
I think that works.
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
11 matches
Mail list logo
