Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2011
    Posts
    10

    Unanswered: Why show table scan

    Hi All,

    I'm tuning query but this query not use index

    ex.

    Code:
    SELECT CONSULTING.CONSULTING_ID
    , CUSTOMER.TSIUSERID
    FROM L_CONSULTING CONSULTING 
    LEFT OUTER JOIN L_CUSTOMER CUSTOMER 
    ON CONSULTING.CCCUSTID = CUSTOMER.CCCUSTID

    Index
    Code:
    CREATE INDEX IDX_L_CONSULTING_CCCUSTID
    ON L_CONSULTING(CCCUSTID);
    
    ALTER TABLE L_CUSTOMER
        ADD CONSTRAINT L_CUSTOMER_PK
    	PRIMARY KEY (CCCUSTID);
    I also attached explain plan.
    Attached Thumbnails Attached Thumbnails explain.png  

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    as there is no where clause - it has to open a table and this with a complete scan and use index for join
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Nov 2011
    Posts
    334
    The join method db2 chose is hsjoin ,which can not use indexes at all.

  4. #4
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Can you explain why hash join cannot use indexes?

  5. #5
    Join Date
    Mar 2003
    Posts
    280
    Are statistics updated? What happens to the plan if you:

    runstats on table <schema>.L_CONSULTING with distribution and detailed indexes all;
    runstats on table <schema>.L_CUSTOMER with distribution and detailed indexes all;

  6. #6
    Join Date
    Aug 2011
    Posts
    46
    Quote Originally Posted by db2girl View Post
    Can you explain why hash join cannot use indexes?
    That's the way hashing algorithm works.
    www.devx.com/assets/ibm/3145.pdf
    From the doc:
    "The inner table (called the build table) is scanned first, and the rows are copied into memory buffers. These buffers are divided into partitions based on a “hash code,” which is computed from the column(s) in the join predicate(s).
    Then the outer table (called the probe table) is scanned. For each row in the probe table the same hash algorithm is applied to the join column(s). If the hash code obtained matches the hash code of an build row, the
    actual join columns are compared."

    There are many things that may influence optimizer's decision to choose a particular join method. Take a look at above mentioned pdf, I find it quite informative.

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The scanning of the data could actually be an index scan if suitable. So an index would be used, although not for the actual join itself because the sorting of the index doesn't provide much benefits.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Nov 2011
    Posts
    334
    Quote Originally Posted by db2girl View Post
    Can you explain why hash join cannot use indexes?
    Because
    1、There is no local predicate ,so db2 have to scan all rows ......
    2、HSjoin need not to sort data,so it can't benifit from the sorted data of indexes.
    3、the INDEX IDX_L_CONSULTING_CCCUSTID and L_CUSTOMER_PK does not cover the selected column so db2 can not use index only access to do index hash join .......
    Last edited by fengsun2; 01-05-12 at 09:12.

Posting Permissions

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