Self Joins and optimization

In the "interpolation" thread, Brian has been expounding on the idea that
he can discover algorithms that he knows to be correct, and that outperform
anything an optimizer can generate. He's mentioned "self joins" and the
idea that he can get a job doen with only one read, where the automatic
solution generated in response to declarative code generates multiple passes
through the data.

My experience leads me to the opposite conclusion from that.

Here's the questions I'd like the newsgroup to consider:

What, if any, algorithms that are demonstrably correct are going to be
overlooked by the optimizer, although discoverable by a human coder? Why
would an optimizer fail to discover the possibility of eliminating
self-joins? Are there any undecidability issues here?

Aside:

I know a little about the DEC Rdb/VMS optimizer. Less than that about the
Oracle CBO, although the Oracle CBO appears to have been conceptually
derived from the Rdb/VMS optimizer. Much less than that about the Oracle
RBO as of 1994. The RBO appears to have been an optimizer in name only.

While I've got a lot of positive opinions about various features of Oracle,
the RBO was not one of those features. Shortly after my cutover to Oracle,
one of the tech types told me: "All the cool people use hints."

After looking at it in as much depth as I cared to, my conclusion (which I
kept to myself) was: "No. All the really cool people find a DBMS that
doesn't require hints to do a good job."

End of aside.







.

Relevant Pages

  • Re: Self Joins and optimization
    ... he can discover algorithms that he knows to be correct, ... anything an optimizer can generate. ... What, if any, algorithms that are demonstrably correct are going to be ... Oracle CBO, although the Oracle CBO appears to have been conceptually ...
    (comp.databases.theory)
  • Re: Why Statistics doesnt match reponse time?
    ... > four patches since then that have substantially improved the optimizer. ... Patches would not help him here. ... His hint causes the optimizer to do the ... As for the Oracle 9.2.0.6, the latest version, the optimizer was improved, ...
    (comp.databases.oracle.server)
  • Re: Oracle Hint Behavior
    ... Oracle would treat them as only "suggestions" but I thought in 10g they ... like to be able to explain why the hint is being ignored now. ... There is an interesting way to influence the optimizer without the use ... of DEC Rdb right after Oracle acquired Rdb back in '94/'95 timeframe. ...
    (comp.databases.oracle.server)
  • Re: Oracle Optmizer does not use the right index (Oracle 9.2.0.6)
    ... The fact remains that in the situation I have described, the Oracle ... Optimizer loses track of the first of the indexes (depending on the ... Hinted, with only the table name/alias, both indexes in place, T2_IND1 ... Oracle to guide the execution plan so that it does not prematurely ...
    (comp.databases.oracle.server)
  • Re: Migrating to cost based optimizer
    ... Our applications are supporting Oracle 8.1.7 and Oracle 9, ... customers with varying level of activity. ... We use the rule based optimizer to ensure the stability of the sql ... Don't just throw CBO into production without testing as thoroughly as possible. ...
    (comp.databases.oracle.server)