Wednesday, July 27, 2011

Analytic functions

A life history of unforgettable moments of clarity:

1983: Jesus
1994: Special relativity
2011: Analytic functions

I've known for a long time that I really should make use of analytic database functions. I think I've RTFMed a half-dozen times over the years, sometimes fumbling through an example or two, but never really getting them. Tom Kyte's appearance at the Dayton-Oracle User Group finally made it clear to me.

Fortunately, (a different delivery of) Tom's talk was recorded. Go, watch it! You don't have to live without analytic functions one more day!

In my case, I'd been tormented by questions about "the most recent record where..." in a MySQL database; the complex classic SQL queries I was using performed horribly in MySQL. MySQL doesn't have analytic functions, so I converted the reporting database to PostgreSQL. It paid off enormously; analytic functions cut several queries from multiple days to a few minutes. I am a Believer.

No comments: