Simple example.
INTAKE_TABLE contains 100k records and each record has up to 8 names and addresses. The following SQL statements would perform the name de-duplication, new id assignment and normalization.
-- initialize
truncate table STAGING_NAMES_TABLE;
-- get unique names
insert /*+ APPEND PARALLEL(STAGING_NAMES_TABLE, 4) */ into
STAGING_NAMES_TABLE (name)
select /*+ PARALLEL(INTAKE_TABLE, 4) */ name1 from INTAKE_TABLE
union
select /*+ PARALLEL(INTAKE_TABLE, 4) */ name2 from INTAKE_TABLE
union
...
union
select /*+ PARALLEL(INTAKE_TABLE, 4) */ name8 from INTAKE_TABLE;
commit;
-- assign new name ids using the name_seq sequence number
insert /*+ APPEND */ into NAMES_TABLE (name, name_id)
select name, name_seq.NEXT_VAL from
(select name from STAGING_NAMES_TABLE
minus
select name from NAMES_TABLE);
commit;
-- normalize the names
insert /*+ APPEND PARALLEL(NORMALIZED_TABLE, 4) */ into NORMALIZED_TABLE (
rec_id,
name_id1,
name_id2,
...
name_id8 )
select /*+ PARALLEL(SNT, 4) */
int.rec_id rec_id,
nt1.name_id name_id1,
nt2.name_id name_id2,
...,
nt8.name_id name_id8
from
INTAKE_TABLE int,
NAMES_TABLE nt1,
NAMES_TABLE nt2,
...
NAMES_TABLE nt8
where
int.name1 = nt1.name and
int.name2 = nt2.name and
...
int.name8 = nt8.name;
commit;
The database can do sorting and de-duplication (i.e., the query UNION operation) much much faster than any application code. Even though the INTAKE_TABLE (100k records) are TABLE-SCANNED 8 times, the union query runs quite fast.
The new id generation does a set exclusion (i.e., the MINUS operation) and then generates new sequence numbers for each new unique name and adds the new records to the table.
The normalization (i.e., the name lookup) step joins the NAME_TABLE that now contains the new names and performs the name to id conversion with the join query.
Realizing that the UNION, MINUS and even nasty 8-way joins can be done by the database engine way faster than application code was eye-opening. I never feared table scans after that. Discovering that the reads (i.e., SELECTs) and writes (i.e., INSERTs) can be done in parallel with optimization hints such as the APPEND hint was a superpower.
Using patterns such a TRUNCATE TABLE (for staging tables) at the top of the SQL script made the scripts idempotent. i.e., we could trivially run the script again. The subsequent runs will not generate the new sequence numbers for the names. With some careful organization of the statements, this became rigorous.
Although I haven't shown here, we used to do the entire normalization process in staging tables and finally do a copy over to the main table using a final INSERT / SELECT statement.
My Oracle SQL-fu is a bit rusty. Apologies for any syntax errors.