Cases where full scans are better than indexes

And this is exactly the sentiment that got us where we are.

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

I'll assume 100WPM, call that two words, billed at $200/hour and call that $0.06, falling under "too cheap to be worth arguing against", which falls under the aforementioned:

>> If it'a a 5 second "this is probably the right choice" kneejerk reaction, maybe it's fine.

That said, there's a decent chance those 6 cents won't pay for themselves if this is a login on a single user system, with any theoretical benefits of O(...) scaling being drowned out by extra compile times, extra code to load - and I'd be plenty willing to NAK code reviews that merely attempt to replace /etc/passwd and /etc/shadow with this, as the extra time code reviewing the replacement still has negative expected ROI, and it'll be a lot more involved than a mere dozen characters.

Now, maybe we're attempting to centralize login management with Kerberos or something, perhaps with good reasons, perhaps which does something similar under the hood, and we could talk about that and possible positive ROI, despite some actual downtime and teething concerns?