Home » SQL & PL/SQL » SQL & PL/SQL » Rowcount with multi-table insert
| Rowcount with multi-table insert [message #162305] |
Thu, 09 March 2006 11:01  |
cgoulet
Messages: 3
Registered: March 2006
|
Junior Member |
|
|
Hi,
I need to insert into 3 different tables from one big source table so I decided to use multi-table insert. I could have used three inserts statements with a Pl/SQL loop but the source table is a big external table and the fastest way to load data is to use INSERT ... SELECT.
I wonder if there is a way to get the exact number of records inserted in each one of the tables (TABLE1, TABLE2, TABLE3)?
I tried using "rowcount" but I all I get is the number of rows inserted in all three tables. Is there a way to get this info without having to execute a "select count" in each table afterward?
Thanks
INSERT /*+ APPEND */
WHEN RES_ENS='PU' THEN
INTO TABLE1
VALUES(CD_ORGNS, NO_ORGNS, DT_DEB, SYSDATE)
WHEN RES_ENS='PR' THEN
INTO TABLE2
VALUES(CD_ORGNS, UNO_ORGNS, DT_DEB, SYSDATE)
ELSE
INTO TABLE3
VALUES(CD_ORGNS, NO_ORGNS, DT_DEB, SYSDATE)
SELECT ES.CD_ORGNS CD_ORGNS, ES.RES_ENS RES_ENS, ES.DT_DEB DT_DEB, ES.NO_ORGNS NO_ORGNS
FROM ETABL_ENSGN_SUP ES
|
|
|
|
| Re: Rowcount with multi-table insert [message #162333 is a reply to message #162305] |
Thu, 09 March 2006 15:15  |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
Simply break it into 3 inserts
INSERT /*+ APPEND */ into table1
SELECT ES.CD_ORGNS CD_ORGNS, ES.RES_ENS RES_ENS, ES.DT_DEB DT_DEB, ES.NO_ORGNS NO_ORGNS
FROM ETABL_ENSGN_SUP ES
where res_ens = 'PU';
INSERT /*+ APPEND */ into table2
SELECT ES.CD_ORGNS CD_ORGNS, ES.RES_ENS RES_ENS, ES.DT_DEB DT_DEB, ES.NO_ORGNS NO_ORGNS
FROM ETABL_ENSGN_SUP ES
where res_ens = 'PR';
INSERT /*+ APPEND */ into table3
SELECT ES.CD_ORGNS CD_ORGNS, ES.RES_ENS RES_ENS, ES.DT_DEB DT_DEB, ES.NO_ORGNS NO_ORGNS
FROM ETABL_ENSGN_SUP ES
where res_ens not in ('PR','PU');
|
|
|
|
| Re: Rowcount with multi-table insert [message #162372 is a reply to message #162305] |
Thu, 09 March 2006 22:59 |
 |
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Adrian Billington created a package to extract the separate counts for inserts and updates from a merge statement:
http://www.quest-pipelines.com/newsletter-v4/0903_D.htm
Applying the same general principles, you could do something similar for the separate counts of inserts into tables using a multi-table insert, as demonstrated below.
-- tables and data for testing:
scott@ORA92> CREATE TABLE etabl_ensgn_sup
2 (cd_orgns VARCHAR2(10),
3 res_ens VARCHAR2(10),
4 dt_deb VARCHAR2(10),
5 no_orgns VARCHAR2(10))
6 /
Table created.
scott@ORA92> INSERT ALL
2 INTO etabl_ensgn_sup VALUES ('cd_orgn1', 'PU', 'dt_deb1', 'no_orgns1')
3 INTO etabl_ensgn_sup VALUES ('cd_orgn2', 'PR', 'dt_deb2', 'no_orgns2')
4 INTO etabl_ensgn_sup VALUES ('cd_orgn3', 'PR', 'dt_deb3', 'no_orgns3')
5 INTO etabl_ensgn_sup VALUES ('cd_orgn4', 'OTHER', 'dt_deb4', 'no_orgns4')
6 INTO etabl_ensgn_sup VALUES ('cd_orgn5', 'OTHER', 'dt_deb5', 'no_orgns5')
7 INTO etabl_ensgn_sup VALUES ('cd_orgn6', 'OTHER', 'dt_deb6', 'no_orgns6')
8 SELECT * FROM DUAL
9 /
6 rows created.
scott@ORA92> CREATE TABLE table1
2 (cd_orgns VARCHAR2(10),
3 no_orgns VARCHAR2(10),
4 dt_deb VARCHAR2(10),
5 date_col DATE)
6 /
Table created.
scott@ORA92> CREATE TABLE table2
2 (cd_orgns VARCHAR2(10),
3 no_orgns VARCHAR2(10),
4 dt_deb VARCHAR2(10),
5 date_col DATE)
6 /
Table created.
scott@ORA92> CREATE TABLE table3
2 (cd_orgns VARCHAR2(10),
3 no_orgns VARCHAR2(10),
4 dt_deb VARCHAR2(10),
5 date_col DATE)
6 /
Table created.
-- package for counting rows inserted:
scott@ORA92> CREATE OR REPLACE PACKAGE row_count AS
2 v_count1 NUMBER;
3 v_count2 NUMBER;
4 v_count3 NUMBER;
5
6 PROCEDURE reset_count;
7
8 FUNCTION update_count (p_num IN NUMBER) RETURN NUMBER;
9 END row_count;
10 /
Package created.
scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> CREATE OR REPLACE PACKAGE BODY row_count AS
2 PROCEDURE reset_count IS
3 BEGIN
4 v_count1 := 0;
5 v_count2 := 0;
6 v_count3 := 0;
7 END reset_count;
8
9 FUNCTION update_count (p_num IN NUMBER) RETURN NUMBER IS
10 BEGIN
11 IF p_num = 1 THEN v_count1 := v_count1 + 1;
12 ELSIF P_num = 2 THEN v_count2 := v_count2 + 1;
13 ELSIF P_num = 3 THEN v_count3 := v_count3 + 1;
14 END IF;
15 RETURN 0;
16 END update_count;
17 END row_count;
18 /
Package body created.
scott@ORA92> SHOW ERRORS
No errors.
-- insert and results:
scott@ORA92> SET SERVEROUTPUT ON
scott@ORA92> BEGIN
2 row_count.reset_count;
3
4 INSERT /*+ APPEND */
5 WHEN RES_ENS = 'PU' THEN
6 INTO TABLE1
7 VALUES (CD_ORGNS, NO_ORGNS, DT_DEB,
8 CASE row_count.update_count (1) WHEN 0 THEN SYSDATE END)
9 WHEN RES_ENS = 'PR' THEN
10 INTO TABLE2
11 VALUES (CD_ORGNS, NO_ORGNS, DT_DEB,
12 CASE row_count.update_count (2) WHEN 0 THEN SYSDATE END)
13 ELSE
14 INTO TABLE3
15 VALUES (CD_ORGNS, NO_ORGNS, DT_DEB,
16 CASE row_count.update_count (3) WHEN 0 THEN SYSDATE END)
17 SELECT ES.CD_ORGNS CD_ORGNS, ES.RES_ENS RES_ENS, ES.DT_DEB DT_DEB, ES.NO_ORGNS NO_ORGNS
18 FROM ETABL_ENSGN_SUP ES;
19
20 COMMIT;
21
22 DBMS_OUTPUT.PUT_LINE (row_count.v_count1 || ' rows inserted into table1');
23 DBMS_OUTPUT.PUT_LINE (row_count.v_count2 || ' rows inserted into table2');
24 DBMS_OUTPUT.PUT_LINE (row_count.v_count3 || ' rows inserted into table3');
25 END;
26 /
1 rows inserted into table1
2 rows inserted into table2
3 rows inserted into table3
PL/SQL procedure successfully completed.
-- confirm results:
scott@ORA92> SET FEEDBACK 1
scott@ORA92> SELECT * FROM table1
2 /
CD_ORGNS NO_ORGNS DT_DEB DATE_COL
---------- ---------- ---------- ---------
cd_orgn1 no_orgns1 dt_deb1 09-MAR-06
1 row selected.
scott@ORA92> SELECT * FROM table2
2 /
CD_ORGNS NO_ORGNS DT_DEB DATE_COL
---------- ---------- ---------- ---------
cd_orgn2 no_orgns2 dt_deb2 09-MAR-06
cd_orgn3 no_orgns3 dt_deb3 09-MAR-06
2 rows selected.
scott@ORA92> SELECT * FROM table3
2 /
CD_ORGNS NO_ORGNS DT_DEB DATE_COL
---------- ---------- ---------- ---------
cd_orgn4 no_orgns4 dt_deb4 09-MAR-06
cd_orgn5 no_orgns5 dt_deb5 09-MAR-06
cd_orgn6 no_orgns6 dt_deb6 09-MAR-06
3 rows selected.
scott@ORA92>
|
|
|
|
|
|
| Re: Rowcount with multi-table insert [message #162529 is a reply to message #162515] |
Fri, 10 March 2006 19:19 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Clever as this is, let's not forget that the purpose of MULTI-TABLE insert is to be faster than 3 separate INSERT statements.
The cost of invoking the function for each row inserted will not be free. The question is, how much does it slow the job down? (I don't know, it might be neglibible)
For instance, if you have a complex SELECT, then you certainly don't want to run it 3 times, but what if you inserted into a GLOBAL TEMPORARY TABLE and then performed your 3 separate inserts sourced from the GTT? Faster or Slower? The only way to find out is to run it.
My advice? Use multi-table insert in the way God intended, and don't get hung up on row counts. If you can't handle that, you need to BENCHMARK the alternatives. Never blindly accept an elegant solution that under-performs.
_____________
Ross Leishman
|
|
|
|
Goto Forum:
Current Time: Sun Aug 31 05:57:44 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
