Merge statement: Index on source table

[Henry J.]

I got a dumb question on the merge statement. I read the following
example of merge statement at the IBM page:
http://publib.boulder.ibm.com/infoce.../r0010873.htm:

MERGE INTO archive ar
USING (SELECT activity, description FROM activities) ac
ON (ar.activity = ac.activity)
WHEN MATCHED THEN
UPDATE SET
description = ac.description
WHEN NOT MATCHED THEN
INSERT
(activity, description)
VALUES (ac.activity, ac.description)

Can't we use activities as the source table directly, as:

MERGE INTO archive ar
USING activities ac
ON (ar.activity = ac.activity)
....

Also, in the original example, if the table activities has an index
on the column activity, can it be utilized since the source table is a
query on the table activities?

Thanks!

[Serge Rielau]

Henry J. wrote:

I got a dumb question on the merge statement. I read the following
example of merge statement at the IBM page:
http://publib.boulder.ibm.com/infoce.../r0010873.htm:

MERGE INTO archive ar
USING (SELECT activity, description FROM activities) ac
ON (ar.activity = ac.activity)
WHEN MATCHED THEN
UPDATE SET
description = ac.description
WHEN NOT MATCHED THEN
INSERT
(activity, description)
VALUES (ac.activity, ac.description)

Can't we use activities as the source table directly, as:

MERGE INTO archive ar
USING activities ac
ON (ar.activity = ac.activity)
....

yes

>
Also, in the original example, if the table activities has an index
on the column activity, can it be utilized since the source table is a
query on the table activities?

yes

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[DA Morgan]

Serge Rielau wrote:

> MERGE INTO archive ar
USING (SELECT activity, description FROM activities) ac
ON (ar.activity = ac.activity)
WHEN MATCHED THEN
UPDATE SET
description = ac.description
WHEN NOT MATCHED THEN
INSERT
(activity, description)
VALUES (ac.activity, ac.description)

This looks like a poster-child case of someone needing about five
minutes behind the woodshed with Joe Celko on the topic of column
naming.
--
Daniel A. Morgan
University of Washington
da******@x.washington.edu (replace x with u to respond)

[Henry J.]

*Also, in the original example, if the table activities has an index

on the column activity, can it be utilized since the source table is a
query on the table activities?

yes

Thanks. So, in general, indexes on a table inside a subquery can be
utilized just fine. Like in this example:

select T1.col1, T3.col4
from T1, (select col3, col4 from T2 where col1 = col2) T3
where T1.col2 = T3.col3

If there is an index on T2(col3), it will be used in joining T1 and T3
on (T1.col2 = T3.col3), right? How if the subquery on T2 gets more
complex? The optimizer can still figure out to use the indexes on T2
to join with T1? How's it implemented?

Or should I avoid subquery in general if possible to increase the
chance of the indexes being utilized? I noticed some of our legacy
code tends to use nested sub-queries, in some cases 3 or 4 level
deep. I guess the performance might be better if the level of nesting
is smaller?

Thanks.

[Lennart]

On 29 Sep, 05:05, "Henry J." <[e-mail]:

*Also, in the original example, if the table activities has an index
on the column activity, can it be utilized since the source table is a
query on the table activities?

yes

Thanks. *So, in general, indexes on a table inside a subquery can be
utilized just fine. *Like in this example:

* select T1.col1, T3.col4
* from T1, (select col3, col4 from T2 where col1 = col2) T3
* where T1.col2 = T3.col3

If there is an index on T2(col3), it will be used in joining T1 and T3
on (T1.col2 = T3.col3), right? *How if the subquery on T2 gets more
complex? *The optimizer can still figure out to use the indexes on T2
to join with T1?

A while back there was a discussion regarding db2's optimizer and
indexes. It will not answer your questions, but perhaps it gives you
some thoughts:

http://tinyurl.com/3k6es2

/Lennart

[...]

[Henry J.]

On Sep 29, 12:54*am, Lennart <[e-mail]:

On 29 Sep, 05:05, "Henry J." <[e-mail]:

*Also, in the original example, if the table activities has an index
on the column activity, can it be utilized since the source table is a
query on the table activities?

yes

Thanks. *So, in general, indexes on a table inside a subquery can be
utilized just fine. *Like in this example:

* select T1.col1, T3.col4
* from T1, (select col3, col4 from T2 where col1 = col2) T3
* where T1.col2 = T3.col3

If there is an index on T2(col3), it will be used in joining T1 and T3
on (T1.col2 = T3.col3), right? *How if the subquery on T2 gets more
complex? *The optimizer can still figure out to use the indexes on T2
to join with T1?

A while back there was a discussion regarding db2's optimizer and
indexes. It will not answer your questions, but perhaps it gives you
some thoughts:

*http://tinyurl.com/3k6es2

/Lennart

[...]- Hide quoted text -

- Show quoted text -

Thanks but that thread is regarding the index usage when a function is
used? My question is on index usage when the base table is tucked
away in a sub-query or in a nested sub-query.

[Serge Rielau]

Henry J. wrote:

On Sep 29, 12:54 am, Lennart <[e-mail]:

>On 29 Sep, 05:05, "Henry J." <[e-mail]:

>>>> Also, in the original example, if the table activities has an index
on the column activity, can it be utilized since the source table is a
query on the table activities?
yes
Thanks. So, in general, indexes on a table inside a subquery can be
utilized just fine. Like in this example:
select T1.col1, T3.col4
from T1, (select col3, col4 from T2 where col1 = col2) T3
where T1.col2 = T3.col3
If there is an index on T2(col3), it will be used in joining T1 and T3
on (T1.col2 = T3.col3), right? How if the subquery on T2 gets more
complex? The optimizer can still figure out to use the indexes on T2
to join with T1?

A while back there was a discussion regarding db2's optimizer and
indexes. It will not answer your questions, but perhaps it gives you
some thoughts:

http://tinyurl.com/3k6es2

/Lennart

[...]- Hide quoted text -

- Show quoted text -

Thanks but that thread is regarding the index usage when a function is
used? My question is on index usage when the base table is tucked
away in a sub-query or in a nested sub-query.

Henry,

DB2 has a sophisticated query-rewrite engine. One of it's goals is to
bring predicates as close to the data as possible so the optimizer can
choose the best access plan.
There is no "will" with a cost based optimizer. Its decisions depend on
statistics. As to how it is implemented that si part of either the
secret sauce, patented or published in research papers. The later two
you can find if you look hard enough, for the first you'll need to hire
into our team :-)

So see what DB2 has come up with you can perform explains (db2expln,
viusal explain or db2exfmt). You will find that in general subqueries
will provide no handicap to DB2 finding a good plan.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Henry J.]

On Sep 30, 6:38*am, Serge Rielau <[e-mail]:

Henry J. wrote:

On Sep 29, 12:54 am, Lennart <[e-mail]:

On 29 Sep, 05:05, "Henry J." <[e-mail]:

>>>*Also, in the original example, if the table activities has an index
on the column activity, can it be utilized since the source table is a
query on the table activities?
yes
Thanks. *So, in general, indexes on a table inside a subquery can be
utilized just fine. *Like in this example:
* select T1.col1, T3.col4
* from T1, (select col3, col4 from T2 where col1 = col2) T3
* where T1.col2 = T3.col3
If there is an index on T2(col3), it will be used in joining T1 and T3
on (T1.col2 = T3.col3), right? *How if the subquery on T2 gets more
complex? *The optimizer can still figure out to use the indexes on T2
to join with T1?
A while back there was a discussion regarding db2's optimizer and
indexes. It will not answer your questions, but perhaps it gives you
some thoughts:

*http://tinyurl.com/3k6es2

/Lennart

[...]- Hide quoted text -

- Show quoted text -

Thanks but that thread is regarding the index usage when a function is
used? *My question is on index usage when the base table is tucked
away in a sub-query or in a nested sub-query.

Henry,

DB2 has a sophisticated query-rewrite engine. One of it's goals is to
bring predicates as close to the data as possible so the optimizer can
choose the best access plan.
There is no "will" with a cost based optimizer. Its decisions depend on
* statistics. As to how it is implemented that si part of either the
secret sauce, patented or published in research papers. The later two
you can find if you look hard enough, for the first you'll need to hire
into our team :-)

So see what DB2 has come up with you can perform explains (db2expln,
viusal explain or db2exfmt). You will find that in general subqueries
will provide no handicap to DB2 finding a good plan.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Excellent. So subqueries will be rewritten by the optimizer, that
answered my "how" question. I guess anything more detailed would not
interest me much for the time being as long as I know it's Serge's
magic in work :)