3 ms·

Once upon a time (25+ years ago) I used to maintain an Oracle database that had around 50M records in its main table and 2M records in a related table. There we

by vivegi 3y ago

Once upon a time (25+ years ago) I used to maintain an Oracle database that had around 50M records in its main table and 2M records in a related table. There were a dozen or so dimension (lookup) tables. Each day, we would receive on the order of 100K records that had to be normalized and loaded into database. I am simplifying this a lot, but that was the gist of the process. Our first design was a PL/SQL program that looped through the 100K records that would be loaded into a staging table from a flat file, performing the normalization and inserts into the destination tables. Given the size of our large tables, indices and the overhead of the inserts, we never could finish the batch process in the night that was reserved for the records intake. We finally rewrote the entire thing using SQL, parallel table scans, staging tables and disabling and rebuilding the indexes and got the entire end-to-end intake process to finish in 45 minutes. Set theory is your friend and SQL is great for that.

zvmaz 3y ago
> Set theory is your friend and SQL is great for that. Could you tell us how set theory was useful in your case?
- wswope 3y ago
  The implication is that the PL/SQL job was operating rowwise. Batch processing, backed by set theory in this case, is far more efficient than rowwise operations because of the potential for greater parallelism (SIMD) and fewer cache misses. E.g., if inserting into a table with a foreign key pointing to the user table, batch processing means you can load the user id index once, and validate referential integrity in one fell swoop. If you do that same operation rowwise, the user id index is liable to get discarded and pulled from disk multiple times, depending on how busy the DB is.
  - lazide 3y ago
    Also, each insert will have to acquire locks, verify referential integrity, and write to any indexes individually. This is time consuming.
- vivegi 3y ago
  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.
gopher_space 3y ago
> loaded into a staging table from a flat file, performing the normalization and inserts into the destination tables. This is how I've always done things, but I'm starting to think decoupling normalization from the entire process might be a good idea. This would have simplified design on a few projects (especially the ones that grew "organically") and seems like it would scale nicely. I'm starting to look at normalization as a completely separate concern and this has led to some fun ideas. E.g. if a system operated on flat files that were already normalized it could save time writing tools; you're at the command line and everyone involved can get their grubby mitts on the data.

by vivegi 3y ago

zvmaz 3y ago

> Set theory is your friend and SQL is great for that. Could you tell us how set theory was useful in your case?

wswope 3y ago
The implication is that the PL/SQL job was operating rowwise. Batch processing, backed by set theory in this case, is far more efficient than rowwise operations because of the potential for greater parallelism (SIMD) and fewer cache misses. E.g., if inserting into a table with a foreign key pointing to the user table, batch processing means you can load the user id index once, and validate referential integrity in one fell swoop. If you do that same operation rowwise, the user id index is liable to get discarded and pulled from disk multiple times, depending on how busy the DB is.
- lazide 3y ago
  Also, each insert will have to acquire locks, verify referential integrity, and write to any indexes individually. This is time consuming.
vivegi 3y ago
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.

gopher_space 3y ago

> loaded into a staging table from a flat file, performing the normalization and inserts into the destination tables. This is how I've always done things, but I'm starting to think decoupling normalization from the entire process might be a good idea. This would have simplified design on a few projects (especially the ones that grew "organically") and seems like it would scale nicely. I'm starting to look at normalization as a completely separate concern and this has led to some fun ideas. E.g. if a system operated on flat files that were already normalized it could save time writing tools; you're at the command line and everyone involved can get their grubby mitts on the data.