Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Алгоритм работы оптимизатора при запросе данных с объединением / 6 сообщений из 6, страница 1 из 1
04.03.2020, 00:24
    #39933779
bankir1980
bankir1980
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Алгоритм работы оптимизатора при запросе данных с объединением
Коллеги, объясните, или направьте, где почитать о такой ситуации
Есть 2 таблицы для хранения атрибутов клиентов, атрибуты бывают разных типов (текст, число, дата), но значение хранится в текстовом поле, а при необходимости преобразовывается в нужный формат.
1 таблица - собственно сам атрибут с указанием типа в столбце id_atr, имеющий так же поле id_value для связи с таблицей 2
2 таблица - хранит значение атрибута во времени (историчность) - имеет 3 поля id_value (связь с таблицей атрибутов), date_value - дата установки значения, cvalue - значение в текстовом виде

Возникла задача отфильтровать некий атрибут по всем клиентам, который в формате даты.

Связываю 2 эти таблицы через id_value с отбором по date_value и id_atr (это отбор однозначно атрибута в формате даты). Т.е. в cvalue всегда будет значение формата "DDDDMMRR", которое нужно привести к дате с помощью TO_DATE и уже потом отфильтровать по этому значению диапазон дат.

Если запускать вот такой запрос - всё работает как надо, в поле dt1 во всех записях даты, ошибок нет.
Код: plsql
1.
2.
3.
4.
5.
6.
7.
select
 v.id_value,
 str_to_date(v.id_value, substr(cvalue,1,8)) dt1
from 
   cus_add_atr_val v, cus_add_atr a
where a.id_atr=259 and a.id_value = v.id_value
   and date_value >=to_date('01012020','ddmmrrrr')



А если запустить вот такой запрос, то получаю ошибку преобразования в дату
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
select * from (
select
 v.id_value,
 str_to_date(v.id_value, substr(cvalue,1,8)) dt1
from 
  cus_add_atr_val v, cus_add_atr a
where a.id_atr=259 and a.id_value = v.id_value
   and date_value >=to_date('01012020','ddmmrrrr')
)
where dt1>=to_Date('01012020','ddmmrrrr')



в данных запросах вместо TO_DATE использую функцию STR_TO_DATE, в которой есть отлов EXCEPTION и вывод ID_VALUE в трейс

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
FUNCTION str_to_date
  ( id_value number,
    cvalue IN varchar2,
    format IN varchar2 default 'yyyymmdd')
  RETURN  date IS
BEGIN
    RETURN to_date(cvalue, format);
EXCEPTION
   WHEN others THEN
    dbms_output.put_line('error: ' ||id_value||' : '||cvalue);
    return null;
END;



Так вот - суть в том, что во втором случае вызов функции STR_TO_DATE происходит для всех записей таблицы cus_add_atr_val, а уже потом происходит объединение таблиц в WHERE по id_value и соотв. фильтрация по ID_ATR = 259

Однако, если вызов заменить на "str_to_date( a .id_value, substr(cvalue,1,8))", то ошибка не возникает, т.к. функция запускается только после объединения таблиц, что собственно и логично.

Проблема в том, что вместо STR_TO_DATE нужно использовать штатный TO_DATE и передаваться будет только v.cvalue и вызов функции будет до фильтрации, т.е. применяться ко всем строкам таблицы, а в других строках в cvalue не даты хранятся и соответственно возникает ошибка.
Может есть хинт какой, указывающий, что функции надо запускать строго после фильтрации и объединения таблиц?

Если честно, всегда думал, что вызов функций к полям выборки происходит только к результирующему набору, отфильтрованному в where.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.03.2020, 01:30
    #39933788
Sayan Malakshinov
Sayan Malakshinov
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Алгоритм работы оптимизатора при запросе данных с объединением
bankir1980
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
select * from (
select
 v.id_value,
 str_to_date(v.id_value, substr(cvalue,1,8)) dt1
from 
  cus_add_atr_val v, cus_add_atr a
where a.id_atr=259 and a.id_value = v.id_value
   and date_value >=to_date('01012020','ddmmrrrr')
)
where dt1>=to_Date('01012020','ddmmrrrr')


Это, конечно, легко лечится с помощью хинтов no_merge и opt_param('_optimizer_filter_pushdown' 'false')
ORA-01843: not a valid month при запросе из view

Но гораздо лучше было бы, если бы вы продублировали id_atr и в таблицу cus_add_atr_val и сделали бы условный вызов str_to_date только к строкам с необходимым типом, т.е.
Код: plsql
1.
decode(v.id_atr,259, str_to_date(v.id_value, substr(cvalue,1,8))) as dt1



а еще лучше если бы сделали вычислимые поля
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.03.2020, 01:38
    #39933789
Sayan Malakshinov
Sayan Malakshinov
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Алгоритм работы оптимизатора при запросе данных с объединением
bankir1980
Проблема в том, что вместо STR_TO_DATE нужно использовать штатный TO_DATE
кстати, в штатном как раз для этого c 12.2 добавили "on conversion error":
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/TO_DATE.html#GUID-D226FA7C-F7AD-41A0-BB1D-BD8EF9440118
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.03.2020, 02:03
    #39933796
bankir1980
bankir1980
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Алгоритм работы оптимизатора при запросе данных с объединением
Спасибо большое за наводку
И хинт помог и с decode сработало

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
select * from (
select 
 v.id_value,
 decode(a.id_atr, 259, to_date(substr(cvalue,1,8),'rrrrmmdd')) dt1
from 
cus_add_atr_val v, cus_add_atr a
where a.id_atr=259 and a.id_value = v.id_value
  and date_value >=to_date('01012020','ddmmrrrr')
) d
where dt1>=to_Date('01012020','ddmmrrrr')



Однако непонятен сам подход оптимизатора. Зачем выполнять функцию для всех строк таблицы, а не только для результата?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.03.2020, 02:05
    #39933798
Sayan Malakshinov
Sayan Malakshinov
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Алгоритм работы оптимизатора при запросе данных с объединением
bankir1980,

Пройди поиском по форуму по _optimizer_filter_pushdown и поймёшь, что оптимизатор все делает правильно с его точки зрения.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.03.2020, 06:42
    #39933847
-2-
-2-
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Алгоритм работы оптимизатора при запросе данных с объединением
bankir1980
Код: plsql
1.
    format IN varchar2 default 'yyyymmdd')

Нафига козе баян.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Алгоритм работы оптимизатора при запросе данных с объединением / 6 сообщений из 6, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 153ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1881506]:
 0ms
get settings:
 9ms
get forum list:
 14ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 43ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 48ms
get tp. blocked users:
 2ms
others: 14ms
total:  153ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]