SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-27-2008, 07:12 AM
| ||||
| ||||
 a strange sql explain 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 .      |
|
02-27-2008, 07:12 AM
| |||
| |||
| 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@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 . > |
|
02-27-2008, 07:17 AM
| ||||
| ||||
| Re: a strange sql explain Phil Sherman wrote: > 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 . > > 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 . |
Linear Mode
