Номер Oracle и соединение varchar
У меня есть запрос, который присоединяется к двум таблицам. Одна таблица имеет столбец, который имеет тип varchar, и другая таблица имеет тип числа. Я выполнил свой запрос на 3 базах данных оракула, и вижу некоторые странные результаты, я надеюсь, может быть объяснен. На двух из баз данных что-то как следующие работы.
select a.col1, b.somecol
from tableA a inner join tableB b on b.col2=a.col1;
В этом запросе tableA.col1 имеет число типа, и tableB.col2 имеет тип varchar. Это хорошо работает в двух из баз данных, но не в третьем. В третьем я получаю (РТЫ 01722) ошибку. В третьем я должен сделать что-то как...
select a.col1, b.somecol
from tableA a inner join tableB b on b.col2=to_char(a.col1);
Это работает во всех базах данных. Вопрос, который я имею, состоит в том почему? Вышеупомянутое является упрощенным запросом, и реальный запрос немного более сложен и получает много данных, следовательно первая версия намного быстрее. Если бы я мог бы заставить это работать во всех средах, это было бы большим.
Кто-либо знает, почему это может работать в некоторых базах данных оракула и не других без броска на типе данных? Существует ли глобальная установка, которая включает такое поведение?
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) )
/