Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367

    Unanswered: table or index scan ?

    Query:
    select .....
    from A, B
    where A.C1 and B.C1
    and A.C2 = 'something' and A.C3 = 'something'


    Index on table A:
    create index I1 on A (C1) include (C2, C3)



    Will DB2 do a table scan on A or can it use index I1 ?

  2. #2
    Join Date
    Jan 2010
    Posts
    335
    Hi,

    that's a good Question!
    I don't know the answer and would have to test it by myself. It also may depend on the size of the Table (# Rows and # Clolumns). Did you already tried it?

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    more than likely it will do a tablespace scan, unless it is rather small. Have you explained the query on your system?
    Dave

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    It really depends. If table A has lots of columns and the SELECT list does not access any other columns than A.C1, A.C2, and A.C3, it may be a chance that DB2 is using an index leaf scan instead of a table scan. The table scan can be more expensive because more data has to be read into memory compared to the index leaf scan. All this is cost-based, so the answer will be given by explaining the query as Dave mentioned.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Jan 2011
    Location
    Bengaluru, India
    Posts
    4

    Index scan or Table scan

    It probably also depends on the size of B. If B is very small, the optimizer should be able to get A's data from its index.

    (Am presuming that the first part of the where clause is A.C1 = B.C1)


    Bharat
    DB R US
    www.boltell.com
    Last edited by bharat.vijay; 01-20-11 at 10:13. Reason: forgot contact info.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    It can only do one of the following:

    • Index leaf scan of all index rows (not using the B-Tree of the index)
    • Table Scan of all rows in the table


    In either case, you might want to create an index with (C2, C3) or (C3, C2).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Stolze wrote:
    It really depends. If table A has lots of columns and the SELECT list does not access any other columns than A.C1, A.C2, and A.C3, it may be a chance that DB2 is using an index leaf scan instead of a table scan.
    ...
    All this is cost-based, so the answer will be given by explaining the query as Dave mentioned.
    Adding more,
    if constraints exists, DB2 may optimize more.

    For example, by adding FOREIGN KEY on table A(and the SELECT list does not access any other columns than A.C1, A.C2, and A.C3),
    DB2 can remove access to table B, even if table B was joined.

    Here is an example:

    1) Create test tables.
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE bello4ka.a
    ( c1 INTEGER NOT NULL
    , c2 CHAR(3) NOT NULL
    , c3 VARCHAR(10)
    , c4 INTEGER
    );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    CREATE UNIQUE INDEX bello4ka.I1
     ON bello4ka.a (c1)
     INCLUDE (c2, c3);
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE bello4ka.b
    ( c1 INTEGER NOT NULL
    , c2 INTEGER NOT NULL
    , c3 INTEGER
    );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    CREATE UNIQUE INDEX bello4ka.b_c1
     ON bello4ka.b (c1)
    ;
    
    ALTER TABLE bello4ka.b
    ADD PRIMARY KEY (c1)
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    SQL0598W  Existing index "BELLO4KA.B_C1" is used as the index for the primary 
    key or a unique key.  SQLSTATE=01550
    2) Add foreign key.
    Code:
    ------------------------------ Commands Entered ------------------------------
    ALTER TABLE bello4ka.a
    ADD CONSTRAINT a_ref_b
        FOREIGN KEY (c1)
        REFERENCES  bello4ka.b;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    3) Query and access path.
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT a.c1 , a.c2 , a.c3
     FROM  bello4ka.a AS a
         , bello4ka.b AS b
     WHERE a.c1 = b.c1
       AND a.c2 = 'A01'
       AND a.c3 = 'abcde';
    ------------------------------------------------------------------------------
    
    C1          C2  C3        
    ----------- --- ----------
    
      0 record(s) selected.
    
    
    Statement:
      
      SELECT a.c1 , a.c2 , a.c3 
      FROM bello4ka.a AS a , bello4ka.b AS b 
      WHERE a.c1 =b.c1 AND a.c2 ='A01' AND a.c3 ='abcde' 
    
    
    Optimizer Plan:
    
        Rows   
      Operator 
        (ID)   
        Cost   
               
         0     
        n/a    
      RETURN   
       ( 1)    
     0.0283109 
        |      
         0     
        n/a    
      IXSCAN   
       ( 2)    
     0.0283109 
        |     
        0     
     Index:   
     BELLO4KA 
     I1

  8. #8
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Yep. DB2 is not like the life as we humans experiance:
    the more contraints the easier the job.
    (my english teacher is not a DBA so will not be reading this)

    As a DBA I say: "yeah, that makes sence"
    As a normal human:"What ?!?"

    P.S. Am I implying that DBA's are not normal humans? ... guess I am

  9. #9
    Join Date
    Jan 2011
    Posts
    24
    Can you explain more about index leaf scan and how its different from B-Tree index scan? Does it scan all leaf pages? And whats the difference between a tablespace scan and a table scan?

    Mike

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Mikky View Post
    Can you explain more about index leaf scan and how its different from B-Tree index scan? Does it scan all leaf pages? And whats the difference between a tablespace scan and a table scan?
    B-Tree index access allows any entry (and qualifying RID's) to be found in a few I/O's by traversing the B-Tree (you can google "b-tree" to get a more detailed explanation). A leaf page scan basically ignores the B-Tree structure and reads each index row in the entire index, in a similar manner as would be required for a table scan (except that indexes "usually" are smaller than tables and have fewer total pages to scan).

    Tablespace scans occur for DB2 z/OS and table scans for DB2 Linux/UNIX/Windows. This is one reason why it is customary to have only one table per tablespace in DB2 z/OS.
    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
  •