An interesting question came through on AskTom recently. The requirement was to perform a single pass through a source table, and load the data into three target tables.
Now that’s trivially achieved with a multi-table insert, but there was a subtle “twist” on this requirement. Each of the three target tables may already contain some, none or all of the rows from the source table. Hence the requirement was to “fill in the blanks”.
So here’s a little demo of one way we could achieve this.
First, here is our source table with 10 rows (1 through 10)
SQL> create table t_source as select rownum s from dual connect by level <= 10; Table created. SQL>
SQL> select * from t_source;
S
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
And here are our three target tables, T1, T2 and T3, each with a subset of the rows already
SQL> create table t1 as select rownum x from dual connect by level <= 5; Table created. SQL> create table t2 as select rownum y from dual connect by level <= 3; Table created. SQL> create table t3 as select rownum z from dual connect by level <= 6; Table created. SQL> select * from t1 order by 1;
X
----------
1
2
3
4
5
5 rows selected.
SQL> select * from t2 order by 1;
Y
----------
1
2
3
3 rows selected.
SQL> select * from t3 order by 1;
Z
----------
1
2
3
4
5
6
6 rows selected.
Now obviously we could perform a simple insert-select-where-not-exists style operation for each table, but we need to meet our poster’s requirement of a single pass through the source table. So we will take advantage of an outer join to pick up just those rows that do not already match.
SQL> insert all
2 when in_tab1 is null then
3 into t1 (x ) values (s )
4 when in_tab2 is null then
5 into t2 (y ) values (s )
6 when in_tab3 is null then
7 into t3 (z ) values (s )
8 select
9 t_source.s,
10 t1.x in_tab1,
11 t2.y in_tab2,
12 t3.z in_tab3
13 from t_source, t1, t2, t3
14 where t_source.s = t1.x(+)
15 and t_source.s = t2.y(+)
16 and t_source.s = t3.z(+)
17 /
16 rows created.
SQL>
SQL> select * from t1 order by 1;
X
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
SQL> select * from t2 order by 1;
Y
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
SQL> select * from t3 order by 1;
Z
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
SQL>
And the job is done. Our poster never really elaborated on why a single pass was necessary – but let’s assume it was due to the source table being large. If we look at the execution plan, we see a swag of cascading hash joins, so whilst a single pass of the source table has been achieved, there is no guarantee that we’re not going to end up with other issues in processing all of those “concurrent” joins.
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 120 | 8 (0)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 10 | 120 | 8 (0)| 00:00:01 |
|* 2 | HASH JOIN OUTER | | 10 | 90 | 6 (0)| 00:00:01 |
|* 3 | HASH JOIN OUTER | | 10 | 60 | 4 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T_SOURCE | 10 | 30 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| T2 | 3 | 9 | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | T1 | 5 | 15 | 2 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | T3 | 6 | 18 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T_SOURCE"."S"="T3"."Z"(+))
2 - access("T_SOURCE"."S"="T1"."X"(+))
3 - access("T_SOURCE"."S"="T2"."Y"(+))
But that’s often life on AskTom.We only get half the story 
