Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Оптимизация условия WHERE IN / 13 сообщений из 13, страница 1 из 1
27.07.2016, 12:36:21
    #39281074
alexeyyv
alexeyyv
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация условия WHERE IN
Добрый день, эксперты!

Оптимизировал следующий запрос заменив оператор 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:55:14
    #39281106
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация условия WHERE IN
alexeyyv,

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

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

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

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

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

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

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

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

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

increm каждый раз fullscan, можно вынести отдельно
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.07.2016, 16:50:17
    #39281406
Fogel
Fogel
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация условия WHERE IN
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, 17:15:29
    #39281423
andreymx
andreymx
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация условия WHERE IN
alexeyyv,

каждый из подзапросов работает быстро? сколько строк возвращает?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.07.2016, 17:46:02
    #39281443
Янеробот
Янеробот
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация условия WHERE IN
Имхо
1. union all вместо union и дистинкт вынести наверх
2. во все индексы добавить s3
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.07.2016, 19:28:27
    #39281503
Andrey.L
Andrey.L
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация условия WHERE IN
а если так?
Код: 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
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Оптимизация условия WHERE IN / 13 сообщений из 13, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 437ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1887787]:
 0ms
get settings:
 5ms
get forum list:
 13ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 177ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 42ms
get tp. blocked users:
 1ms
others: 185ms
total:  437ms
x
x
Закрыть


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