Description:
Setting index_merge_sort_union=off seems to disable other index merge plans, even though the intention is to only disable sort_union index merge plans.
How to repeat:
create table t (i int, j int, k int, key(i), key(j, k), key(j));
insert into t (WITH RECURSIVE a (i) AS ( SELECT 0 union all SELECT i+1 from a where i < 9 ), b (i) AS (SELECT x.i + y.i * 10 + z.i * 100 + w.i * 1000 + v.i * 10000 FROM a x, a y, a z, a w, a v) SELECT b.i, b.i, b.i from b order by i);
explain select * from t where i = 0 or j = 2;
set optimizer_switch='index_merge_sort_union=off';
explain select * from t where i = 0 or j = 2;
explain select /*+ INDEX_MERGE(t i,j_2) */ * from t where i = 0 or j = 2;
drop table t;
The 3 explains give the following output:
explain select * from t where i = 0 or j = 2;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL index_merge i,j,j_2 i,j 5,5 NULL 2 100.00 Using sort_union(i,j); Using where
set optimizer_switch='index_merge_sort_union=off';
explain select * from t where i = 0 or j = 2;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL ALL i,j,j_2 NULL NULL NULL 100000 0.00 Using where
explain select /*+ INDEX_MERGE(t i,j_2) */ * from t where i = 0 or j = 2;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL index_merge i,j,j_2 i,j_2 5,5 NULL 2 100.00 Using union(i,j_2); Using where
You can see that after turning off index_merge_sort_union=off, we fall back to full table scan. However, I would expect it to fallback to a union plan instead, since index_merge_union is on. If I change the query to use optimizer hints, then you can see that the union plan is possible (and chosen).
