Re: Hints proposal

>What is it about hinting that makes it so easily breakable with new versions?  I >don't have any experience with Oracle, so I'm not sure how they screwed logic like >this up.  

I don't have a ton of experience with oracle either, mostly DB2, MSSQL and PG. So, I thought I'd do some googling, and maybe others might find this useful info.

http://asktom.oracle.com/pls/ask/f?p=4950:8:2177642270773127589::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:7038986332061

Interesting quote: "In Oracle Applications development (11i apps - HR, CRM, etc) Hints are strictly forbidden. We find the underlying cause and fix it." and
"Hints -- only useful if you are in RBO and you want to make use of an access
path."

Maybe because I haven't had access to hints before, I've never been tempted to use them. However, I can't remember having to re-write SQL due to a PG upgrade either.

Oh, and if you want to see everything that gets broken/depreciated with new versions, just take a look at oracle's release notes for 9i and 10g. I particularly dislike how they rename stuff for no apparent reason (e.g. NOPARALLEL is now NO_PARALLEL - http://www.oracle-base.com/articles/10g/PerformanceTuningEnhancements10g.php)

At the very least, I agree it is important to separate the query (what data do I want) from performance options (config, indexes, hints, etc). The data I want doesn't change unless I have a functionality/requirements change. So I'd prefer not to have to go back and change that code just to tweak performance. In addition, this creates an even bigger mess for dynamic queries. I would be much more likely to consider hints if they could be applied separately.

- Bucky