Difference between build index and gather stat

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 Received on Tue Apr 18 2006 - 23:35:23 CDT