"Mark Woodward" <[EMAIL PROTECTED]> writes: > The analyzer, at least the last time I checked, does not recognize these > relationships.
The analyzer is imperfect but arguing from any particular imperfection is weak because someone will just come back and say we should work on that problem -- though I note nobody's actually volunteering to do so whereas they appear to be for hints. I think the stronger argument is to say that there are some statistical properties that the analyzer _cannot_ be expected to figure out. Either because a) they're simply too complex to ever expect to be able to find automatically, b) too expensive to make it worthwhile in the general case, or c) because of some operational issue such as the data changing frequently enough that the analyzes that would be necessary to keep the statistics up to date would become excessively expensive or even be impossible to perform rapidly enough. The people arguing that hints themselves are of negative benefit are taking the argument far too far. I've never heard an Oracle DBA gripe about having to fix hints on an upgrade; they're usually the first ones to suggest hinting a poorly written query. In fact Oracle is going in the opposite direction of even relying on hints internally. Its plan stability feature depends on generating and storing hints internally associated with every query. The argument against hints is usually that the effort would be better spent elsewhere, not that hints are inherently a bad idea. We already have enable_* parameters and they are absolutely necessary for testing and experimenting to understand whether the planner is incorrect and where it has gone wrong. Hints are just a more precisely targeted version of these. There have been plenty of instances on this list where people posted 20-30 line query plans with several joins of each type where the enable_* parameters were too coarse grained to use effectively. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
