What is the command for Index optimization and update statistics for Oracle 10g and 11g?

Question

I am Loading large no of rows into a table from a csv data file . For every 10000 records I want to update the indexs on the table for optimization (update statistics ). Any body tell me what is the command i can use? Also what is SQL Server "UPDATE STATISTICS" equivalent in Oracle.is Update statistics means index optimization or gatehring statistics. I am using Oracle 10g and 11g. Thanks in advance.

Answer 1

Index optimization is a tricky question. You can COALESCE an index to eliminate adjacent empty blocks, and you can REBUILD an index to completely trash and recreate it. In my opinion, what you may wish to do for the period of your data load, is make the indexes UNUSABLE, then when you're done, REBUILD them.

ALTER INDEX my_table_idx01 DISABLE;

-- run loader process

ALTER INDEX my_table_idx01 REBUILD;

You only want to gather statistics once when you're done, and that's done with a call to DBMS_STATS, like so:

EXEC DBMS_STATS.GATHER_TABLE_STATS ('my_schema', 'my_table');