Re: Cost based optimizer

Cost based optimization works much like a chess game programme. The optimizer assesses the possible ways of doing things based on some sort of internal table, that taxes the alternatives according to well defined rules. In this particular case, the optimizer usually tries to choose the alternative that aparently transfers less blocks between the disks and the SGA buffer pool. Space occupation and block distribution is what the collected statistics that help make the optimizer's decisions are all about.

But, despite of knowing all about the size of our data structures, the optimizer is totally blind to our data's semantic rules. It doesn't know, for instance, that almost all soldiers in an army facility are males, and thus can go for scanning the whole of a personnel data table looking for a female casualty, when we have an index for sex that could speed up the query. That's why we'll always need hints, although their use should be rare.

A Ustby <austby_at_centuryinter.net> escreveu no artigo <E0vpF3.K83_at_boss.cs.ohiou.edu>...
> I just got back from OOW and was reading a paper titled "Tuning Oracle
 SQL"
> (paper #171).
>
> It talked about adding "hints" to SQL statements that were using the Cost
 

> based optimizer.
>
> I thought the whole reason for using a cost based optimizer vs rules
 based was
> that the optimizer was suppose to be smart and determine the best path to
 get
> to the data (and thus making the coder of the SQL not have to know the
> physical DB design - so that when changes are made to the physical
 structure,
> you don't have to go out and change code "hints" or special ordering in
 the
> where clause).
>
> So if you add hints to SQL statements that use cost based optimization
 (and
> collect statistics), what is the advantage of cost based over rules based
 

> optimizer ????????????
>
> Thanks to anyone who can give me insight on this subject.
>
> Art
>
>
Received on Sun Nov 17 1996 - 00:00:00 CET