--查看 表统计信息 (采样比例sample_size小于blocks不支持索引时)
select table_name,num_rows,blocks,sample_size,last_analyzed
from user_tables where table_name='TABLE_NAME' --测试
1、采样强制索引方式查询一下效果明显,可修改采样比例sample_size(reg--别名 V_REG_APP_INFO_STATUS--索引名)
select /*+ INDEX(reg INDEX1) */ count(1) as ID
from v1_REG reg
2、修改采样比例
execute dbms_stats.gather_table_stats('USER','TABLENAME');
常用sql:
分区表的统计信息查看:select table_name,partition_name,num_rows,blocks,sample_size,last_analyzed from user_tab_partitions
where table_name='tablename';
普通表统计信息参看:select table_name,num_rows,blocks,sample_size,last_analyzed from user_tables
where table_name='tablename';
索引统计信息查看:select table_name,index_name,num_rows,sample_size,last_analyzed from user_indexes where table_name in ('tablename');
强制索引测试语句:
select /*+ INDEX(t2 t2_index1) INDEX(t1 t1_index1) */ count(1) as num
from table1 t1,
table2 t2
where
t1.id=t2.fid and t1.type='1' and t2.ctype='2'