In some cases, NOT IN is better than NOT EXISTS. In
other cases, the opposite is true.
Moral: It never pays to discount an option out of hand
- eg, NOT IN often works very very nicely for
uncorrelated subqueries
hth
connor
--- Post, Ethan [EMAIL PROTECTED] wrote: Hold
the press. NOT IN
It's the count of a certain type of fetch operations of blocks from the
database buffer cache. See Why you should focus on LIOs instead of
PIOs at www.hotsos.com/catalog for details.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- Hotsos Clinic, Dec 9-11 Honolulu
Cary,
I read your wonderful article. What is the exact difference between CR and
CU, blocks fetched in Consistent and Current mode?
Regards
Naveen
-Original Message-
Sent: Friday, November 15, 2002 2:54 PM
To: Multiple recipients of list ORACLE-L
It's the count of a certain type of
Title: RE: RE: CONSISTANT GETS
Funny ... that Cary mentioned it
Some developers here think that by setting some magic instance parameters we can make all RBO tuned code run well under CBO ... (I just bought a 6 pack of Mylanta yesterday ...)
Raj
.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
!DOCTYPE HTML PUBLIC -//W3C//DTD HTML 3.2//EN
HTML
HEAD
META HTTP-EQUIV=Content-Type CONTENT=text/html; charset=iso-8859-1
META NAME=Generator CONTENT=MS Exchange Server version 5.5.2654.19
TITLERE: RE: CONSISTANT GETS/TITLE
/HEAD
BODY
HEAD
META HTTP-EQUIV=Content-Type CONTENT=text/html; charset=iso-8859-1
META NAME=Generator CONTENT=MS Exchange Server version 5.5.2654.19
TITLERE: RE: CONSISTANT GETS/TITLE
/HEAD
BODY
PFONT SIZE=2Funny ... that Cary mentioned it /FONT
/P
PFONT SIZE=2Some developers here think that by setting
Title: RE: RE: RE: CONSISTANT GETS
I've seen worse. My programmers don't know how to use NOT EXISTS even though I've explained it many times. And that's the least of my problems. Look at this mess:
SELECT *
FROM sar.pax_header_suspense_err_temp
WHERE manifest_type
.
Jared
Whittle Jerome Contr NCI [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
11/15/2002 08:21 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: RE: RE: CONSISTANT GETS
I've seen worse. My programmers
/html; charset=iso-8859-1
META NAME=Generator CONTENT=MS Exchange Server version 5.5.2654.19
TITLERE: RE: CONSISTANT GETS/TITLE
/HEAD
BODY
PFONT SIZE=2Funny ... that Cary mentioned it /FONT
/P
PFONT SIZE=2Some developers here think that by setting some magic
instance
parameters we can make all
Hold the press. NOT IN better than NOT EXISTS? Is this theory or fact? If
so is there any supporting evidence out there? This is the first I have
heard of this.
Thanks!
-Original Message-
Sent: Friday, November 15, 2002 11:35 AM
To: Multiple recipients of list ORACLE-L
Jerry,
I
Naveen,
A CU block is fetched as-is; whatever's in the block at the time of
the read is what comes back from the LIO. CU mode is used when read
consistency is not an issue; for example, when reading your own
(private) sort blocks. I think if you DELETE without a WHERE clause,
you'll see CU blocks
Title: RE: CONSISTANT GETS
Jared,
I'm still on 7.3.4 but I'm sure that you are right about the WHERE clause in this case. It went from an INDEX FULL SCAN to an INDEX UNIQUE SCAN on the same index once the blasted concatenations were removed. One programmer says he likes to write
Whittle Jerome Contr NCI wrote:
Jared,
I'm still on 7.3.4 but I'm sure that you are right about the WHERE
clause in this case. It went from an INDEX FULL SCAN to an INDEX
UNIQUE SCAN on the same index once the blasted concatenations were
removed. One programmer says he likes to write it
Hold the press. NOT IN better than NOT EXISTS?
If so is there any supporting evidence out there?
I think you're joking, but if not there's a nice comparison chart of several
tests in Harrison, p. 268.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Greg Moore
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: RE: RE: CONSISTANT GETS
Hold the press. NOT IN better than NOT EXISTS? Is this theory or fact?
If
so is there any supporting evidence out there? This is the first I have
heard
forget about parameters ... look at fixing the SQL
that is causing the problem
hth
connor
--- Hamid Alavi [EMAIL PROTECTED] wrote:
Dear List,
I am monitoring a database, I findout there is a
transaction which runing
a long time and others are waiting for this
transaction, this
Hamid,
I'm sorry: Unless your SQL returns fewer than about 800,000 rows to the
calling application (or an aggregation of 800,000 rows), then the
statement we have done all the necessary tuning on all the SQL queries
is not yet true.
If your SQL does actually return about 800,000 rows, then it is
Sorry for asking such a obvious question, but CONSISTANT GETS means calling
rows from Database
Thanks,
-Original Message-
Sent: Thursday, November 14, 2002 10:35 AM
To: Multiple recipients of list ORACLE-L
Hamid,
I'm sorry: Unless your SQL returns fewer than about 800,000 rows to
18 matches
Mail list logo
