Log Buffer #47: a Carnival of the Vanities for DBAs
June 1st, 2007 – by Ronald Bradford

Welcome to the 47^th edition of Log Buffer, the weekly review of database blogs. No time to wait, lets read more about this week’s database blogging activities.

The PostgreSQL Conference for Users and Developers wrapped up this week and Peter Eisentraut gives us a review including the lightning talks and wrap-up session with a charity auction in PGCon Day 4. Meanwhile Alex Gorbachev is at Miracle Scotland Database Forum – Day One, sounds like from his post there is a lot of drinking and tasting going on.

Tim Hall in Schema Owners and Application Users… starts with “I was trying to explain to a colleague the concept of using application users, rather than logging directly into the schema owner.” As he mentions, it’s an introductory topic however his article gives us a detailed discussion of implementation.

An OTN blog consolidates a number of announcements for Oracle, PHP and OPAL in it’s concise publication New PHP Doc and Software. Plenty of reading links here.

Paul Moen of Pythian covers an important step in Oracle: Standby Automatic File Management follows on from his earlier posts in creating a standby database.

Roland Bouman in MySQL User Defined Function Repository has made an effort to build a central repository of the varied UDF’s that exist to extend MySQL SQL Function syntax. His follow up article The MySQL UDF Repository: lib_mysqludf_sys shows just how dangerous these can really be.

Partitioning, a key new feature in MySQL 5.1 can be incorrectly used as Sergey Petrunia has described in Partition pruning tip: Use comparisons of column, not of partitioning function value. While 5.1 is not GA yet, and maybe not for some time Kevin Burton was quick to comment about software functionality about to be deployed with this new functionality. Of course partitioning in MySQL is free, in Oracle it is not as Mathias describes in Rant about partitioning licensing. Not only do you have to purchase the top of the line Enterprise Edition at 40K, you have to purchase an additional option on top of that. I didn’t know that.

Peter Zaitsev of the MySQL Performance Blog discusses Predicting how long data load would take. This is a very common problem particularly in a direct changeover of a production system to MySQL, and as with Peters example of loading 1TB, this is no longer an uncommon problem with MySQL.

Jag Singh from Optimize Data Warehouse uses a practical approach in Data type validation using regular expressions with a procedural language before data loading into a database, or performed within a database. It’s important we don’t lose site that some things are best done not in the database.

Slashdot starts a flame war with aptly titled 8 Reasons Not To Use MySQL (And 5 To Adopt It) with a reference between Five Compelling Reasons to Use MySQL and Eight Sound Reasons Not to Use MySQL, both published by CIO magazine on the same day. MySQL Performance Blog, and Curt Monash were quick to respond with MySQL – to use or not to use and Whether or not to use MySQL respectively.

Q&A Webinar Part 4 – MySQL Cluster by Ivan Zoratti gives us 33 points of reference with MySQL Cluster. Being involved with MySQL Cluster, it’s interesting to read the types of questions people are asking. Following up, Jonathan Miller a seasoned veteran with MySQL Cluster shows in Just when you think you know something that even the experts can be stretched with the new MySQL Cluster Certification Exam. Likewise I was surprised when I passed the exam myself recently of it’s complexity.

Andy Campbell in his blog Oracle Stuff I Should Have Known demonstrates he has too much spare time on his hand in understanding terminal colors in a novel but interesting Adding some colour to SQL*Plus. Would have looked better with some blue!

One of the features I promote in MySQL, and that exists in other RDBMS’, but Oracle does not have is native Multirow Inserts. Robert Vollman however provides Oracle’s two verbose alternatives. Only knew about one of them myself. Still they are far from the simplicity other database products have.

Brian Duff of DuffBuff writes If I had five Oracle wishes, they would be…. We all have wish lists and Brian writes, “I was thinking about things in the Oracle-sphere that I’d love to see happen over the next few years”. I won’t spoil his wish list, I think point 4 is a good one.

Using the OUTPUT and OUTPUT INTO clauses in SQL Server 2005 describes the new features for retrieving values that were just inserted/updated/deleted by a DML statement. Vardecimal Storage Format in SP2 discusses another new feature in SQL Server 2005, the new Vardecimal format. The caveat being that this functionality is restricted to the Enterprise and Developer Editions. One using SQL Server should also know about the Swiss-knife features in SQL Server to help you.

Things to take care before installing SQL Server 2005 on Vista operating system or Windows Server 2008?. Nothing more to say here, perhaps you need to read if your a SQL Server user.

Data Geek Gal Beth touches on one of my pet peeves in Data Quality: As Meets the Needs of an Organization. If data has a structure, appropriate rules should be put in place to ensure this is maintained in the database. You will forever be doing data cleansing if you simply bother to consider this basic step later. Developers should really learn to be smarter in this area. One point not discussed is, “Where are the validation rules applied, the application or the database?”

View Larger Image

I’m not sure if a Log Buffer has had any images before, but I recently stopped at the Oracle HQ in Redwood city to snap this photograph. The irony of the experience not portrayed within the photograph was I worked for Oracle Corporation in the 1990’s but never visited the HQ, and while I was out taking this photo I was wearing a MySQL shirt. (No honking by any peak hour Oracle workers).

That’s it for this week, until next week. Clocks ticking Frank!