Oracle-L: Re:RE: CONSISTANT GETS

From: <dgoulet_at_vicr.com>
Date: Fri, 15 Nov 2002 12:33:34 -0800
Message-ID: <F001.00504AB5.20021115123334@fatcity.com>

Jerry,

I'll take better performing over nicer looking anyday.

Dick Goulet

____________________Reply Separator____________________
Author: "Whittle Jerome Contr NCI" <Jerome.Whittle_at_scott.af.mil>
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.
jerome.whittle_at_scott.af.mil
618-622-4145

> -----Original Message-----
> From: Jared.Still_at_radisys.com [SMTP:Jared.Still_at_radisys.com]
>
> 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" <Jerome.Whittle_at_scott.af.mil>
>
> 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.
> jerome.whittle_at_scott.af.mil
> 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 -->

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.

jerome.whittle_at_scott.af.mil

<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-----

From:   Jared.Still_at_radisys.com [SMTP:Jared.Still_at_radisys.com]

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" <Jerome.Whittle_at_scott.af.mil>

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 (

& nbsp; SELECT    manifest_type

& nbsp;        || manifesting_station

& nbsp;        || fiscal_year

& nbsp;        || manifest_serial_number

& nbsp;   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.

jerome.whittle_at_scott.af.mil

<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: dgoulet_at_vicr.com

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: ListGuru_at_fatcity.com (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).

Received on Fri Nov 15 2002 - 14:33:34 CST