Не работает coalesce() для поля типа TIMESTAMP в Query метода репозитория / Java

2.0.29

Планшетная версия Контакт Правила FAQ Помощь

Гость

Нов. | Гор. | Избр.

Действия ...

Доб. в избранное
Игнор. тему
Прикреп. тему
Пометить прочит. / непрочит.
Фильтр:
Сообщения автора темы
Сообщение содержит вложения
Сообщение содержит картинки
Сообщение содержит видеоклипы
Сообщение содержит аудиоклипы
Сообщение содержит картинки или видео 18+

Форумы / Java [игнор отключен] [закрыт для гостей] / Не работает coalesce() для поля типа TIMESTAMP в Query метода репозитория / 22 сообщений из 22, страница 1 из 1

21.06.2021, 13:21

#40079131

chikaginsk

Участник

Сообщения: 117
Рейтинг: 0 / 0

Не работает coalesce() для поля типа TIMESTAMP в Query метода репозитория

Здравствуйте. В Query метода репозитория (extends JpaRepository<..., Long>) не получается заставить работать функцию coalesce() в условии для поля типа TIMESTAMP.
В запросе (БД Oracle 12.2.0.1.0) есть условие:

Код: plsql

"... and close_date is null"

Но с таким условием не работает индекс и план запроса очень плохой (работает медленно).
Чтобы индекс заработал пришлось поменять условие на:

Код: plsql

"... and coalesce(close_date, to_timestamp('3000-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) = to_timestamp('3000-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')"

Т.е. для NULL значений поля типа TIMESTAMP возвращаем значение определённой даты и выбираем их же по условию равенства с этой датой. При этом индекс заработал и план запроса стал значительно лучше (смотрю это в DataGrip).
Но когда пытаюсь перенести этот код в Query метода репозитория (привёл "to_timestamp()" для примера)

Код: java

1.
2.
3.
4.
5.
6.

    @Query("from DepartmentDocVersion bp join fetch bp.master" +
            " where bp.master.departmentType in ?1 " +
            "   and bp.master.openDate is not null" +
            "   and bp.master.openDate <= current_date" +
            "   and coalesce(bp.master.close_date, to_timestamp('3000-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) = to_timestamp('3000-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')")
    List<DepartmentDocVersion> findAllOnlyActiveCurrentPeriodCloseDateNull(List<DepartmentType> departmentTypeList);

то при сборке проекта получаю ошибку:

Код: java

Caused by: org.hibernate.QueryException: could not resolve property: close_date of: <класс - модель таблицы, содержащей поле "close_date">

Проще говоря, в HQL Query нужно заменить условие "timestamp is null" на условие, которое бы заменяло NULL значения поля типа TIMESTAMP на постоянное значение и сравнивало бы с этим же значением.
Может кто-то сталкивался с подобной задачей или есть более корректное решение?

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

21.06.2021, 14:01

#40079145

chikaginsk

Участник

Сообщения: 117
Рейтинг: 0 / 0

Не работает coalesce() для поля типа TIMESTAMP в Query метода репозитория

upd
Ошибка была из-за того, что в Query неправильно имя поля указал: было " and coalesce(bp.master.close_date,...", а нужно " and coalesce(bp.master.closeDate,".
Но главный вопрос темы пока открыт.

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

21.06.2021, 14:46

#40079157

Stanislav Bashkyrtsev

Гость

Не работает coalesce() для поля типа TIMESTAMP в Query метода репозитория

А как выглядит DDL для создания индекса? И как так получилось что с coalesce(close_date...) не выбрасывало ту же ошибку? Это был нативный запрос?
Было бы здорово еще увидеть план запроса.

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

21.06.2021, 15:55

#40079174

chikaginsk

Участник

Сообщения: 117
Рейтинг: 0 / 0

Не работает coalesce() для поля типа TIMESTAMP в Query метода репозитория

Stanislav Bashkyrtsev,

DDL для создания индекса (для двух полей timestamp) самый элементарный:

Код: plsql

create index IDX_DEPARTMENT_DOC_MASTER_OPENDT_CLOSEDT on DEPARTMENT_DOC_MASTER(open_date, close_date);

Как временное решение пока решил использовать:

Код: java

1.
2.
3.
4.
5.
6.

    @Query("from DepartmentDocVersion bp join fetch bp.master" +
            " where bp.master.departmentType in ?1 " +
            "   and bp.master.openDate is not null" +
            "   and bp.master.openDate <= current_date" +
            "   and coalesce(bp.master.close_date, current_timestamp) = current_timestamp")
    List<DepartmentDocVersion> findAllOnlyActiveCurrentPeriodCloseDateNull(List<DepartmentType> departmentTypeList);

т.к., учитывая миллисекунды, подобных значений в поле с текущим current_timestamp должно быть немного (если они вообще попадутся), а значений с NULL в поле бОльшая часть. Зато используется индекс и Cost значительно ниже.
План запроса без использования индекса:

используется условие "... and ddm.open_date <= current_date and ddm.close_date is null"

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.

Plan hash value: 901981106
 
-----------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                        | 72322 |    20M|       |  2191   (1)| 00:00:01 |
|*  1 |  HASH JOIN         |                        | 72322 |    20M|    10M|  2191   (1)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| DEPARTMENT_DOC_MASTER  | 72322 |  9393K|       |   446   (1)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| DEPARTMENT_DOC_VERSION | 85125 |    13M|       |   547   (1)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
"   1 - access(""DEPARTMENT0_"".""DEPARTMENT_DOC_MASTER_ID""=""DEPARTMENT1_"".""ID"")"
"   2 - filter(""DEPARTMENT1_"".""CLOSE_DATE"" IS NULL AND ""DEPARTMENT1_"".""DEPARTMENT_TYPE""='VSP' "
"              AND ""DEPARTMENT1_"".""OPEN_DATE""<=CURRENT_DATE)"
 
Note
-----
   - this is an adaptive plan

План запроса c использованием индекса:

используется условие "... and ddm.open_date <= current_date and coalesce(ddm.close_date, current_timestamp) = current_timestamp"

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.

Plan hash value: 662555323
 
---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                                     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                                          |   836 |   240K|   870   (1)| 00:00:01 |
|*  1 |  HASH JOIN                           |                                          |   836 |   240K|   870   (1)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| DEPARTMENT_DOC_MASTER                    |   836 |   108K|   323   (2)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | IDX_DEPARTMENT_DOC_MASTER_OPENDT_CLOSEDT |   846 |       |   301   (2)| 00:00:01 |
|   4 |   TABLE ACCESS FULL                  | DEPARTMENT_DOC_VERSION                   | 85125 |    13M|   547   (1)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
"   1 - access(""DEPARTMENT0_"".""DEPARTMENT_DOC_MASTER_ID""=""DEPARTMENT1_"".""ID"")"
"   2 - filter(""DEPARTMENT1_"".""DEPARTMENT_TYPE""='VSP')"
"   3 - access(""DEPARTMENT1_"".""OPEN_DATE""<=CURRENT_DATE)"
"       filter(SYS_EXTRACT_UTC(COALESCE(INTERNAL_FUNCTION(""DEPARTMENT1_"".""CLOSE_DATE""),CURRENT_TIMESTAMP(6)))=SYS_EXTRACT_"
              UTC(CURRENT_TIMESTAMP(6)))
 
Note
-----
   - this is an adaptive plan

P.S. "И как так получилось что с coalesce(close_date...) не выбрасывало ту же ошибку?"
Эта форма записи работает в DataGrip, но не в Java-коде (там поля Entity называются не так, как поля таблицы).
Если есть замечания или советы, как ещё ускорить выполнение запроса, буду только рад.

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

21.06.2021, 16:59

#40079183

Stanislav Bashkyrtsev

Гость

Не работает coalesce() для поля типа TIMESTAMP в Query метода репозитория

Хм. Если эти два запроса должны выдавать один и тот же результат, почему результирующее кол-во строк выдает очень разное: 72322 vs 836?

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

21.06.2021, 17:42

#40079192

chikaginsk

Участник

Сообщения: 117
Рейтинг: 0 / 0

Не работает coalesce() для поля типа TIMESTAMP в Query метода репозитория

Stanislav Bashkyrtsev,

Странно, что так в планах запросов отображается, но оба запроса возвращают одинаковое кол-во строк (проверил с count(*)) и оно даже на 250 больше, чем в первом плане отображено.

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

21.06.2021, 18:17

#40079202

Stanislav Bashkyrtsev

Гость

Не работает coalesce() для поля типа TIMESTAMP в Query метода репозитория

Ну это видимо выводится предполагаемое кол-во записей, основанное на статистике. Почему-то Oracle считает что если close_date is null, то не использовать индекс по open_date бесполезно. Странно, возможно это из-за того что под open_date условие подпадает слишком много строк и Оракл считает что по индексу идти бесполезно.

Наверняка Ораклу можно как-то сказать пересчитать эту статистику. Может это его заставит передумать. Так-то запрос с coalesce() быстрей работать не может.

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

21.06.2021, 18:19

#40079203

Leonid Kudryavtsev

Участник

Сообщения: 9 300
Рейтинг: 0 / 0

Не работает coalesce() для поля типа TIMESTAMP в Query метода репозитория

В плане продолжают оставаться Table Access Full и Hash Join

т.ч. Ваше утверждение про "индекс заработал" полное лукавство

Для Вашего запроса должно быть Nested Loop's и два Index'а

create index IDX_DEPARTMENT_DOC_MASTER_OPENDT_CLOSEDT on DEPARTMENT_DOC_MASTER(open_date, close_date);

Для оптимизации выражения может использоваться только ОДИН индекс. По логике запроса, доступ должен отбирать данные по полю departmentType , т.ч. именно это поле и должно быть в индексе.

Т.е. правильный индекс:

department_type, open_date, close_date

Все остальное, т.е. любые планы, где есть Hash Join и/или Table Access Full - это порнография. Еще большая порнография, только если Вы увидете в планах Index to Bitmap conversion и прочие другие слова со словами Bitmap. Это будет уже не просто порно, а хард порно для особых ценителей извращений с сервером.

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

IMHO

Проще говоря, в HQL Query нужно заменить условие "timestamp is null" на условие, которое бы заменяло NULL значения поля...

НЕ нужно.

Нужен нормальный индекс содержащий department_type (именно по нему и будет идти B-tree поиск) и дополнительно поля с датами для оптимизации чтения данных из таблицы (AFAIK скорее всего B-Tree поиск по ним идти не будет, просто будет налагаться filter, но будет не нужно лишнее случайное обращение к таблице за данными для наложения filter)

AFAIK

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

21.06.2021, 18:30

#40079207

Leonid Kudryavtsev

Участник

Сообщения: 9 300
Рейтинг: 0 / 0

Не работает coalesce() для поля типа TIMESTAMP в Query метода репозитория

Stanislav Bashkyrtsev

https://www.sql.ru/forum/1336929/medlennaya-rabota-zaprosa-na-odnom-iz-znacheniy-parametra

На мой взгляд, "нормальный" план это

Nested Loop
по master в качестве главной таблице с отбором по индексу по полю departmentType (плюс остальные поля для оптимизации)
дальше уходим в detail с отбором по индексу по полю DEPARTMENT_DOC_MASTER_ID

Скорее всего статистика и распределение данных такая, что Oracle (скорее всего осмысленно!) считает, что ПОСЛЕДОВАТЕЛЬНЫЙ full table scan по таблице будет БЫСТРЕЕ чем СЛУЧАЙНЫЙ доступ по RowId из индекса. И на индексы в лучшем случае забивает нано-болт (full table scan), а в худшем, начинает глючить и воспаленная нейронная сеть оптимизатора генерирует разную порнографию.

Тот, кто проектировал базу, не подумал, что при исторической таблице хорошо бы еще в качестве дополнительных полей даты добавить.

Самое простое, нарезать более широкие индексы. Где будут и истинно "ключевые" поля (поиск по B-tree) и дополнительные поля с датами, что бы случайный доступ для наложения фильтра был НЕ НУЖЕН.

По b-tree будет быстро находится departmentType , потом ПОСЛЕДОВАТЕЛЬНЫМ чтением из ИНДЕКСА будут извлекаться даты, накладываться фильтр и лишь для полностью подходящих под фильтр записей будет случайный доступ к таблице.

IMHO, могу ошибаться. Но скорее всего это поможет.

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

22.06.2021, 13:26

#40079333

Stanislav Bashkyrtsev

Гость

Не работает coalesce() для поля типа TIMESTAMP в Query метода репозитория

В общем я еще поискал немного инфы: похоже что когда мы используем функции (в данном случае coalesce()), Oracle не может расчитывать статистику. Хотя его можно заставить это делать с помощью каких-то средств в DBMS_STATS - некие Extended Statistics. Видимо из-за этого он и использует индекс. А в случае когда функции нет, он опирается на статистику, а та неверная и говорит ему что будет выбрано очень много записей из таблиц. Поэтому оптимизатор считает что индексы использовать бессмысленно.

Но это все академический интерес, а чтоб запрос выполнялся на самом деле быстро, нужно следовать советам от Leonid Kudryavtsev (если я правильно понимаю его эмоциональные выплески):
1. Нужен индекс на те поля которые участвуют в поиске
2. Нужен индекс на колонку из внутренней таблицы по которой те объединяются (department_doc_master.id) потому как по этой колоне будет поиск во время join'a. Если создать индекс где используется эта join колонка, а также те колонки по которым мы отфильтровуем записи во внутренней таблице, то наверно (не проверял) по этому одному индексу поиск и пройдет. И тогда вообще в табличный файл СУБД пойдет только когда все что нужно отфильтровали.

Однако возможно поля с низкой мощностью (cardinality) не имеет смысл добавлять в этот индекс (подозреваю что это department_type, и может даже close_date раз там одни null'ы). Потому как в B+ Tree он не особо поможет. По таким колонкам строят Bitmap индексы, однако их не получится одновременно смешивать с деревьями. Т.е. нужно все-таки протестировать разницу и точно узнать стоит ли department_type и close_date включать в индекс.

PS: т.к. заведено две темы - не понятно теперь в какую постить. Это очень неудобно, больше так не делай плз.

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

22.06.2021, 14:35

#40079350

Leonid Kudryavtsev

Участник

Сообщения: 9 300
Рейтинг: 0 / 0

Не работает coalesce() для поля типа TIMESTAMP в Query метода репозитория

Stanislav Bashkyrtsev

По таким колонкам строят Bitmap индексы, однако их не получится одновременно смешивать с деревьями.

"Вредные советы" ( C ) Маршак

1) Bitmap для OLAP. В OLTP приложении может сделать очень-очень плохо . Начиная от блокировок при update и заканчивая совершенно дурацкими планами "новомодного" оптимизатора

2) Современный Oracle умеет их смешивать . Мало того, иногда, когда несколько B-Tree по разным колонкам, он B-Tree преобразует в Bitmap и начинает смешивать - но с точки зрения плана и скорости, обычно это полная порнография и от таких планов все встает колом AFAIK. Я бы нафиг это фичу бы отключал, но я не админ, и обычно удается обойтись хинтами ))).

Stanislav Bashkyrtsev

Однако возможно поля с низкой мощностью (cardinality) не имеет смысл добавлять в этот индекс

Нужно.

Вы не поняли главной идеи. Дело не в кардинальности, дело в том, что для наложения фильтра будет НЕ нужен СЛУЧАЙНЫЙ (дорогой) доступ в таблицу. Все поля для фильтрации уже будут в индексе и будут браться ПОСЛЕДОВАТЕЛЬНЫМ ЧТЕНИЕМ (быстрым) из индекса, исключив СЛУЧАЙНЫЙ (медленный) доступ к таблицы

Если поля будут не все, то для полей для фильтрации придется лезть в основную таблицу. И Oracle может посчитать, что последовательный Full Table Scan по таблице, быстрее чем Index + случайный (медленный) доступ в Table

Я думаю, что статистика правильная, просто данные (историчность!) таковы, что индекс без учета дат не сильно помогает, а возможно даже замедляет работу. Тут дело не только в кардинальности, но например и в "размазонности" данных по таблице.

Note: Вроде стоимость последовательного и случайного доступа определяется https://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams144.htm#REFRN10143
Но трогать его не следует. Т.к. ясно, что индексы нарезаны крайне не оптимально. Нужно добавлять индексы.

Note2:
Когда работал с CC&B, вообще пришлось огромное кол-во полей в индекс включать, все поля которые были использовались в запросе, и access/filter и просто из select list'а. Распределение данных было таково, что нужный результат был "размазан" по всей таблице CI_FT, и последовательный доступ приводил к извлечению массы ненужной информации, фактически всей таблице. Единственный вариант который смогли придумать, продублировать таблицу в индексе. Оно конечно выглядело коряво, но по крайне мере стало работать (запросы с 2-3 минут , удалось довести до 5-10 сек. в худшем случае). Т.к. данные стали хранится последовательно.
Пытался играться с Index organization / Cluster Table - но на нашей версии Oracle оно работало "странно". Т.ч. отказались, и когда было нужно, просто дублировали информацию из таблице в индексы.
Место на диске сейчас не является проблемой. Т.ч. экономить место в индексах в ущерб производительности - я лично смысла не виж.

Stanislav Bashkyrtsev

2. Нужен индекс на колонку из внутренней таблицы по которой те объединяются (department_doc_master.id) потому как по этой колоне будет поиск во время join'a.

У ТС он есть, но так же не подхватился.
Т.ч. только добавлять даты и по максимому. IMHO

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

22.06.2021, 14:55

#40079359

Stanislav Bashkyrtsev

Гость

Не работает coalesce() для поля типа TIMESTAMP в Query метода репозитория

Leonid Kudryavtsev1) Bitmap для OLAP. В OLTP приложении может сделать очень-очень плохо. Начиная от блокировок при update и заканчивая совершенно дурацкими планами "новомодного" оптимизатораНу как бы если у тебя boolean/enum колонки, то плохо-неплохо, а выбора нет. Деревья в таком случае бессмысленны (хотя в композитных индексах может и норм будет).
Leonid Kudryavtsev2) Современный Oracle умеет их смешивать.Мм.. не слышал о таком. Есть ссылочка? Leonid KudryavtsevМало того, иногда, когда несколько B-Tree по разным колонкам, он B-Tree преобразует в Bitmap и начинает смешиватьНу преобразование индексов на ходу и изначальное построение bitmap+btree - эт разные вещи.
Leonid KudryavtsevВы не поняли главной идеи. Дело не в кардинальности, дело в том, что для наложения фильтра будет НЕ нужен СЛУЧАЙНЫЙ (дорогой) доступ в таблицу. Все поля для фильтрации уже будут в индексе и будут браться ПОСЛЕДОВАТЕЛЬНЫМ ЧТЕНИЕМ (быстрым) из индекса, исключив СЛУЧАЙНЫЙ (медленный) доступ к таблицыИндексы - это доп структура которую нужно поддерживать. Да, мы может получим доп буст на пару миллисекунд в SELECT если создадим такой индекс, но при этом можем просесть при вставке/обновлении, а также будет занимать доп место. Плюс нужно понимать что идеальная картина нарушается еще больше если какие-то записи одновременно обновляются . Ибо для поддержания MVCC возможно базе все равно прийдется ходить по табличному файлу (в индексе будут два значения - новое и старое, однако MVCC инфа в индексах не хранится - прийдется идти в таблицу и проверять версию).

Поэтому я бы рекомендовал проверить разницу в производительности. И если она окажется не существенной, то лишние поля в индекс не добавлять. Все что мы тут обсуждаем - это догадки. TC же может проверить это все на практике, зачем ему гадать.
Leonid KudryavtsevЯ думаю, что статистика правильная, просто данные (историчность!) таковы, что индекс без учета дат не сильно помогает, а возможно даже замедляет работу. ТС писал выше что с индексом работает намного быстрей. И кол-во записей которые он получает намного меньше чем то что показывает статистика.

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

22.06.2021, 17:23

#40079402

Leonid Kudryavtsev

Участник

Сообщения: 9 300
Рейтинг: 0 / 0

Не работает coalesce() для поля типа TIMESTAMP в Query метода репозитория

Stanislav Bashkyrtsev

Ну как бы если у тебя boolean/enum колонки, то плохо-неплохо, а выбора нет. Деревья в таком случае бессмысленны (хотя в композитных индексах может и норм будет).

В OLAP - да.
В OLTP - нет. Т.к. битмап индекс на 2 значения, при любом update заблокирует половину таблицы. А оно такое надо?

Могу ошибаться (т.к. не спец. по битмапам), но битмап это не для OLTP.

Stanislav Bashkyrtsev

Leonid Kudryavtsev2) Современный Oracle умеет их смешивать.

Мм.. не слышал о таком. Есть ссылочка?

http://www.dba-oracle.com/t_bitmap_conversion_to_rowid.htm
И сопустствующие.
Могу ошибаться. Когда такое видно в плане - тут же возникает желание похинтовать.

Stanislav Bashkyrtsev

Да, мы может получим доп буст на пару миллисекунд в SELECT если создадим такой индекс

Нужно мерить на реальных задачах.

У меня было ускорение с 2-3 и более минут, до 5-10 секунд на реальных запросах от CC&B

Когда SELECT просто не работает (а у ТС он именно что НЕ работает), оптимизировать пару миллисекунд при вставке/обновлении - такое себе. А экономия места в 2021 году вообще смешно ))).

Stanislav Bashkyrtsev

Ибо для поддержания MVCC возможно базе все равно прийдется ходить по табличному файлу (в индексе будут два значения - новое и старое, однако MVCC инфа в индексах не хранится - прийдется идти в таблицу и проверять версию).

Так глубоко не знаю.

Stanislav Bashkyrtsev

ТС писал выше что с индексом работает намного быстрей. И кол-во записей которые он получает намного меньше чем то что показывает статистика.

У ТС Hash Join и Full Table Scan на подчиненную таблица.
Т.е. текущие индексы не работают вообще. В таком случае обсуждать, быстро/медленно IMHO бессмысленно.

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

Единственно правильный план, два доступа по индексу и Nested Loop. IMHO

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

23.06.2021, 13:15

#40079572

chikaginsk

Участник

Сообщения: 117
Рейтинг: 0 / 0

Не работает coalesce() для поля типа TIMESTAMP в Query метода репозитория

Добрый день. Извиняюсь, что вчера не отвечал (экспериментировал с индексами).
Пока остановился на таком варианте запроса:

Это запрос, который Hibernate отправляет в БД Oracle.

Код: plsql

1.
2.
3.
4.
5.

select * from department_doc_version department0_
         inner join department_doc_master department1_ on department0_.department_doc_master_id = department1_.id
where (department1_.department_type in ('VSP'))
  and (department1_.open_date is not null)
  and (trunc(current_date) between trunc(department1_.open_date) and nvl(trunc(department1_.close_date), trunc(current_date)));

В ORM запросе NVL заменено COALESCE.

Код: java

1.
2.
3.
4.
5.

    @Query("from DepartmentDocVersion bp join fetch bp.master" +
            " where bp.master.departmentType in ?1 " +
            "   and bp.master.openDate is not null" +
            "   and trunc(current_date) between trunc(bp.master.openDate) and coalesce(trunc(bp.master.closeDate), trunc(current_date))"
    )

Воспользовался советом Leonid Kudryavtsev (ещё раз благодарю) и создал индекс, который бы охватывал все поля в условиях выборки:

Код: plsql

create index idx_department_doc_master_type_opendt_closedt_trunc on DEPARTMENT_DOC_MASTER(department_type, trunc(open_date), trunc(close_date));

Индекс отображается в плане запроса (стоимость значительно уменьшилась - было почти 2200):

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.

Plan hash value: 3411709025
 
--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                                                | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                                                     |   210 | 62370 |   445   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                        |                                                     |   210 | 62370 |   445   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| DEPARTMENT_DOC_MASTER                               |   210 | 28350 |    25   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | IDX_DEPARTMENT_DOC_MASTER_TYPE_OPENDT_CLOSEDT_TRUNC |   755 |       |     3   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID BATCHED| DEPARTMENT_DOC_VERSION                              |     1 |   162 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN                  | IDX_DEPARTMENT_DOC_VERSION_MASTER_ID                |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
"   2 - filter(NVL(TRUNC(INTERNAL_FUNCTION(""DEPARTMENT1_"".""CLOSE_DATE"")),TRUNC(CURRENT_DATE))>=TRUNC(CURRENT_DATE) AND "
"              ""DEPARTMENT1_"".""OPEN_DATE"" IS NOT NULL)"
"   3 - access(""DEPARTMENT1_"".""DEPARTMENT_TYPE""='VSP' AND TRUNC(INTERNAL_FUNCTION(""OPEN_DATE""))<=TRUNC(CURRENT_DATE))"
"   5 - access(""DEPARTMENT0_"".""DEPARTMENT_DOC_MASTER_ID""=""DEPARTMENT1_"".""ID"")"
 
Note
-----
   - this is an adaptive plan

Правда столкнулся со странным поведением БД - вчера стоимость запроса (после создания этого индекса) составляла те же 445, а сегодня (когда подключился заново и выполнил план запроса) была больше 900.
Снёс все индексы (вчера создавал на разные комбинации полей, т.к. "ускорить" нужно было ещё пару запросов), создал указанный выше индекс заново и стоимость в плане запроса опять стала 445.

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

23.06.2021, 19:41

#40079682

Leonid Kudryavtsev

Участник

Сообщения: 9 300
Рейтинг: 0 / 0

Не работает coalesce() для поля типа TIMESTAMP в Query метода репозитория

AFAIK Насколько я знаю, стоимость измеряется в попугаях. Т.ч. смотреть на нее можно, но придавать ей большое значение не стоит.

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

24.06.2021, 05:39

#40079732

Basil A. Sidorov

Участник

Сообщения: 11 633
Рейтинг: 0 / 0

Не работает coalesce() для поля типа TIMESTAMP в Query метода репозитория

Можно сопоставлять стоимость разных планов одного и того же запроса (без учёта хинтов - подсказок для оптимизатора).
Если запрос меняется, например, в части where, то это уже другой запрос, даже если по своему смыслу он должен выдать идентичный набор записей.

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

24.06.2021, 13:28

#40079828

chikaginsk

Участник

Сообщения: 117
Рейтинг: 0 / 0

Не работает coalesce() для поля типа TIMESTAMP в Query метода репозитория

Basil A. Sidorov,
В том-то и дело, что сам запрос не менялся вообще (условия в where тоже). Но сегодня опять подключился к БД, выполнил план запроса и вместо вчерашних Cost = 445 ( 22339104 ) и использования индексов на обеих таблицах join-а увидел это:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.

Plan hash value: 901981106
 
---------------------------------------------------------------------------------------------
| Id  | Operation          | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                        |  4193 |  1216K|   997   (1)| 00:00:01 |
|*  1 |  HASH JOIN         |                        |  4193 |  1216K|   997   (1)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| DEPARTMENT_DOC_MASTER  |  4193 |   552K|   450   (2)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| DEPARTMENT_DOC_VERSION | 85125 |    13M|   547   (1)| 00:00:01 |
---------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
"   1 - access(""DEPARTMENT0_"".""DEPARTMENT_DOC_MASTER_ID""=""DEPARTMENT1_"".""ID"")"
"   2 - filter(NVL(TRUNC(INTERNAL_FUNCTION(""DEPARTMENT1_"".""CLOSE_DATE"")),TRUNC(CURRENT"
"              _DATE))>=TRUNC(CURRENT_DATE) AND ""DEPARTMENT1_"".""DEPARTMENT_TYPE""='VSP' AND "
"              TRUNC(INTERNAL_FUNCTION(""OPEN_DATE""))<=TRUNC(CURRENT_DATE) AND "
"              ""DEPARTMENT1_"".""OPEN_DATE"" IS NOT NULL)"
 
Note
-----
   - this is an adaptive plan

Причём, этот индекс сегодня не работает и на других запросах, на которых вчера значения Cost тоже были значительно лучше с его использованием.

Код: sql

create index idx_department_doc_master_type_opendt_closedt_trunc on DEPARTMENT_DOC_MASTER(department_type, trunc(open_date), trunc(close_date));

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

24.06.2021, 13:36

#40079834

mayton

Участник

Откуда: loopback
Сообщения: 53 422
Рейтинг: 0 / 0

Не работает coalesce() для поля типа TIMESTAMP в Query метода репозитория

chikaginsk

Сразу после создания индексов сделай dbms_stats.gather_index_stats или *table_stats.

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

24.06.2021, 13:38

#40079835

Андрей Панфилов

Участник

Откуда: Москва > Melbourne
Сообщения: 3 331
Рейтинг: 0 / 0

Не работает coalesce() для поля типа TIMESTAMP в Query метода репозитория

chikaginsk

сегодня опять

Код: plsql

1.
2.
3.

Note
-----
    - this is an adaptive plan

вчера было вчера, а сегодня - уже сегодня, оракл решил что так будет лучше

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

24.06.2021, 16:33

#40079894

chikaginsk

Участник

Сообщения: 117
Рейтинг: 0 / 0

Не работает coalesce() для поля типа TIMESTAMP в Query метода репозитория

mayton,

А это актуально только сразу после создания индексов? Нельзя таким методом заставить использовать уже ранее (вчера) созданный индекс?
Попробовал несколько вариантов dbms_stats.gather_table_stats (с разными параметрами, методами оптимизации) и dbms_stats.gather_index_stats не удаляя индекса и результата не получил (план запроса остался прежним).

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

25.06.2021, 09:38

#40080027

chikaginsk

Участник

Сообщения: 117
Рейтинг: 0 / 0

Не работает coalesce() для поля типа TIMESTAMP в Query метода репозитория

mayton,

Не помогло. Вчера вечером "дропнул" индексы, создал заново

Код: plsql

1.
2.

create index IDX_DEPARTMENT_DOC_MASTER_TYPE_OPENDT_CLOSEDT_TRUNC on DEPARTMENT_DOC_MASTER(department_type, trunc(open_date), trunc(close_date));
create index IDX_DEPARTMENT_DOC_MASTER_TYPE_OPENDT_CLOSEDT on DEPARTMENT_DOC_MASTER(department_type, open_date, close_date);

и выполнил:

Код: plsql

1.
2.
3.
4.

begin
    dbms_stats.gather_index_stats(OWNNAME=>'TEST', INDNAME=>'IDX_DEPARTMENT_DOC_MASTER_TYPE_OPENDT_CLOSEDT_TRUNC');
    dbms_stats.gather_index_stats(OWNNAME=>'TEST', INDNAME=>'IDX_DEPARTMENT_DOC_MASTER_TYPE_OPENDT_CLOSEDT');
end;

И, как и раньше было, после создания стоимость плана запроса с использованием индекса 'IDX_DEPARTMENT_DOC_MASTER_TYPE_OPENDT_CLOSEDT_TRUNC' составляла 445, а сегодня утром опять 997.

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

25.06.2021, 09:39

#40080028

PetroNotC Sharp

Участник

Сообщения: 10 098
Рейтинг: 0 / 0

Не работает coalesce() для поля типа TIMESTAMP в Query метода репозитория

chikaginsk,

Вам в ветку по субд

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

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

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=59&mobile=1&tid=2120413]:	0ms
get settings:	16ms
get forum list:	6ms
check forum access:	1ms
check topic access:	1ms
track hit:	54ms
get topic data:	6ms
get forum data:	1ms
get page messages:	442ms
get tp. blocked users:	1ms
others:	401ms

total:	929ms