在某些情况下,UNION和UNION ALL查询可以优于使用OR连接的谓词的等效查询.据我所知,这部分是因为UNION子选项可以并行执行,因此它们可以具有特定于OR连接谓词的每个部分的自己的“子计划”,这可能由于更简单的适用查询转换而更加优化.
但是,即使将子查询分解应用于UNION ALL解决方案,写入OR连接的谓词通常更易读和简洁.我的问题是：有没有办法向Oracle指出,一个单一的,昂贵的OR连接的谓词应该转换为UNION ALL操作？如果有这样的提示/方法,在什么情况下可以应用(例如,需要在谓词中涉及的列中存在任何限制等)？一个例子：
CREATE TABLE a AS SELECT 1 x,2 y FROM DUAL UNION ALL SELECT 2 x,1 y FROM DUAL; -- This query... SELECT * FROM a WHERE x = 1 OR y = 1 -- Is sometimes outperformed by this one,for more complex table sources... -- Note: in my case,I can safely apply UNION ALL. I know the two predicates to -- be mutually exclusive. SELECT * FROM a WHERE x = 1 UNION ALL SELECT * FROM a WHERE y = 1
注意,我知道/*+ USE_CONCAT */提示：
SELECT /*+ USE_CONCAT */ * FROM a WHERE x = 1 OR y = 1
但是似乎并没有产生我需要的(执行计划中没有强制执行UNION ALL操作)：
------------------------------------------- | Id | Operation | Name | E-Rows | ------------------------------------------- | 0 | SELECT STATEMENT | | | |* 1 | TABLE ACCESS FULL| A | 2 | -------------------------------------------
也许这个提示有一些限制吗？我有Oracle 11g2可用.
解决方法
我相信这可能与您在OR谓词中使用的列上存在的索引有关.
我在11gR2中使用以下测试.
create table scott.test as
select level l,decode(mod(level,2),1,2) x,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中解释了以下查询,(解释计划)
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,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,z from test where (floor(z) = 1 and x = 1)
union all
select x,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
create index test_x on test (x,y);
begin
dbms_stats.gather_table_stats('scott','test');
end;
/
现在重新运行查询：
select x,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,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
也许你可以试试这个？
