Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    24

    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 [1] SQL statements from the input file
    Recommending indexes...
    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)
    [0.00%] improvement


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

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by James Frankman
    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
    Depending on the number of distinct values in A.locdiscrim,
    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
    __http://www.abis.be/

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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:
    Code:
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •