Log Buffer #122: A Carnival of the Vanities for DBAs

Welcome to the 122^nd edition of Log Buffer, the weekly review of database blogs.

Let’s start with MySQL. As you probably know, the Americans were at the polls this week. In keeping with this, Keith Murphy is polling for opinion on the question: MySQL on Debian or Ubuntu? The conversation is here; the poll is on Keith’s Diamond Notes.

A quick note by Sun CEO Jonathan Schwartz on how, with the outcome of those other polls, change has come to America, in the form of its new president and his choice of DBMS. Okay, it’s probably not his choice, but the association must be appealing.

Duleepa “Dups” Wijayawardhana also has a big new job. He’s taking over from Jay Pipes as MYSQL Community Manager, and his advice to himself is, Don’t Panic!. As a compatriot of Dups, I say: congratulations, eh!

Can we create a cross tab in MySQL? Yes we can! Arnold Daniels shows us how. Sheeri and Corey show some more.

On so many trails … so little time, Pabloj elucidates new syntax (the new WITH ROLLUP) for the old challenge of adding a “Total” row at the end of your tabular output.

Brian Aker was barking up a nearby tree . . .  or a nearby column, at any rate. Here’s his item on column stores in Drizzle. He writes, “I believe the second most important decision we will make long term for engines is going to be which column store we pick up on. I suspect we might even need two.” But which two? The discussion sheds some light on this.

Neelakanth Nadgir gives his introduction to the Innodb IO subsystem, which includes links to improvements to some of that engine’s limitations.

Shlomi Noach of code.openark.org also spent time under the hood. He offers the first of a series of posts on two storage engines; different plans, which aims to answer the question: “Can an execution plan change for different storage engines?” In this road test, MyISAM and InnoDB.

Let’s look at SQL Server, starting with a post that neatly bridges the gap between it and MySQL: Emulating MySQL’s GROUP_CONCAT() Function in SQL Server 2005, on Rational Relational. He writes, “There are tons of articles and blog posts about [this] on the web. Some give you the code for special UDFs and some give you solutions that use cursors. A 3 year old post by Xaprb suggests using local variables.  . . .  I suggest a different approach based on a ‘hack’ which utilizes the new xml functions in SQL Server 2005 to concatenate the values in a column.”

Aaron Bertrand published a very well liked item offering his stored procedure “best practices” checklist. Very useful and lots of worthwhile discussion too! Alexander Kuznetsov responded with his piece asserting that assigning multiple variables with one SELECT works faster.

Over on SQLCat Blogs, Kevin Cox and Xiaoyu Li in turn give their best practices for integrated full-text search in SQL 2008, which includes links to tutorials and other documentation.

Even following those best practices cannot keep you from making mistakes. Chris Shaw knows that, because he has made them. And he knows that you have too. Chris has started a game of blog tag, which he gets going by confessing two of his mistakes, before tagging Brent Ozar and Jason Massie. Unless I’m mistaken, this could be the longest blog tag ever.

Craig Freedman couldn’t find a thorough answer to the question, “What is the difference between sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats?”. So he published one himself. And for that, we should say, thank you, Craig.

Thanks also to Rob’s SQL Server Blog for its lucid explanation of SQL Server 2005 multi-site clustering with Windows Server 2008.

On the Oracle side of the aisle, Francisco Munoz Alvarez was also doing the cluster thing. He published the first of a series on Oracle RAC 11g and Oracle Enterprise Linux 5. This one is a walk-through of the installation process.

Gerald Venzl writes that he has found a good tool for Data Modeling: Oracle SqlDeveloper Data Modeling. He has put it through its paces and has the results for us.

Ken Downs, the Database Programmer, reminds us that, if we’re going to use denormalized databases, “This requires a very well thought out strategy to make sure these repeated values are always correct.” He outlines several strategies in his item, Keeping Denormalized Values Correct.

Norms, styles, and attitudes. How do they relate? So Bradd Piontek ponders in his blog, Don’t Cramp My (SQL) Style, where “style” is how we format our SQL and PL/SQL. Most everyone is quite attached to their own style, and some insist that there are de facto styles that ought to be observed. Brad’s attitude is more adaptable: “It is my job as the DBA to work with the developers, not push my personal style down their throats. It is my belief that DBAs and developers need to work hand in hand in a collaborative way. If I ask someone to change something, I better have a better reason than ‘It is not standard’.”

Here’s a quick update from a Log Buffer alumnus. Lisa Dobson is back on her Oracle Newbies Blog with her latest news. Not only does she still exist, but also Lisa’s in the running for the UKOUG Board of Directors. Good luck, Lisa!

And that is all for now. Please return after an interval of approximately 168 hours for another one, and keep doing so. Please get involved, too. Leave your favourite blogs from this week in the comments, or even do a Log Buffer yourself. Read the guidelines, send me an email, and we’ll get started.

Till next time!