ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Оптимизация условия WHERE IN
13 сообщений из 13, страница 1 из 1
Оптимизация условия WHERE IN
    #39281074
alexeyyv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyyv
Гость
Добрый день, эксперты!

Оптимизировал следующий запрос заменив оператор OR между условиями WHERE, на подзапросы с UNION-ами.
СУБД Oracle 11g, 16 ядер, 64 ГБ RAM.
Количество строк в "test_sample" - 130 млн.
Количество строк в "increm" - 10 тыс.
Типы данных всех полей NVARCHAR2(от 20 до 300), кроме p.S3 - это NUMBER.
Индексы на каждый атрибут в WHERE есть. Составные индексы не использовал.
Среднее время выполнения 70-80 минут, другой нагрузки на оракл в момент выполнения запроса нет.
Можно ли что то улучшить?


Код: 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.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
SELECT
p.ENTITY_ID,
p.S1,
p.S2,
p.S3

FROM test_sample p
    
WHERE p.S3 IN (
SELECT DISTINCT S3 
FROM test_sample p, increm s 
WHERE s.S1=p.S1 AND s.S2=p.S2 

UNION
SELECT DISTINCT S3 
FROM test_sample p, increm s 
WHERE s.F1=p.F1 AND s.F4=p.F4 AND s.F5=p.F5 


UNION 
SELECT DISTINCT S3 
FROM test_sample p, increm s 
WHERE s.F2=p.F2 AND s.F3=p.F3 AND s.F6=p.F6 AND s.F7=p.F7


UNION 
SELECT DISTINCT S3 
FROM test_sample p, increm s 
WHERE s.F2=p.F2 AND s.F3=p.F3 AND s.F4=p.F4 AND s.F5=p.F5

UNION 
SELECT DISTINCT S3 
FROM test_sample p, increm s 
WHERE s.F1=p.F1 AND s.F2=p.F2 AND s.F8=p.F8 AND s.F7=p.F7 

UNION 
SELECT DISTINCT S3 
FROM test_sample p, increm s 
WHERE s.F2=p.F2 AND s.F3=p.F3 AND s.F8=p.F8 AND s.F7=p.F7 

UNION 
SELECT DISTINCT S3 
FROM test_sample p, increm s 
WHERE s.F1=p.F1 AND s.F2=p.F2 AND s.F6=p.F6 AND s.F7=p.F7

)
...
Рейтинг: 0 / 0
27.07.2016, 12:36:21
| Ответить | Цитировать | Написать  
Оптимизация условия WHERE IN
    #39281106
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
alexeyyv,

Выкидывайте все DISTINCT-ы. В In они и так не нужны, а они еще и UNION-ом дублируются.
Возможно, UNION имеет смысл заменить на UNION ALL.

Наборы полей в секциях WHERE всегда постоянные или варьируются?
...
Рейтинг: 0 / 0
27.07.2016, 12:55:14
| Ответить | Цитировать | Написать  
Оптимизация условия WHERE IN
    #39281134
ora601
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ora601
Участник
alexeyyv,

План давай в студию) И вообщем то проверь на NL/HASH ведь если на выходе джоина у тебя не много данных, то там везде должен прослеживаться NL, к тому же есть идексы на все колонки (а проверить что они используются btw ? ) , т.к. если там хєш то можно упасть в какой нибудь не оптимальный multi-pass на твоих 130 млн. UNION и DISTINCT тоже могут быть проблемой, если в каждой операции возвращаеться достаточно большое количество строк ( 6 раз сортировать большой сет после добавления очередной порции, ну как то вообще не оптимально, верно ?)
...
Рейтинг: 0 / 0
27.07.2016, 13:11:46
| Ответить | Цитировать | Написать  
Оптимизация условия WHERE IN
    #39281153
ora601
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ora601
Участник
alexeyyvСоставные индексы не использовал.

Не заметил. Конечно они здесь нужны.
...
Рейтинг: 0 / 0
27.07.2016, 13:21:19
| Ответить | Цитировать | Написать  
Оптимизация условия WHERE IN
    #39281328
alexeyyv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyyv
Гость
miksoft,

набор полей всегда одинаковый. Пробую составные индексы на поля таблицы со 130 млн записей?
выкинул DISTINCT-ы, действительно их UNION заменит.
...
Рейтинг: 0 / 0
27.07.2016, 15:45:49
| Ответить | Цитировать | Написать  
Оптимизация условия WHERE IN
    #39281347
alexeyyv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyyv
Гость
ora601,

прикрепил план. Правильно я понимаю, что наибольший cost дают подзапросы?

Исключение distinct-ов значительно запрос не ускорило.
...
Рейтинг: 0 / 0
27.07.2016, 15:58:09
| Ответить | Цитировать | Написать  
Оптимизация условия WHERE IN
    #39281392
ora601
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ora601
Участник
alexeyyv,
план с gather_plan_statistics покажи. Если есть bitmap, то необязательно иметь составные индексы.
...
Рейтинг: 0 / 0
27.07.2016, 16:36:07
| Ответить | Цитировать | Написать  
Оптимизация условия WHERE IN
    #39281394
andreymx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andreymx
Участник
alexeyyvДобрый день, эксперты!

Оптимизировал следующий запрос заменив оператор OR между условиями WHERE, на подзапросы с UNION-ами.
***
Среднее время выполнения 70-80 минут, другой нагрузки на оракл в момент выполнения запроса нетмне кажется, оптимизация в результате выглядит как-то по-другому
...
Рейтинг: 0 / 0
27.07.2016, 16:38:58
| Ответить | Цитировать | Написать  
Оптимизация условия WHERE IN
    #39281404
heroin2
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
heroin2
Гость
union -> union all

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

increm каждый раз fullscan, можно вынести отдельно
...
Рейтинг: 0 / 0
27.07.2016, 16:48:47
| Ответить | Цитировать | Написать  
Оптимизация условия WHERE IN
    #39281406
Фотография Fogel
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Fogel
Участник
alexeyyv, посмотрев ваш план, попробовал бы запрос переписать в такой вид:
Код: 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.
25.
SELECT
p.ENTITY_ID, p.S1, p.S2, p.S3
FROM test_sample p  
WHERE p.S3 IN (SELECT /*+ PRECOMPUTE_SUBQUERY */ S3 
FROM test_sample p, increm s 
WHERE s.S1=p.S1 AND s.S2=p.S2)

UNION all

SELECT
p.ENTITY_ID, p.S1, p.S2, p.S3
FROM test_sample p    
WHERE p.S3 IN (SELECT /*+ PRECOMPUTE_SUBQUERY */ S3 
FROM test_sample p, increm s 
WHERE s.F1=p.F1 AND s.F4=p.F4 AND s.F5=p.F5) 

UNION ALL
....
UNION ALL

SELECT
p.ENTITY_ID, p.S1, p.S2, p.S3   
WHERE p.S3 IN (SELECT /*+ PRECOMPUTE_SUBQUERY */ S3 
FROM test_sample p, increm s 
WHERE s.F1=p.F1 AND s.F2=p.F2 AND s.F6=p.F6 AND s.F7=p.F7



в общем смысл, думаю, понятен
...
Рейтинг: 0 / 0
27.07.2016, 16:50:17
| Ответить | Цитировать | Написать  
Оптимизация условия WHERE IN
    #39281423
andreymx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andreymx
Участник
alexeyyv,

каждый из подзапросов работает быстро? сколько строк возвращает?
...
Рейтинг: 0 / 0
27.07.2016, 17:15:29
| Ответить | Цитировать | Написать  
Оптимизация условия WHERE IN
    #39281443
Янеробот
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Янеробот
Гость
Имхо
1. union all вместо union и дистинкт вынести наверх
2. во все индексы добавить s3
...
Рейтинг: 0 / 0
27.07.2016, 17:46:02
| Ответить | Цитировать | Написать  
Оптимизация условия WHERE IN
    #39281503
Andrey.L
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Andrey.L
Участник
а если так?
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
SELECT ...
FROM test_sample p0
WHERE EXISTS(
SELECT NULL
FROM test_sample p, increm s 
WHERE s.S1=p.S1 AND s.S2=p.S2 
AND p0.s3 = p.s3)
OR EXISTS(
SELECT NULL
FROM test_sample p, increm s 
WHERE s.F1=p.F1 AND s.F4=p.F4 AND s.F5=p.F5 
AND p0.s3 = p.s3)
...
OR EXISTS(
SELECT NULL
FROM test_sample p, increm s 
WHERE s.F1=p.F1 AND s.F2=p.F2 AND s.F6=p.F6 AND s.F7=p.F7
AND p0.s3 = p.s3)
...
Рейтинг: 0 / 0
27.07.2016, 19:28:27
| Ответить | Цитировать | Написать  
13 сообщений из 13, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Оптимизация условия WHERE IN
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&msg=39281074&tid=1887787]:
 0ms
get settings:
 5ms
get forum list:
 9ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 715ms
get topic data:
 11ms
get forum data:
 3ms
get page messages:
 46ms
get tp. blocked users:
 1ms
others: 187ms
total:  981ms
созд. / загр.: 981ms
x
x
Закрыть


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