Results 1 to 8 of 8

Thread: db2 index scan

  1. #1
    Join Date
    Nov 2011
    Posts
    87

    Unanswered: db2 index scan

    hi,

    DB2 v9.7 / win 2008 R2

    i have taken the access plan for a small query like

    Select id, time_stamp,name from customer where id='XYZ'

    I can see it shows INDEX SCAN on customer table.

    The customer table has cluster index on column ID and up-t-odate runstats.
    It contains around 20 million rows.


    I am expecting to see a SEEK rather than INDEX SCAN.

    Is SEEK available in DB2 ? Is anythink better than INDEX SCAN in DB2?

    please advise.
    Thanks.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    What is "SEEK" and why do you think it's preferable to "IXSCAN"?

    http://pic.dhe.ibm.com/infocenter/db.../r0052023.html
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Nov 2011
    Posts
    87
    SEEK is used in MS SQL which performs direct index access.

    Anyway forget the SEEK...
    what i am thinking is , the CUSTOMER table has clustered index.

    So, the INDEX SCAN means, almost it performs TABLE SCAN as the index data is in same order as TABLE data.

    This is really confusing...can you explain more pls.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by db2mtrk View Post
    So, the INDEX SCAN means, almost it performs TABLE SCAN as the index data is in same order as TABLE data.
    I don't think that is what it means. If you post the explain output, then we can verify that.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Quote Originally Posted by db2mtrk View Post
    So, the INDEX SCAN means, almost it performs TABLE SCAN as the index data is in same order as TABLE data.
    I am quite new to DB2 and I had the same (wrong) impression. If you have an Oracle or MSSQL background it's quite confusing, I can admint. For example, in Oracle we might have:
    - Index unique scan
    - Index range scan
    - Index full scan
    Well, in DB2 there is a single operator - IXSCAN - for all of them. That's why you need to verify the explain output.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  6. #6
    Join Date
    Nov 2011
    Posts
    334
    In db2 , if you found a ixscan operator ,then the descirbe section of the plan will tell you db2
    how to scan the indx ,
    if you see
    Code:
                    Predicates:
                    ----------
                    9) Start Key Predicate,
                            Comparison Operator:            Equal (=)
                            Subquery Input Required:        No
                            Filter Factor:                  6.1903e-07
    
                            Predicate Text:
                            --------------
                          
    
    
                    9) Stop Key Predicate,
                            Comparison Operator:            Equal (=)
                            Subquery Input Required:        No
                            Filter Factor:                  6.1903e-07
    
                            Predicate Text:
                            --------------
    that is what you means Seek ( in db2 it is called "index lookup" and i guess it is the index uniq scann and index range scan in oralce )

    if you see
    Code:
    15) Sargable Predicate,
                            Comparison Operator:            Less Than or Equal (<=)
                            Subquery Input Required:        No
                            Filter Factor:                  0.333333
    
                            Predicate Text:
                            --------------
    That means db2 need to scan the whole index leaf ( it is called index screen in db2 and
    i guess it is "index full scan" in oracle )

    And in db2 v10 there is a new index scan method which is called index jump scan .
    Last edited by fengsun2; 08-08-12 at 03:36.

  7. #7
    Join Date
    Nov 2011
    Posts
    87

    Thanks for the explanation

    Hi,
    Thanks a lot for the detail explanation.
    I will take the explain output and post it on Monday.

    Thanks.

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by aflorin27 View Post
    For example, in Oracle we might have:
    - Index unique scan
    - Index range scan
    - Index full scan
    Well, in DB2 there is a single operator - IXSCAN - for all of them.
    In DB2 z/OS there is:
    - Matching index scan (uses b-tree)
    - Non-Matching index scan (ignores b-tree and always scans every index entry)

    I have no idea why DB2 for LUW never adopted the same terminology, which would save a lot of confusion.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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