在某些情况下,UNION和UNION ALL查询可以优于使用OR连接的谓词的等效查询.据我所知,这部分是因为UNION子选项可以并行执行,因此它们可以具有特定于OR连接谓词的每个部分的自己的“子计划”,这可能由于更简单的适用查询转换而更加优化.
但是,即使将子查询分解应用于UNION ALL解决方案,写入OR连接的谓词通常更易读和简洁.我的问题是：有没有办法向Oracle指出,一个单一的,昂贵的OR连接的谓词应该转换为UNION ALL操作？如果有这样的提示/方法,在什么情况下可以应用(例如,需要在谓词中涉及的列中存在任何限制等)？一个例子：
@H_403_4@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 */提示：
但是似乎并没有产生我需要的(执行计划中没有强制执行UNION ALL操作)：
@H_403_4@------------------------------------------- | Id | Operation | Name | E-Rows | ------------------------------------------- | 0 | SELECT STATEMENT | | | |* 1 | TABLE ACCESS FULL| A | 2 | -------------------------------------------也许这个提示有一些限制吗？我有Oracle 11g2可用.
解决方法
我相信这可能与您在OR谓词中使用的列上存在的索引有关.
我在11gR2中使用以下测试.
@H_403_4@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中解释了以下查询,(解释计划)
@H_403_4@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 @H_403_4@create index test_x on test (x,y); begin dbms_stats.gather_table_stats('scott','test'); end; /现在重新运行查询：
@H_403_4@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也许你可以试试这个？
