Joe Celko reviews “SQL Performance Explained”

This is just a short note to point you to Joe Celko’s review of “SQL Performance Explained”. Yes, Joe Celko read my book :)

Here is the full review.

And remember: we are offering free shipping to 150+ countries on http://sql-performance-explained.com/.


Choosing NoSQL For The Right Reason

Observing the NoSQL hype through the eyes of an SQL performance consultant is an interesting experience. It is, however, very hard to write about NoSQL because there are so many forms of it. After all, NoSQL is nothing more than a marketing term. A marketing term that works pretty well because it goes to the heart of many developers that struggle with SQL every day.

My unrepresentative observation is that NoSQL is often taken for performance reasons. Probably because SQL performance problems are an everyday experience. NoSQL, on the other hand, is known to “scale well”. However, performance is often (more...)

Finding the Best Match With a Top-N Query

There was an interesting index related performance problem on Stack Overflow recently. The problem was to check an input string against a table that holds about 2000 prefix patterns (e.g., LIKE 'xyz%'). A fast select is needed that returns one row if any pattern matches the input string, or no row otherwise.

I believe my solution is worth a few extra words to explain it in more detail. Even though it’s a perfect fit for Use The Index, Luke it’s a little early to put it as an exercise there. It is, however, a very good complement (more...)

Use The Index, Luke!

Today, I’d like to introduce my new Web-Book Use The Index, Luke. Use The Index, Luke is a guide to Oracle database performance for developers.

Use The Index, Luke! A Guide to SQL Performance for Developers

 

I started the book because I noticed that almost every existing book or online document on that topic is stuffed with plenty of information that is not relevant to developers.

Although a certain know-how of the database’s internals is required to get the best performance, Use The Index, Luke keeps that information at a minimum and presents it from a developers perspective.

The book is supplemented by the Ask Use The Index, Luke community (more...)

Analytic Top-N Queries

One of the more advanced tricks I like to exploit are analytic Top-N queries. Although I am using them for quite a while, I recently discovered a “limitation” that I was not aware of. Actually—to be honest—it’s not a limitation; it is a missing optimization in a rarely used feature that can easily worked around. I must admit that I ask for quite a lot in that case.

The article starts with a general introduction into Top-N queries, applies that technique to analytic queries and explains the case where I miss an optimization. But is is really worth all that (more...)

About Software Quality

You might have noticed that this blog never had a kickoff post that explains what this blog is about. Time has come to spend some words on the topic of software quality—as I see it—and how this blog covers some aspects of software quality.

First of all, I’d like to introduce the two most frequently considered software quality aspects:

Correctness

The software must produce correct results. There is no point in using software, which produces results that you need to verify before you can trust them.

Usability

If you know the task you want to perform, the software should make (more...)

Analyze That

As Jonathan Lewis commented on my article Clustering Factor: Row Migrtion’s Victim, there is even more to say about the difference between the good, old, and deprecated ANALYZE statement and the DBMS_STATS package. Jonathan mentioned that the CBO is using the CHAIN_CNT value in the statistics, if present, and suggested to try my “trapQL” after analyzing the base table in the old fashion.

For easy C&P I the complete new script to try:

CREATE TABLE row_mig1 (
  a CHAR(2000),
  b CHAR(2000),
  c CHAR(2000),
  x NUMBER      NOT NULL,
  filter NUMBER NOT NULL,
  CONSTRAINT row_mig1_pk PRIMARY KEY (x)
) ENABLE ROW  (more...)

Working for the Community: hatools 2.14

This post is about my current work on release 2.14 of my most beloved OSS project: hatools. Just some observations, objectives, rants and an advertisement.

Scope

My scope for this release was rather limited: implement -v switches to hatimerun and halockrun to make them more communicative.

The reason for that is quite simple; hatools will be easier to learn and use if they talk more. I also noticed that there are two “new” tools in the ever growing locking tool zoo—not to mention flock(1)—that talk more than hatools:

My original (more...)

Clustering Factor: Row Migration’s Victim

This article describes the effects of a high row migration rate on the clustering factor and the optimizer’s ability to select the best execution plan.

In my previous article—Row Migration and Row Movement—I have demonstrated that the “insert empty, update everything” anti-pattern can lead to 100% row migration. This article continues the research on row migration and unveils surprising effects on the clustering factor. To be precise, the clustering factor can become completely bogus in presence of a very high row migration rate. Once the clustering factor is “wrong”, it’s just a finger exercise to construct an optimizer (more...)

Row Migration and Row Movement

The Oracle database knows three distinct processes that are easily mixed up: Row Chaining, Row Migration and Row Movement.

Luckily all three are well described in excellent articles: The Secrets of Oracle Row Chaining and Migration and Row Movement in Oracle.

For the impatient, I provide some very short definitions:

Row Chaining
Distribution of a single table row across multiple data blocks.
Row Migration
Relocation of an entire table row to a new place, without updating the indexes.
Row Movement
Relocation of an entire table row to a new place and updating the indexes.

This article was inspired by the (more...)