Multi table insert; does it do a single pass over the source?
in [DB2]
|
From: Doug B on 11 Aug 2010 19:54 I found this example by Serge of a multi table insert in a thread from 2008: WITH source(c1, c2, c3) AS (VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9)), ins1(d) AS (SELECT 1 FROM NEW TABLE(INSERT INTO T1(c1) SELECT c1 FROM source WHERE c3 < 5)), ins2(d) AS (SELECT 1 FROM NEW TABLE(INSERT INTO T2(c2) SELECT c2 FROM source WHERE c3 BETWEEN 5 AND 8)), ins3(d) AS (SELECT 1 FROM NEW TABLE(INSERT INTO T3(c3) SELECT c3 FROM source WHERE c3 > 8)) VALUES 1; I've been able to successfully use this form on my DB2 9.1 installation, but I can't tell from the plan whether DB2 is scanning the source multiple times, once per insert target, or just once. How could I prove one or the other to myself? For a VALUES source it doesn't matter much, but if the source is a complex, multi-table join it would. Thanks!
From: Serge Rielau on 12 Aug 2010 11:55 On 8/11/2010 7:54 PM, Doug B wrote: > I found this example by Serge of a multi table insert in a thread from > 2008: > > WITH source(c1, c2, c3) AS (VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9)), > ins1(d) AS (SELECT 1 > FROM NEW TABLE(INSERT INTO T1(c1) > SELECT c1 FROM source > WHERE c3< 5)), > ins2(d) AS (SELECT 1 > FROM NEW TABLE(INSERT INTO T2(c2) > SELECT c2 FROM source > WHERE c3 BETWEEN 5 AND 8)), > ins3(d) AS (SELECT 1 > FROM NEW TABLE(INSERT INTO T3(c3) > SELECT c3 FROM source > WHERE c3> 8)) > VALUES 1; > > I've been able to successfully use this form on my DB2 9.1 > installation, but I can't tell from the plan whether DB2 is scanning > the source multiple times, once per insert target, or just once. How > could I prove one or the other to myself? For a VALUES source it > doesn't matter much, but if the source is a complex, multi-table join > it would. The optimizer plan will clearly show the common table expression i.e. source. And yes it will do multiple passes. If you want to do it in one pass you have two choices. 1. Use a dynamic compound BEGIN ATOMIC FOR myrow AS SELECT * FROM T DO INSERT INTO T1 SELECT * FROM (VALUES (myrow.c1, ....)) AS S1(c1, ..) WHERE c3 < 5; INSERT INTO T2 SELECT * FROM (VALUES (myrow.c1, ....)) AS S1(c1, ..) WHERE c3 BETWEEN 5 AND 8; ... END FOR; END 2. Build a union all view and insert into that. CREATE VIEW v AS SELECT * FROM T1 WHERE c3 < 5 UNION ALL SELECT ..... INSERT INTO v SELECT .... It's been a long time since I have done insert through union all but it should work. The dynamic compound should be the fastest though. -- Serge Rielau SQL Architect DB2 for LUW IBM Toronto Lab
From: Doug B on 12 Aug 2010 12:20 On Aug 12, 8:55 am, Serge Rielau <srie...(a)ca.ibm.com> wrote: > On 8/11/2010 7:54 PM, Doug B wrote: > > > > > I found this example by Serge of a multi table insert in a thread from > > 2008: > > > WITH source(c1, c2, c3) AS (VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9)), > > ins1(d) AS (SELECT 1 > > FROM NEW TABLE(INSERT INTO T1(c1) > > SELECT c1 FROM source > > WHERE c3< 5)), > > ins2(d) AS (SELECT 1 > > FROM NEW TABLE(INSERT INTO T2(c2) > > SELECT c2 FROM source > > WHERE c3 BETWEEN 5 AND 8)), > > ins3(d) AS (SELECT 1 > > FROM NEW TABLE(INSERT INTO T3(c3) > > SELECT c3 FROM source > > WHERE c3> 8)) > > VALUES 1; > > > I've been able to successfully use this form on my DB2 9.1 > > installation, but I can't tell from the plan whether DB2 is scanning > > the source multiple times, once per insert target, or just once. How > > could I prove one or the other to myself? For a VALUES source it > > doesn't matter much, but if the source is a complex, multi-table join > > it would. > > The optimizer plan will clearly show the common table expression i.e. > source. > And yes it will do multiple passes. > If you want to do it in one pass you have two choices. > 1. Use a dynamic compound > BEGIN ATOMIC > FOR myrow AS SELECT * FROM T DO > INSERT INTO T1 SELECT * FROM (VALUES (myrow.c1, ....)) > AS S1(c1, ..) WHERE c3 < 5; > INSERT INTO T2 SELECT * FROM (VALUES (myrow.c1, ....)) > AS S1(c1, ..) WHERE c3 BETWEEN 5 AND 8; > ... > END FOR; > END > > 2. Build a union all view and insert into that. > CREATE VIEW v AS > SELECT * FROM T1 WHERE c3 < 5 > UNION ALL SELECT ..... > > INSERT INTO v SELECT .... > > It's been a long time since I have done insert through union all but it > should work. > > The dynamic compound should be the fastest though. > > -- > Serge Rielau > SQL Architect DB2 for LUW > IBM Toronto Lab- Hide quoted text - > > - Show quoted text - Thanks for the reply, Serge. I had thought I'd proven to myself that only one pass was occurring by doing a test using a SEQUENCE object in the source/common table expression like so: WITH source AS( SELECT NEXTVAL FOR my_schema.my_seq id FROM sysibm.sysdummy1), ins1(d) AS ( SELECT 1 FROM NEW TABLE(INSERT INTO my_schema.table_001 SELECT * FROM source WHERE id between 0 and 999999999) ), ins2(d) AS ( SELECT 1 FROM NEW TABLE(INSERT INTO my_schema.table_002 SELECT * FROM source WHERE id between 0 and 999999999) ), ins3(d) AS ( SELECT 1 FROM NEW TABLE(INSERT INTO my_schema.table_003 SELECT * FROM source WHERE id between 0 and 999999999) ) VALUES 1; The 3 "target" tables each contained the same id value from the sequence, not three consecutive ones, so my assumption was that only a single pass against the source had happened.
From: Serge Rielau on 12 Aug 2010 22:39 On 8/12/2010 12:20 PM, Doug B wrote: > On Aug 12, 8:55 am, Serge Rielau<srie...(a)ca.ibm.com> wrote: >> On 8/11/2010 7:54 PM, Doug B wrote: >> >> >> >>> I found this example by Serge of a multi table insert in a thread from >>> 2008: >> >>> WITH source(c1, c2, c3) AS (VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9)), >>> ins1(d) AS (SELECT 1 >>> FROM NEW TABLE(INSERT INTO T1(c1) >>> SELECT c1 FROM source >>> WHERE c3< 5)), >>> ins2(d) AS (SELECT 1 >>> FROM NEW TABLE(INSERT INTO T2(c2) >>> SELECT c2 FROM source >>> WHERE c3 BETWEEN 5 AND 8)), >>> ins3(d) AS (SELECT 1 >>> FROM NEW TABLE(INSERT INTO T3(c3) >>> SELECT c3 FROM source >>> WHERE c3> 8)) >>> VALUES 1; >> >>> I've been able to successfully use this form on my DB2 9.1 >>> installation, but I can't tell from the plan whether DB2 is scanning >>> the source multiple times, once per insert target, or just once. How >>> could I prove one or the other to myself? For a VALUES source it >>> doesn't matter much, but if the source is a complex, multi-table join >>> it would. >> >> The optimizer plan will clearly show the common table expression i.e. >> source. >> And yes it will do multiple passes. >> If you want to do it in one pass you have two choices. >> 1. Use a dynamic compound >> BEGIN ATOMIC >> FOR myrow AS SELECT * FROM T DO >> INSERT INTO T1 SELECT * FROM (VALUES (myrow.c1, ....)) >> AS S1(c1, ..) WHERE c3< 5; >> INSERT INTO T2 SELECT * FROM (VALUES (myrow.c1, ....)) >> AS S1(c1, ..) WHERE c3 BETWEEN 5 AND 8; >> ... >> END FOR; >> END >> >> 2. Build a union all view and insert into that. >> CREATE VIEW v AS >> SELECT * FROM T1 WHERE c3< 5 >> UNION ALL SELECT ..... >> >> INSERT INTO v SELECT .... >> >> It's been a long time since I have done insert through union all but it >> should work. >> >> The dynamic compound should be the fastest though. >> >> -- >> Serge Rielau >> SQL Architect DB2 for LUW >> IBM Toronto Lab- Hide quoted text - >> >> - Show quoted text - > > Thanks for the reply, Serge. I had thought I'd proven to myself that > only one pass was occurring by doing a test using a SEQUENCE object in > the source/common table expression like so: > > WITH > source AS( > SELECT NEXTVAL FOR my_schema.my_seq id > FROM sysibm.sysdummy1), > ins1(d) AS ( > SELECT 1 > FROM NEW TABLE(INSERT > INTO my_schema.table_001 > SELECT * > FROM source > WHERE id between 0 and 999999999) > ), > ins2(d) AS ( > SELECT 1 > FROM NEW TABLE(INSERT > INTO my_schema.table_002 > SELECT * > FROM source > WHERE id between 0 and 999999999) > ), > ins3(d) AS ( > SELECT 1 > FROM NEW TABLE(INSERT > INTO my_schema.table_003 > SELECT * > FROM source > WHERE id between 0 and 999999999) > ) > VALUES 1; > > The 3 "target" tables each contained the same id value from the > sequence, not three consecutive ones, so my assumption was that only a > single pass against the source had happened. Well.. true, but the the result is stored in a temp table which is then scanned three times. -- Serge Rielau SQL Architect DB2 for LUW IBM Toronto Lab
|
Pages: 1 Prev: Oracle PL/SQ L , Toronto, ON, Canada Next: Purging in table partition9.7 with global indexes. |