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
