a strange sql explain

[heming_g@hotmail.com]

two tables with the same table structure : tb_xxx and tb_xxx_tmp

in table tb_xxx , column "listno" is the primary key of itself and
foreign key of dozen of tables .

here is my sql .. " update tb_xxx set ( listno ) = (select listno from
tb_xxx_tmp where listno = 11 ) where listno = 11 "

the explain result (use db2expln ) is amazing ... about a thousand
lines !!!! ,refering all of the dozen of tables . but when i try
anther two table tb_yyy and tb_yyy_tmp .

its explain is simple :

UPDATE
| |
NLJOIN TB_yyy
| |
TB_yyy TB_yyy_tmp

the difference between tb_xxx and tb_yyy is that tb_yyy is less refered
by other tables .

[Phil Sherman]

Re: a strange sql explain

Check the SQL Reference, Vol 2; CREATE TABLE; FOREIGN KEY; ENFORCED.

Any time a parent row is deleted, the dependent table should be examined
to determine what to do with rows that matched the modified parent. The
ENFORCED parameter is used to control database manager checking of the
constraint.

I'd check the definitions for dependents of xxx and yyy to see if they
use different enforcement options. This would easily account for the
explain differences.

Phil Sherman



heming_g@hotmai l.com wrote:[color=blue]
> two tables with the same table structure : tb_xxx and tb_xxx_tmp
>
> in table tb_xxx , column "listno" is the primary key of itself and
> foreign key of dozen of tables .
>
> here is my sql .. " update tb_xxx set ( listno ) = (select listno from
> tb_xxx_tmp where listno = 11 ) where listno = 11 "
>
> the explain result (use db2expln ) is amazing ... about a thousand
> lines !!!! ,refering all of the dozen of tables . but when i try
> anther two table tb_yyy and tb_yyy_tmp .
>
> its explain is simple :
>
> UPDATE
> | |
> NLJOIN TB_yyy
> | |
> TB_yyy TB_yyy_tmp
>
> the difference between tb_xxx and tb_yyy is that tb_yyy is less refered
> by other tables .
>[/color]

[heming_g@hotmail.com]

Re: a strange sql explain


Phil Sherman wrote:[color=blue]
> Check the SQL Reference, Vol 2; CREATE TABLE; FOREIGN KEY; ENFORCED.
>
> Any time a parent row is deleted, the dependent table should be examined
> to determine what to do with rows that matched the modified parent. The
> ENFORCED parameter is used to control database manager checking of the
> constraint.
>
> I'd check the definitions for dependents of xxx and yyy to see if they
> use different enforcement options. This would easily account for the
> explain differences.
>
> Phil Sherman
>
>
>
> heming_g@hotmai l.com wrote:[color=green]
> > two tables with the same table structure : tb_xxx and tb_xxx_tmp
> >
> > in table tb_xxx , column "listno" is the primary key of itself and
> > foreign key of dozen of tables .
> >
> > here is my sql .. " update tb_xxx set ( listno ) = (select listno from
> > tb_xxx_tmp where listno = 11 ) where listno = 11 "
> >
> > the explain result (use db2expln ) is amazing ... about a thousand
> > lines !!!! ,refering all of the dozen of tables . but when i try
> > anther two table tb_yyy and tb_yyy_tmp .
> >
> > its explain is simple :
> >
> > UPDATE
> > | |
> > NLJOIN TB_yyy
> > | |
> > TB_yyy TB_yyy_tmp
> >
> > the difference between tb_xxx and tb_yyy is that tb_yyy is less refered
> > by other tables .
> >[/color][/color]

actually , their definition are the same without ENFORCED parameter .
the question is when the update statement is simple as " update xxx set
a = 1 " , its explain result is normal . but when it comes to "update
xxx set (a) = (select a from ...) " . the explain result is refering
all of its depandent tables .