Re: Hints - experiences from other rdbms

>
> Rather than telling the planner what to do or not to do, I'd much rather
> have hints that give the planner more information about the tables and
> quals involved in the query. A typical source of bad plans is when the
> planner gets its cost estimates wrong. So rather than telling the
> planner to use a nested loop join for "a INNER JOIN b ON a.id = b.id",
> the user could tell the planner that there are only 10 rows that match
> the "a.id = b.id" qual.

Instead of gathering statistics for all possible joins ( and join orders) , in Oracle there is a functionality that can be switched on where the optimizer is given cardinality feedback for the chosen plans, so it can choose another plan if the same statement comes around.

Secondly, there is functionality to insert a hint into an SQL statement. That's very good for COTS apps where the statement can't be altered. Now I know that there's relatively not much COTS for the Postgresql, ( hence arguments like 'we should not implement hints so we're forcing people to solve the underlying problem' ), but as Postgresql will replace oracle in the lower end of the market, this functionality is usefull.