Shulou(Shulou.com)06/01 Report--

Sometimes when building an index, we do not go to the index, excluding the problems of field data and sql writing, and the problem of index failure, we have to consider that the statistical information is too old and has to be collected again. Check the statistics of the table, look at user_index 's last_analyze (index), look at user_tab_col_statistics 's last_analyze (field)

One: unlock statistics

In order to stabilize the execution of the plan, general statistics will be locked and must be unlocked when updating statistics. ① unlocks by user schema: EXEC DBMS_STATS.UNLOCK_schema_STATS ('user_name'); ② unlocks by table mode: first find the locked table select table_name from user_tab_statistics where stattype_locked is not null; and then exec dbms_stats.unlock_table_stats (' user_name',' table name')

Second, the method of collecting statistical information:

1. Analysis table

Begin dbms_stats.gather_table_stats (ownname = > 'TEST', tabname = > STUDENT', estimate_percent = > dbms_stats.AUTO_SAMPLE_SIZE, degree = > 4, cascade = > TRUE); end

two。 Analyze users

Begin dbms_stats.gather_schema_stats (ownname = > 'TEST',estimate_percent = > dbms_stats.AUTO_SAMPLE_SIZE,degree = > 4 cascade = > TRUE); end

3. Analytical index

Begin dbms_stats.gather_index_stats (ownname = > 'TEST', indname = >' IDX_STUDENT_BIRTH', estimate_percent = > dbms_stats.AUTO_SAMPLE_SIZE, degree = > 4); end; can also be analyzed by analyze, for example: ANALYZE TABLE (table_name) COMPUTE STATISTICS;-- ANALYZE TABLE (table_name) COMPUTE STATISTICS FOR ALL INDEXED COLUMNS -- Analytical index column ANALYZE TABLE (table_name) COMPUTE STATISTICS FOR ALL INDEXES FOR ALL COLUMNS;-- Analytical index and index column three: must be re-locked after updating statistics. CALL DBMS_STATS.LOCK_TABLE_STATS ('user_name','table_name')

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.