table or index scan ?

db2girl · #1 (**permalink**) 01-19-11, 22:20

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 ?

nvk@vhv · #2 (**permalink**) 01-20-11, 03:10

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?

dav1mo · #3 (**permalink**) 01-20-11, 07:20

more than likely it will do a tablespace scan, unless it is rather small. Have you explained the query on your system?
Dave

stolze · #4 (**permalink**) 01-20-11, 08:12

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.

bharat.vijay · #5 (**permalink**) 01-20-11, 09:12

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

Marcus_A · #6 (**permalink**) 01-20-11, 13:52

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

tonkuma · #7 (**permalink**) 01-20-11, 16:20

Stolze wrote:

Quote:

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

dr_te_z · #8 (**permalink**) 01-21-11, 02:49

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

Mikky · #9 (**permalink**) 01-21-11, 05:30

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

Marcus_A · #10 (**permalink**) 01-21-11, 05:58

Quote:

Originally Posted by Mikky

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech