Re: Oracle statistics

Hi Maria,

The question I have for you is why do you drop tables, indexes etc. each day and then recreate them. The term "rather odd" springs to mind.

In answer to your question, I'll give you the "dummies" answer ;)

When you write a piece of SQL, you are effectively telling Oracle *what* to do, it's kinda up to Oracle *how* to do it.

In a very simple example, if you run something such as:

SELECT * FROM bowie where artist_name = 'David Bowie';

what Oracle needs to determine is how best to execute this. Should it read all the rows looking for all the 'David Bowie's or should it use the index on artist_name looking for all the 'David Bowie's.

Well the answer is, it depends.

If there are only a few David Bowie's in the table and the table is quite large, then the index would be the way to go. Or if there are proportionally heaps of David Bowies or the table is very small then reading all the rows is the way to go.

How does Oracle's Cost Based Optimizer (CBO) determine this. By looking at the statistics of the table and index and determining things such as the number of rows and blocks in the table, the cardinality or the number of distinct values for the artist_name column, the number of blocks and index entries in the index, the height or levels in the index etc. and then determining the *cost* of using the index vs. using the Full Table Scan. Whichever has the lower cost is the correct answer.

Therefore accurate statistics are important for the CBO to make correct decisions and for your code to run as efficiently as possible. They don't need to be precise as such, so long as the statistics are in the correct ball park, the CBO (all things being equal) will pick the appropriate execution paths.

Make sense ?

Richard
"Maria" <evoradba_at_yahoo.ca> wrote in message news:351fd9d6.0209101512.afae2b7_at_posting.google.com...
> Hi there
> I have a dummy question
> I have this production database in which we have sripts everyday that
> drops the tables, index, triggers etc then recreates them back, at the
> end we run statitics which takes hours. Can someone please tell me why
> dop we have to run the statitics, like what does do? Should we run
> this everytime, where does it store this information etc
> I would appreciate any help
> Thanks
>
> Maria
Received on Tue Sep 10 2002 - 19:18:05 CDT