Оптимизация запроса

← →
McSimm2 (2003-07-15 08:46) [0]

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

SELECT s.*, kv.* FROM sobes_analiz s, (SELECT k.rajon, k.jeu, k.n_lc, k.ulica, k.dom, k.kvart, k.p_kom, k.famil, k.kkom, v.famil AS vfamil, v.imj AS vimj, v.otch AS votch FROM (SELECT * FROM ksm k2 WHERE ( (k2.kkom <> "5") AND (k2.kkom <> "7") AND (k2.kkom <> "8") AND (k2.kkom <> "9") ) OR k2.kkom IS NULL) k, vet v WHERE ((k.jeu = v.jeu(+)) AND (k.rajon = v.rajon(+)) AND (k.n_lc = v.n_lc(+)))) kv WHERE (( s.ksmstreet = kv.ulica(+) AND s.ksmhouse = kv.dom(+) AND NVL (s.ksmflat, -1) = NVL (kv.kvart(+), -1) ) ) UNION SELECT s.*, kv.* FROM sobes_analiz s, (SELECT k.rajon, k.jeu, k.n_lc, k.ulica, k.dom, k.kvart, k.p_kom, k.famil, k.kkom, f.famil AS vfamil, "" AS vimj, "" AS votch FROM (SELECT * FROM ksm_ved k2 WHERE ( (k2.kkom <> "5") AND (k2.kkom <> "7") AND (k2.kkom <> "8") AND (k2.kkom <> "9") ) OR k2.kkom IS NULL) k, vet_ved f WHERE ((k.jeu = f.jeu(+)) AND (k.rajon = f.rajon(+)) AND (k.n_lc = f.n_lc(+)))) kv WHERE (( s.ksmstreet = kv.ulica(+) AND s.ksmhouse = kv.dom(+) AND NVL (s.ksmflat, -1) = NVL (kv.kvart(+), -1) ) ) UNION SELECT s.*, kv.* FROM sobes_analiz s, (SELECT k.rajon, k.jeu, k.n_lc, k.ulica, k.dom, k.kvart, 0, k.famil, "", f.famil AS vfamil, "" AS vimj, "" AS votch FROM ksm_chs k, vet_chs f WHERE ((k.jeu = f.jeu(+)) AND (k.rajon = f.rajon(+)) AND (k.n_lc = f.n_lc(+)))) kv WHERE (( s.ksmstreet = kv.ulica(+) AND s.ksmhouse = kv.dom(+) AND NVL (s.ksmflat, -1) = NVL (kv.kvart(+), -1) ) )

Причём кусок запроса

SELECT k.rajon, k.jeu, k.n_lc, k.ulica, k.dom, k.kvart, k.p_kom, k.famil, k.kkom, v.famil AS vfamil, v.imj AS vimj, v.otch AS votch FROM (SELECT * FROM ksm k2 WHERE ( (k2.kkom <> "5") AND (k2.kkom <> "7") AND (k2.kkom <> "8") AND (k2.kkom <> "9") ) OR k2.kkom IS NULL) k, vet v WHERE ((k.jeu = v.jeu(+)) AND (k.rajon = v.rajon(+)) AND (k.n_lc = v.n_lc(+)))
Этот кусок отрабатывается меньше секунды, а уже если его использовать в общем запросе, то 3 минуты. Весь запрос работает минут 7.

База Ksm - 180.000 записей, Vet - 450.000. Все остальные - мелкие - порядка 10.000

Мастера, как запрос соптимизировать можно ?

← →
Sergey13 © (2003-07-15 09:09) [1]

2McSimm2 (15.07.03 08:46)
1.Попробуй избавиться от конструкций типа
NVL (s.ksmflat, -1) = NVL (kv.kvart(+), -1)
Если тебе надо по умолчанию -1 то так и сделай.
2.Отрабатывай каждый "кусок" отдельно. Планчики смотри.
3.Попробуй хинтами пользоваться.
4.Попробуй сделать вьюху по твоему подзапросу.
5.Индексы
6.Статистику собираешь?
n-1. Вопросы у тебя, однако. Тут до хрена чего можно крутить. 8-)

← →
Dan (2003-07-15 09:17) [2]

если нет цели убрать повторяющиеся строки , может попробовать поставить UNION ALL.

← →
McSimm2 (2003-07-15 09:20) [3]

Индексы сделаны - по всем таблицам; Каждый кусок я отрабытаю - в вопросе я это и описал.
Как можно от NVL (s.ksmflat, -1) = NVL (kv.kvart(+), -1)избавиться? Для моей задачи именно NVL и нужен , что бы можно было коннектить по полям если значение null в них

← →
passm © (2003-07-15 09:23) [4]

McSimm2 (15.07.03 08:46)> СУБД?

← →
McSimm2 (2003-07-15 09:25) [5]

Dan (15.07.03 09:17)
Unioon all я ставил - скорость увеличивается, но не намного.
Конкретно по работе отдельных кусков, как предлогал
Sergey13 © (15.07.03 09:09)

получается, что
SELECT k.rajon, k.jeu, k.n_lc, k.ulica, k.dom, k.kvart, k.p_kom, k.famil, k.kkom, v.famil AS vfamil, v.imj AS vimj, v.otch AS votch FROM (SELECT * FROM ksm k2 WHERE ( (k2.kkom <> "5") AND (k2.kkom <> "7") AND (k2.kkom <> "8") AND (k2.kkom <> "9") ) OR k2.kkom IS NULL) k, vet v WHERE ((k.jeu = v.jeu(+)) AND (k.rajon = v.rajon(+)) AND (k.n_lc = v.n_lc(+)))

работает 16 милисекунд, а

sELECT s.*, kv.* FROM sobes_analiz s, (SELECT k.rajon, k.jeu, k.n_lc, k.ulica, k.dom, k.kvart, k.p_kom, k.famil, k.kkom, v.famil AS vfamil, v.imj AS vimj, v.otch AS votch FROM (SELECT * FROM ksm k2 WHERE ( (k2.kkom <> "5") AND (k2.kkom <> "7") AND (k2.kkom <> "8") AND (k2.kkom <> "9") ) OR k2.kkom IS NULL) k, vet v WHERE ((k.jeu = v.jeu(+)) AND (k.rajon = v.rajon(+)) AND (k.n_lc = v.n_lc(+)))) kv WHERE (( s.ksmstreet = kv.ulica(+) AND s.ksmhouse = kv.dom(+) AND NVL (s.ksmflat, -1) = NVL (kv.kvart(+), -1) ) )

отрабатывает хорошо за 3 минуты

← →
Dan (2003-07-15 09:25) [6]

а ты построй 2 индекса по выражениям NVL (s.ksmflat, -1) и NVL (kv.kvart, -1)

← →
McSimm2 (2003-07-15 09:25) [7]

passm © (15.07.03 09:23)

[Oracle]

← →
McSimm2 (2003-07-15 09:28) [8]

Dan (15.07.03 09:25)

Индексы стоят так:
по sobes_analiz стоит ksmstreet,ksmhouse,ksmflat
по vet,vetchs,vetved - jeu,rajon,n_lc
по ksm,ksmchs,ksmved - jeu,rajon,n_lc
7 индексов итого - вроде никакие не пересекаются

← →
Dan (2003-07-15 09:34) [9]

план выполнения запроса смотрел ??? чего он рисует ?

← →
Johnmen © (2003-07-15 09:34) [10]

1.Весь этот немыслимый запрос можно переписать так, чтобы не было "запрос из запроса", а лишь соединение таблиц, хоть и левое.
2.Серьезно и вдумчиво подойти к проектированию БД. Чтобы не было мучительно больно в ожидании выполнения многоэтажных запросов...:)

← →
McSimm2 (2003-07-15 09:36) [11]

Dan (15.07.03 09:34)
Как это можно осуществить и где ? Пользуюсь Toad`ом

← →
Dan (2003-07-15 09:41) [12]

там есть, но не помню где :-(( просто я пользуюсь SQL Navigator.
Должно быть что-то вроде Explain plan tool

← →
Sergey13 © (2003-07-15 09:43) [13]

2McSimm2 (15.07.03 09:25)
А планы то смотрел? Что они показывают?
>Индексы сделаны - по всем таблицам
С NVL они работать скорее всего не будут. Смотри планы выполнения. Хотя это видно и по скорости выполнения которые ты привел.

>Как можно от NVL (s.ksmflat, -1) = NVL (kv.kvart(+), -1)избавиться? Для моей задачи именно NVL и нужен , что бы можно было коннектить по полям если значение null в них

Я предлагал изменить структуру данных - если это возможно. Если в этих полях не нужны NULL, то и избавься от них раз и навсегда.

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

>WHERE ( (k2.kkom <> "5")
AND (k2.kkom <> "7")
AND (k2.kkom <> "8")
AND (k2.kkom <> "9")
)
Вот эту часть попробовать сделать через равенство а не через неравенство. Ускоряет иногда, за счет лучшего использования индекса. Или можно попробовать переделать на NOT IN (5,7,8,9).

Да дофига вариантов. Оптимизация - дело тонкое.

← →
Dan (2003-07-15 09:45) [14]

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

← →
McSimm2 (2003-07-15 09:51) [15]

Dan (15.07.03 09:45)
Нужно попробовать

← →
Dan (2003-07-15 10:05) [16]

До сих пор работает ? :-))

← →
McSimm2 (2003-07-15 10:29) [17]

ВЕСЬ запрос стал работать 3 минуты после того, как я его переделал.

Превратилось это вот во что:

SELECT s.*, kv_all.* FROM sobes_analiz s, (SELECT k.rajon, k.jeu, k.n_lc, k.ulica, k.dom, k.kvart, k.p_kom, k.famil, k.kkom, v.famil AS vfamil, v.imj AS vimj, v.otch AS votch FROM (SELECT * FROM ksm k2 WHERE (k2.kkom NOT IN ("5", "7", "8", "9")) OR k2.kkom IS NULL) k, vet v WHERE ( (k.jeu = v.jeu(+)) AND (k.rajon = v.rajon(+)) AND (k.n_lc = v.n_lc(+)) ) UNION ALL SELECT k.rajon, k.jeu, k.n_lc, k.ulica, k.dom, k.kvart, k.p_kom, k.famil, k.kkom, f.famil AS vfamil, "" AS vimj, "" AS votch FROM (SELECT * FROM ksm_ved k2 WHERE (k2.kkom NOT IN ("5", "7", "8", "9")) OR k2.kkom IS NULL) k, vet_ved f WHERE ( (k.jeu = f.jeu(+)) AND (k.rajon = f.rajon(+)) AND (k.n_lc = f.n_lc(+)) ) UNION ALL SELECT k.rajon, k.jeu, k.n_lc, k.ulica, k.dom, k.kvart, 0, k.famil, "", f.famil AS vfamil, "" AS vimj, "" AS votch FROM ksm_chs k, vet_chs f WHERE ( (k.jeu = f.jeu(+)) AND (k.rajon = f.rajon(+)) AND (k.n_lc = f.n_lc(+)) )) kv_all WHERE (( s.ksmstreet = kv_all.ulica(+) AND s.ksmhouse = kv_all.dom(+) AND NVL (s.ksmflat, -1) = NVL (kv_all.kvart(+), -1) ) )

Самое обидное, что все union работают ~20 милисекунд и возвращает ~500000 записей,а когда я сверху этот sobes_analiz приляпываю , вкотором 9000 записей, то запрос начинает работать 3 минуты (правда это уже не 7 как раньше)

Так что спасибо всем кто откликнулся и указал в каком направлении копать ....

← →
Desdechado © (2003-07-15 10:38) [18]

есть подозрение, что подзапрос отрабатывает для каждой записи главного запроса
сделай лучше ХП, в которой FOR SELECT из подзапроса, а внутри анализируй результат и возвращай только нужное

← →
DenK_vrtz © (2003-07-15 10:40) [19]

McSimm2 (15.07.03 10:29), согласен на все 100% с Johnmen © (15.07.03 09:34). Пересмотри структуру, поможет!
И еще. Я не вникал с сам запрос, но на вскидку, по собственному опыту, много индексов не есть хорошо. Лучше меньше, но лучше!

← →
McSimm2 (2003-07-15 10:44) [20]

DenK_vrtz © (15.07.03 10:40)
Индексы у таблицы только по тем полям - по которым идёт связывание таблиц.

← →
DenK_vrtz © (2003-07-15 10:48) [21]

McSimm2 (15.07.03 10:44), не всегда помогает. Эти поля хорошей селективностью должны обладать, по отдельности или в совокупности.

← →
McSimm2 (2003-07-15 10:49) [22]

Каждый индекс состоит из савокупности 3 полей, которые все вместе создают уникальность

← →
DenK_vrtz © (2003-07-15 10:54) [23]

Хорошей селективностю считается совокупность тех полей, которая составляет 3-5% всех данных. Вот и смотри.

← →
Sergey13 © (2003-07-15 11:03) [24]

Исчо раз повторю - избавься от NVL в конечном Where. Что за поля s.ksmflat и kv_all.kvart - каков их смысл. Почему нельзя сразу вставлять туда -1?
И еще - а у тебя все алиасы - таблицы, или и вьюхи есть? Проблемы могут быть и в этом.

Без приведения планов выполнения спор становится бессмысленным и беспредметным.
Какая версия Оракла? Какой оптимизатор включен по умолчанию? Если стоимостной то статистика когда последний раз собиралась?

← →
McSimm2 (2003-07-15 11:15) [25]

>>Исчо раз повторю - избавься от NVL в конечном Where.
>>Что за поля s.ksmflat и kv_all.kvart - каков их смысл.
>>Почему нельзя сразу вставлять туда -1?
Связь таблиц происходит по полям улица, дом, квартира.
Очень возможна ситуация, когда квартиры у дома может
не быть - тогда запись не присоединится, поэтому я
пользуюсь NVL.

>>И еще - а у тебя все алиасы - таблицы, или и вьюхи есть?
>>Проблемы могут быть и в этом.
что такое вьюха ? Область видимости. Если - "да", то
все таблицы у меня ссылаются на одну и ту же схему.

>>Какая версия Оракла?
8

>>Какой оптимизатор включен по умолчанию?
не знаю - где узнать ?
>>Если стоимостной то статистика когда последний раз собиралась?
не понял ...

>McSimm2 (15.07.03 10:29)
>Самое обидное, что все union работают ~20 милисекунд и
>возвращает ~500000 записей,а когда я сверху этот sobes_analiz
>приляпываю , вкотором 9000 записей, то запрос начинает работать
>3 минуты (правда это уже не 7 как раньше)

За ~20 мс выбираются первые несколько записей, поставьте alter session set optimizer_goal=all_rows, и время кардинально изменится. Т.к. подзапрос участвует в union, Oracle приходится выбирать все, да еще сортировать ( все же union all нужен )
Разобраться в запросе сходу тяжело, :))) могу дать общий совет - построить подзапросы так, чтобы они возвращали минимальное кол-во записей, а потом уже делать join и union, пусть даже количество "этажей" при этом вырастет.

McSimm2 (15.07.03 11:15), 8 - это еще не версия! :-)
Бывает 8.0.4, 8.0.5, 8.1.6, 8.1.7 ..., а 8.0 и 8i две вещи разные!

← →
McSimm2 (2003-07-15 11:39) [28]

2McSimm2 (15.07.03 11:15)
>Связь таблиц происходит по полям улица, дом, квартира.
Очень возможна ситуация, когда квартиры у дома может
не быть - тогда запись не присоединится, поэтому я
пользуюсь NVL.

А почему не принять что -1 это и есть отсутствие квартиры.

>что такое вьюха ? Область видимости. Если - "да", то
все таблицы у меня ссылаются на одну и ту же схему.
Вююха (view) - это представление, иначе сохраненный запрос.

>>Какой оптимизатор включен по умолчанию?
не знаю - где узнать ?
>>Если стоимостной то статистика когда последний раз собиралась?
не понял ...

А не почитать ли тебе книжек по Ораклу. Это все же не ИБ, тут просто так ничего не добьешься. Оракл - рулез, но им рулить надо!

20 милисекунд * 9000 записей = 180 сек = 3 мин
Арифметика-с. Твой запрос выполняется заново для каждой из 9000 записей. Добавь связь с sobes_analiz в каждый из подзапросов в Union - и то выиграешь неимоверно

> ЮЮ © (16.07.03 03:16)
> Твой запрос выполняется заново для каждой из 9000 записей
Ну это Вы перебрали, все ж в Oracle ( да и других компаниях ) не совсем дураки сидят, 9000 раз запрос выполнять при join

>9000 раз запрос выполнять при join

Тогда запрос надо писать так, чтобы он был похож на JOIN, а не прятать весь смысл в маленьком +
WHERE ((s.ksmstreet = kv_all.ulica (+)

Это же форум программирования в Delphi, а не на C :-)

P.S. И если это не так, то на что тратятся остальные сотни тысяч милисекунд? Не на связывание же таблицы и запроса по трём полям :-)

Not in () - это отвратительно, медленно и иногда не предсказуемо.
Предложения:
- все OR заменяются на UNION, тогда не везде нужны (+), если там NULL, так может и вязать не нужно
- Not in () заменяется на MINUS
Это пока не влезая в логику запроса

Оптимизация запроса Найти похожие ветки