Jerry,
I'll take better performing over nicer looking anyday.
Dick Goulet
____________________Reply Separator____________________
Author: "Whittle Jerome Contr NCI" <[EMAIL PROTECTED]>
Date: 11/15/2002 11:34 AM
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 that way because it's simpler and nicer looking!
Still I've seen some nice speed gains when just converting some WHERE statements
from NOT IN to NOT EXISTS.
YMMV
Jerry Whittle
ACIFICS DBA
NCI Information Systems Inc.
[EMAIL PROTECTED]
618-622-4145
> -----Original Message-----
> From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]]
>
> Jerry,
>
> I suspect that the improvments are more likely due to your
> rewriting the WHERE clause rather than the use of NOT EXISTS.
>
> Especially if the database were 9i, where NOT IN actually
> seems get a better execution path than NOT EXISTS.
>
> That original WHERE clause is really a piece of work.
>
> Jared
>
> "Whittle Jerome Contr NCI" <[EMAIL PROTECTED]>
>
> 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
> || manifesting_station
> || fiscal_year
> || manifest_serial_number NOT IN (
> SELECT manifest_type
> || manifesting_station
> || fiscal_year
> || manifest_serial_number
> FROM manifest_serial_number_history)
>
> Takes over an hour to run. I rewrote it as such:
> SELECT *
> FROM sar.pax_header_suspense_err_temp t
> WHERE NOT EXISTS
> (SELECT 'X'
> FROM manifest_serial_number_history h
> WHERE
> t.manifest_type = h.manifest_type and
> t.manifesting_station = h.manifesting_station and
> t.fiscal_year = h.fiscal_year and
> t.manifest_serial_number = h.manifest_serial_number )
>
> Under a second.
>
> Jerry Whittle
> ACIFICS DBA
> NCI Information Systems Inc.
> [EMAIL PROTECTED]
> 618-622-4145
>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<TITLE>RE: CONSISTANT GETS</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/rtf format -->
<P><SPAN LANG="en-us"><FONT COLOR="#0000FF" FACE="Arial">Jared,</FONT></SPAN>
</P>
<P><SPAN LANG="en-us"><FONT COLOR="#0000FF" FACE="Arial">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 that way
because it's simpler and nicer looking!</FONT></SPAN></P>
<P><SPAN LANG="en-us"><FONT COLOR="#0000FF" FACE="Arial">Still I've seen some
nice speed gains when just converting some WHERE statements from NOT IN to NOT
EXISTS.</FONT></SPAN>
</P>
<P><SPAN LANG="en-us"><FONT COLOR="#0000FF" FACE="Arial">YMMV</FONT></SPAN>
</P>
<P><SPAN LANG="en-us"><FONT FACE="Arial">Jerry Whittle</FONT></SPAN>
<BR><SPAN LANG="en-us"><FONT FACE="Arial">ACIFICS DBA</FONT></SPAN>
<BR><SPAN LANG="en-us"><FONT FACE="Arial">NCI Information Systems
Inc.</FONT></SPAN>
<BR><SPAN LANG="en-us"><FONT
FACE="Arial">[EMAIL PROTECTED]</FONT></SPAN>
<BR><SPAN LANG="en-us"><FONT FACE="Arial">618-622-4145</FONT></SPAN>
</P>
<UL>
<P><SPAN LANG="en-us"><FONT SIZE=1 FACE="Arial">-----Original
Message-----</FONT></SPAN>
<BR><SPAN LANG="en-us"><B><FONT SIZE=1 FACE="Arial">From: </FONT></B>
<FONT SIZE=1 FACE="Arial">[EMAIL PROTECTED]
[SMTP:[EMAIL PROTECTED]]</FONT></SPAN>
</P>
<P><SPAN LANG="en-us"><FONT SIZE=2 FACE="Arial">Jerry,</FONT></SPAN>
</P>
<P><SPAN LANG="en-us"><FONT SIZE=2 FACE="Arial">I suspect that the improvments
are more likely due to your</FONT></SPAN>
<BR><SPAN LANG="en-us"><FONT SIZE=2 FACE="Arial">rewriting the WHERE clause
rather than the use of NOT EXISTS.</FONT></SPAN>
</P>
<P><SPAN LANG="en-us"><FONT SIZE=2 FACE="Arial">Especially if the database were
9i, where NOT IN actually</FONT></SPAN>
<BR><SPAN LANG="en-us"><FONT SIZE=2 FACE="Arial">seems get a better execution
path than NOT EXISTS.</FONT></SPAN>
</P>
<P><SPAN LANG="en-us"><FONT SIZE=2 FACE="Arial">That original WHERE clause is
really a piece of work.</FONT></SPAN>
</P>
<P><SPAN LANG="en-us"><FONT SIZE=2 FACE="Arial">Jared</FONT></SPAN>
</P>
<P><SPAN LANG="en-us"><FONT SIZE=2 FACE="Arial">"Whittle Jerome Contr
NCI" <[EMAIL PROTECTED]></FONT></SPAN>
</P>
<P><SPAN LANG="en-us"><FONT SIZE=2 FACE="Arial">I've seen worse. My programmers
don't know how to use NOT EXISTS even </FONT></SPAN>
<BR><SPAN LANG="en-us"><FONT SIZE=2 FACE="Arial">though I've explained it many
times. And that's the least of my problems. </FONT></SPAN>
<BR><SPAN LANG="en-us"><FONT SIZE=2 FACE="Arial">Look at this
mess:</FONT></SPAN>
<BR><SPAN LANG="en-us"><FONT SIZE=2 FACE="Arial"> SELECT *
</FONT></SPAN>
<BR><SPAN LANG="en-us"><FONT SIZE=2 FACE="Arial"> FROM
sar.pax_header_suspense_err_temp </FONT></SPAN>
<BR><SPAN LANG="en-us"><FONT SIZE=2 FACE="Arial">
WHERE manifest_type </FONT></SPAN>
<BR><SPAN LANG="en-us"><FONT SIZE=2
FACE="Arial"> ||
manifesting_station </FONT></SPAN>
<BR><SPAN LANG="en-us"><FONT SIZE=2
FACE="Arial"> ||
fiscal_year </FONT></SPAN>
<BR><SPAN LANG="en-us"><FONT SIZE=2
FACE="Arial"> ||
manifest_serial_number NOT IN ( </FONT></SPAN>
<BR><SPAN LANG="en-us"><FONT SIZE=2
FACE="Arial"> &
nbsp; SELECT manifest_type </FONT></SPAN>
<BR><SPAN LANG="en-us"><FONT SIZE=2
FACE="Arial"> &
nbsp; || manifesting_station
</FONT></SPAN>
<BR><SPAN LANG="en-us"><FONT SIZE=2
FACE="Arial"> &
nbsp; || fiscal_year </FONT></SPAN>
<BR><SPAN LANG="en-us"><FONT SIZE=2
FACE="Arial"> &
nbsp; || manifest_serial_number
</FONT></SPAN>
<BR><SPAN LANG="en-us"><FONT SIZE=2
FACE="Arial"> &
nbsp; FROM manifest_serial_number_history)</FONT> </SPAN>
</P>
<P><SPAN LANG="en-us"><FONT SIZE=2 FACE="Arial">Takes over an hour to run. I
rewrote it as such: </FONT></SPAN>
<BR><SPAN LANG="en-us"><FONT SIZE=2 FACE="Arial">SELECT * </FONT></SPAN>
<BR><SPAN LANG="en-us"><FONT SIZE=2 FACE="Arial"> FROM
sar.pax_header_suspense_err_temp t </FONT></SPAN>
<BR><SPAN LANG="en-us"><FONT SIZE=2 FACE="Arial"> WHERE NOT EXISTS
</FONT></SPAN>
<BR><SPAN LANG="en-us"><FONT SIZE=2 FACE="Arial">(SELECT 'X' </FONT></SPAN>
<BR><SPAN LANG="en-us"><FONT SIZE=2 FACE="Arial"> FROM
manifest_serial_number_history h </FONT></SPAN>
<BR><SPAN LANG="en-us"><FONT SIZE=2 FACE="Arial"> WHERE </FONT></SPAN>
<BR><SPAN LANG="en-us"><FONT SIZE=2 FACE="Arial"> t.manifest_type =
h.manifest_type and </FONT></SPAN>
<BR><SPAN LANG="en-us"><FONT SIZE=2 FACE="Arial"> t.manifesting_station =
h.manifesting_station and </FONT></SPAN>
<BR><SPAN LANG="en-us"><FONT SIZE=2 FACE="Arial"> t.fiscal_year =
h.fiscal_year and </FONT></SPAN>
<BR><SPAN LANG="en-us"><FONT SIZE=2
FACE="Arial"> t.manifest_serial_number =
h.manifest_serial_number )</FONT> </SPAN>
</P>
<P><SPAN LANG="en-us"><FONT SIZE=2 FACE="Arial">Under a second.</FONT> </SPAN>
</P>
<P><SPAN LANG="en-us"><FONT SIZE=2 FACE="Arial">Jerry Whittle </FONT></SPAN>
<BR><SPAN LANG="en-us"><FONT SIZE=2 FACE="Arial">ACIFICS DBA </FONT></SPAN>
<BR><SPAN LANG="en-us"><FONT SIZE=2 FACE="Arial">NCI Information Systems Inc.
</FONT></SPAN>
<BR><SPAN LANG="en-us"><FONT SIZE=2 FACE="Arial">[EMAIL PROTECTED]
</FONT></SPAN>
<BR><SPAN LANG="en-us"><FONT SIZE=2 FACE="Arial">618-622-4145 </FONT></SPAN>
</P>
</UL>
</BODY>
</HTML>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
