4 ms·

> 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.