Re: Difference between build index and gather stat

On 18 Apr 2006 21:35:23 -0700, xxgeorge_at_gmail.com wrote:

>hi all,
>
>
>I'm not very clear about the concept of build-index and gather-stat.
>Grateful if you can give me some idea.
>
>I have a table [user] where [user_id] is the primary key. Initially
>the table is empty. An application will read from a text file and load
>about 100000 rows of user info into the table. Right after that the
>application will start a process which scan through the 100000 users,
>do calculations and update some status flags row by row.
>
>The update process, which involves only straight-forward update
>statements (UPDATE user SET status = ? WHERE user_id = ?) is extremely
>slow. It is found that the Optimizer opts for full-table-scan instead
>of using the primary key for the updates. If I manually gather
>statistics for the tables after loading the data and before running the
>calculations the performance is much much better (trimmed from several
>hours to several seconds).
>
>
>I'd assume:
>
>1. with the primary key defined, whenever a row is being inserted, a
>index-tree is built somewhere;
>2. the index-tree is always there, but the Optimizer may not use it
>(depends on cost)
>3. gathering stat 'tells' the Optimizer to use the primary key
>
>
>If that's the case, next time I bulk-load a new batch of data, is it
>right to assume the index already there (1), and the Optimizer will use
>the primary key (2)? Does it mean that I only need to gather stat once
>to teach the Optimizer? Or should I gather stat again or re-build the
>index? What's the difference and the mechanism behind?
>
>
>Sorry if I didn't make it clear. Thanks in advance
>george
>

Oracle will keep and use gathered statistics until you replace them, for instance by gathering them again. Secondly, if you have an enabled primary key, Oracle will keep an index on the column or columns on which you defined the key, and will keep this up to date when using DML. So yes, you can expect Oracle to use your index again.

Jaap. Received on Wed Apr 19 2006 - 14:35:21 CDT