I have several large (between 1GB and 8GB) tables in my warehousing process that are currently loaded on a Trunc-Load-Index approach. The sequence of operations is - Drop Indexes - Trunc table - Load Table - Create Indexes Here is how I came by my indexes: These tables are used in INNER JOINs in queries. I analyzed the queries in "Database Engine Tuning Advisor". It gave me some suggestions for indexes and statistics. I followed those suggestions and got some really good results - the queries ran incredibly fast afterwards. My questions are: In a scenario where a 20GB table has multiple ***multi-column inclusive indexes*** and ***multiple statistics***: - What happens to the statistics when a Trunc-Load-Index operation is carried out? - What kind of maintenance do I have to do on these statistics after the indexes are built?
First of all I must state that statistics can be made manually on (a) column/s whether it/they is/are indexed or not. Moreover, when you create index on column/s, respective statistics are created. So Dropping indexes of a table would not always mean that all the statistics for that table would be dropped. Only the statistics for the index/es would be dropped Now, coming to your question 1. For Trunc-Load-Index (Assuming you dropped the indexes before), the statistics of the indexes dropped would be dropped as well. But any other statistics which were made manually will remain there. 2. After indexes are rebuild, the statistics for the index/es would be up to date. But for manual statistics, I personally would update those statistics with FULL SCAN mode. So I would have the most optimal execution plans. Cheers.