I've tried and can't get it any significantly faster.
I've tried with a composite index on first_name,last_name,street1 and birth_dt, which prevents a scan of the first subquery, and I've tried without it. Either way it's about the same.
I've tried using hash joins and without. About the same. Maybe a little quicker with hash.
Looks like they are searching for duplicates in the table, joining on itself.
Is there any ideas from those really good at sql ? Any obvious flaws ?
The query used to work ok when they first made it, but some of these semi-permanent submission tables get big, and the query takes 3-4 minutes.

Any help would be appreciated.

Thank you !
Floyd

Here is the explain plan.

QUERY:
------
SELECT {+USE_HASH (s701362/build)} t1.token AS __token,
t1.first_name AS firstName,
t1.initial as middleInitial,
t1.last_name as lastName,
substr(t1.street1,1,8) as streetAddress,
t1.birth_dt as dateOfBirth
FROM s701362 AS t1
WHERE 1 = (SELECT {+USE_HASH (s701362/build)} Count(*) FROM s701362 AS t2 WHERE NVL(UPPER(t1.first_name),'') =
NVL(UPPER(t2.first_name),'')

AND NVL(UPPER(t1.last_name),'') = NVL(UPPER(t2.last_name),'')
AND NVL(UPPER(t1.initial),'')= NVL(UPPER(t1.initial),'')
AND NVL(UPPER(substr(t1.street1,1,8)),'')=NVL(UPPER(su bstr(t2.street1,1,8)),'')
AND NVL(t1.birth_dt,'')=NVL(t2.birth_dt,''))
AND t1.token in (select __token token from s701362_er t1 where t1.warn_id='86')
--AND t1.token in (select __token token from s701362_er where warn_id='86')


DIRECTIVES FOLLOWED:
USE_HASH ( s701362/BUILD )
DIRECTIVES NOT FOLLOWED:

Estimated Cost: 2147483647
Estimated # of Rows Returned: 5856

1) root.t1: INDEX PATH

Filters: >;subquery;subquery

On 21/05/2010 11:02, Floyd Wellershaus wrote:
Hi,

Check out the cost :

2147483647 = 0x7FFFFFFF => Sign of an optimizer overflow :/

Version and O/S?

What statistics on the table(s)?

Never heard of an optimizer overflow. This is IDS10.0FC5 on Aix 5.3.
an update stats high on the table was just done.

Thanks,
floyd




----- Original Message -----
From: bigpotato (AT) usenet-news (DOT) net
Sent: Fri, May 21, 2010, 7:00 AM
Subject: Re: help tuning query

On 21/05/2010 11:02, Floyd Wellershaus wrote:

Excellent point Obnoxio.
I did this. It runs much qucker ( like only 28 seconds as opposed to 7 minutes ) because it's doing a dynamic hash join
It seems to provide the same info, however in a different order. Not sure if it's really the same query anymore.

SELECT
t1.token as __token,
t1.first_name AS firstName,
t1.initial as middleInitial,
t1.last_name as lastName,
substr(t1.street1,1,8) as streetAddress,
t1.birth_dt as dateOfBirth

FROM s701362 t1,s701362 t2 WHERE NVL(UPPER(t1.first_name),'') = NVL(UPPER(t2.first_name),'')

AND NVL(UPPER(t1.last_name),'') = NVL(UPPER(t2.last_name),'')
AND NVL(UPPER(t1.initial),'')= NVL(UPPER(t1.initial),'')
AND NVL(UPPER(substr(t1.street1,1,8)),'')=NVL(UPPER(su bstr(t2.street1,1,8)),'')
AND NVL(t1.birth_dt,'')=NVL(t2.birth_dt,'')
AND t1.token in (select __token token from s701362_er t1 where t1.warn_id='86')





----- Original Message -----
From: obnoxio (AT) serendipita (DOT) com
Sent: Fri, May 21, 2010, 7:00 AM
Subject: Re: help tuning query

Floyd Wellershaus wrote: