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=EPT, 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?
if index is on dept_no,emp_no and where is on dept_no=? the index is ok and should be used
check stats.. check opening table in explain - is this the only predicate?
nonmatching index scan : scan entire index to find the data pages
index-data pages are being read
what is the output of db2advis
Best Regards, Guy Przytula
Database Software Consultant
Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
Information Server Datastage Certified http://www.infocura.be
Please publish more concrete informations.
(1) DB2 version/release and platform OS(z/OS?).
(2) DDLs(CREATE TABLE statements and CREATE INDEX statements)
(3) Copy and paste the SQL statements which you executed, beter one and worse one.
(4) Ouput of explain.