Блог

Прежде всего, я знаю об основах.

select to_number('A231') from dual; —это не сработает, но

select to_char('123') from dual; — это сработает

select to_number('123') from dual; — это тоже сработает

На самом деле в моем пакете у нас есть 2 таблицы A(X number ) и B(Y varchar) есть много столбцов, но нас беспокоит только X и Y . X содержит только числовые значения, такие как 123,456 и т. Д., Но Y содержит некоторые string и некоторые number , например '123','HR123','Hello' . Мы должны объединить эти 2 таблицы. его устаревшее приложение, поэтому мы не можем изменять таблицы и столбцы. До этого времени состояние ниже работало нормально

 to_char(A.x)=B.y;
 

Но так как есть индекс по Y, команда performance предложила нам сделать
A.x=to_number(B.y); это в dev env.

Мой вопрос в том, при каких обстоятельствах этот запрос выдаст ошибку? если он выберет '123' определенно, он даст 123 . но если он выберет 'AB123' , то потерпит неудачу. может ли это потерпеть неудачу? может ли он выбирать 'AB123' , даже когда он соединяется с другими table .

может ли это потерпеть неудачу?

ДА. Он должен пропустить каждую строку, TO_NUMBER прежде чем сможет проверить, соответствует ли она условию фильтра. Поэтому, если у вас есть какая-либо одна строка, в которой она потерпит неудачу, она всегда будет терпеть неудачу.

Из Oracle 12.2 (поскольку вы отметили Oracle 12) вы можете использовать:

 SELECT *
FROM   A
       INNER JOIN B
       ON (A.x = TO_NUMBER(B.y DEFAULT NULL ON CONVERSION ERROR))
 

В качестве альтернативы, добавьте индекс TO_CHAR(A.x) и используйте свой исходный запрос:

 SELECT *
FROM   A
       INNER JOIN B
       ON (TO_CHAR(A.x) = B.y)
 

Также обратите внимание: наличие индекса B.y не означает, что индекс будет использоваться. Если вы выполняете фильтрацию TO_NUMBER(B.y) (с ошибкой преобразования по умолчанию или без нее), вам потребуется индекс на основе функции TO_NUMBER(B.Y) для используемой функции. Вам следует профилировать запросы и проверять планы объяснений, чтобы узнать, есть ли какие-либо улучшения или изменения в использовании индексов.

Никогда не преобразуйте VARCHAR2 столбец, который может содержать немумерные строки to_number .

Это может частично сработать, но в конечном итоге окончательно потерпит неудачу.

Небольшой Пример

 create table a as
select rownum X from dual connect by level <= 10;

create table b as
select to_char(rownum) Y from dual connect by level <= 10
union all
select 'Hello' from dual;
 

Это может сработать (так как вы ограничиваете строки, чтобы преобразование работало; если вам повезет и Oracle выберет правильный план выполнения; что вероятно, но не гарантировано;)

 select * 
from a
join b on A.x=to_number(B.y)
where B.y =  '1';
 

Но это не удастся

 select * 
from a
join b on A.x=to_number(B.y)

ORA-01722: invalid number
 

Производительность

Но так как на Y есть индекс, команда performance предложила нам сделать A. x=to_number(B. y);

Вы должны изменить команду, как если бы вы использовали функцию для индекса столбца ( to_number(B.y) ), которую нельзя использовать.

Напротив, ваш исходный запрос может идеально использовать следующие индексы:

 create index b_y on b(y);
create index a_x on a(x);
 

Запрос

 select * 
from a
join b on to_char(A.x)=B.y
where A.x = 1;
 

План Выполнения

 --------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     5 |     1   (0)| 00:00:01 |
|   1 |  NESTED LOOPS     |      |     1 |     5 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN| A_X  |     1 |     3 |     1   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN| B_Y  |     1 |     2 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------

 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("A"."X"=1)
   3 - access("B"."Y"=TO_CHAR("A"."X"))