Как вы препятствуете оптимизатору Oracle на основе затрат делать плохие оптимизации?

Question

Рассмотрим следующий сценарий. У меня есть стол stupid_table) в схеме, которую я не могу контролировать. Это третье лицо, запрещено. Не обидчивый Я могу запросить его, но не добавлять индексы или новые таблицы или изменять дизайн.

Каждый столбец в stupid_table это VARCHAR2(50 BYTE)Есть много столбцов, но мне нужно только два из них: row_type а также magic_number, magic_number заполняется строковым представлением целого числа, но только там, где row_type установлен в 'DATA'Мне нужны только магические числа, которые больше нуля.

SELECT TO_NUMBER(magic_number)
FROM stupid_table
WHERE row_type = 'DATA'
AND TO_NUMBER(magic_number) > 0;

Это приводит к ошибке Oracle "неверное число", поскольку оптимизатор на основе затрат (CBO) выбирает оценку TO_NUMBER до проверки row_type и есть целая куча строк с другим row_type и другое использование для magic_number поле.

Хорошо, как насчет того, чтобы сначала отфильтровать строки, а затем выполнить сравнение?

SELECT TO_NUMBER(t.magic_number)
FROM (
    SELECT magic_number
    FROM stupid_table
    WHERE row_type = 'DATA'
) t
AND TO_NUMBER(t.magic_number) > 0;

Теперь CBO, кажется, решает, что запрос довольно прост и игнорирует хитрость, которую я использовал, что дает план запроса, идентичный оригиналу.

Наконец, в отчаянии, я прибегаю к грязным взломам: использование /*+RULE*/ подсказка запроса, чтобы заставить Oracle использовать старый оптимизатор на основе правил. Это работает как сон, но в этом нет необходимости, не говоря уже о том, что он использует функцию Oracle, которая больше не поддерживается.

Есть лучший способ сделать это?

Answer 1

Вы можете просто избежать использования TO_NUMBER вообще? Похоже, это улучшит производительность в любом случае. Что-то вроде:

WHERE t.magic_number != '0'

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

Answer 2

Заставьте CASE сделать работу за вас

select to_number(magic_number) 
from stupid_table
where row_type = 'DATA'
and case when row_type = 'DATA' then to_number(magic_number) else 0 end > 0

В моем тестовом случае у меня были проблемы с воссозданием вашей ошибки, так что удивляйтесь, если есть DATA строки, в которых нет номеров. Но это также может быть способ, которым оптимизатор обрабатывает мои запросы.

Я думаю, что подсказка no_merge также может решить вашу проблему, но, поскольку у меня возникли проблемы с воспроизведением проблемы, я не могу быть уверен.

SELECT --+ no_merge(t)
  TO_NUMBER(t.magic_number)
FROM (
    SELECT magic_number
    FROM mike_temp_stupid_table
    WHERE row_type = 'DATA'
) t
where TO_NUMBER(t.magic_number) > 0;

Answer 3

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

CREATE OR REPLACE FUNCTION my_to_number( p_str IN VARCHAR2 )
  RETURN number
IS 
BEGIN
  RETURN to_number( p_str );
EXCEPTION
  WHEN OTHERS THEN
    RETURN null;
END;

а затем измените запрос

SELECT TO_NUMBER(magic_number)
FROM stupid_table
WHERE row_type = 'DATA'
AND MY_TO_NUMBER(magic_number) > 0;

За исключением этого, вы, конечно, можете взять план запроса, сгенерированный RBO, и создать профиль, который заставит CBO использовать этот план. Возможно, это немного проще в управлении, чем попытка предоставить полный набор подсказок, которые не позволят CBO когда-либо применять предикат MAGIC_NUMBER перед предикатом ROW_TYPE.

Answer 4

Точный способ заключается в использовании ordered_predicates подсказка, чтобы изменить порядок вашего WHERE условия оцениваются.

Документация: Oracle ORDERED_PREDICATES Подсказка

SELECT /*+ ORDERED_PREDICATES */ TO_NUMBER(magic_number)
FROM stupid_table
WHERE row_type = 'DATA'
AND TO_NUMBER(magic_number) > 0;

Теперь попробуйте поменять условия, и вы снова получите ошибку. Пожалуйста, рассмотрите другие ответы, потому что я также сомневаюсь, что вызов TO_NUMBER - это ваше лучшее решение.

Answer 5

Как насчет создания материализованного представления фрагмента stupid_table, который содержит только тип строки DATA?

Answer 6

Я обычно добавляю rownum, чтобы прекратить толкование предикатов. (Подсказки могут сделать это тоже, но их так легко ошибиться, и с этим типом проблемы, если вы ошибаетесь, вы можете не заметить сразу.) Также вам, вероятно, следует добавить комментарий, чтобы кто-то позже не пытался "оптимизировать" свой код и удалить то, что выглядит как ненужная логика.

SELECT TO_NUMBER(t.magic_number)
FROM (
    --Bad data, use rownum for type safety
    SELECT magic_number, rownum
    FROM stupid_table
    WHERE row_type = 'DATA'
) t
AND TO_NUMBER(t.magic_number) > 0;

Answer 7

Оператор with позволяет применять определенный порядок оценки.

WITH
has_numerics_only AS
(
    SELECT magic_number
    FROM stupid_table
    WHERE row_type = 'DATA'
)
SELECT TO_NUMBER(t.magic_number)
FROM has_numerics_only
WHERE TO_NUMBER(t.magic_number) > 0;

Также рассмотрите возможность того, что в одной или нескольких строках "ДАННЫЕ" действительно содержатся неверные данные.

Answer 8

Вы можете попробовать:

SELECT TO_NUMBER(magic_number)
FROM stupid_table
WHERE row_type = 'DATA'
AND REGEXP_LIKE(magic_number, '^\d{1,}$');

Если это по-прежнему не работает, перемещение условия в предложение HAVING может заставить оптимизатор сначала выполнить его оценку.

SELECT TO_NUMBER(magic_number)
FROM (
SELECT magic_number
FROM stupid_table
WHERE row_type = 'DATA'
GROUP BY magic_number
HAVING REGEXP_LIKE(magic_number, '^\d{1,}$')) ilv;

В противном случае материализованное представление или использование курсора PL/SQL могут быть единственным способом.