I know that tune

Certain solitary acts give me great pleasure. Oddly, I consider the intellectual challenge of getting some of my customers' queries to fly a solitary act, it's me against the database. Or perhaps me against the customer.

I have been working on a set of poorly performing queries that have been causing a customer a lot of grief. They are typically characterised by joining a single value from a 15,000 row table to the partition key of a large partitioned table and then aggregating a large percentage of the rows from a single partition of the large table. The query plans showed that a problem with the estimate of rows returned from the first table. The pragmatic approach was to replace the existing indexes with ones that better suited the queries; the new cardinality estimates were spot on for the data set; the original 3 hour query runs in five minutes without changing the SQL, the customer seems happy, so the change was implemented.

The next day, some users complain that their queries (not the ones I have just tuned) have become slow. One of the fundamental problems with ad-hoc query tools is that it is next to impossible to test every conceivable query before implementing a database change, we try hard to cover most bases but something will go wrong; in the end it comes down to "do I get more benefit from making the change than not making it". To be honest I was surprised that queries were slowing, but looking at the plan and it's convoluted mess of nested loops, bitmap joins and goodness knows what else I could see that my indexes where being used too late. I was loath to revert to the original index scheme as it was plainly wrong-headed but the query degradation was not acceptable. Then the reason for the bad plan hit home, functions on three of the predicates supplied to query prevented the use of the the bitmap indexes on the fact table. to_char(department_no,'000')=7 is perhaps not the smartest way of coding things especially since department_no and 7 are both numbers. Fixed up the predicates so there are no functions or data type conversions and it flies.

Which reminds me, the original queries, although now running much better, could be reduced to a few seconds by changing the question asked - sometimes we get too hung up about squeezing the last ounce of speed (mixed metaphor alert!) from a system when we should step back and think, what is it we want to do?

I had thought I would save you from more of the saga of the the garden; but one more installment - we won!