473,320 Members | 1,947 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Query Performance / Table Scan.

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
8 3051
My bad ... all SW_ID fields are DECIMAL.

May 23 '06 #2

"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
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
there is a temporary fix

ALTER TABLE T_SW_ID
VOLATILE

May 23 '06 #5
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Mahesh Hardikar | last post by:
Hi , Oracle 8.1.7.0.0 on HP-UX 11.0 I am a newbie as far as PL-SQL is concerned . I have Sybase/MSSQL T-SQL background. We have a report which uses a select statement . This select...
3
by: Paul Mateer | last post by:
Hi, I have been running some queries against a table in a my database and have noted an odd (at least it seems odd to me) performance issue. The table has approximately 5 million rows and...
2
by: Yonatan Goraly | last post by:
I have a query that uses the same view 6 times. It seems that the database engine is calculating the view each time. The result is very poor performance. The same query takes 2 sec with MS SQL,...
6
by: Steven D.Arnold | last post by:
I have a query which does not use column indexes that it should use. I have discovered some interesting behaviors of Postgres which may indicate a bug in the database's query planning. Take a...
8
by: Együd Csaba | last post by:
Hi All, how can I improve the query performance in the following situation: I have a big (4.5+ million rows) table. One query takes approx. 9 sec to finish resulting ~10000 rows. But if I run...
7
by: Bing Wu | last post by:
Hi Folks, I have a very large table containing 170 million rows of coordinats: CREATE TABLE "DB2ADMIN"."COORDINATE" ( "FID" INTEGER NOT NULL , "AID" INTEGER NOT NULL , "X" REAL NOT NULL ,...
1
by: Peter Alberer | last post by:
Hi there, i have a problem with a query that uses the result of a plsql function In the where clause: SELECT assignments.assignment_id, assignments.package_id AS package_id,...
10
by: varlagas | last post by:
I execute a query (against DB2 for iSeries), which, in its generic form is as follows. This query runs just fine, executing in a couple of seconds SELECT V.FIELD01, V.FIELD02, V.FIELD03,...
6
by: Hemant Shah | last post by:
Folks, I am having trouble with a query. DB2 does not use index, it does relation scan of the table. I am running DB2 UDB 8.2 on Fedora Core release 4 (Stentz) # db2level DB21085I ...
5
by: wugon.net | last post by:
question: db2 LUW V8 UNION ALL with table function month() have bad query performance Env: db2 LUW V8 + FP14 Problem : We have history data from 2005/01/01 ~ 2007/05/xx in single big...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.