Как оптимизировать запрос? / Oracle

ReSQL.ru

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

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Цитировать

Написать

Автор*:

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

Тема*:

Сообщение

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

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

Автор:

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

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

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

Вложение:

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

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

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

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

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Oracle [игнор отключен] [закрыт для гостей] / Как оптимизировать запрос?

49 сообщений из 49, показаны все 2 страниц

все

Как оптимизировать запрос?

#39297732

nata44845

Участник

Откуда: Красноярск

Сообщения: 356

Рейтинг: 0 / 0

Есть таблица записей, условно говоря это строки документов, содержат товар (ID), дату (со временем), ну и ссылку на шапку документа.

На таблицу уже есть индексы по дате, продукту, документу.

Есть запрос по движению товара, рекомендован индекс по продукту, то есть он через индекс продуктов собирает данные по продукту по всей таблице (500 млн строк), а это может быть и 1000 и 150 тысяч, а потом отбирает их по дате.
Причем даже если делаешь отбор за 2 дня все равно выбирает все продукты к примеру 115 тысяч строк, то есть длится запрос может пол часа, а вот если за те же 2 дня отбираешь по дате, то он отбирает 800 тысяч и по ним отбирает уже продукт, но получается это все равно за 4 минуты, видимо потому что эти данные все лежат в одном месте, а не размазаны по нескольким файлам.

Ставила в подсказки индекс и по дате тоже (оба), но тогда и за пол года начинает отбирать по дате, и все тушите свет.

Напрашивается индекс продукт-дата, делала причем даже делала DESC по дате, индекс получился какой-то очень громоздкий, 16 гб и постоянно висел в памяти почти весь, и был по объему даже больше, чем хранимая в памяти часть таблицы.
Не то.

Может какие-то хитрости есть? может партиционирование поможет, хотя не могу понять как.

...

Рейтинг:

0 / 0

25.08.2016, 12:27

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

Как оптимизировать запрос?

#39297744

mefman

Участник

Сообщения: 1 562

Рейтинг: 0 / 0

nata44845Может какие-то хитрости есть?
Нанять специалиста.

...

Рейтинг:

0 / 0

25.08.2016, 12:38

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

Как оптимизировать запрос?

#39297752

nata44845

Участник

Откуда: Красноярск

Сообщения: 356

Рейтинг: 0 / 0

mefman,

У нас разработчик есть на это дело, но он чего-то ускорять ничего не стремится, и так сойдет.
Просто интересно, что тут можно предпринять.

...

Рейтинг:

0 / 0

25.08.2016, 12:45

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

Как оптимизировать запрос?

#39297758

брат краткости

Гость

mefmannata44845Может какие-то хитрости есть?
Нанять специалиста.который сможет сформулировать тему менее литературно, но более содержательно.

...

Рейтинг:

0 / 0

25.08.2016, 12:47

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

Как оптимизировать запрос?

#39297765

nata44845

Участник

Откуда: Красноярск

Сообщения: 356

Рейтинг: 0 / 0

Давайте кратко: нужен отбор и по дате и по продукту, если используешь индекс по продукту идет по всей таблице от сотворения, а потом отбирает по дате, если используешь индекс по дате даже за 2 дня отбирает офигенный объем данных, а потом из них отбирает нужный продукт, период месяц-полгода по дате дают просто нереальные объемы.

...

Рейтинг:

0 / 0

25.08.2016, 12:53

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

Как оптимизировать запрос?

#39297833

Taciturn12

Гость

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

...

Рейтинг:

0 / 0

25.08.2016, 13:43

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

Как оптимизировать запрос?

#39297838

Taciturn12

Гость

nata44845Напрашивается индекс продукт-дата, делала причем даже делала DESC по дате, индекс получился какой-то очень громоздкий, 16 гб и постоянно висел в памяти почти весь, и был по объему даже больше, чем хранимая в памяти часть таблицы.
Не то.

И что же в написанном не устраивает? если в таблице 3 поля и индекс создается по 2 из ним, почему он должен быть маленьким? он должен быть сравним с размером таблицы.
То что объем индекса в кэше больше таблицы, так чего вы хотите, если почти все данные хранятся в индексе? база обращается к таблице только когда требуется id шапки документа, в других случаях нужная информация уже получена из индекса.

...

Рейтинг:

0 / 0

25.08.2016, 13:49

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

Как оптимизировать запрос?

#39297845

mefman

Участник

Сообщения: 1 562

Рейтинг: 0 / 0

nata44845Давайте кратко
Если кратко - нужен autotrace, или скрин sql-monitora.
Ну и сам запрос конечно.

...

Рейтинг:

0 / 0

25.08.2016, 13:56

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

Как оптимизировать запрос?

#39297849

partition by range

Гость

nata44845,

Пробовали таблицу партиционировать (напр. по месяцам) по дате + локальный индекс по продукту?

...

Рейтинг:

0 / 0

25.08.2016, 13:58

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

Как оптимизировать запрос?

#39297864

Taciturn12

Гость

partition by range,

В принципе вариант тоже может помочь, другое дело что согласятся ли переводить таблицу в партицированную, все-таки составной индекс более универсальный вариант. Но если партицирование не проблема то попробуйте оба варианта и выберете в итоге.

...

Рейтинг:

0 / 0

25.08.2016, 14:05

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

Как оптимизировать запрос?

#39297867

Taciturn12

Гость

Ой, вместо цитирования ответить нажал )

...

Рейтинг:

0 / 0

25.08.2016, 14:07

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

Как оптимизировать запрос?

#39297908

опс...

Гость

nata44845, советую хотя бы схематично табличку с полями, плана запроса сюда накидать.
иначе 80% заглянувших в топик даже читать не станут ваше литературное описалово проблемы.

...

Рейтинг:

0 / 0

25.08.2016, 14:35

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

Как оптимизировать запрос?

#39297914

heroin2

Гость

если сделать индекс по функции trunc(), он будет поменьше таблицы и скорее всего будет лучше восприниматься оптимизатором, чем индекс по дате.

...

Рейтинг:

0 / 0

25.08.2016, 14:41

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

Как оптимизировать запрос?

#39297933

-2-

Участник

Сообщения: 15 349

Рейтинг: 0 / 0

heroin2если сделать индекс по функции trunc(), он будет поменьше таблицы и скорее всего будет лучше восприниматься оптимизатором, чем индекс по дате.советую прочитать раздел про типы данных и автора темы.

...

Рейтинг:

0 / 0

25.08.2016, 14:52

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

Как оптимизировать запрос?

#39297972

heroin2

Гость

-2-, спасибо!
автор вроде бы не пишет что ищет тоже со временем, если ты об этом

...

Рейтинг:

0 / 0

25.08.2016, 15:15

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

Как оптимизировать запрос?

#39298019

-2-

Участник

Сообщения: 15 349

Рейтинг: 0 / 0

heroin2если ты об этомпроблем задействовать индекс нет:nata44845Ставила в подсказки индекс и по дате тоже (оба), но тогда и за пол года начинает отбирать по дате, и все тушите свет.и с другой стороны. В чем польза от trunc? Продемонстрируй конкретными числами.

...

Рейтинг:

0 / 0

25.08.2016, 15:49

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

Как оптимизировать запрос?

#39298108

nata44845

Участник

Откуда: Красноярск

Сообщения: 356

Рейтинг: 0 / 0

heroin2,

Во-во, такая мысль меня тоже посещает. Индекс по продукту - дате со временем получается слишком ветвящимся из-за того что по одному продукту может идти 10-50 продаж за одну дату, и в датах на каждом уровне по 1 листу.

Индекс только по дате будет иметь больше листьев на уровне, но опять же будет ли он использоваться если запрос вида
ДАТА>='01.01.2016 00:00:00' и ДАТА<='01.02.2012 23:59:59'

Ведь в индексе дата обрезана, получается для 01.02.2012 придется все равно лезть в таблицу уточнять.
Полезет?

...

Рейтинг:

0 / 0

25.08.2016, 17:03

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

Как оптимизировать запрос?

#39298112

nata44845

Участник

Откуда: Красноярск

Сообщения: 356

Рейтинг: 0 / 0

ПС. В таблице не 3 столбца, 3 столбца это рабочие, по которым данные отбираются, а их там еще штук 50, строки длинные...

...

Рейтинг:

0 / 0

25.08.2016, 17:06

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

Как оптимизировать запрос?

#39298125

nata44845

Участник

Откуда: Красноярск

Сообщения: 356

Рейтинг: 0 / 0

Запрос приблизительный

SELECT /*+USE_CONCAT ORDERED INDEX(X X_BY_PRODUCT) INDEX(Y Y_PK)*/
X.ID as P54526635,X.DOCUMENT as P54526637
FROM X,Y
WHERE ((X.CLASS IN(14286850,14286851)) AND (X.DATE>=TO_DATE('23.08.2016','DD.MM.YYYY'))
AND (X.DATE<=TO_DATE('26.08.2016','DD.MM.YYYY')) AND (X.PRODUCT='8C000000000165F8')
AND (X.DOCUMENT=Y.ID) AND (X.STATE>0) AND (X.TYPE<>42401878))

...

Рейтинг:

0 / 0

25.08.2016, 17:17

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

Как оптимизировать запрос?

#39298128

компресс

Гость

на индекс положить, если места не хватат

...

Рейтинг:

0 / 0

25.08.2016, 17:21

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

Как оптимизировать запрос?

#39298278

ora601

Участник

Сообщения: 785

Рейтинг: 0 / 0

Taciturn12partition by range,

другое дело что согласятся ли переводить таблицу в партицированную

Ну да нужно подождать пока там будет 1 млрд записей, потом можно уже)

Битмап партиционированные индексы хороший вариант тут.

...

Рейтинг:

0 / 0

26.08.2016, 00:00

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

Как оптимизировать запрос?

#39298460

дружбан

Гость

партицирование по дате упростит таблицу для исходной выборки, потом строить индексы

...

Рейтинг:

0 / 0

26.08.2016, 11:14

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

Как оптимизировать запрос?

#39298477

mefman

Участник

Сообщения: 1 562

Рейтинг: 0 / 0

Во-первых - форматирование:

Код: sql

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

SELECT /*+USE_CONCAT ORDERED INDEX(X X_BY_PRODUCT) INDEX(Y Y_PK)*/ 
X.ID as P54526635,X.DOCUMENT as P54526637
FROM X,Y
WHERE ((X.CLASS IN(14286850,14286851)) AND (X.DATE>=TO_DATE('23.08.2016','DD.MM.YYYY')) 
AND (X.DATE<=TO_DATE('26.08.2016','DD.MM.YYYY')) AND (X.PRODUCT='8C000000000165F8') 
AND (X.DOCUMENT=Y.ID) AND (X.STATE>0) AND (X.TYPE<>42401878))

Во-вторых:

Код: sql

/*+USE_CONCAT ORDERED INDEX(X X_BY_PRODUCT) INDEX(Y Y_PK)*/

крЫсотища...
без хинтов пробовали?
ORDERED - #аццскийсотона, IMHO.

...

Рейтинг:

0 / 0

26.08.2016, 11:32

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

Как оптимизировать запрос?

#39298480

pihel

Гость

nata44845,

зачем таблица Y , если она не используется в select или where?
В индексе есть столбец "DATE" до других range столбцов в фильтре?
При наличии индекса стоит сравнить IN LIST ITERATOR замен планса с USE_CONCAT

...

Рейтинг:

0 / 0

26.08.2016, 11:44

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

Как оптимизировать запрос?

#39298498

arlx

Гость

nata44845,

домино?

...

Рейтинг:

0 / 0

26.08.2016, 12:02

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

Как оптимизировать запрос?

#39298560

nata44845

Участник

Откуда: Красноярск

Сообщения: 356

Рейтинг: 0 / 0

Код: sql

1.
2.
3.
4.
5.

SELECT /*+USE_CONCAT ORDERED INDEX(X X_BY_PRODUCT) INDEX(Y Y_PK)*/ 
CASE THEN Y...... THEN X.PRICE ELSE -X.PRICE END P,X.ID,X.DOCUMENT
FROM X,Y
WHERE ((Y.CLASS IN(:P9,:P10)) AND (X.DATE>=:P11) AND (X.DATE<=:P12) AND (X.PRODUCT=:P13) 
AND (Y.DEPARTMENT IN(:P14,:P15,:P16,:P17,:P18,:P19,:P20,:P21,:P22,:P23,:P24,:P25,:P26,:P27,:P28,:P29,:P30,:P31,:P32,:P33,:P34,:P35,:P36,:P37,:P38,:P39,:P40,:P41,:P42,:P43,:P44,:P45,:P46,:P47,:P48,:P49,:P50,:P51,:P52,:P53,:P54,:P55,:P56,:P57,:P58,:P59,:P60,:P61,:P62,:P63,:P64,:P65,:P66,:P67,:P68,:P69,:P70,:P71,:P72,:P73,:P74,:P75,:P76,:P77,:P78,:P79,:P80,:P81,:P82,:P83,:P84,:P85,:P86,:P87,:P88,:P89,:P90,:P91,:P92,:P93,:P94,:P95,:P96,:P97,:P98,:P99,:P100,:P101,:P102,:P103,:P104,:P105,:P106,:P107,:P108,:P109,:P110,:P111,:P112,:P113,:P114,:P115,:P116,:P117,:P118,:P119,:P120,:P121,:P122,:P123,:P124,:P125,:P126,:P127,:P128,:P129,:P130,:P131,:P132,:P133,:P134,:P135,:P136,:P137,:P138,:P139,:P140,:P141,:P142,:P143,:P144,:P145,:P146,:P147,:P148,:P149,:P150,:P151,:P152,:P153,:P154,:P155,:P156,:P157,:P158,:P159,:P160,:P161,:P162,:P163,:P164,:P165,:P166)) AND (X.DOCUMENT=Y.ID) AND (X.STATE>0) AND (X.TYPE<>:P167))

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

...

Рейтинг:

0 / 0

26.08.2016, 13:06

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

Как оптимизировать запрос?

#39298572

mefman

Участник

Сообщения: 1 562

Рейтинг: 0 / 0

pihelnata44845,

зачем таблица Y , если она не используется в select или where?
В индексе есть столбец "DATE" до других range столбцов в фильтре?
При наличии индекса стоит сравнить IN LIST ITERATOR замен планса с USE_CONCAT

Код: sql

AND (X.DOCUMENT=Y.ID) AND (X.STATE>0) AND (X.TYPE<>42401878))

таки используется

...

Рейтинг:

0 / 0

26.08.2016, 13:23

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

Как оптимизировать запрос?

#39298665

pihel

Гость

nata44845,

1. покажите план запроса с предикатами. Используется ли в access доступе для индекса дата DATE?
Интересно посмотреть во что развернет +USE_CONCAT такое количество IN, должен быть адский план.

2. покажите статистику на столбцах фильтрации: сколько уникальных значений на каждом. На самых селективных создан ли индекс? Есть ли перекос в данных в этих столбцах, если есть то созданы ли гистограммы (надеюсь bind pickup не отключен)

...

Рейтинг:

0 / 0

26.08.2016, 15:05

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

Как оптимизировать запрос?

#39298703

nata44845

Участник

Откуда: Красноярск

Сообщения: 356

Рейтинг: 0 / 0

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

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

Сегодня наблюдала как тетка делает отчет по бананам за 3 дня, так как задействован индекс продукт, она полтора часа сидела ждала, по индексу продукт выбралось 450 тысяч записей по всей базе, потом ей надоело.
А есть еще кофе 3в1 почти 2 миллиона записей за весь период.

...

Рейтинг:

0 / 0

26.08.2016, 16:05

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

Как оптимизировать запрос?

#39298709

pihel

Гость

nata44845,

нужно чтобы фильтр по дате был до других сканирований по диапазону (STATE, TYPE), т.е. чтобы по продукту и дате был access (не filter ! )

nata44845Есть некоторые продукты по которым продано порядка 2000 товаров, там вжик и все, очень удобно, а есть просто гиганты.

Т.е. написанные вами хинты в этом случае только мешают. Если нужно отобрать большой кусок, то тут делать hash join + parallel + хорошо бы побить фактовую таблицу на секции, чтобы было меньше сканировать, т.к. не будет индекса и будет full table scan. Желательно всего этого добиться без хинтов, чтобы оракл сам понимал когда нужен NL по 2000 товаров, а когда hash join по 450т записей.

...

Рейтинг:

0 / 0

26.08.2016, 16:12

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

Как оптимизировать запрос?

#39298717

nata44845

Участник

Откуда: Красноярск

Сообщения: 356

Рейтинг: 0 / 0

pihel,

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

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

Без подсказки запрос скатывается в индекс по дате, что тоже не есть хорошо.

...

Рейтинг:

0 / 0

26.08.2016, 16:27

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

Как оптимизировать запрос?

#39298726

pihel

Гость

nata44845,

INDEX RANGE SCAN - 458К (access по индексу)
TABLE ACCESS BY INDEX ROWID - 998 строк (дофильтрация индекса filter).
Сделайте индекс чтобы после access было минимум строк. Такого рода запрос будет нормально работать с NL.

По статистике не видно какие предикаты были у запроса, странно что нет вкладки "plan". Именно там можно увидеть какие столбцы были acces по индексу, а какие filter

...

Рейтинг:

0 / 0

26.08.2016, 16:37

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

Как оптимизировать запрос?

#39298729

nata44845

Участник

Откуда: Красноярск

Сообщения: 356

Рейтинг: 0 / 0

pihel,

Про то что нужен индекс и по дате и по индексу я и так поняла, но тот который я делала по продукту и дате получился тяжелым и ветвистым.
Сегодня на маленькой тестовой делала индекс по product, trunc(date) desc
А потом еще делала по ASC

Ставила его в рекомендованные в тестовом запросе, индекс уходил в FULL SCAN.
В понедельник еще базу с партиционированной таблицей там подниму.

А вообще всем приятных выходных, до понедельника.

...

Рейтинг:

0 / 0

26.08.2016, 16:39

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

Как оптимизировать запрос?

#39298732

nata44845

Участник

Откуда: Красноярск

Сообщения: 356

Рейтинг: 0 / 0

тьфу блин, по продукту, заговорилась

...

Рейтинг:

0 / 0

26.08.2016, 16:41

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

Как оптимизировать запрос?

#39299346

опс...

Гость

nata44845Сегодня на маленькой тестовой делала индекс по product, trunc(date) desc
А потом еще делала по ASC
Ставила его в рекомендованные в тестовом запросе, индекс уходил в FULL SCAN.

вот скажите, у вас в запросе фигурирует просто дата в диапазоне, так на уя в индексе trunc лепить, кто вам эту ерунду насоветовал ?

...

Рейтинг:

0 / 0

29.08.2016, 09:30

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

Как оптимизировать запрос?

#39299353

heroin2

Гость

опс... , это был вредный совет

...

Рейтинг:

0 / 0

29.08.2016, 09:38

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

Как оптимизировать запрос?

#39299498

Avotge

Гость

Код: plsql

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

SELECT CASE THEN Y...... THEN X.PRICE ELSE -X.PRICE END P,
       X.ID, X.DOCUMENT
  FROM X, Y
 WHERE Y.CLASS IN (:P9, :P10)
   AND X.DATE BETWEEN :P11 AND :P12
   AND X.PRODUCT = :P13
   AND Y.DEPARTMENT IN(:P14,:P15,:P16,:P17,:P18,:P19,:P20,:P21,:P22,:P23,:P24,:P25,:P26,:P27,:P28,:P29,
                       :P30,:P31,:P32,:P33,:P34,:P35,:P36,:P37,:P38,:P39,:P40,:P41,:P42,:P43,:P44,:P45,
                       :P46,:P47,:P48,:P49,:P50,:P51,:P52,:P53,:P54,:P55,:P56,:P57,:P58,:P59,:P60,:P61,
                       :P62,:P63,:P64,:P65,:P66,:P67,:P68,:P69,:P70,:P71,:P72,:P73,:P74,:P75,:P76,:P77,
                       :P78,:P79,:P80,:P81,:P82,:P83,:P84,:P85,:P86,:P87,:P88,:P89,:P90,:P91,:P92,:P93,
                       :P94,:P95,:P96,:P97,:P98,:P99,:P100,:P101,:P102,:P103,:P104,:P105,:P106,:P107,
                       :P108,:P109,:P110,:P111,:P112,:P113,:P114,:P115,:P116,:P117,:P118,:P119,:P120,
                       :P121,:P122,:P123,:P124,:P125,:P126,:P127,:P128,:P129,:P130,:P131,:P132,:P133,
                       :P134,:P135,:P136,:P137,:P138,:P139,:P140,:P141,:P142,:P143,:P144,:P145,:P146,
                       :P147,:P148,:P149,:P150,:P151,:P152,:P153,:P154,:P155,:P156,:P157,:P158,:P159,
                       :P160,:P161,:P162,:P163,:P164,:P165,:P166) 
   AND X.DOCUMENT = Y.ID
   AND X.STATE > 0 
   AND X.TYPE <> :P167

1. денормализация с добавлением class и department в x
2. create index index_name on x (product, date, class, department, state, type, price .... (ну и все поля, участвующие в селекте :))
3. пишем запрос только к x:

Код: plsql

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

SELECT CASE THEN ...... THEN PRICE ELSE -PRICE END P,
       ID, DOCUMENT
  FROM X
 WHERE CLASS IN (:P9, :P10)
   AND DATE BETWEEN :P11 AND :P12
   AND PRODUCT = :P13
   AND DEPARTMENT IN(:P14,:P15,:P16,:P17,:P18,:P19,:P20,:P21,:P22,:P23,:P24,:P25,:P26,:P27,:P28,:P29,
                       :P30,:P31,:P32,:P33,:P34,:P35,:P36,:P37,:P38,:P39,:P40,:P41,:P42,:P43,:P44,:P45,
                       :P46,:P47,:P48,:P49,:P50,:P51,:P52,:P53,:P54,:P55,:P56,:P57,:P58,:P59,:P60,:P61,
                       :P62,:P63,:P64,:P65,:P66,:P67,:P68,:P69,:P70,:P71,:P72,:P73,:P74,:P75,:P76,:P77,
                       :P78,:P79,:P80,:P81,:P82,:P83,:P84,:P85,:P86,:P87,:P88,:P89,:P90,:P91,:P92,:P93,
                       :P94,:P95,:P96,:P97,:P98,:P99,:P100,:P101,:P102,:P103,:P104,:P105,:P106,:P107,
                       :P108,:P109,:P110,:P111,:P112,:P113,:P114,:P115,:P116,:P117,:P118,:P119,:P120,
                       :P121,:P122,:P123,:P124,:P125,:P126,:P127,:P128,:P129,:P130,:P131,:P132,:P133,
                       :P134,:P135,:P136,:P137,:P138,:P139,:P140,:P141,:P142,:P143,:P144,:P145,:P146,
                       :P147,:P148,:P149,:P150,:P151,:P152,:P153,:P154,:P155,:P156,:P157,:P158,:P159,
                       :P160,:P161,:P162,:P163,:P164,:P165,:P166) 
   AND STATE > 0 
   AND TYPE <> :P167

4. по возможности избавиться от DEPARTMENT IN (например, добавлением отдельного признака/справочника типа группы отделов)
5. ну и с патишенами было бы удобнее наверно ж

...

Рейтинг:

0 / 0

29.08.2016, 12:51

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

Как оптимизировать запрос?

#39299599

heroin2

Гость

Avotge, что толку от денормализации? а индекс по всем полям это вообще жесть, нужно будет попробовать.

у автора проблема в том что запрос при разных условиях должен идти по разным планам(не важно 1 там таблица или 10) при том что все выполняется по связываемым переменным и план в общем-то один. его можно тюнить подсказками, но все равно в другом случае опять будет неоптимально. кстати есть cursor_sharing_exact, но это тоже изврат.

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

...

Рейтинг:

0 / 0

29.08.2016, 14:23

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

Как оптимизировать запрос?

#39299647

Avotge

Гость

heroin2что толку от денормализации? а индекс по всем полям это вообще жесть, нужно будет попробовать.

у автора проблема в том что запрос при разных условиях должен идти по разным планам(не важно 1 там таблица или 10) при том что все выполняется по связываемым переменным и план в общем-то один. его можно тюнить подсказками, но все равно в другом случае опять будет неоптимально. кстати есть cursor_sharing_exact, но это тоже изврат.

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

2. По индексу по всем полям.
Как бы очевидно, что запрос будет работать быстрее, если все поля запроса уже будут в индексе, особенно если идет большой разброс самих данных по диску. При этом речь не идет о том, чтобы засунуть все поля таблицы, а именно те, которые используются в запросе. Такая практика вроде тоже имеет место быть )

3. По проблеме автора.
В проблему не вникал :) Просто мысли по представленному запросу ) Насчет того, что запрос должен идти по разным планам в зависимости от параметров, наск. понимаю, зависит от статистики, но имхо, если выполнить п.1 и п.2 с большой вероятностью итак будет нормуль ) (в крайнем построить еще необх. индекс(ы), а там пусть выбирает ))

4. По патиционированию. Не факт, что поможет в плане скорости:
- смотря как патиционировать
- основное время тратиться ж на чтения с диска, при этом:
- сначала читается индекс одной таблицы + данные
- плюс читается индекс другой таблицы + данные
Если же выполнить п.1. и п.2 будет читаться один индекс и все.

...

Рейтинг:

0 / 0

29.08.2016, 15:19

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

Как оптимизировать запрос?

#39300141

Berd.

Участник

Сообщения: 292

Рейтинг: 0 / 0

nata44845Есть таблица записей, условно говоря это строки документов, содержат товар (ID), дату (со временем), ну и ссылку на шапку документа.

На таблицу уже есть индексы по дате, продукту, документу.

Есть запрос по движению товара, рекомендован индекс по продукту, то есть он через индекс продуктов собирает данные по продукту по всей таблице (500 млн строк), а это может быть и 1000 и 150 тысяч, а потом отбирает их по дате.
Причем даже если делаешь отбор за 2 дня все равно выбирает все продукты к примеру 115 тысяч строк, то есть длится запрос может пол часа, а вот если за те же 2 дня отбираешь по дате, то он отбирает 800 тысяч и по ним отбирает уже продукт, но получается это все равно за 4 минуты, видимо потому что эти данные все лежат в одном месте, а не размазаны по нескольким файлам.

Ставила в подсказки индекс и по дате тоже (оба), но тогда и за пол года начинает отбирать по дате, и все тушите свет.

Напрашивается индекс продукт-дата, делала причем даже делала DESC по дате, индекс получился какой-то очень громоздкий, 16 гб и постоянно висел в памяти почти весь, и был по объему даже больше, чем хранимая в памяти часть таблицы.
Не то.

Может какие-то хитрости есть? может партиционирование поможет, хотя не могу понять как.
Тут задача и админам, и программистам.

500 млн строк - на таком объёме уже давно пора использовать партиционирование.
Партиционирование прежде всего по месяцу, вторые партиции - по конкретной дате.
Можно вообще разбить на помесячные таблицы и сделать в них партиционирование подневно. А для общих запросов использовать вьюху с таким же названием, как сейчас у этой таблицы. Только надо будет обновлять вьюху каждый месяц и каждый месяц создавать новую месячуню таблицу с подневным партиционированием.
Это всё можно сделать при помощи Job-ов.
Так точно будет работать очень быстро.

Ещё вот это прочитай - для новичков хорошо написано
Также, если позволяет железо - можно запускать один запрос в несколько потоков.

...

Рейтинг:

0 / 0

30.08.2016, 11:53

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

Как оптимизировать запрос?

#39307852

nata44845

Участник

Откуда: Красноярск

Сообщения: 356

Рейтинг: 0 / 0

Тестировала запрос на партиционированной и не партиционированной небольших базах, подняла две одинаковых схемы, в одной таблицы партиционировала, и опять подняла.
При индексе только по продукту количество чтений с диска 5000 партиционированная против 25000 не партиционированная.
При индексе по продукту и дате стоимость практически не отличается во всех видах индексов (product+date,date desc,trunc(date),trunc(date) desc), 3300-3800.
Потом решила вынести эти таблицы в отдельные табличные пространства, а в партиционированной каждую партицию.

Пока это делала залезла в табличное пространство в главной базе, а там... 20 файлов, таблицы, индексы навалены, копии базы поднимали 2 раза тестово и тоже в это табличное пространство. Пол субботы распихивала это по отдельным табличным пространствам.
В табличном пространстве остались небольшие таблицы и самая большая таблица, занимает процентов 70 табличного пространства, 300 гиг. И все это размазано по файлам ровным слоем.

Вопрос что с этим делать, можно вытащить мелкие таблицы, а эту оставить одну в табличном пространстве. Можно как-нибудь перетащить записи в первые n файлов, а прочие удалить? move на нее займет часов 15 в лучшем случае.
Или просто shrink сделать?

...

Рейтинг:

0 / 0

12.09.2016, 17:26

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

Как оптимизировать запрос?

#39308125

опс...

Гость

nata44845, не майтесь дурью с табличными пространствами, используйте более рационально вашу неуемную энергию.
у вас честно, какая-то каша. У вас есть четкая цель - что вы хотите получить ?
что у вас там за железо ? что такое 3300 чтений - и чем оно вас так пугает? такое количество должно выполняться за доли сек. Почитайте какие виды join оптимизатор оракла может использовать, что больше подходит под вашу задачу. Сколько строк должен вернуть запрос.
А обсуждение здесь пока очень похоже на обсуждение того самого теоретического коня округлой формы, насоветуют вам на полгода исследований.

...

Рейтинг:

0 / 0

13.09.2016, 08:26

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

Как оптимизировать запрос?

#39308237

nata44845

Участник

Откуда: Красноярск

Сообщения: 356

Рейтинг: 0 / 0

опс...,

Результат беру из этого запроса

Код: sql

1.
2.
3.

SELECT sqL_text, executions, buffer_gets, disk_reads, rows_processed 
from v$sql
order by disk_reads desc;

сразу после перезапуска тестовой базы и запуска первого запроса, то есть запрос каждый раз выполняю один раз, чтобы не брал данные из кэша. buffer_gets кстати тоже различаются в разы.

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

А вот пожать данные мне ничего не мешает, да и индекс создать тоже, но как я поняла индексы что про trunc, что desc тут бессмысленны, по desc вообще странный план получается, поэтому проще сделать индекс asc по двум столбцам, и посмотреть что будет.
А вот как бы главное табличное пространство с этой таблицей подрезать еще... Там без этой таблицы вся база гиг 50, а с нею 500.

...

Рейтинг:

0 / 0

13.09.2016, 11:22

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

Как оптимизировать запрос?

#39308244

nata44845

Участник

Откуда: Красноярск

Сообщения: 356

Рейтинг: 0 / 0

ПС. У меня есть главный сервер, где железо я считаю очень неплохое, вчера утащила в отдельное табличное пространство продукты, 7 миллионов, и перестраивала индексы, на каждый индекс фулл скан 2 минуты.

А есть сервер тестовый виртуальный, со всеми вытекающими, но на нем можно сравнить разные варианты.

...

Рейтинг:

0 / 0

13.09.2016, 11:28

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

Как оптимизировать запрос?

#39308262

опс...

Гость

nata44845сразу после перезапуска тестовой базы и запуска первого запроса, то есть запрос каждый раз выполняю один раз, чтобы не брал данные из кэша.
охох )
Вы теоретическое исследование делаете или реальной базой занимаетесь ? )
1. Сколько строк возвращает запрос.
2. Сколько фактически строк в каждой по-отдельности таблице строк, удовлетворяющих условиям запроса(без join).
3. План запроса, время выполнения разных планов.
4. ...

еще раз - ну бросьте вы табличные пространства мучить, эффективность этого к затраченному времени - если не отрицательная, то практически абсолютный ноль )

...

Рейтинг:

0 / 0

13.09.2016, 11:38

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

Как оптимизировать запрос?

#39308263

nata44845

Участник

Откуда: Красноярск

Сообщения: 356

Рейтинг: 0 / 0

План для desc

В плане для Asc дата уходит в INDEX RANGE SCAN - Predicate - по результатам самый оптимальный

...

Рейтинг:

0 / 0

13.09.2016, 11:39

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

Как оптимизировать запрос?

#39315992

nata44845

Участник

Откуда: Красноярск

Сообщения: 356

Рейтинг: 0 / 0

опс...,

Не знаю почему вы так считаете, я всего лишь делаю по рекомендациям на 111-112 странице Oracle 10G Луни-Брила.
Вот что пока получилось.

Утащила временные табличные пространства каждой схемы на винчестер с бэкапами (это я давно сделала).
Растащила данные левых пользователей по их табличным пространствам, все равно эти схемы тестовые и с ними не работают.
Разнесла крупные таблицы в отдельные табличные пространства и индексы тоже.
Мелкие таблицы пыталась поделить на группы по интенсивности доступа.

Код: sql

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

SELECT OBJECT_NAME,COUNT(*)
        FROM v$sql_plan v
WHERE OBJECT_OWNER='X'
  AND OBJECT_TYPE='TABLE'
  AND OBJECT_ALIAS NOT LIKE OBJECT_NAME||'%'
  group BY OBJECT_NAME
  ORDER BY 2 desc

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

В главном табличном пространстве осталась самая большая таблица и индексная таблица, которая пересоздается ночью (это не моя задумка), ее тоже утащила к мелким таблицам, табличное пространство по умолчанию поменяла, корзину отключила.
Индексная таблица там конечно тоже не очень хорошее решение.

Главную таблицу пытаюсь сжать хотя бы COMPACT, очень грузит систему, и не могу понять при прерывании SHRINK возвращается все на свои места или нет. MOVE для данной таблицы сделать быстро не реально, она процентов 90 всей базы.

И еще проблема из запроса

Код: sql

1.
2.
3.
4.
5.

select a.owner,a.object_type,a.object_name,count(*) buffers,count(*)*8192/1024/1024 bsize,((count(*)/(select count(*) from v$bh))*100) buffer_percent
from dba_objects a,v$bh b
where a.object_id=b.objd and a.OWNER not in ('SYS','SYSTEM')
group by a.owner,a.object_type,a.object_name
order by 6 DESC;

пропали все остальные таблицы и индексы корме главной таблицы L и индексной, не могу понять обновляется вьюшка v$bh или нет.
То есть таблицы явно в памяти, не может он их читать каждый раз, но тут их не видно, загадка.

...

Рейтинг:

0 / 0

27.09.2016, 06:30

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

Как оптимизировать запрос?

#39316004

Добрый Э - Эх

Гость

nata44845,

эх, твою бы энергию, да в нужное русло...

...

Рейтинг:

0 / 0

27.09.2016, 08:43

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

Как оптимизировать запрос?

#39339532

nata44845

Участник

Откуда: Красноярск

Сообщения: 356

Рейтинг: 0 / 0

Дело кончилось тем, что я создала новый индекс на 2 столбца продукт и дата, помучилась с привязкой к нему планов, посмотрела, как медленно стали удаляться данные и за доп. индекса, посмотрела на соседнюю таблицу (а там в индекс по продукту добавлена дата, видимо разработчиком), грохнула свой индекс и добавила дату в продуктовый индекс (тем более, что тот индекс прописан в подсказках во всех запросах), привязки планов соответственно почистила.

...

Рейтинг:

0 / 0

02.11.2016, 08:04

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

49 сообщений из 49, показаны все 2 страниц

все

Форумы / Oracle [игнор отключен] [закрыт для гостей] / Как оптимизировать запрос?

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

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

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

start [/forum/topic.php?all=1&fid=52&tid=1887102]:	0ms
get settings:	9ms
get forum list:	16ms
check forum access:	4ms
check topic access:	4ms
track hit:	168ms
get topic data:	8ms
get forum data:	2ms
get page messages:	85ms
get tp. blocked users:	1ms
others:	213ms

total:	510ms

	Необходимые cookie
	Cookie для сбора статистики
	Cookie для маркетинга и рекламы