Thursday, August 29, 2013

Nothing Ever Always

Sometimes I get really frustrated by the things I used to know, but somehow forgot.
For instance, I USED to know that, in a relational database, nothing is ever always true.

Perhaps an example will be illuminating.

Indexes - we are told that they help reads and hurt writes.  I suppose that's true, on some level, because of the processing the RDBMS has to do in order to perform the actual operation of writing to the table, and updating the indexes.

However, I had a situation today wherein I found myself creating an index to DRAMATICALLY speed up a write operation.

Both for a delete where and an update where, adding the index cut the execution (in DB2, measured in timerons) by 60 - 80 %.  Wow. 

Honestly, I should have known this.  I've been doing this for a while. I get used to pat ideas and the looking at a problem in a very particular way, or on a small set of data.

Bottom line, in relational databases, nothing ever always.

Creating Hash Values for Strings In MSSQL, DB2 UDB for LUW, and Oracle

It will often occur that we need to search a large character string in a relational database, something like an email address, postal addres...