Есть 2 таблицы. 1-я - справочник. Содержит в себе список работников и первичный ключ. Вторая - список заказов с прикреплёнными к ним работниками. Содержит 4 поля - внешние ключи - ссылки на первичный ключ первой таблицы и 5-е поле - первичный ключ заказов.

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

Как сделать с хранимой процедурой с четырьмя подзапросами - знаю.

Можно ли как-то выполнить задачу одним запросом? Спасибо.

> Как сделать с хранимой процедурой с четырьмя подзапросами - знаю.
процедура и 4-ре подзапроса для объединения 2-х таблиц? мощно! внишаит!...

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

> sniknik ©   (23.04.09 23:06) [1]

А то!  Хочу энто видеть !

> [1] sniknik ©   (23.04.09 23:06)
> хочу знать способ. заинтриговал чертяка. а то одним запросом
> это как то банально и мелко...

Бартер?

Как-то так:




Возвращает то, что мне нужно.

Можно ли как-то компактнее сделать?

т.е. тебе нужно вот это
select VOC_CONTANT
from VOCAB, TEST
where TEST.TEST_UID = 1 and
        (VOCAB.VOC_UID = TEST.TEST1 or
         VOCAB.VOC_UID = TEST.TEST2 or
         VOCAB.VOC_UID = TEST.TEST3 or
         VOCAB.VOC_UID = TEST.TEST4)
должен выдать список из 4х полей.

но вообще, так не делается, соединяют обычно по одному полю, потому как располагают данные в "высоту" таблицы, а не "ширину" тогда и количество этих данных не будет ограничено 4-мя... да и работать удобнее.

> т.е. тебе нужно вот это select VOC_CONTANT



> должен выдать список из 4х полей.


Запрос выдаст только одно поле. И 4 условия лишние.


> но вообще, так не делается, соединяют обычно по одному полю, потому как располагают данные в "высоту" таблицы, а не "ширину" тогда и количество этих данных не будет ограничено 4-мя... да и работать удобнее.


Если бы удалось выполнить задачу одним запросом - можно было бы отдавать в качестве результата множество записей. Сейчас BETWEEN никак не напишешь, конечно.

Как вообще делается я знаю. Можно было бы предложить сделать четыре справочника вместо одного. Тогда, конечно, никаких проблем бы не было. Но, дело в том, что одни и те же люди могут входить в разные справочники одновременно (поля TEST1 - TEST4 - это, как я поняли из задания, специальности, необходимые для выполнения задания. они у разных людей могут пересекаться). Поэтому добавление еще трёх справочников приведёт к явной денормальзации базы.

> апрос выдаст только одно поле. И 4 условия лишние.


Сорри, проглючило :)  or не увидел. Буду тестить. Возможно - самое то...

> (поля TEST1 - TEST4 - это, как я поняли из задания, специальности,
>  необходимые для выполнения задания. они у разных людей
> могут пересекаться).


такой глупый вопрос - а если для выполнения задания потребуется, не дай Аллах, пять специальностей ?

Вроде как примеров с Job и Skill в любом учебнике по SQL навалом - бери да переписывай любой.

Ыыыы.... Не то. Таки да - вернулось же много записей с одним полем. И Between-то работает, но не так, как нужно. ХП то как раз возвращает одну запись с четырьмя полями... Но - слишком сложно и только для одного uid'а...

> такой глупый вопрос - а если для выполнения задания потребуется,
>  не дай Аллах, пять специальностей ?


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


> Вроде как примеров с Job и Skill в любом учебнике по SQL
> навалом - бери да переписывай любой.


Порылся - сходу не нашел. Самому как-то такие задачи не попадались ранее.

Нашел таки :)




Не видел раньше алиасов у таблиц :)

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

> Порылся - сходу не нашел. Самому как-то такие задачи не попадались ранее.
вообще то их полно. но они на непонятной для тебя, правильной, классической логике. которую ты не воспринимаешь.

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

> sniknik ©   (24.04.09 01:56) [14]

Количество двойных кавычек превысило все разумные пределы.
:)

> Нашел таки :)
даже не написал на нашел? докатились.

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

> например простая накладная - справочник товаров + справочник
> документов, отбрасываем разные отвлекающие поля вроде типа
> накладной, цен товаров для записей, и т.д. оставляем только
> названия товаров и id для связей как у тебя.остается - поле
>  с номером накладной, и поле связи по id со справочником
> товаров. в "высоту" прошу заметить, никому в голову не приходит
> для каждой накладной, полей в "ширину" добавлять, если вдруг
> товаров больше пришло. и все. по этим двум полям все находится,
>  все записи накладной, по ее номеру, все названия товаров
> из присоединенного по полю связи справочника. и все в "высоту".
>  так удобнее.не, конечно и там среди бухгалтеров попадаются
> отщепенцы, придумали такую вещь как шахматка... но и ее
> делают. чаще всего "поворачивая"  полученный результат на
> клиенте, делая из "высоты" "ширину". можно и на сервере,
>  легко могу сделать на access или mssql  но на FB боюсь
> их синтаксис не пройдет, даже не пытаться не стоит (а трудов
> чтобы разбираться задача не стоит. глупая логика).


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


> даже не написал на нашел?


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


> данные во всех  полях связей обязательны,


Без проблем. Делаем дефолтное значение 0 у таблицы джобов, а нулевому юиду ставим в соответствие что-нибудь типа 'не назначено'.

> [17] OtherSie   (24.04.09 02:56)
> Что то ты в дебри полез. Было достаточно сказать, что у
> таблиц могут быть псевдонимы.

И это всё, что ты увидел в ответе, хотя об этом в нём не говорилось.

Экселем мыслим.

>Имелось в виду, что нашел, что у таблиц могут быть алиасы. Хорошая фича.

Как много нам открытий чудных
Готовит просвещенья дух
И опыт, сын ошибок трудных,
И гений, парадоксов друг
 :))

(с) "Очевидное-невероятное"...

шЮтка, конечно А.С.Пушкин.:-)

А потом заказчики удивляются, когда профи, которого просят "чуток доделать уже почти готовую" систему, требует денег за написание новой.
Дамс.

> Имелось в виду, что нашел, что у таблиц могут быть алиасы. Хорошая фича.
кто ж знал, что ты не знаешь про псевдонимы, про которые пишут на 15-20 й странице любого учебника по sql. и проблему ты видишь только в этом...
я вот вижу у тебя проблему в нежелании работать со списками, непонимании логики баз. а вовсе не в алиасах.

кстати, у твоего подхода есть минус, кроме того что там полей не напасешься, ну вот представь, что после будет например 200 человек... 200 полей, и 200 объединений таблицы самой с собой, вместо одно... это хороший способ поставить "на колени" любой sql сервер. даже четыре могут тормозить при определенных условиях. (размер таблиц/отсутствие или невозможность использование индексов)
вот запиши объединение в явном виде, с помощью join-ов сразу поймешь что имеется в виду. (join-ы это тоже хорошая фича, про которую ты наверное не знаешь, и рекомендуемая к использованию т.к. она ближе к тому как работает sql сервер, больше способствует пониманию, чем неявные объединения)

> (join-ы это тоже хорошая фича, про которую ты наверное не
> знаешь, и рекомендуемая к использованию т.к. она ближе к
> тому как работает sql сервер, больше способствует пониманию,
>  чем неявные объединения)

Зато с ними тяжелее оптимизить запросы.

> И это всё, что ты увидел в ответе, хотя об этом в нём не
> говорилось.


Я собственно это и говорил - что алисов не увидел - сам нашел.


> кто ж знал, что ты не знаешь про псевдонимы, про которые
> пишут на 15-20 й странице любого учебника по sql.


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

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

Вроде бы никакой крамолы. Во всяком случае, пока...

200, как и 1000 человек, думаю, никак не повлияют на скорость. 200 джобов, думаю, там не будет никогда. Далее, в случае необходимости, будем оптимизировать базу. Пока ограничимся имеющимся запросом.

Всем спасибо за ответы.

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

> Вроде бы никакой крамолы нет.
4 объединения в любом случае, даже самом оптимистичном, в 4 раз медленнее чем одно. при том же результате, только в непривычном для тебя виде.

> Далее, в случае необходимости, будем оптимизировать базу.
не получится. далее тебе ее придется переделывать с 0.

> Пока ограничимся имеющимся запросом.
вольному воля.

Сникника пора судить за растрату бисерного фонда :)

Сниксника пора судить за стрельбу из пушек по воробьям. Не каждая задача требует абсолютной оптимизации.

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

Не давая никакого практического выигрыша.

> [28] OtherSie   (25.04.09 16:44)

Ни хочешь слушать советы - твоё право. Но зачем тогда спрашивал, напрягал людей, заметь - бесплатно?

Удалено модератором

Удалено модератором

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

явными хорошо, если ты уже знаешь оптимальный план.

А вот если ты его подбираешь, то частенько вылезает необходимость поменять порядок обхода таблиц. С явными джойнами надо запрос посильнее править.
Да и букав писать больше.
У явных джойнов мне нравятся 2 вещи :
1) Больше вариантов объединения (правда "лишние" варианты весьма экзотические и фулл оутер я всего раз в жизни применял на практике).
2) Труднее забыть про связки и нарваться на картезиан.

> С явными джойнами надо запрос посильнее править.
> Да и букав писать больше.
скопипастить 40 символов намного труднее чем 10?

> 1) Больше вариантов объединения
?
вообще то от синтаксиса конкретного sql сервера зависит, например когда то в первисиве не было синтаксиса явных объединений, но никаких ограничений на их варианты не было, лефт джойн делался += , райт =+ , полное объединение * (или *= с любой стороны, не помню).

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

> sniknik  (27.04.2009 10:25:35)  [35]

Что там первисив, в Оракле не было.

А я до сих пор в оракле (+) пишу - оно как-то понятнее и серверу и мне и моим коллегам

> А я до сих пор в оракле (+) пишу - оно как-то понятнее и
> серверу и мне и моим коллегам

Вот и я о том же.


> скопипастить 40 символов намного труднее чем 10?

Если у меня хинт ordered и я начинаю играть порядком обхода, то при неявном объединении мне надо будет только поменять местами таблицы во фроме (вырезать+вставить). А при явном - переделывать текст запроса.

А пониманию больше способствует аккуратное форматирование запросов. Потому как кашу из явных джойнов тоже задерешься разгребать.

>Игорь Шевченко ©   (27.04.09 12:02) [37]
>А я до сих пор в оракле (+) пишу - оно как-то понятнее и серверу и мне и моим коллегам
так, вообще говоря, (+) не эквивалентен left/right outer join
да и (+)-синтаксис при необходимости full outer join не слишком спасает)

>ANB   (27.04.09 13:55) [38]
>Если у меня хинт ordered и я начинаю играть порядком обхода, то при >неявном объединении мне надо будет только поменять местами >таблицы во фроме (вырезать+вставить). А при явном - переделывать >текст запроса.
а потом, например, dba врубает plan stability, и вся система идет под откос
хинты - это зло, зло и зло)

Кщд   (27.04.09 14:53) [39]


> так, вообще говоря, (+) не эквивалентен left/right outer
> join


"Три следующих запроса, в первом из которых используется старый синтаксис, семантически одинаковы:

SELECT c.course_name, c.period,
 e.student_name
FROM course c, enrollment e
    WHERE c.course_name = e.course_name(+)

        AND c.period = e.period(+);

SELECT c.course_name, c.period,
 e.student_name
FROM course c LEFT OUTER JOIN enrollment e
    ON c.course_name = e.course_name
       AND c.period = e.period;

SELECT c.course_name, c.period,
 e.student_name
FROM enrollment e RIGHT OUTER JOIN course c
    ON c.course_name = e.course_name
       AND c.period = e.period;
"

http://www.oracle.com/global/ru/oramag/march2002/dev_ansi.html


> да и (+)-синтаксис при необходимости full outer join не
> слишком спасает)


не спасает. Когда требуется full outer join ставится full outer join. Беда только в том, что редко требуется :)

> хинты - это зло, зло и зло


Воистину

> full outer join

Сколько раз в жизни вы им пользовались ?

> хинты - это зло, зло и зло
>
>
> Воистину

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

> К сожалению, без них частенько никак


Кривое не может сделаться прямым

> Кривое не может сделаться прямым

1) Ради одного отчета никто не будет переделывать БД
2) Даже если структуру БД сделать идеальной, то конвертить туда данные просто нереально.
3) Все равно придется применять хинты в особо сложных случаях - хэш джойн, распараллеливание запросов. Оптимизатор не идеален.

ANB   (29.04.09 10:11) [46]

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

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

Если оптимизатор сразу дает примерно правильный план - я и не хинтую.

>Игорь Шевченко ©   (27.04.09 15:01) [41]
>"Три следующих запроса, в первом из которых используется старый >синтаксис, семантически одинаковы:
говорил о случаях, когда не одинаковы)
например,

>ANB   (29.04.09 14:55) [48]
>Если оптимизатор сразу дает примерно правильный план - я и не хинтую.
каким образом смотрите план?

> ANB  (29.04.2009 14:55:48)  [48]

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

Кщд   (29.04.09 14:56) [49]

А планы в студию для обоих вариантов не затруднит ?

>Игорь Шевченко ©   (29.04.09 19:17) [52]
затруднит)


собственно, об этом и говорил

или ещё пример:
как переписать на (+)-синтаксисе такой запрос

> ORA-01719: оператор внешнего соединения (+) не разрешен
> в операндах OR или IN


Уел :)

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

> каким образом смотрите план?

F5 в девелопере


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

Я сталкивался. Когда сталкивался - тогда и писал другим синтаксисом. Но это так редко бывает . . .

>Игорь Шевченко ©   (30.04.09 14:06) [54]
>Вот что странно - за долгую и многотрудную жизнь ни разу не приходилось >сталкиваться с необходимостью составлять подобные запросы.
очень редко, но случалось, поэтому сейчас, когда oracle залатал баги, связанные с join, пишу в join-синтаксисе
собственно, против (+)-синтаксиса ничего против не имею

>ANB   (04.05.09 09:57) [55]
>F5 в девелопере
реальный план можно увидеть только в трейсе
может случиться так, что Вы хинтами оптимизируете пустоту

> реальный план можно увидеть только в трейсе
> может случиться так, что Вы хинтами оптимизируете пустоту

Ну ни разу еще не нарывался. Как задумано - так и работает.

Если план по Ф5 показывает ерунду - запрос висит. Если показывает то, что надо - работает.

Чисто теоретически, есно, возможны расхождения. Пока не нарывался.
Иначе и смысла бы в он-лайн просмотре планов бы не было.

>sniknik ©   (24.04.09 11:08) [23]
> не, конечно и там среди бухгалтеров попадаются отщепенцы,
>  придумали такую вещь как шахматка... но и ее делают. чаще
> всего "поворачивая"  полученный результат на клиенте, делая
> из "высоты" "ширину". можно и на сервере, легко могу сделать
> на access или mssql  но на FB боюсь их синтаксис не пройдет,
>  даже не пытаться не стоит (а трудов чтобы разбираться задача
> не стоит. глупая логика).


Пытаться уже очень давно стоит.
http://delphiplus.org/articles/ib/firebird-2-full-capability/1/index.html

> ANB   (29.04.09 14:55) [48]
> Если оптимизатор сразу дает примерно правильный план - я и не хинтую.

В Firebird это может аукнуться. Например, генеришь IBExpert-ом  тестовых записей миллиона два-три, чтобы скучно ему не было. Выполняешь скрипт - вроде и план правильный подхватился. А потом на реальных данных "вес" индекса другой получается, т.к реальные данные далеко не равномерно распределенные случайные величины, как в генераторе IBExpert-a. И план твоего запроса (имеется в виду внутри ХП) после первого backup/restore "поедет" куда-то. Так что все-таки бывают случаи, когда планы иногда надо указывать явно. Лично у мну есть пример запроса с "дефолтным" планом, выполняющийся за 18 секунд, и "ручным", выполняющимся за миллисекунды.

Зато при переходе от версии к версии (как это было при 1.5 -> 2.0) есть вероятность наступить на очень древние грабли с изменениями в оптимизаторе, когда твоя БД тупо не восстанавливается из бэкапа из-за кривых (по мнению новой версии) статически прописанных планов. И тогда начинается увлекательнейший квест с перелопачиванием штук 800-1000 хранимых процедур. Так что тут есть свои и плюсы и минусы.

Есть еще третий, компромиссный вариант: хинты. Например, когда индексное поле попадает в условие WHERE, то к нему можно прибавлять 0, дабы отключить использование индекса. Такой изврат на данный момент времени дает похожий план запроса на разных версиях оптимизатора.

> Пытаться уже очень давно стоит.
> http://delphiplus.org/articles/ib/firebird-2-full-capability/1/index.html
и где ты там увидел синтаксис access или mssql? или не понятен смысл сказанного?

у mssql нет first, для access используется инструкция transform. для обоих, не обязательно (в одном случае даже нельзя подобную) делать процедуру, проще ограничится запросом. для access вложенные подзапросы группируются не так.

а то, что в FB это как то возможно, так вроде ничего в моем высказывании этому не противоречит.

>ANB   (04.05.09 15:15) [58]
>Чисто теоретически, есно, возможны расхождения. Пока не нарывался.
в любой сколько-нибудь "тяжелой"(миллионники, запутанная логика в SQL) системе - это данность

>Иначе и смысла бы в он-лайн просмотре планов бы не было.
большого смысла и нет
explain plan - это средство первой линии диагностики - поверхностной и приблизительной

>PEAKTOP ©   (04.05.09 16:57) [59]
>В Firebird это может аукнуться. Например, генеришь IBExpert-ом  тестовых >записей миллиона два-три, чтобы скучно ему не было. Выполняешь скрипт - >вроде и план правильный подхватился. А потом на реальных данных "вес" >индекса другой получается, т.к реальные данные далеко не равномерно >распределенные случайные величины
именно так
это справедливо и для Oracle, и для MS SQL
правильная структура данных, грамотное индексирование, сбор статистики - рецепт эффективной БД
хинты уместны, как и говорил ANB, для разовых выборок, отчетов
хинт в серверном коде - зло

> миллионники


это что такое ?

>Игорь Шевченко ©   (05.05.09 12:41) [63]
имел в виду таблицы с порядком записей 10^6 и выше

Кщд   (05.05.09 13:33) [64]

Понятно. А вот такой вопрос - есть таблица, с порядком записей 10^8. Есть запрос к этой таблице и еще нескольким (у одной из нескольких порядок записей 10^6, у других несущественный (меньше 10^4))
Оптимизатор определяет, что толстую таблицу он будет обрабатывать FULL SCAN, запрос выполняется порядка получаса. Создал я нужный индекс по толстой таблице (функция от одного поля и и еще набор полей), индекс стал использоваться, COST запроса сократился, IO сократились, запрос стал выполняться пару минут. Это при незагруженном сервере. А при загруженном сервере запрос выполняется полтора часа, то есть, в три раза медленней, чем при FULL SCAN толстой таблицы. Время выполнения запроса с FULL SCAN от загруженности сервера практически не зависит, полчаса, плюс-минус 5 минут.

Oracle 10.2.0.3 под Linux, сервер достаточно толстый по памяти, процессору и проч. Статистика собирается регулярно, в том числе и системная.
В сессии выставлены параметры:
Optimizer_Index_Caching=90
Optimizer_Index_Cost_Adj=40

Вопрос в следующем: В какую строну рыть ? :)

> В Firebird это может аукнуться. Например, генеришь IBExpert-
> ом  тестовых записей миллиона два-три, чтобы скучно ему
> не было. Выполняешь скрипт - вроде и план правильный подхватился.
>  А потом на реальных данных "вес" индекса другой получается,
>  т.к реальные данные далеко не равномерно распределенные
> случайные величины, как в генераторе IBExpert-a. И план
> твоего запроса (имеется в виду внутри ХП) после первого
> backup/restore "поедет" куда-то. Так что все-таки бывают
> случаи, когда планы иногда надо указывать явно. Лично у
> мну есть пример запроса с "дефолтным" планом, выполняющийся
> за 18 секунд, и "ручным", выполняющимся за миллисекунды.
>

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

> в любой сколько-нибудь "тяжелой"(миллионники, запутанная
> логика в SQL) системе - это данность

Скорее всего ваши админы вообще мышей не ловят.
У нас как раз запутанная логика в SQL, нету внешних ключей и все прочее, от чего нормальный архитектор БД пришел бы в ужас.
Таблица с несколькими миллионами записей у нас считается маленькой.

Однако, повторюсь, на наших базах ни разу не нарывался, что план на тесте по Ф5 не совпадает с планом на реалке по трэйсу.

> хинт в серверном коде - зло

Но неизбежное.

> Вопрос в следующем: В какую строну рыть ? :)

План покажи

> План покажи


Смотри

>Игорь Шевченко ©   (05.05.09 13:50) [65]
>COST запроса сократился, IO сократились, запрос стал выполняться пару >минут. Это при незагруженном сервере. А при загруженном сервере >запрос выполняется полтора часа, то есть, в три раза медленней
если навскидку, то я бы посмотрел, чего ждет сессия(v$session_wait), в которой работает данный запрос, при выполнении на продуктивном сервере
затем - смотрел бы на план из трейса

кстати, ф-ция в function based index - deterministic?

PS и, конечно, патчился бы до 10.2.0.4
понимаю-понимаю, что патчить продуктив не всегда можно)

>Игорь Шевченко ©   (05.05.09 14:47) [70]
навскидочку, если 6-ой hash join заменить на nested loops?

> кстати, ф-ция в function based index - deterministic?


Явно задана в при создании индекса




Кщд   (05.05.09 14:54) [72]

> навскидочку, если 6-ой hash join заменить на nested loops?


Это план без индекса


> если навскидку, то я бы посмотрел, чего ждет сессия(v$session_wait),
>  в которой работает данный запрос


db_file_sequential_read она ждет


> затем - смотрел бы на план из трейса


а это исключено политикой безопасности

план смотрелся из SQL*Plus с включенной опцией autotrace

собственно план с индексом:




Из которого видно, что 6-ой HAS JOIN заменился на NESTED LOOPS

|   8 |       TABLE ACCESS BY INDEX ROWID| BIG_T3     |   219 |  4818 |     4 |
|   9 |        INDEX RANGE SCAN          | IX_        |    15 |       |     1 |

Сколько всего записей в BIG_T3 и сколько примерно из них попадает в запрос ?

Сколько записей в таблице индекса IX_SMA_T2 и сколько из них попадает в запрос при первичном отборе ?

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

После он потерялся.

ANB   (05.05.09 15:43) [74]


> Сколько всего записей в BIG_T3 и сколько примерно из них
> попадает в запрос ?


всего десятки миллионов, попадают десятки тысяч


> Сколько записей в таблице индекса IX_SMA_T2 и сколько из
> них попадает в запрос при первичном отборе ?


Сколько-то тысяч, сколько-то сотен попадает.


> Хотя даже навскидку - выкини нафик индекс


Какой именно ?


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


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

Любопытная жизнь у индексов.

Может у тебя 2 террабайта памяти...

Anatoly Podgoretsky ©   (05.05.09 17:03) [76]

Тогда бы не наблюдалось явления, описанного в [65], из двух Тб оно бы преспокойно быстренько выбиралось в течение недели, а не только по выходным. На выходные память не наращивают, это точно.

Кщд   (05.05.09 14:51) [71]

Кстати, обманул насчет версии Oracle

SQL> select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.4.0 - 64bit Production
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

>Игорь Шевченко ©   (05.05.09 17:35) [78]
покажите, пожалуйста, сам запрос

>ANB   (05.05.09 14:10) [67]
>Скорее всего ваши админы вообще мышей не ловят.
никакой dba не в силах изменить простую аксиому: данные explain plan и данные трейса - это вовсе не одно и то же)

Кщд   (06.05.09 07:56) [79]


> покажите, пожалуйста, сам запрос

>Игорь Шевченко ©   (06.05.09 11:16) [81]
результаты тайминга  на сервере без нагрузки были приведены для insert или - всё же - для select?
есть стойкое подозрение, что на продуктиве реальный план другой
снять план из трейса категорически невозможно?

Кщд   (06.05.09 12:32) [82]


> результаты тайминга  на сервере без нагрузки были приведены
> для insert или - всё же - для select?


Для insert, разумеется.


> снять план из трейса категорически невозможно?


Да, невозможно. План для SELECT с использованием AUTOTRACE снимался с выдачей данных, при этом время выполнения одного SELECT примерно соответствовало времени выполнения INSERT. Я к тому, что основная доля времени падает на SELECT

>Игорь Шевченко ©   (06.05.09 12:59) [83]
в порядке эксперимента предложил бы
1. убрать оставшийся hash join (индекс на sma_t5(cc))
2. использовать --+ append

если это выполнить возможно, то, пожалуйста, выложите планы и тайминги, но именно для insert

PS
как известно, были баги, связанные с тем, что в отлаженных select'ах при использовании в insert into select from "разлетались" планы

Кщд   (06.05.09 13:12) [84]


> 2. использовать --+ append


то, куда insert-ится - это global temporary table, смысла в append как-то не вижу.


> 1. убрать оставшийся hash join (индекс на sma_t5(cc))


Таблица sma_t5 мала - десятки тысяч записей.


> PS
> как известно, были баги, связанные с тем, что в отлаженных
> select'ах при использовании в insert into select from "разлетались"
> планы


Дело в том, что как я писал:

"План для SELECT с использованием AUTOTRACE снимался с выдачей данных, при этом время выполнения одного SELECT примерно соответствовало времени выполнения INSERT. Я к тому, что основная доля времени падает на SELECT"

так что я бы не очень принимал во внимание "были баги при insert ... select"

и INSERT и SELECT ждут dbfile sequential read и dbfile scattered read.

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

Я даже не могу к себе данные перетащить и потрассировать запросы - у меня XE, а данных всяко больше 4-х Гб

>Игорь Шевченко ©   (06.05.09 13:38) [85]

то, куда insert-ится - это global temporary table, смысла в append как-то не вижу.

согласен


Таблица sma_t5 мала - десятки тысяч записей.

тем не менее, хотелось бы увидеть максимально оттюненный SQL и уже потом, разбираться с "тормозами"


так что я бы не очень принимал во внимание "были баги при insert ... select"

как понимаете, время не аргумент
если план для insert показывает то же, что план select, то (при отсутствии возможности снять настоящий план из трейса) предложение снимается


и INSERT и SELECT ждут dbfile sequential read и dbfile scattered read.

т.е., другими словами, это индексные чтения и FTS, поэтому и хотел бы свести всё к индексным


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

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

Кщд   (06.05.09 14:02) [86]

Из поста [65] "Статистика собирается регулярно, в том числе и системная." :)


> тем не менее, хотелось бы увидеть максимально оттюненный
> SQL и уже потом, разбираться с "тормозами"


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

Тут еще момент - сейчас индекс отключен, а развлекаться с ним я могу, когда сервер не занят, то есть, в очередные выходные.

>Игорь Шевченко ©   (06.05.09 14:33) [87]

Из поста [65] "Статистика собирается регулярно, в том числе и системная." :)

прошу прощения, взгляд "замылился")


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

возможно, под нагрузкой собранная статистика становится недостоверной(в течение дня интенсивные insert/update/delete) и индекс слетает
в общем, всё это без трейса, увы, гадание на кофейной гуще...
ну, разве что посмотреть, на каком именно индексе висят ожидания sequential read
и попробовать увеличить buffer cache

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

Кщд   (06.05.09 14:50) [88]

собрали трассировку с криками е.т.м :)

Получили странное из статистики:







и







Сильно не понравилось близость цифр elapsed на Execute и db file sequential read в ожиданиях.

Будем думать.

План кстати не изменился

Странноватая ситуация. Обычно индекс_ранг_скан слабо зависит от нагрузки.
Я мог предположить, что завис шел при очистке целевой таблицы транкейтом (была у нас такая грабля), но тут явно другое.

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

> Странноватая ситуация


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

В больших таблицах первого варианта по ~30 и ~50 миллионов, в больших таблицах второго варианта ~50 и ~150 миллионов, второй вариант выполняется быстрее и статистика ожиданий у него более похожа на правду.

Я просто к чему - дело не в запросе. С запросами я бороться более или менее умею. Мне интересно, в чем может причина.


> Скорее всего надо внимательно посмотреть на табличное пространство,
>  в которое посажен новый функциональный индекс


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

Все индексы в одном tablespace

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

Сколько всего строк в таблице с этим индексом и сколько из них отбирается ?

Хотя если без нагрузки работает быстро . . .

Табличное пространство дефрагментрировать не пробовали ?

ANB   (08.05.09 16:07) [93]

Ты как тот раввин - "а жаль, еще столько идей было".


> Табличное пространство дефрагментрировать не пробовали ?


Пробовали, не помогло. На праздниках попробую ANALYZE INDEX VALIDATE STRUCTURE по индексам (их немного) таблиц проблемного запроса.

> Ты как тот раввин - "а жаль, еще столько идей было".

Угу. Оптимизация толстых запросов - это почти всегда пляски с бубном. :)

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

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


Это как ?

insert into T_Rpt
select
 *
from
 T1
,T2
,T4
,T5
,T6
where
 условия

заменяем на несколько инсертов типа

insert into T_Rpt_1
select
 *
from
 T1
,T2
where
 условия

insert into T_Rpt_2
select
 *
from
 T_Rpt_1
,T4
where
 условия

и. т. Есно с набором полей вместо *

В каждой порции подбираем оптимальный план, (хэш джойн или нестед лупс), при надобности - распараллеливаем.

Ресурсов, правда, жрет такой метод до хрена. Зато работает в приемлемые сроки. И отлаживать проще.

ANB   (13.05.09 16:15) [97]

Такой путь неприемлем

>Игорь Шевченко ©   (06.05.09 16:48) [89]
разберетесь, выложите, пожалуйста, здесь результат?

>ANB   (13.05.09 14:09) [95]
>Угу. Оптимизация толстых запросов - это почти всегда пляски с бубном. :)
оптимизатор - это набор правил, статистики и багов
бубна требуют лишь баги для разработки workaround
да и то - проблема с последними - зачастую - решается прочтением соответствующей ноты с metalink
поэтому всякое упоминание о "колдунстве" со стороны разработчиков БД лично я воспринимаю, как попытку набить себе цену
уж извините)

Кщд   (14.05.09 07:21) [99]

> разберетесь, выложите, пожалуйста, здесь результат?


Постараюсь, только неизвестно, сколько времени займет этот процесс - он в фоновом режиме

Удалено модератором

Удалено модератором