|
View previous topic :: View next topic
|
| Author |
Message |
sumit_tumba
New User
Joined: 13 Feb 2007
Posts: 4
Location: kolkata
|
|
|
|
My program has multiple sqls and creating performance issue recently.
The one select query creating the performance problem is -
In TABLE 1 -19000 records- when I checked in PLAN_TABLE it is actually using nonmatching index scan (access type = I, match col=0).
Other 2 tables are having -49000 records (much more than previous table) but using Table space scan (access type = R, prefetch = S) and the result is coming much faster.
My query is -
1) Hence tablespace scan is working much efficiently here than nonmatching index scan?
2) What shall be done to execute the sql on Table 1 run faster (even in spufi its showing -905, and in job taking very long time).
Note: the sql on table1 is just a select query - where DEPT_NO=:DEPT, and index is created on table on column (DEPT_NO,EMP_NO). Should I create 1 more index on table 1 with only DEPT_NO in it? |
|
| Back to top |
|
 |
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK
|
|
|
|
| Did you ask your team-mates? Your DBA? |
|
| Back to top |
|
|
sumit_tumba
New User
Joined: 13 Feb 2007
Posts: 4
Location: kolkata
|
|
|
|
| Not yet, wanted to check what experts think on this forum. |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
| Quote: |
| Not yet, wanted to check what experts think on this forum. |
wrong answer 
first You have to carry on the <due diligence> using your organization resources
and after you might ask on a forum
asking on a forum first You make US to do the job for which Your support/peers are paid for. |
|
| Back to top |
|
|
sumit_tumba
New User
Joined: 13 Feb 2007
Posts: 4
Location: kolkata
|
|
|
|
| Can any expert shed some light? |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
| Back to top |
|
|
Furor
New User
Joined: 04 Jun 2009
Posts: 30
Location: Bangalore
|
|
|
|
| Quote: |
2) What shall be done to execute the sql on Table 1 run faster (even in spufi its showing -905, and in job taking very long time).
Note: the sql on table1 is just a select query - where DEPT_NO=:DEPT, and index is created on table on column (DEPT_NO,EMP_NO). Should I create 1 more index on table 1 with only DEPT_NO in it? |
You can try various approaches to deal with this
Try this as the first attempt-
Since match col=0 , the Index Scan is not helping.
You should try to force DB2 to avoid Index Scan -
Update your where clause from
| Code: |
| Where DEPT_NO=:DEPT |
TO
| Code: |
| Where DEPT_NO=:DEPT OR 0=1 |
Share your results with the forum |
|
| Back to top |
|
|
|
|
