Sep 022013
 
delete 
from   previously_large_table;

insert /*+ append */ 
into   previously_large_table (
  cannot_reuse_existing_space,
  so_when_you
)
  select more_data, to_put_in
  from   here_space_consumed_grows;

When you delete, Oracle doesn't deallocate the space that was used by the table. 
Inserts using the append hint (aka direct path loads) allocates new space to the 
table, rather than re-using what's there. Misunderstanding of this can lead to 
tables consuming vast amounts of space, yet having (relatively) few rows in them - 
particularly if this is a frequent wipe-and-load staging table

For a quick and dirty script showing how this works (vs. convential inserts), keep reading. For more detailed discussions, take a look at this oracle-base article or this asktom question.
Continue reading »

Jul 052013
 
insert into baby (mouth)
  select healthy_snacks
  from   parents;

0 rows inserted

insert into baby (mouth)
  select random_junk
  from   the_floor;

432,149 rows inserted

The joy of getting your child to eat "the right things". Perhaps the solution 
is to cover the floor in healthy foods and try feeding them dirt...
May 272013
 
insert into data_you_thought_was_gone
  select how_it_looked_before,
         the_users_hit_delete
  from   data_you_thought_was_gone
           as of timestamp 
           sysdate - interval '1' hour
  minus
  select how_it_looked_before 
           as how_it_looks_now,
         the_users_hit_delete
  from   data_you_thought_was_gone;

Flashback to the rescue again! This approach will only work if you're notified of the data deletion 
before your undo retention runs out, otherwise you've got to go your backups. You may need to add 
further filtering if there's been updates to your data as well as deletions too. Still can come in very 
handy in certain scenarios though :)

For a script to see this in action, click here. For a thorough explanation of flashback, have a look at this oracle-base article.

May 172013
 
insert into joyous_memories
  select making_vast_improvements
  from   that_horrible_query
  union  all
  select designing_data_models
  from   scalable_applications
  union  all
  select heart_warming_smile
  from   your_newborn_child;

While there can be a lot of effort put in before getting these outcomes, the reward is worth it! :)
Apr 152013
 
insert into when_you_can_have_columns (
  reason_1,
  reason_2,
  reason_3,
  reason_4,
  reason_5
) values (
  'Joins are a #!@% to write',
  'Aggregation functions are a pain',
  'You''re not in 1st normal form',
  'You''re limited to 1000 values',
  'To add more reasons needs a schema change'
);

A similar design flaw is to implement rows as a column separated list "overcoming" the final two limitations.
However, they introduce the costs of everything is a string and having to do messy substrings to extract the "rows".