When I was a kid Christmas was the most exciting day of the year. I’d rip open the packages to see if I finally owned what I’d spent months hoping for. In technology we don’t have a scheduled yearly Christmas, but occasionally we get exciting new technology. And I can dream of the great things I’d like to see. Here’s my wish list of what I’d like to see databases do:

Automatic Transactions

Many times when testing or troubleshooting I need to see the history of record changes. I wish the following SQL were valid:

SELECT * FROM item_master

WHERE item_master.item_id = 475

FROM SYSDATE – 1 TO SYSDATE

Some of you are going to argue, “What’s wrong with creating a trigger and writing changes to a transactional table?”. There isn’t anything wrong with that approach, but it’s a time waster: create the trigger, create the table, maintain the table when fields are added/dropped, create reports on the transactional table, blah, blah. Millions of databases around the world face the same problem and apply the same generic solutions. Generic problems should be solved in one place, not millions of places.

Automatic Indexing

In my opinion discussions about which database is fastest is usually a moot point. In the practical world the fastest database is the one with the best indexing. (And sufficient IO capacity) Most databases are severely under indexed. Above a certain cost threshold databases should keep statistics on queries and automatically create indexes for costly or common queries. (Probably a combination of the two factors: a very common query with low cost deserves and index as much as a uncommon query with high cost) The RDBMS would also need to track the cost savings of the new indexes and drop them if they no longer provide sufficient savings. There would need to be a cap on index creation if a table has too many indexes (i.e. if the indexes are interfering with updates and inserts) Index creation would need to be load aware so it wouldn’t kick off during the busiest time of the day. . . as you can see this would not be trivial.

Automatic indexing would not replace manually defined (i.e. permanent) indexes, but it would be awesome. Especially for purchased applictaions. Purchased applications are chronically mal-indexed (one reason is that no one customer uses the application in exactly the same way) and no one at the customer sites knows what goes on under the hood to fix it. Indexing problems in purchased applications are rarely resolved in a timely manner (or at all).

Appeal to MySQL Developers

I wish these features were part of Oracle or SQL Server, but other than natural product evolution I don’t think the giants have much innovation left in them. If we’re going to see innovation in databases it has to come from somewhere else.

Jeff’s Book Recommendations:

The Mythical Man-Month is the software engineering classic. This book should be mandatory reading for the professional programmer.

Share this: Twitter

Facebook

Like this: Like Loading... Related