Re: Oracle statistics

Hi Richard
Thanks for the info
We have to drop and create tables indexes etc in order to clean the database, etc, at least that is what I have been told, I'm new at this place, and they have this for 1 year, where then run a ctas every day, is like a export import of the database, I just don't understand what update STATITISCS does, why to do we have to do this all the time, where does it store this infomartion, the statistics takes a long time to run which is not acceptable (7 hours at times) Do you have any other suggestions
Thanks
Maria

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:<CRvf9.29152$g9.84170_at_newsfeeds.bigpond.com>...
> 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 Wed Sep 11 2002 - 08:32:56 CDT