Hi,
I am having the follwoing query :
/* Select * FROM S1, S2
WHERE S1.SOURCE_ID = S2.SOURCE_ID and
S1.PARTITION_FLAG = '0' AND S2.PARTITION_FLAG = '0' */
Both the tables are having indexs on source id and partition flag. But Db2 is only using index for partition flag (as seen in explain plan) and not for source_id which is joining field.I dont know how I tell Db2 to use that index also so that it will improve perf drastically . Currently there is full table scan on these two tables.

Any help would be great..really great.

Thanks
Kunal

First, try to do a runstants on the table (and use "with indexes all" option). Do "distribution on key columns".

If that does not work, alter the table to "volatile", but I cannot guarantee that it will run faster.

Hi Marcus,

Thanks for reply.
I have already tried Re org- runstats . But it still does not work. I am not getting why is it using only index? Isnt there some kind of hint I give to DB2 that please use the other index also.

Waiting for ans.

Thanks

So, each table has two indexes: on source_id and, separately, on partition_flag? Could you possibly post index definitions and the execution plan?

If DB2 needs to access at least one row on each page, then it is almost always faster to not use an index. A merge scan join (if that is what is being used) instead of a nested loop join is not necessarily a slower access plan given the query you have.

Hi
Thanks for reply.
I am need not proecssing each of the row but just 6.5 % of the total. i.e in the query /* Select * FROM STG_INCY_HIST S1, STG_INCY_HIST_EFCV_DT S2
WHERE S1.PARTITION_FLAG = '0'
AND S2.PARTITION_FLAG = '0' and S1.SOURCE_ID = S2.SOURCE_ID */
For the " PARTITION_FLAG = '0'" I have only 6.5 % of the total data.
The indexes are :-
1. S1.SOURCE_ID = S_INCY_HIST_SRC_ID
2. S2.SOURCE_ID = S_INCY_EF_SRC_ID
3. S1.PARTITION_FLAG = S_INCY_HIST_IND
4. S2.PARTITION_FLAG = S_INCY_HIST_EF_IND

I have attached the plan from which one can see that DB2 is using ONLY the source id index on S1 and ONLY partition flg index on S2.
This is stupidity coz to acess the rows DB2 is going for Table scan.
God knows why it is not using both the indexs?Without them it makes this query run for marathon!!!!

Plz help.
Thanks Kunal

I don't see anything wrong with the access plan, given your choice of indexes.

Try creating indexes on PARTITION_FLAG, SOURCE_ID (in that order) for both tables, you may have better luck. On top of that, you may also want to rewrite the WHERE clause: "WHERE S1.PARTITION_FLAG = '0' AND S2.PARTITION_FLAG = '0' AND S1.PARTITION_FLAG = S2.PARTITION_FLAG".

By the way, to obtain index definitions you would normally use DESCRIBE INDEXES FOR TABLE ... SHOW DETAIL.

the plan is using ixscan to access the base tables
sorting the second result - probably merge scan
the tbscan is from the result table after the ixscan-fetch-sort..

hi,

Thanks for the replies.
I have tried creating the composite index on PARTITION_FLAG, SOURCE_ID in this ordr and then manipulating the join condition. But it had worse impact as now it used index only for one table and other is full acessed. The performance worsen much more.

Guy- Correct me if I am wrong ..but I see only one type of join here and that is excecuted as Nested loop..Also initially looking closely at explain plan I see a table being IX scan only for attribute i.e S2 is IXscan on Partition flag but to get Src id it is going for Complete table scan which is bad.

I have given the index details and It has looks strange. They are very diff for two tables even though they holds almost very similer data.
(note:first is table S1 and second is table S2).

Thanks for replies . I am still waiting for help.)

Thnaks Kunal

Have you updated table statistics after creating the indexes?

That's nice, but it's kinda difficult to help when you show us some funny pictures instead of the index DDL scripts and db2exfmt output...

Hi
I did reorg and runstat after creating the new indexes. The index DDL is
************************************************** *******
-- Create Index RIDQADM.S_INCY_HIST_IND
--------------------------------------------------
create Index RIDQADM.S_INCY_HIST_IND
on RIDQADM.STG_INCY_HIST
(PARTITION_FLAG) Allow Reverse Scans;

--------------------------------------------------
-- Create Index RIDQADM.S_INCY_HIST_SRC_ID
--------------------------------------------------
create Index RIDQADM.S_INCY_HIST_SRC_ID
on RIDQADM.STG_INCY_HIST
(SOURCE_ID) Cluster Allow Reverse Scans;

AND

--------------------------------------------------
-- Create Index RIDQADM.S_INCY_EF_SRC_ID
--------------------------------------------------
create Index RIDQADM.S_INCY_EF_SRC_ID
on RIDQADM.STG_INCY_HIST_EFCV_DT
(SOURCE_ID) Cluster Allow Reverse Scans;

--------------------------------------------------
-- Create Index RIDQADM.S_INCY_HIST_EF_IND
--------------------------------------------------
create Index RIDQADM.S_INCY_HIST_EF_IND
on RIDQADM.STG_INCY_HIST_EFCV_DT
(PARTITION_FLAG) Allow Reverse Scans;
************************************************** ********

Let me know for anything else .

Kunal ..

Have you tried using the optimization hint?

Hi
I did run the SQL optimiser for DB2 which gave hints like coalesce etc . which did not work ...however I did not find any "HINTS" in the manner we obtain in ORACLE like /*+ format. In fact I am very eagrly looking for how to supply a hint which will tell DB2 to use Index .
Plz let me know if anyone knows.