Re: Object Oriented Wrapper classes to SQL statements

Clifford,

Yes, I hope Rachel has understood what she needs, too.

> Until the mid-80's, almost no-one was using cost-based optimisers, they
> were all using heuristics - amazing! Once one starts doing actual cost
> estimates and gathering enough statistics to support them, it's great
> how often you can come up with the actual best plan - some of the chosen
> best query plans are surprisingly different from what a human would even
> be likely to think of...
>

Well, I'm speaking from the depths of ignorance here, but if you'll bear with me...

Rdb was launched by Digital Equipment Corp. (DEC) in the early '80s. It had a cost based optimizer
right from version one. That didn't have much impact, at the beginning because physical design was
pretty much limited to table and index design, with maybe a tiny bit more. It wasn't until version 3.1
some time around 1985, that the optimizer began to make a difference in terms of how useful the product was.

DEC Rdb was sold to Oracle in 1994.

I've studied the internals of Rdb (a few years back, now), but not very much of the optimizer. I was busy learning some of the lower level data structures. So some of this is guesswork, for which I apologize.

I think, although I could be wrong, that heuristics played a vital role in the optimizer's behavior. First off, there were two parts to the optimizer, the strategy generator, and the strategy evaluator. I believe the strategy generator used heuristics to come up with strategies that might be worth evaluating. And of course the strategy evaluator used clever ways of estimating what the probable cost of a strategy might be. The value of using an index might depend on something called the "fan out factor", as well as the underlying cardinalities.

Anyway, DEC Rdb's optimizer was famous in speed circles for a few years in the early '90s. I'm told, third hand, that Oracle decided to build the CBO for Oracle's RDBMS after looking at Rdb's optimizer for a while. I think they still have two largely separate engineering teams developing Rdb and "regular Oracle", but there must be some cross pollination going on.

For a while, I started thinking about writing up a comparison between Rdb and Oracle from various points of comparison, like treatment of NULLS, or data compression, or metadata schema structure, etc. etc. My problem is fitting this into the ongoing necessity of earning a living, and living a life. Another problem is that my knowledge is kind of light in some of the areas. Optimizer internals is one of the areas where I wish I knew a little more than I do.

I wonder if the people who are building new RDBMS systems would benefit from such a comparison. But maybe the successes and omissions of the past just aren't relevant in this field.

--
Regards,
    David Cressey
    www.dcressey.com
"Clifford Heath" <cjh_nospam_at_osa.com.au> wrote in message
news:3C1838EA.61E3857D_at_osa.com.au...


> David Cressey wrote:

> >

> > Clifford,

> > Or maybe I shouldn't be surprised.

>

> I've never seen Rdb, but there aren't many logical choices for such a


syntax.


> It has to be prefix-notation and you can put the function-name outside or


> inside the brackets (vis C/Pascal/etc style vs Lisp style).

>

> I hope it's now clear to Rachel what objects she must create... Any

program


> that must do algebraic manipulation creates an object class for each


operator


> type, whether using an O-O language or not.


>

> > That output is after the optimizer has done reworking the plan,

> > and decided on the cheapest path.  That, in turn can be used to

> > tune the query, or to provide hints.

>

> I love the graphical output of Microsoft SQL Server's Query analyser.

> You can float the mouse over any node or any connection and see the

> statistics and cost estimates for that item. The optimiser itself has

> some weaknesses (LIKE throws it *right* off!), though generally it's

> been fine.

>

> > BTW, Rdb had (still has)  a really great optimizer.

>

> --

> Clifford Heath, ManageSoft Corporation