Menu
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.
301
The following example shows how to update statistics for an Oracle database at the schema level. Replace schema name, owner name, and table name with the appropriate schema, owner, and table names. EXEC DBMS_STATS. GATHER_SCHEMA_STATS( ownname=> ' schema name ' , cascade=> TRUE, estimate_percent=> DBMS_STATS.
Enables you to change the default values of the parameters used by the DBMS_STATS. GATHER_*_STATS procedures for any object in the database that does not have an existing table preference. All parameters default to the global setting unless a table preference is set or the parameter is explicitly set in the DBMS_STATS.
In Oracle Database, optimizer statistics collection is the gathering of optimizer statistics for database objects, including fixed objects. The database can collect optimizer statistics automatically. You can also collect them manually using the DBMS_STATS package.
Use the ANALYZE statement to collect statistics, for example, to: Collect or delete statistics about an index or index partition, table or table partition, index-organized table, cluster, or scalar object attribute.
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');
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
