Failed Deployment...

236 Days
20 Hours
48 Minutes
30 Seconds

I hate making mistakes but I've made another one. My streak ends almost 237 days from my previous one.

Something so silly too.

In our source system, data was been double loaded somehow. So we decided on a surgical delete. A total of 7 DELETE statements needed to be run; 4 on the source system and 3 on the target system.

The source system went off without a hitch. I babysat the re-load of the source tables and was ready to have our load jobs run in our target system.

What's this? It ran in half the time?! How's that possible?

I pulled up our logs to find that zero rows were loaded into one of our tables. There should have been 45 Million plus.

I started to run down the possible causes:
1. Did the job we have in the scheduler that TRUNCATEs our persistent staging tables run? Nope.
2. Did I fail to instruct the DBAs correctly in the critical CR? Nope. Instructions look good.
3. Next to the logs, it ran fine on Saturday morning but not Sunday morning. What happened yesterday?
4. Ah yes, my CR. Open up the script...nothing out of the ordinary...and then I saw it.

On our target system, we use work tables to pre-generate a keys. It makes things a heck of a lot faster and removes the need for PL/SQL lookups in SQL (no, we don't have incremental builds yet).

So the work table needs to be DELETEd from first based on the keys from the first:

DELETE FROM some_key_table a
WHERE EXISTS ( SELECT NULL
               FROM the_main_table
               WHERE business IN ( 'TTT', 'TTR' )
                 AND dateof = TO_DATE( '24-MAR-08', 'DD-MON-YY' )
                 AND my_key = a.my_key );

OK, no funny business there.

Then I DELETE from the main table:

DELETE FROM the_main_table a
WHERE EXISTS ( SELECT NULL
               FROM the_main_table
               WHERE business IN ( 'TTT', 'TTR' )
                 AND dateof = TO_DATE( '24-MAR-08', 'DD-MON-YY' ) );

As I look at it I wonder WTH I was thinking using an EXISTS clause on the main table. That's the source.

But do you see what I missed?

See it yet?

OK, I left out the "AND my_key = a.my_key" from the inner query. Obviously a stupid approach, but it would have worked. The best way to do it is to just get rid of the EXISTS clause:

DELETE FROM the_main_table a
WHERE business IN ( 'TTT', 'TTR' )
  AND dateof = TO_DATE( '24-MAR-08', 'DD-MON-YY' ) );

Live and learn, live and learn...