#sql #database #oracle #oracle11g
Вопрос:
Ниже запрос выдает ошибку.
select sob.set_of_books_id, orginfo.org_information1
from
gl_sets_of_books sob,
hr_organization_information orginfo
where
sob.set_of_books_id = to_number(orginfo.org_information1);
Причина set_of_books_id в том, что это столбец чисел и org_information1 varchar, который содержит строку, а также числовую строку. так что это несоответствие типов. мы должны выбрать только те значения, в которых есть числовая строка org_information1
чтобы преодолеть это, мы использовали regexp_like функцию, которая будет выбирать только те записи, которые являются числовыми.
select sob.set_of_books_id, orginfo.org_information1
from hr_organization_information orginfo, gl_sets_of_books sob
where sob.set_of_books_id = to_number(orginfo.org_information1)
and REGEXP_LIKE(orginfo.org_information1, '^[[:digit:]]
Мы только что добавили эту строку and REGEXP_LIKE(orginfo.org_information1, '^[[:digit:]] $'); в предыдущий запрос, и она работает правильно.
Мой вопрос даже в последнем запросе, мы используем предложение where и присоединяемся к тому же условию, которое не удалось выполнить в первом запросе .и where определенно будет выполняться раньше AND . так почему же он не терпит неудачу? будет ли это неудачно для некоторых записей? или запрос правильный? есть ли лучший способ использовать второй запрос?
если я не использую существующее where условие, то это приводит к ошибке. я не знаю, в чем проблема с нижеприведенным запросом.
Мы не можем использовать to_char on set_of_books_id , потому что этот столбец содержит индекс.и мы не можем изменить его для создания индекса на основе функций.Мы должны использовать индекс в нашем запросе
Комментарии:
1. не могу изменить, чтобы создать индекс на основе функций - почему вы не можете создать индекс на основе функций?
2. Привет, Крис, Потому что стол не принадлежит нам. Это приложение EBS, в котором к одной таблице обращаются многие приложения. Они отказались создавать индекс на основе функций. мы используем их таблицу для панели мониторинга.
Ответ №1:
whereобязательно побегу раньшеand
Нет!
Оптимизатор может свободно изменять условия в вашем запросе. Нет никакой гарантии, что он обрабатывает их сверху вниз. Это может привести к неожиданным последствиям, если план изменится (например, из-за того, что вы добавили/удалили индексы).
Предполагая, что вы используете 12.2 или выше, вместо регулярного выражения вы можете использовать on conversion error предложение для сопоставления всех нечисловых значений с null :
with rws as (
select level x,
case mod ( level, 3 )
when 0 then chr ( level 64 )
else to_char ( level )
end y
from dual
connect by level <= 10
)
select y from rws
where x = to_number ( y default null on conversion error );
Y
1
2
4
5
7
8
10
Комментарии:
1. Привет, Крис, я использую 11g. также я не могу использовать to_char(sob.set_of_books_id) для этого, потому что этот столбец содержит индекс в базовой таблице. поэтому мы хотим использовать индекс. можем ли мы каким-то образом изменить приведенный выше запрос и убедиться, что он не завершится неудачей ни при каких обстоятельствах?
Ответ №2:
Используйте TO_CHAR в столбце число, а не TO_NUMBER в столбце строка:
select sob.set_of_books_id,
orginfo.org_information1
from hr_organization_information orginfo
INNER JOIN gl_sets_of_books sob
ON ( TO_CHAR(sob.set_of_books_id) = orginfo.org_information1 );
Или, начиная с Oracle 12.2, вы можете использовать TO_NUMBER(value DEFAULT NULL ON CONVERSION ERROR) :
select sob.set_of_books_id,
orginfo.org_information1
from hr_organization_information orginfo
INNER JOIN gl_sets_of_books sob
ON ( sob.set_of_books_id
= TO_NUMBER(orginfo.org_information1 DEFAULT NULL ON CONVERSION ERROR)
);
Комментарии:
1. Мы не можем использовать to_char для set_of_books_id, потому что этот столбец содержит индекс.и мы не можем изменить его для создания индекса на основе функций
2. "Мы не можем использовать to_char для set_of_books_id, потому что этот столбец содержит индекс". Индекс не мешает вам использовать функцию. Это просто означает, что если вы используете функцию, индекс не может быть использован для разрешения запроса, что, вероятно, приведет к полному сканированию таблицы. Но это также не обязательно может быть проблемой. Вы действительно устали от этого?
3. Привет, на самом деле более раннее условие было to_char(set_of_books_id)=org_information1 . команда perf предложила нам изменить и использовать to_number, чтобы можно было использовать индекс. теперь можно использовать индекс, но мы сталкиваемся с проблемой преобразования, потому что org_information1 также содержит буквенно-цифровую строку. таким образом, преобразование их в число приведет к ошибке.
);
Мы только что добавили эту строку and REGEXP_LIKE(orginfo.org_information1, '^[[:digit:]] $'); в предыдущий запрос, и она работает правильно.
Мой вопрос даже в последнем запросе, мы используем предложение where и присоединяемся к тому же условию, которое не удалось выполнить в первом запросе .и where определенно будет выполняться раньше AND . так почему же он не терпит неудачу? будет ли это неудачно для некоторых записей? или запрос правильный? есть ли лучший способ использовать второй запрос?
если я не использую существующее where условие, то это приводит к ошибке. я не знаю, в чем проблема с нижеприведенным запросом.
Мы не можем использовать to_char on set_of_books_id , потому что этот столбец содержит индекс.и мы не можем изменить его для создания индекса на основе функций.Мы должны использовать индекс в нашем запросе
Комментарии:
1. не могу изменить, чтобы создать индекс на основе функций — почему вы не можете создать индекс на основе функций?
2. Привет, Крис, Потому что стол не принадлежит нам. Это приложение EBS, в котором к одной таблице обращаются многие приложения. Они отказались создавать индекс на основе функций. мы используем их таблицу для панели мониторинга.
Ответ №1:
whereобязательно побегу раньшеand
Нет!
Оптимизатор может свободно изменять условия в вашем запросе. Нет никакой гарантии, что он обрабатывает их сверху вниз. Это может привести к неожиданным последствиям, если план изменится (например, из-за того, что вы добавили/удалили индексы).
Предполагая, что вы используете 12.2 или выше, вместо регулярного выражения вы можете использовать on conversion error предложение для сопоставления всех нечисловых значений с null :
Комментарии:
1. Привет, Крис, я использую 11g. также я не могу использовать to_char(sob.set_of_books_id) для этого, потому что этот столбец содержит индекс в базовой таблице. поэтому мы хотим использовать индекс. можем ли мы каким-то образом изменить приведенный выше запрос и убедиться, что он не завершится неудачей ни при каких обстоятельствах?
Ответ №2:
Используйте TO_CHAR в столбце число, а не TO_NUMBER в столбце строка:
Или, начиная с Oracle 12.2, вы можете использовать TO_NUMBER(value DEFAULT NULL ON CONVERSION ERROR) :
Комментарии:
1. Мы не можем использовать to_char для set_of_books_id, потому что этот столбец содержит индекс.и мы не можем изменить его для создания индекса на основе функций
2. «Мы не можем использовать to_char для set_of_books_id, потому что этот столбец содержит индекс». Индекс не мешает вам использовать функцию. Это просто означает, что если вы используете функцию, индекс не может быть использован для разрешения запроса, что, вероятно, приведет к полному сканированию таблицы. Но это также не обязательно может быть проблемой. Вы действительно устали от этого?
3. Привет, на самом деле более раннее условие было to_char(set_of_books_id)=org_information1 . команда perf предложила нам изменить и использовать to_number, чтобы можно было использовать индекс. теперь можно использовать индекс, но мы сталкиваемся с проблемой преобразования, потому что org_information1 также содержит буквенно-цифровую строку. таким образом, преобразование их в число приведет к ошибке.