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
]
sfaroult@orio cc:
le.com Subject: Re: Long-running SQL
Sent
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.
Hi,
What is the explain plan
Jack
Cherie_Machler@ge
Hi Cherie,
do you know for sure it is using the index? Have you traced it? Can you
post the explain plan? What's the cardinality of account_number in this
table?
I can just hear those people over there screaming at you saying it's your
problem...
Lisa Koivu
Oracle Database Administrator
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
30 matches
Mail list logo
