Oracle-style execution hints

Question

When you write rather complex SQL for Oracle, sooner or later you will have to apply the odd execution hint because Oracle can't seem to figure out the "best" execution plan itself.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/hintsref.htm

Now this is certainly not a SQL standard. But still, I'm wondering, are there any other RDBMS that support these kinds of hints, and I really mean hints that are "embedded" in SQL? Are they similar, syntactically (i.e. also placed between the SELECTkeyword and the first selected COLUMN)? Do you know of a general documentation page comparing hints in various RDBMS?

N.B: I'm mostly interested in these RDBMS: Postgres, MySQL, HSQLDB, H2, Derby, SQLite, DB2, Sybase, SQL Server

Answer 1

I know that in db2 the plans are made fixed in some way, not how. In Oracle 11g there are other options besides adding hints to queries. These are SQLProfiles and SQLPlan Baselines, both very powerful. I just finished a performance tuning project where we did not add even a single hint to the code, on the contrary.

Answer 2

You can add Oprimizer Hints to any SQL Server Query

The PLAN clause allows you to define a particular plan to your query in Firebird.

AFAIK, nothing standard nor close to it, but in general, you can do this in a lot of RDBM's, but not all.

Answer 3

I'd also remind you, if you are making some sort of comparison with other DB platforms, that hints in Oracle are entirely non-binding. Which is to say that Oracle is free to disregard your hint if it so chooses.

Hints can be helpfull but I find that I rarely use them anymore - at least not compared to the past when I was working with the older optimizers in earlier Oracle versions. Back then hints were much more of a staple to performance tuning than they are now.