Campaign against incorrect data types
July 16th, 2007 by Peter Scott
Those that have met me know that beneath that benign, placid exterior lurks a data-geek of great passion. Show me the work of others where they reinvent the wheel by writing their own procedural methods (in PL/SQL or T-SQL or whatever) to replicate a native function of the database or store numbers as characters, or dates as strings and I degenerate into rant mode. Hiding the true nature of data denies the database performance; the query optimiser no longer can say that 5th July 2007 is the day following 4th July 2007 if the dates are just a sequence of characters, and the implicit and explicit conversion of data types that wreaks havoc with the use of indexes for performance gain.
But what really gets my goat is the use of the Oracle LONG data type to store big lumps of text. As that seaside puppet anti-hero, Punch, could have said: “That’s the way [NOT] to do it!”
Today I have been looking at a badly-designed third party database that as the ability to duplicate records, which oddly does not matter to the application as both records have unique ids based on a sequence, but does matter to an interface table that uses the latest datestamp and the parent id. Ideally the vendor should fix the code, but for now support (my people) needs to remove the duplicate. And like all good support people we want to keep the original data just in case we make a mistake. It’s a shame that:
[sourcecode language=’sql’]Create TABLE MY_CPY_TABLE as
SELECT * from SILLY_LONG_TABLE
where ITEM_ID = 1234567;[/sourcecode]
Just does not work.
July 17th, 2007 at 7:01 am
Don’t even get me started on LONGs!
It’s not just third parties to be fair…even Oracle still uses them in the data dictionary at 10gR2…I’ve not got access to 11g but I’m not hopeful that they’ll have disappeared and I’ll still have to resort to faffing about to search the contents of such columns when I’m doing impact analysis.
July 17th, 2007 at 7:42 am
Taking nothing away from the valid point, could one option on the MY_CPY_TABLE table be to TO_CLOB the LONG?
July 17th, 2007 at 9:02 am
Jeff - indeed, I hate partition bounds being a long ! writing code to target partitions is a pain.
Dom - take away my rant would you?
A valid way forward for my case but still…
July 26th, 2007 at 6:04 am
CHARs are another major irritation in my current project. We have various CHAR(2) columns containing values like ‘F ‘ and ‘U ‘, so the code is full of TRIM() expressions (although you don’t need them for direct comparisons with literals, so “where somecol = ‘F’” would work, but you do for expressions, so “where upper(somecol) = ‘F’” would not). Why anybody would use a CHAR for any purpose, ever, is a mystery to me, and I would like the person responsible found and shot.