December 29, 2007
Optimizer geekdom
I’ve been a DBMS analyst since before there were cost-based optimizers or, for that matter, a whole lot of relational DBMS. And in the 20 years that optimizers have been around, I’ve never fully understood why they’re so simple-minded. Even so, I think they’re pretty cool, as per the fanboyish discussion in this 2004 Computerworld column.
So I’m delighted to see that the Oracle folks have started a hardcore blog on optimizer details. If you want to get a sense of how smart a leading DBMS is or isn’t, I encourage you to check it out.
Keep getting great research about database management and related technologies. Sign up today! No hassle, no spam!
Comments
One Response to “Optimizer geekdom”
Leave a Reply
According to the great theory of RDBMS’s, if you have two queries that are written differently in the query language but have the same semantics, the optimizer should execute the same plan for both. With Oracle, this is far from true, and if you rephrase a query the “right” way, you can get huge performance increases. So you have to be an Oracle wizard to know that, which is one reason that there are Oracle wizards for hire, some at amazing hourly rates. I have been told about a product you can buy that takes Oracle queries and outputs equivalent Oracle queries that are faster; if their query optimizer were ideal, there would be no use for such a thing. I have heard that IBM’s DB/2 has a much better optimizer in this regard, but this is all just from anecdotes.