All geek questions in one place

What is the command to optimize indexing and update statistics for Oracle 10g and 11g?

I load a large number of rows into a table from a csv data file. For every 10,000 records, I want to update table indexes for optimization (update statistics). Will any body tell me what a command I can use is? Also, equivalent to SQL Server "UPDATE STATISTICS" in Oracle.is. Update statistics means index optimization or goal statistics. I am using Oracle 10g and 11g. Thanks in advance.

+4
source share
2 answers

Index optimization is not an easy question. You can COALESCE index to eliminate neighboring empty blocks, and you can EXECUTE the index to completely destroy and recreate it. In my opinion, what you might want to do during the data loading period does the UNUSABLE indexes, and then when you are done, reload them.

ALTER INDEX my_table_idx01 DISABLE; -- run loader process ALTER INDEX my_table_idx01 REBUILD;

You want to get statistics only once when this is done, and this is done with a DBMS_STATS call, for example:

 EXEC DBMS_STATS.GATHER_TABLE_STATS ('my_schema', 'my_table');
+7
source

I would recommend using a different approach. I would reset the index (s), load the data, and then recreate the index. After you turn on Oracle, Oracle will create a good index for the data you just downloaded. Two things are done here: the records will load faster, and the index will be rebuilt with a properly balanced tree. (Note: Be careful if the table is really large, you may need to declare a temporary table space for it to work.)

 drop index my_index; -- uber awesome loading process create index my_index on my_table(my_col1, my_col2);
0
source

Source: https://habr.com/ru/post/1306028/

More articles:


All Articles