Thread: Left outer join never ends.
03-23-08, 21:48 #1Registered User
- Join Date
- Feb 2004
Unanswered: Left outer join never ends.
I am using DB2 v9.1 Enterprise Edition and am having performance trouble with a simple left outer join. When I execute this statement the processor and Memory both hit 100% on the DB2 server and the result set is never returned after several minutes or even hours depending upon my frustration, I force the application to kill the query. I have checked the indexes, run statistics and even reogranized both tables. But still no dice. Here is the query:
select distinct A.locdiscrim, A.zip5, A.zip4, A.state, A.city, A.fulladdress, A.street, A.halfhousenumber, A.incareof, A.county
from fbnexus.location_intermediate A
left outer join fbnexus.fblocation B on A.locdiscrim=B.locdiscrim and A.zip5=B.zip5 and A.zip4=B.zip4 and A.state=B.state and A.city=B.city and A.fulladdress=B.fulladdress and A.street=B.street and A.halfhousenumber=B.halfhousenumber and A.incareof=B.incareof
where A.locdiscrim<>'T' and B.locdiscrim is null;
DB2Advis utility was not help at all:
execution started at timestamp 2008-03-23-16.44.05.712000
found  SQL statements from the input file
total disk space needed for initial set [ 0.000] MB
total disk space constrained to [ 762.436] MB
Trying variations of the solution set.
0 indexes in current solution
[190665.0000] timerons (without recommendations)
[190665.0000] timerons (with current solution)
-- LIST OF RECOMMENDED INDEXES
-- no indexes are recommended for this workload.
-- RECOMMENDED EXISTING INDEXES
-- RUNSTATS ON TABLE "FBNEXUS "."FBLOCATION" FOR INDEX "FBNEXUS "."IDX_FBLOCATION_LOCDISCRIM_ZIP5_ZIP4_STATE_CITY_ FULLADDRESS_STREET_HALFHOUSE_INCAREOF" ;
-- COMMIT WORK ;
-- UNUSED EXISTING INDEXES
-- DROP INDEX "FBNEXUS "."IDX_FBLOCATION_TOWNSHIPS";
-- DROP INDEX "FBNEXUS "."IDX_LOCATION_INTERMEDIATE_LOCDISCRIM_ZIP5_ZIP4_ STATE_CITY_STREET_HALFHOUSE_FULLADDRESS_INCAREOF";
-- DROP INDEX "FBNEXUS "."IDX_FBLOCATION_LOCDISCRIM_ZIP5_ZIP4_STATE_CITY_ STREET_HALFHOUSE_FULLADDRESS_INCAREOF";
-- DROP INDEX "FBNEXUS "."idx_LOCATION_INTERMEDIATE_OLIEDETAILKEY";
-- DROP INDEX "FBNEXUS "."IDX_LOCATION_INTERMEDIATE";
-- DROP INDEX "FBNEXUS "."IDX_FBLOCATION_ZIP5_ZIP4_STATE_CITY_STREET_HALF HOUSE_FULLADDRESS_INCAREOF";
-- DROP INDEX "FBNEXUS "."IDX_LOCATION_INTERMEDIATE_TOWNSHIP";
3 solutions were evaluated by the advisor
DB2 Workload Performance Advisor tool is finished.
I have looked at what I think are the obvious reasons for this behavior such as indexes, table reorgs, and the like but have not found any solution. If there something inheritly wrong with the above query, or is there something else I should be looking at?
FBLocation table has 452,000 rows. Location_intermdeate table has 712,000 thousand.
03-24-08, 06:32 #2Registered User
Originally Posted by James Frankman
- Join Date
- Sep 2004
if that is not too large, try replacing the condition "A.locdiscrim<>'T'" by "A.locdiscrim IN ('X', 'Y', 'Z')" (or whatever); that will make the condition indexable.
Then add indexes on at least A.locdiscrim and B.locdiscrim and see if that helps.
(Don't forget runstats, and also have a look at EXPLAIN output.)--_Peter Vanroose,
__IBM Certified Database Administrator, DB2 9 for z/OS
__IBM Certified Application Developer
__ABIS Training and Consulting
03-24-08, 10:15 #3:-)
Provided Answers: 1
- Join Date
- Jun 2003
- Toronto, Canada
Since your purpose seems to find all records in A that don't exist in B, try to eliminate the left join by using the EXCEPT operator:
select A.* from fbnexus.location_intermediate A EXCEPT select A.* from fbnexus.location_intermediate A inner join fbnexus.fblocation B...---
"It does not work" is not a valid problem statement.