Re: help tuning query

Floyd Wellershaus wrote: > 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 How fast are the queries when you run them separately? > 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(substr(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> = 1 > > (1) Index Keys: token (Serial, fragments: ALL) > Lower Index Filter: root.t1.token = ANY <subquery> > > Subquery: > --------- > DIRECTIVES FOLLOWED: > USE_HASH ( s701362/BUILD ) > DIRECTIVES NOT FOLLOWED:> > Estimated Cost: 110480 > Estimated # of Rows Returned: 1 > > 1) root.t2: SEQUENTIAL SCAN > > Filters: ((((NVL (root.t2.birth_dt , '' ) = NVL > (root.t1.birth_dt , '' ) AND NVL (UPPER(SUBSTR (root.t2 > .street1 , 1 , 8 ) ) , '' ) = NVL (UPPER(SUBSTR (root.t1.street1 , 1 , 8 > ) ) , '' ) ) AND NVL (UPPER(root.t2.last_n > ame ) , '' ) = NVL (UPPER(root.t1.last_name ) , '' ) ) AND NVL > (UPPER(root.t2.first_name ) , '' ) = NVL (UPPER(root > .t1.first_name ) , '' ) ) AND NVL (UPPER(root.t1.initial ) , '' ) = NVL > (UPPER(root.t1.initial ) , '' ) ) > > > Subquery: > --------- > Estimated Cost: 187994 > Estimated # of Rows Returned: 556267 > > > > 1) root.t1: INDEX PATH > > (1) Index Keys: __token warn_id (Key-Only) (Serial, > fragments: ALL) > Index Key Filters: (root.t1.warn_id = 86 ) -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.