[color=red]
经常发现建立索引后,sql的执行计划不会发生变化,对表运行下述语句后,才能起作用(在plsql 命令窗口中运行)
execute dbms_stats.gather_table_stats(ownname => 'USER_NAME',tabname => 'TABLE_NAME' ,estimate_percent => null ,method_opt => 'for all indexed columns' ,cascade => true ,degree => 1);
[/color]
索引不起作用的例子
表fsys_model_param一共有1229993条数据
查询:
消耗24.56秒
创建索引
再次查询
消耗0.00秒,基本不消耗时间
查看建立的索引:
经常发现建立索引后,sql的执行计划不会发生变化,对表运行下述语句后,才能起作用(在plsql 命令窗口中运行)
execute dbms_stats.gather_table_stats(ownname => 'USER_NAME',tabname => 'TABLE_NAME' ,estimate_percent => null ,method_opt => 'for all indexed columns' ,cascade => true ,degree => 1);
[/color]
索引不起作用的例子
select b.logon_id,
count(logon_id) as shop_count,
max(b.add_date) as last_view_date
from shop_logon_view_pd_info b
group by b.logon_id;
select /*+ index(idx_lvpi_lid_adate)*/
b.logon_id,
count(logon_id) as shop_count,
max(b.add_date) as last_view_date
from shop_logon_view_pd_info b
group by b.logon_id
-- 对于产品访问表shop_logon_view_pd_info做group by统计(记录数,最新日期),由于count这些方法会扫描所有数据行,因此虽然对表建立了logon_id和add_date的组合所以,在oracle CBO还是会自动选择走全表扫描,及时使用hint语法强制指定还是走全表扫描
表fsys_model_param一共有1229993条数据
查询:
select * from fsys_model_param where model_id = '1682'
消耗24.56秒
创建索引
create index idx_fsys_model_param_model_id on fsys_model_param(model_id);
drop index idx_fsys_model_param_model_id on fsys_model_param;
再次查询
select * from fsys_model_param where model_id = '1682'
消耗0.00秒,基本不消耗时间
查看建立的索引:
show index from fsys_model_info;