Номер Oracle и соединение varchar - программирование

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

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

В этой таблице запросаA.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);

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

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

Ответ 1

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

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) )
/