Пусть Oracle преобразует OR-связанные предикаты в операции UNION ALL

UNION и UNION ALL запросы могут превзойти эквивалентные запросы с использованием OR -связанных предикатов при определенных обстоятельствах. Насколько мне известно, это частично связано с тем, что подселекты UNION могут выполняться параллельно, и поэтому они могут иметь свой собственный "подплан", специфичный для каждой части предиката OR -связанного, что, вероятно, намного более оптимально из-за упрощенные преобразования запросов.

Но запись OR -связанных предикатов обычно гораздо читабельнее и кратким, даже если факторинг подзапроса был применен к решению UNION ALL. Мой вопрос: есть ли способ указать 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 */ hint:

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.

Ответы

Ответ 1

Я считаю, что это может иметь какое-то отношение к индексам, существующим в столбцах, которые вы используете в предикате 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

Похоже, что после добавления индекса (хотя он не использовался) оптимизатор решил использовать подсказку в конце концов!

Возможно, вы могли бы попробовать это?