A Tale of Two Models

 Bad practices, Good practices  No Responses »
May 202013
  
select dm.name
from   data_models dm
join   data_model_values qc
on     dm.id = qc.model_id
join   data_model_values de
on     dm.id = de.model_id
join   data_model_values coa
on     dm.id = coa.model_id
where  qc.attr = 'Query complexity'
and    qc.val = 'Hideously mind-boggling'
and    de.attr = 'Datatype enforcement'
and    de.val = 'Non-existent'
and    coa.attr = 'Calculation of aggregates'
and    coa.val = 'Almost impossible';

NAME
----------------------
ENTITY ATTRIBUTE VALUE

select name
from   data_models
where  query_complexity = 
         'Simple and straightforward'
and    datatype_enforcement = 
         'Strong and complete'
and    calculation_of_aggregates = 
         'As easy as group by';

NAME
----------
RELATIONAL

While it's possible to end up with some dodgy queries and datatypes in a "normal" relational model, 
it's much easier to fix the problems when compared to EAV. If you've got any other comparisons 
between EAV and normal relational models, please add them in the comments!

To be fair, there are (very) few cases where EAV is appropriate. This provides a nice discussion of when you should/shouldn’t use this.

Index Mangling Queries

 Bad practices, Good practices  No Responses »
Feb 132013
  
select how_to_write_this_properly
from   index_breaking_predictates
where  trunc(i_want_all_the_times) =
         to_date (:on_this_date, 'dd/mm/yyyy')
and    the_numbers + 1 = :one_less_than_this
and    instr(text, :starting_with_this) = 1;

HOW_TO_WRITE_THIS_PROPERLY
---------------------------------------------
where i_want_all_the_times >= 
    to_date (:on_this_date, 'dd/mm/yyyy')
and i_want_all_the_times < 
    to_date (:on_this_date, 'dd/mm/yyyy') + 1 
and the_numbers = :one_less_than_this - 1
and text like :with_this_at_the_start || '%'
While you could create function-based indexes to satisfy these conditions, they will be less reusable.
trunc(date) is a common offender in my experience, with indexes like this preventing "time-of-day" query indexing

Saving you from partition maintenance headaches

 Database concepts, Good practices  2 Responses »
Jan 302013
  
create table use_interval_partitioning (
  and_new_partitions            number,
  will_be_created_automatically varchar2(1)
                                default 'Y',
  for_each_new                  date,
  so_your_system_wont_break     varchar2(1)
                                default 'Y',
  if_maintenance_is_forgotten   varchar2(100)
                                default 'Yay!'
) partition by range (for_each_new)
  interval (numtodsinterval(1,'DAY')) (
    partition first values less than (
      date '2013-01-30')
);
A very handy feature, I just wish there was a way to specify a regexp for new partition names; 
I don't like systems cluttered up with SYS_Pxxx partitions...

The named parameter mix-up

 Bad practices, Good practices  No Responses »
Jan 282013
  
begin
  with_good_coding_practice (
    naming_our_parameters     => 'Y',
    so_our_code_was_robust    => true,
    but_emergency_fixes_later => 3,
    'quality control slipped',
    'and our good intentions fell down',
    'leaving us with a horrible mismash');
end;
/Naming your parameters is an excellent way to protect against unexpected parameter changes
and make mixed-up parameters much easier to spot...

Please, help me decypher your complex logic

 Good practices  No Responses »
Jan 212013
  
create procedure use_many_comments as
--*******************************************
-- Use many comments
-- Purpose:
--  Make it easier for yourself and others to
--  figure out what you were trying to 
--  accomplish.
-- Modified:
--  2013.01.02 
--  Brian Leach of Anchorage Alaska Created
-- Notes:
--  Comments are critical, especially when you
--  are doing something unusual. When the 
--  system  is down or giving an erroneous 
--  result, good  comments may mean the
--  difference between a 10 minute fix or a 10
--  hour ordeal. When it comes time for 
--  enhancements, good comments eliminate the 
--  need to trace through every detail of the 
--  code.
-- *******************************************
begin
  a_complex_series_of_logic();
end;
/
I've been through the pain of trying to understand complex code out-of-hours;
some basic comments go a long way to easing the frustration of these situations

Many thanks to Brian Leach, fellow PL/SQL Challenge player, for providing this

 Older Entries