sql – 让Oracle将OR连接的谓词转换为UNION ALL操作

但是,即使将子查询分解应用于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可用.

解决方法

我在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

所以看起来这个提示不行.然后我将一个索引添加到x& y列：

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

看来,添加索引后(即使没有被使用),优化器决定使用提示！

也许你可以试试这个？

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!