Hello,
DB2 V8 FP 11 running on Linux.
Given two tables:
T_SW_ID (SW_ID INTEGER, SW_NAME VARCHAR);
T_SW (MACHINE_ID varchar, SW_ID DECIMAL (8), VERSION varchar,
Product_ID varchar)
PK for T_SW_ID is SW_ID. This table has 20k rows.
PK for T_SW has all the above fields. This table has 1.3M rows.
I am trying to run a simple statement:
select b.sw_name,a.sw_id,a.version,a.product_id,
from t_sw a, T_sw_id b
where a.sw_id=b.sw_id and machine_id='xyz'
However the actual execution plan shows a table scan for T_SW_ID, even
though there is an index on field SW_ID. I have just reorged the table
and updated statistics, but the plan won´t change. Here it is:
Access Plan:
-----------
Total Cost: 526.872
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
69.1982
HSJOIN
( 2)
526.872
108.224
/------+------\
19405 69.1982
TBSCAN FETCH
( 3) ( 4)
444.791 80.7132
105 3.22443
| /---+---\
19405 69.1982 944701
TABLE: ASSET IXSCAN TABLE: ASSET
TBL_ASSET_SW_ID ( 5) TBL_ASSET_SW
50.0724
2
|
944701
INDEX: ASSET
INDEX_MACHINE_ID
(...)
3) TBSCAN: (Table Scan)
Cumulative Total Cost: 444.791
Cumulative CPU Cost: 3.41142e+07
Cumulative I/O Cost: 105
Cumulative Re-Total Cost: 12.6275
Cumulative Re-CPU Cost: 3.34171e+07
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 25.0198
Estimated Bufferpool Buffers: 105
Arguments:
---------
JN INPUT: (Join input leg)
OUTER
MAXPAGES: (Maximum pages for prefetch)
ALL
PREFETCH: (Type of Prefetch)
SEQUENTIAL
ROWLOCK : (Row Lock intent)
NEXT KEY SHARE
SCANDIR : (Scan Direction)
FORWARD
TABLOCK : (Table Lock intent)
INTENT SHARE
TBISOLVL: (Table access Isolation Level)
CURSOR STABILITY
Input Streams:
-------------
1) From Object ASSET.TBL_ASSET_SW_ID
Estimated number of rows: 19405
Number of columns: 3
Subquery predicate ID: Not
Applicable
Column Names:
------------
+Q1.$RID$+Q1.SW_NAME+Q1.SW_ID
Output Streams:
--------------
2) To Operator #2
Estimated number of rows: 19405
Number of columns: 2
Subquery predicate ID: Not
Applicable
Column Names:
------------
+Q1.SW_NAME+Q1.SW_ID
Any ideas on how to avoid the table scan and force DB2 to use the
existing index?
Thanks in Advance,
-Michel