Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2013
    Posts
    1

    Unanswered: Non-matching index scan

    My program has multiple sqls and creating performance issue recently.
    The one select query creating the performance problem is -
    In TABLE 1 -19000 records- when I checked in PLAN_TABLE it is actually using nonmatching index scan (access type = I, match col=0).

    Other 2 tables are having -49000 records (much more than previous table) but using Table space scan (access type = R, prefetch = S) and the result is coming much faster.

    My query is -
    1) Hence tablespace scan is working much efficiently here than nonmatching index scan?
    2) What shall be done to execute the sql on Table 1 run faster (even in spufi its showing -905, and in job taking very long time).
    Note: the sql on table1 is just a select query - where DEPT_NO=EPT, and index is created on table on column (DEPT_NO,EMP_NO). Should I create 1 more index on table 1 with only DEPT_NO in it?

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    if index is on dept_no,emp_no and where is on dept_no=? the index is ok and should be used
    check stats.. check opening table in explain - is this the only predicate?
    nonmatching index scan : scan entire index to find the data pages
    index-data pages are being read
    what is the output of db2advis
    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
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please publish more concrete informations.
    For example:
    (1) DB2 version/release and platform OS(z/OS?).
    (2) DDLs(CREATE TABLE statements and CREATE INDEX statements)
    (3) Copy and paste the SQL statements which you executed, beter one and worse one.
    (4) Ouput of explain.
    so on...

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Other 2 tables are having -49000 records (much more than previous table) but using Table space scan and the result is coming much faster.
    Hence tablespace scan is working much efficiently here than nonmatching index scan?
    You are comparing different tables and (possibly) different queries. So, not a valid comparison to come to any conclusion about the efficiency of the two types of scans.

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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