DB2 MERGE STATEMENT ARE using table scan

#1

I have a merge statement run pretty slow, when checking explain. It
simply scan both tables.
Source table and Target table are partitioned by different columns,
both indexed.
This is on db2 v9.1
merge tgt
using src
on tgt.primarykey = src.key
when matched
when not matched.

I am expecting it scan only the src table.
Any idea?

#2

On Mar 6, 11:44*pm, Anwei Shen <shenan... (AT) gmail (DOT) com> wrote:

Quote:

I have a merge statement run pretty slow, when checking explain. It
simply scan both tables.
Source table and Target table are partitioned by different columns,
both indexed.
This is on db2 v9.1
merge tgt
using src
on tgt.primarykey = src.key
when matched
when not matched.

I am expecting it scan only the src table.
Any idea?

Even I create another table like Target table using the same partition
key, merge will do 2 table scan.

#3

On Mar 8, 5:26*am, Anwei Shen <shenan... (AT) gmail (DOT) com> wrote:

Quote:

On Mar 6, 11:44*pm, Anwei Shen <shenan... (AT) gmail (DOT) com> wrote:

I have a merge statement run pretty slow, when checking explain. It
simply scan both tables.
Source table and Target table are partitioned by different columns,
both indexed.
This is on db2 v9.1
merge tgt
using src
on tgt.primarykey = src.key
when matched
when not matched.

I am expecting it scan only the src table.
Any idea?

Even I create another table like Target table using the same partition
key, merge will do 2 table scan.

Hello Anwei,

Could you provide some DDL that would allow us to recreate your
situation? There are many factors involved in the optimizer's decision
to use a table scan. The information you're giving is too vague to
comment on.

--
Frederik Engelen

#4

On 3/7/2011 8:26 PM, Anwei Shen wrote:

Quote:

On Mar 6, 11:44 pm, Anwei Shen<shenan... (AT) gmail (DOT) com> wrote:
I have a merge statement run pretty slow, when checking explain. It
simply scan both tables.
Source table and Target table are partitioned by different columns,
both indexed.
This is on db2 v9.1
merge tgt
using src
on tgt.primarykey = src.key
when matched
when not matched.

I am expecting it scan only the src table.
Any idea?

Even I create another table like Target table using the same partition
key, merge will do 2 table scan.
Drop the optimization level to 3. That disables hashjoin.

Do you have stats on the source table? How big is the source table
compared to the target table?

--
Serge Rielau
SQL Architect DB2 for LUW, IBM Toronto Lab
Blog: tinyurl.com/SQLTips4DB2
Wiki: tinyurl.com/Oracle2DB2Wiki
Twitter: srielau

#5

On Mar 8, 5:06*pm, Serge Rielau <srie... (AT) ca (DOT) ibm.com> wrote:

Quote:

On 3/7/2011 8:26 PM, Anwei Shen wrote:

On Mar 6, 11:44 pm, Anwei Shen<shenan... (AT) gmail (DOT) com> *wrote:
I have a merge statement run pretty slow, when checking explain. It
simply scan both tables.
Source table and Target table are partitioned by different columns,
both indexed.
This is on db2 v9.1
merge tgt
using src
on tgt.primarykey = src.key
when matched
when not matched.

I am expecting it scan only the src table.
Any idea?

Even I create another table like Target table using the same partition
key, merge will do 2 table scan.

Drop the optimization level to 3. That disables hashjoin.
Do you have stats on the source table? How big is the source table
compared to the target table?

--
Serge Rielau
SQL Architect DB2 for LUW, IBM Toronto Lab
Blog: * *tinyurl.com/SQLTips4DB2
Wiki: * *tinyurl.com/Oracle2DB2Wiki
Twitter: srielau- Hide quoted text -

- Show quoted text -

"The information you're giving is too vague to comment on."

Ok, yes, well, euhm, Serge can, of course...

--
Frederik

#6

"Serge Rielau" <srielau (AT) ca (DOT) ibm.com> wrote

Quote:

Drop the optimization level to 3. That disables hashjoin.
Do you have stats on the source table? How big is the source table
compared to the target table?
--
Serge Rielau
SQL Architect DB2 for LUW, IBM Toronto Lab
Blog: tinyurl.com/SQLTips4DB2
Wiki: tinyurl.com/Oracle2DB2Wiki
Twitter: srielau

Any chance of setting optimization level at the statement level (sort of
like can be done with isolation level)?

#7

Quote:

Any chance of setting optimization level at the statement level (sort of
like can be done with isolation level)?

db2 set current query optimization 3
<run your statement here>
db2 set current query optimization <old value>

--
Helmut K. C. Tessarek
DB2 Performance and Development
IBM Toronto Lab

#8

Quote:

Any chance of setting optimization level at the statement level (sort of
like can be done with isolation level)?

You can't set the optimization level at statement level (within the SQL
statement), but you can use the following special register:

set current query optimization = 3
<run your statement here>
set current query optimization = <old value>

--
Helmut K. C. Tessarek
DB2 Performance and Development
IBM Toronto Lab

#9

"Helmut Tessarek" <tessarek (AT) evermeet (DOT) cx> wrote

Quote:

Any chance of setting optimization level at the statement level (sort of
like can be done with isolation level)?

You can't set the optimization level at statement level (within the SQL
statement), but you can use the following special register:

set current query optimization = 3
run your statement here
set current query optimization = <old value

--
Helmut K. C. Tessarek
DB2 Performance and Development
IBM Toronto Lab

Yes, I already know that. My question is whether there is any chance of IBM
making an enhancement to DB2 to have it set at the statement level.

Changing the current optimization is unacceptable in most cases.

#10

Mark,

http://publib.boulder.ibm.com/infoce...c/c0024649.htm

Cheers
Serge

PS: No this is not the complete answer, but knowing you you should be
able to connect the dots...

--
Serge Rielau
SQL Architect DB2 for LUW, IBM Toronto Lab
Blog: tinyurl.com/SQLTips4DB2
Wiki: tinyurl.com/Oracle2DB2Wiki
Twitter: srielau