有时候建立索引的时候不走索引,排除了字段数据问题和sql写法问题之外,应该是统计信息有问题,得重新收集。
一:解锁统计信息
为了稳定执行计划,一般统计信息都会被锁住的,在更新统计信息的时候得先解锁。①按用户schema解锁:EXEC DBMS_STATS.UNLOCK_schema_STATS(‘user_name‘);②按表模式解锁:先查出被锁定的表select table_name from user_tab_statistics where stattype_locked is not null;然后exec dbms_stats.unlock_table_stats(‘user_name‘,‘表名‘);
二:收集统计信息方法:
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;
2. Analyze Users
begin dbms_stats.gather_schema_stats(ownname => ‘TEST‘,estimate_percent => dbms_stats.AUTO_SAMPLE_SIZE,degree => 4,cascade => TRUE);end;
3. Analysis Index
begin dbms_stats.gather_index_stats( ownname => ‘TEST‘, indname => ‘IDX_STUDENT_BIRTH‘, estimate_percent => dbms_stats.AUTO_SAMPLE_SIZE, degree => 4);end;还可以用analyze 来分析,例如:ANALYZE TABLE (table_name) COMPUTE STATISTICS; --分析表ANALYZE TABLE (table_name) COMPUTE STATISTICS FOR ALL INDEXED COLUMNS; --分析索引列ANALYZE TABLE (table_name) COMPUTE STATISTICS FOR ALL INDEXES FOR ALL INDEXED COLUMNS; --分析索引和索引列三:更新完统计信息后得重新锁住。CALL DBMS_STATS.LOCK_TABLE_STATS(‘user_name‘,‘table_name‘);
Oracle index invalidation, UPDATE statistics