1

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

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • I think you're gonna have to do the hard yards and read the docs for each of those DBMSs. – Jeffrey Kemp Mar 11 '11 at 02:09
  • @Jeffrey: I probably will. But before I do, I checked if there is a big expert around here :-) – Lukas Eder Mar 11 '11 at 06:52
  • agreed, there's no harm in asking, no need to reinvent the wheel. – Jeffrey Kemp Mar 11 '11 at 08:04
  • What would be the point of the list? is this purely academic or is there a programming goal to this? – Stephanie Page Jan 24 '12 at 14:58
  • @StephaniePage: At the time I asked this question, I was looking for the best way to support SQL hints in the [jOOQ API](http://www.jooq.org). From the answers, I couldn't find a good solution so far, so only [Oracle-style hints are supported](http://www.jooq.org/manual/ADVANCED/OracleHints/). In that way, yes, there is a programming goal in this: Both API design and implementation of hints in jOOQ – Lukas Eder Jan 24 '12 at 15:06

3 Answers3

2

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.

Stephanie Page
  • 3,875
  • 1
  • 18
  • 22
  • That's very interesting input. In my case, it's really about the syntactical impact of hints if there are any other databases that support such a thing. I'll specify that in the question. But your input might actually be helping out in an entirely different problem I'm having in an entirely different project! :-) Do you have a documentation link to SQLProfiles and plan baselines? – Lukas Eder Mar 10 '11 at 18:56
  • Sure Lukas, no problem. This is a good starting point. –  Mar 10 '11 at 21:00
  • Thank you so much! This is going to help me a lot with some tough performance issues we're having since recently! – Lukas Eder Mar 10 '11 at 21:33
1

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.

jachguate
  • 16,976
  • 3
  • 57
  • 98
  • Hmm, yeah interesting... So the syntax is entirely different again in SQL server – Lukas Eder Mar 10 '11 at 18:59
  • @Lukas: Hints are sort of vendor-specific features, AFAIK the sql standard does not require anything on this field, maybe a recent version I don't know about, so you can't bet on any standard way to do this even in 2 different DBMS's, much less in a wider range of it. – jachguate Mar 10 '11 at 19:17
  • I know. But usually, these RDBMS designers copy from one another, mostly because they think that a migration from the other one will be easier that way (e.g. the `DUAL`, or `SYSIBM.DUMMY1` tables, the `SYS`, or `INFORMATION_SCHEMA` schemata, etc.) – Lukas Eder Mar 10 '11 at 19:20
  • @Lukas, I disagree, maybe from some system to another specifically, but you just can't generalize that way on the broad variety of vendors. IMHO is, in fact, the sql standard the one which lead, at some extent, to the nowadays query language similarity and ease to migration from one to another, but if you start to look deeper, you'll find lot's of differences. Just take a look on the trigger/sp language and semantics between 4 or 5 different vendors, you'll realize that migration is not too easy at the end of the day. – jachguate Mar 10 '11 at 19:54
  • Yes I agree with you. It's not simple at all. But I know many developers that try to develop against Derby or HSQLDB and then run a productive system on Oracle. Just to save money. And Derby or HSQLDB somehow try to make their RDBMS look like DB2 or Oracle, respectively... Don't get me wrong: I think it's a very bad idea to just "switch" databases like that... – Lukas Eder Mar 10 '11 at 19:58
1

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.

Michael Broughton
  • 4,045
  • 14
  • 12
  • That's certainly true. But I have had a request in my database abstraction tool jOOQ http://jooq.sourceforge.net to support hints. Now I'm trying to find a way of **generically** modelling any syntax. So the choice of whether or not to use hints are entirely up to the jOOQ users... – Lukas Eder Mar 10 '11 at 19:23
  • 2
    Hints are directives and Oracle has to obey them unless they (the hints) are invalid, incompatible with other hints or not applicable due to query transformation. – Ronnis Mar 10 '11 at 22:06
  • And since you cannot control the query transformation, especially with the expanded path investigations ORacle uses since 10g when the cost based query transformation engine was implemented .... not to mention that Oracle even supplies a hidden parameter _oracle_ignore_hints. So yes they are directives, but my contention stands: they are a very small part of the tuner's toolkit. – Michael Broughton Mar 11 '11 at 14:06