Re: Oracle using indexes

As Oracle Corp keeps saying, the Rule Based Optimizer is no longer being developed. There are dozens of execution paths that the Cost Based Optimizer can can take which the Rule Based Optimizer does not know about.

The frequency and precision of your Analyze depends on the nature of the data. If the data is very 'flat' you can analyze very rarely, and with a low precision. e,g, once per week for a couple of thousand rows.

If the date is 'rolling' due to datestamping or sequencing that you may need to analyze more frequently to make sure that Oracle knows the top end of the range is moving on.

If the data is very skewed (flags with 99 Yes's for each No) then you need to include a histogram on that column with the analyze (and write code that does not use bind variables against that column).

etc. etc. etc.

Rule 1 - always try to get away with the minimum possible analysis.

Rule 2 - it's nicer to avoid using hints, but don't give yourself headaches overdoing it.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Christ Follower wrote in message <88221o$ukv$1_at_nnrp1.deja.com>...
>I did some reading in last few days and found this "driving table"
>thing in a book. As Brian mentioned, the last (2nd) table in the FROM
>clause is the "driving table". The book says that Oracle performs full
>scan on the driving table. It seems true in most cases. However, I did
>run into a real world scenario (can't find that query any more) that
>Oracle performs an INDEX SCAN on the first table and ACCESS BY ROWID
>(with index scan) on the 2nd table.
>
>It seems to me that Oracle optimizer has a mind of its own. Running
>explain plan is the only sure way to tune a query.
>
>I am considering to change our database to be cost-based. How often do
>I need to analyze tables and indexes? Any suggestions?
>
Received on Fri Feb 11 2000 - 16:36:24 CST