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.

Sequence out of fetch? More problems with committing in loops

 Bad practices, Database concepts  No Responses »
Apr 292013
  
create sequence ora 
start  with -1002 minvalue -1002;

begin
  for commits in (
    select loops 
    from   queries
    where  we = 'specify'
    for    update
  ) loop
    dbms_output.put_line(
      'Causes an ' || sqlerrm(ora.nextval));
    commit;
  end loop;
end;
/

Error report:
ORA-01002: fetch out of sequence
Issuing a commit after opening a cursor with the "for update" releases the row locks
and invalidates the cursor, meaning you can no longer fetch data out of it!

Click here for a complete script to see this in action.

Who needs rows…

 Bad practices  No Responses »
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".

Lord of the Lookup Tables

 Bad practices  No Responses »
Mar 252013
  
create table one_true_lookup_table (
  one_table_to_find_them integer primary key,
  one_table_to_bring_them_all varchar2(1000)
) tablespace and_in_the_darkness_bind_them;

Just like the one ring, the one true lookup table (OTLT) is a concept that should be thrown into Mount Doom

For discussions as to why the OTLT is a bad idea, have a look here, here and here.

(Not) Getting the first value in an ordered set

 Bad practices, Database concepts  No Responses »
Mar 042013
  
select a_random_row
from   this_table
where  rownum = 1
and    i_wanted_the_first = 'Y'
order  by according_to_this;
This issue is slightly more insidious than last week's, though based on a similar misunderstanding. 
This query can appear to be workingly correctly, until the physical ordering of data changes 
(e.g. when migrating to a new environment); at which point it becomes apparent that the order by is done after rownum

For the correct way to do top-n analysis in Oracle, see 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

I’ve a distinct feeling you’re doing it wrong…

 Bad practices  No Responses »
Feb 042013
  
select distinct o.rows_and_columns
from   one_table o, 
       matching_rows_in_another m
where  o.i_should_have_used = m.a_subquery
and    m.saved_myself_a_sort = 'Y'
While there's some legitimate uses of distinct, 
if you're relying on it to remove duplicates something is probably wrong

Click here for a script showing this and (better) alternative queries

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