Re: full-scan vs index for "small" tables

On 6/29/06, Cary Millsap <cary.millsap_at_hotsos.com> wrote:
> > > There's just this inconvenience of
> > > having to inform the Oracle query optimizer about your data, the
> > > process for doing which involves so many complicated decisions that
> > > probably few people will really ever do it very well.
> >
> >
> > Do you see any future developments in this area besides the usual "we
> > won't need dbas in the next release, it's all self-tuning and self-
> > administered"?
>
> I don't know of any. I think it's a fertile field for some Oracle
> partner to invent something fantastically clever and helpful.

I'd say that there was considerable scope for the approach Oracle are taking with SQL Profiles to contribute nicely to the class of problem that Mark mentioned. In essence looking for more accurate stats at the sql statement level rather than at the object level, how you get away from the considerable cost of doing all this extra work I don't know - doing it at runtime for a statement with an estimated execution time measured in hours is probably fine, for estimated sub second queries this is not so good.

perhaps there is also mileage in extending the histogram idea to correlation coefficients etc..

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info
--
http://www.freelists.org/webpage/oracle-l