Номер Oracle и соединение varchar

Question

У меня есть запрос, который объединяет две таблицы. Одна таблица имеет столбец типа varchar, а другая таблица имеет тип числа. Я выполнил свой запрос к 3 базам данных Oracle и вижу некоторые странные результаты, которые, я надеюсь, можно объяснить. На двух базах работает что-то вроде следующего.

select a.col1, b.somecol 
from tableA a inner join tableB b on b.col2=a.col1;

В этом запросе tableA.col1 имеет номер типа, а tableB.col2 имеет тип varchar. Это прекрасно работает в двух базах данных, но не в третьей. В третьем я получаю (ORA-01722) ошибку. В третьем мне нужно сделать что-то вроде...

select a.col1, b.somecol 
from tableA a inner join tableB b on b.col2=to_char(a.col1);

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

Кто-нибудь знает, почему это может работать в некоторых базах данных оракула, а не в других без преобразования типа данных? Есть ли глобальная настройка, которая разрешает такое поведение?

Answer 1

Одна из причин сбоя неявных преобразований заключается в том, что объединяющий столбец varchar содержит данные, которые не являются числовыми. Oracle обрабатывает число в соединениях varchar2 путем преобразования строк (см. Цитату Гэри в его комментарии), поэтому он фактически выполняет это:

select a.col1, b.somecol 
from tableA a inner join tableB b on to_number(b.col2)=a.col1;

Если tableB.col2 содержит значения, которые не являются числовыми - кажется вполне вероятным, это строка в конце концов - тогда он будет отбрасывать ORA-01722: invalid number, Явно приводя числовой столбец к строке, вы нарушаете поведение Oracle по умолчанию.

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

Что касается производительности, вы могли бы построить индекс на основе функций...

create index whatever_idx on tableA ( to_char(col1) )
/