Tom Kyte is on record as wanting the abolition of "WHEN OTHERS", "Autonomous Transactions" and "Triggers". I think he's also mention COMMIT in procedures too.
For today's rant, I'm going up against the humble concatenation operator. Yup the double pipe ( || ), or concat if you want to be 'portable'.
Not entirely, of course. I'm not a zealot.I recognize that sometimes you'll need to join a State to a Postcode (or Zip code) when outputting an address.
But they should only be in the top-most SELECT. Not in a subselect, or a predicate. And definitely not if you are inserting the combined value into another column. The first rule about normalisation is you don't talk about normalisation. No, sorry that's Fight Club. But First Normal Form does include Atomicity, which means you shouldn't be gluing fields together. This is a data model, not an Airfix model.
If you do "column_a||column_b", you'll get confused about whether 'abc' was 'ab'||'c' or 'a'||'bc' and be lost.
Even if you are smart and stick some delimiter in there (column_a||'-'||column_b), you've lost your columns. Can't use them for Referential Integrity. Can't gather stats on them. The optimizer is going to get lost. If the original columns were numbers or dates, you've added datatype conversion into the mix. If both the original values were null, you've got nothing but a delimiter in the new field. Yuck.
But mostly, at some time in the future some poor blighter is going to have to come along and break them apart with obscure regular expressions. It may be me, and I won't be happy. And then you'll find out what I can do with a double pipe !
2 comments:
Hi,
in my opinion concatenating strings is part of business logic and therefor totally ok to be used in views or packages.
For example you store zip code and town in seperate columns in your table, it might be perfectly ok to present them as one column in a view.
Peter
Haha, enjoyed the last statement. :D
Post a Comment