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');

Answer 2

I would recommend taking a different approach. I would drop the index(es), load the data and then recreate the index. After enabling it Oracle will build a good index on the data you just loaded. Two things are accomplished here, the records will load faster and the index will be rebuilt with a properly balanced tree. (Note: Be careful here, if the table is a really big table, you may need to declare a temporary tablespace for it to work in.)

drop index my_index;

-- uber awesome loading process

create index my_index on my_table(my_col1, my_col2);