ramblings on PHP, SQL, the web, politics, ultimate frisbee and what else is on in my life
back

One more thing on hints

Yes hints are evil, because they circumvent the entire beauty of runtime query optimizers. However if you really do know whats best and know that your query optimizer really does not know whats best, even if you make sure the table stats etc are all in order you can decide that "hardcoding" the query execution plan is best.

Now SQL tuning books devote entire chapters on how to add redundant where conditions, redundant additions and multiplications. The last such chapter I have read was in "SQL tuning". There it gave a tip on how to control the order in which joins are done using the following trick:

... AND T1.Key2_ID=T2.Key2_ID
AND T1.Key3_ID+0*T2.Key2_ID=T3.Key3_ID ...

Now this is getting insane! It seems obvious that there is a demand for being able to restrict or even hardcode the entire query execution plan! However the SQL standard is just concerned about providing ways to express set operations that get you the data you want. Its not really concerned with performance. This is why indexes are not covered in the SQL standard.

However the problem is that the reality is that people do this kind of stuff and database vendors come up with all sorts of foobar syntax options for this. And DBA's come up with all sorts of ways, like shown above, to get a handle on the things they do not have an explicit syntax for yet. However these often rely on the optimizer not being able to see that the statements are redundant. So this is obviously not portable, not even across different versions of the same RDBMS.

It therefore seems obvious to me that either the SQL standard needs to get with the programm and expand its reach to allow people to allows specify their access plans or that RDBMS vendors should work together to add a standard independent of the official ANSI SQL standard. Maybe this could be another opportunity for the open source database consortium?

While speaking about the osdb consortium there is also a planet now that aggregates blogs from various open source database projects.

Comments



Re: One more thing on hints

I wonder if it would help folks to talk a bit about relational algebra?