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

My bad ... all SW_ID fields are DECIMAL.

"Michel Esber" <michel@us.automatos.com> wrote in message
news:1148394691.127225.28810@38g2000cwa.googlegrou ps.com...[color=blue]
> My bad ... all SW_ID fields are DECIMAL.
>[/color]
Are the two SW_ID fields the same precision? In other words are they both
DECIMAL(8) or is one DECIMAL(8) while the other is DECIMAL(11,3)? I don't
remember if a difference in precision or scale will prevent an index being
used for this join but your odds of the join using the index are definitely
better if both DECIMAL fields have the exact same precision and scale.

Also, is the SQL you put in your previous post being executed from the
command line or from within a program? If it is in a program and you don't
rebind the package after running doing your REORG/RUNSTATS, you won't see
the access plan improve. In other words, if the code is in a program rebind
the package after you complete the REORG/RUNSTATS so that DB2 can
re-evaluated the access paths in the light of the new information. If the
SQL is being executed from the command line, the package shouldn't be a
factor so you won't need to rebind anything.

--
Rhino

Both tables have DECIMAL (8,0), so there should be no reason for DB2
not to use the index because of decimal data types. At least I don´t
see why any limitation like this should exist.

I am running the SQL inside a command line. As you said, rebinding is
not an issue here.

Thanks for the help.

there is a temporary fix

ALTER TABLE T_SW_ID
VOLATILE

I tried that solution, and unfortunately it did not work for my case.
There was no difference in the access plan.

I read the docs regarding this VOLATILE CARDINALITY, and it seems
interesting. In fact, my table has +- 20k and will hardly change and
doesn´t seem to apply to the document description.

Thanks for the help.

I'm not sure if this will fix your problem or not. But we had similar
situation
a while back. "RUNSTATS .. on key columns and indexes all" fixed our
problem.

Michel Esber wrote:[color=blue]
> 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:
>
> [...]
>
> Any ideas on how to avoid the table scan and force DB2 to use the
> existing index?[/color]

Are you actually having a performance issue with this query?

I presume that the optimizer is calculating that the performing the
table scan (with hash join) is more efficient because it takes fewer
I/Os (105) than it would if it had to access both the index and
then fetch the row from the table (>138 I/Os) if it was doing a nested
loop join.




Ian

I have executed a few event monitor and saw that my applications needs
some changes. Even though the access plan does not use an index for one
of the tables, the query itself returns in a reasonable time.

Ian: how do you actually calculate that accessing both the index and
fetch rows would take 138+ I/Os ? I don´t see that in the access plan
....

Thanks for all the input guys.