If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Re: analyzing table

Hi,

Analyzing your table "could" speed up your query only if your DB is on the Cost Based Optimizer mode.

Basically analyzing a table generates some vital stats about the data in the tables like number of rows, number of unique values in a column etc.
The CBO uses the above generated information for coming up with several plans & uses the plan with the least cost for the query.

Because then Oracle can use its cost based optimizer to decide on the execution plan using statistics based on the data you have in your tables. By statistics it actually calculates things like number of rows, data distribution in the columns etc. The advantage is obviuosly that it can look at your query and decide how selective your criteria will be and thus come up with a better faster execution plan.

Obviously if your data changes then you should reanalyze. Their are stored procedures to help you analyze a schema or whole databases. Put them in an oracle job and it will do it on a regular basis.

Originally posted by jaggu
Alan,
Could u pls. explain me the following scenario, Lets consider the table T1
1. T1 is not analyzed

You force the CBO to use built-in defaults or revert to RBO rules that can, occassionly yield decent access paths but usually don't.

Originally posted by jaggu
2. T1 is analyzed quite some time back

CBO uses old stats that may no longer represent the current nature of the data possibly resulting in poor access paths.

Originally posted by jaggu
3. T1 is analyzed as of now

This usually yields the best access paths; however, sometimes a DBA is in a unique position to know info about the data that aren't tracked by Oracle stats -- in this case "hints" can be used to influence CBO decisions.

Finally, running stats should be "event based". Changes in volume (deletes or inserts), skew, and partitioning are some typical reasons. There a examples in the Oracle manuals to help you write scripts that can detect these changes and cause stats to be executed on the appropriate objects.