By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,786 Members | 2,241 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,786 IT Pros & Developers. It's quick & easy.

Query Performance / Table Scan.

P: n/a
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

May 23 '06 #1
Share this Question
Share on Google+
8 Replies


P: n/a
My bad ... all SW_ID fields are DECIMAL.

May 23 '06 #2

P: n/a

"Michel Esber" <mi****@us.automatos.com> wrote in message
news:11*********************@38g2000cwa.googlegrou ps.com...
My bad ... all SW_ID fields are DECIMAL.

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
May 23 '06 #3

P: n/a
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.

May 23 '06 #4

P: n/a
there is a temporary fix

ALTER TABLE T_SW_ID
VOLATILE

May 23 '06 #5

P: n/a
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.

May 23 '06 #6

P: n/a
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.

May 23 '06 #7

P: n/a
Ian
Michel Esber wrote:
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?


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
May 24 '06 #8

P: n/a
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.

May 24 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.