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@hotmail.com wrote:
> 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 .
>