我相信这可能与您在OR
谓词中使用的列上存在的索引有关。
我在11gR2中使用以下代码进行了测试。
create table scott.test as
select level l,
decode(mod(level,2), 1, 1, 2) x,
decode(mod(level,2), 1, 2, 1) y,
dbms_random.value(1, 3) z from dual
connect by level < 1000;
/
begin
dbms_stats.gather_table_stats('scott', 'test');
end;
/
然后,我在TOAD中解释了以下查询,(EXPLAIN PLAN FOR
)
select x, y, z from scott.test
where (floor(z) = 1 and x = 1) or (floor(z) = 2 and y = 1)
;
SELECT STATEMENT Optimizer Mode=ALL_ROWS 10 4
TABLE ACCESS FULL COS_DM.TEST 10 280 4
select /*+ USE_CONCAT */ x, y, z from scott.test
where (floor(z) = 1 and x = 1) or (floor(z) = 2 and y = 1)
;
SELECT STATEMENT Optimizer Mode=ALL_ROWS 10 4
TABLE ACCESS FULL COS_DM.TEST 10 280 4
select x, y, z from test where (floor(z) = 1 and x = 1)
union all
select x, y, z from test where (floor(z) = 2 and y = 1)
;
SELECT STATEMENT Optimizer Mode=ALL_ROWS 10 8
UNION-ALL
TABLE ACCESS FULL COS_DM.TEST 5 140 4
TABLE ACCESS FULL COS_DM.TEST 5 140 4
因此,似乎提示不起作用。然后,我在x&y列中添加了一个索引:
create index test_x on test (x, y);
begin
dbms_stats.gather_table_stats('scott', 'test');
end;
/
现在重新运行查询:
select x, y, z from scott.test
where (floor(z) = 1 and x = 1) or (floor(z) = 2 and y = 1)
;
SELECT STATEMENT Optimizer Mode=ALL_ROWS 10 4
TABLE ACCESS FULL COS_DM.TEST 10 280 4
select /*+ USE_CONCAT */ x, y, z from scott.test
where (floor(z) = 1 and x = 1) or (floor(z) = 2 and y = 1)
;
SELECT STATEMENT Optimizer Mode=ALL_ROWS 10 8
CONCATENATION
TABLE ACCESS FULL COS_DM.TEST 5 140 4
TABLE ACCESS FULL COS_DM.TEST 5 140 4
select x, y, z from test where (floor(z) = 1 and x = 1)
union all
select x, y, z from test where (floor(z) = 2 and y = 1)
;
SELECT STATEMENT Optimizer Mode=ALL_ROWS 10 8
UNION-ALL
TABLE ACCESS FULL COS_DM.TEST 5 140 4
TABLE ACCESS FULL COS_DM.TEST 5 140 4
也许您可以尝试一下?