Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

ORACLE index invalidation, update statistics

2025-03-31 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report