sybase IQ HG 索引问题
今天遇到一个问题,和大家讨论一下:
说明:
SQL: select col1,sum(col2-col3) from table
where col4 <= 20101122
group by col1
环境: 在col4上建立了HG索引,满足条件col4<=20101122 的数据有4000W行, 表的总数据量也差不多4000W多一点;
问题:执行速度很慢,大概25m左右,并且在执行中有这样一句话:HG index selected due to index cutoff heuristic
请问题这是什么问题?能详细解释一下 HG index selected due to index cutoff heuristic 的意思吗?
执行计划如下:
Elapsed time 0:27:33.525910
Condition 1 (Invariant) (t.cal_date_id <= 20101122)
Condition 1 Selectivity 1.00000000
Condition 1 Usefulness 5.00000000
Condition 1 Distincts in Range 3262
Condition 1 Distincts in Inverted Range 0
Condition 1 Elapsed time 0:00:00.028046
Condition 1 Rows remaining after condition 39295819
Condition 1 Index HG dm.common_anal_fact.ASIQ_IDX_T3233_C_CAL_DATE_ID_HG
Condition 1 Note HG index selected due to index cutoff heuristic
Condition 1 Note Optimized by inversion
Projection Per Row 18 columns (161 bytes)
Output Vector 18 entries (232 data bytes)
说明:
SQL: select col1,sum(col2-col3) from table
where col4 <= 20101122
group by col1
环境: 在col4上建立了HG索引,满足条件col4<=20101122 的数据有4000W行, 表的总数据量也差不多4000W多一点;
问题:执行速度很慢,大概25m左右,并且在执行中有这样一句话:HG index selected due to index cutoff heuristic
请问题这是什么问题?能详细解释一下 HG index selected due to index cutoff heuristic 的意思吗?
执行计划如下:
Elapsed time 0:27:33.525910
Condition 1 (Invariant) (t.cal_date_id <= 20101122)
Condition 1 Selectivity 1.00000000
Condition 1 Usefulness 5.00000000
Condition 1 Distincts in Range 3262
Condition 1 Distincts in Inverted Range 0
Condition 1 Elapsed time 0:00:00.028046
Condition 1 Rows remaining after condition 39295819
Condition 1 Index HG dm.common_anal_fact.ASIQ_IDX_T3233_C_CAL_DATE_ID_HG
Condition 1 Note HG index selected due to index cutoff heuristic
Condition 1 Note Optimized by inversion
Projection Per Row 18 columns (161 bytes)
Output Vector 18 entries (232 data bytes)
作者: xiaojiang520033 发布时间: 2010-11-23
请打开index advisor和query detail选项,
并把完整的query plan发上来。
并把完整的query plan发上来。
作者: mdkii 发布时间: 2010-11-24