Oracle. Предотвращение подзапроса слияния и основных условий запроса

У меня есть большое значение атрибута сущности, как таблица. Я пытаюсь выбрать несколько строк из этой таблицы с помощью подзапроса, а затем фильтровать со строками. Как я могу предотвратить слияние подзапроса и основного запроса в этой ситуации?

Например:

EMP:
EMPNO | ENAME  | SAL
---------------------
1000  | KING   | 10000
1001  | BLAKE  | 7500

CREATE VIEW EAV(ID,ATTR,VALUE) AS
select empno, 'name'||ename, ename from emp -- subquery 1
union
select empno, 'sal'||ename, ename from emp -- subquery 2
union
select empno, 'mgr'||ename, ename from emp -- subquery 3

НОТА: ||ename был добавлен только для того, чтобы Oracle не оптимизировал следующие запросы, добавив фильтр "(null is not null)" в подзапрос 1 и 3

В подзапросе я выбираю все строки с атрибутом sal%, а затем фильтрую их в основном запросе:

select *
FROM (select id,value from EAV where attr like 'sal%')
WHERE to_number(value) > 5000;

Это падение запроса вызывает оптимизатор слияния подзапроса с внешним запросом. После объединения БД попытайтесь применить to_number ко всем значениям в столбце "значение", но некоторые из них имеют строковое значение. Witch HINT предотвратить эту оптимизацию?

PS Я хочу получить тот же результат, что и

WITH t as (
   select /*+ materialize */ id,value
   from eav
   where attr like 'sal%') 
select * from t where to_number(value) > 5000;

но без CTE.

Источник

2 ответа

Решение

ROWNUM это самый безопасный способ предотвратить преобразования оптимизатора и обеспечить безопасность типов. С помощью ROWNUM заставляет Oracle думать, что порядок строк имеет значение, и предотвращает такие вещи, как использование предикатов и просмотр слияния.

select *
from
(
   select id, value, rownum --Add ROWNUM for type safety.
   from eav
   where attr like 'sal%' 
)
where to_number(value) > 5000;

Есть и другие способы сделать это, но ни один из них не является надежным. Не беспокойтесь простыми встроенными представлениями, общими табличными выражениями, CASE, предикат заказа или подсказки. Эти распространенные методы не надежны, и я видел, как все они терпят неудачу.

Лучшее долгосрочное решение - изменить таблицу EAV, чтобы иметь разные столбцы для каждого типа, как я описываю в этом ответе. Исправьте это сейчас, или будущие разработчики будут проклинать ваше имя, когда им придется писать сложные запросы, чтобы избежать ошибок типа.

Источник

Я сомневаюсь, что ваша проблема действительно имеет отношение к оптимизатору. По крайней мере, в вашем примере VALUE установлен в ENAME для всех трех атрибутов. Это хорошо для атрибута "name", но для "sal" это, вероятно, должно быть SAL. Для "мгр" я понятия не имею, поскольку ваш пример не дает достаточно информации.

Я бы также порекомендовал удалить часть "||ename", опять же при условии, что оптимизатор не является проблемой.

Наконец, измените UNION на UNION ALLS, если EMPNO является вашим основным ключом в EMP. UNION пытается свести результаты к уникальным строкам, что является ненужной обработкой, если они уже уникальны по ID, ATTR.

Пересмотрите представление, а затем "выберите * в EAV, где ATTR =" sal "" и убедитесь, что вы видите зарплаты. Это должно позволить вам сделать to_number(ATTR) для sal без проблем.

Источник
Другие вопросы по тегам