Как составить запрос к базе

← →
Кщд (2009-04-27 14:55) [40]

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

← →
Игорь Шевченко © (2009-04-27 15:01) [41]

Кщд (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. Беда только в том, что редко требуется :)

← →
Игорь Шевченко © (2009-04-27 15:02) [42]

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

Воистину

← →
ANB (2009-04-28 09:27) [43]

> full outer join

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

← →
ANB (2009-04-28 09:31) [44]

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

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

← →
Игорь Шевченко © (2009-04-28 15:45) [45]

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

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

← →
ANB (2009-04-29 10:11) [46]

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

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

← →
Игорь Шевченко © (2009-04-29 10:56) [47]

ANB (29.04.09 10:11) [46]

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

← →
ANB (2009-04-29 14:55) [48]

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

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

← →
Кщд (2009-04-29 14:56) [49]

>Игорь Шевченко © (27.04.09 15:01) [41]
>"Три следующих запроса, в первом из которых используется старый >синтаксис, семантически одинаковы:
говорил о случаях, когда не одинаковы)
например,
select t.*, t2.* from tmp t, tmp2 t2 where t.id = t2.id(+) or t.id2 = t2.id2(+) select t.*, t2.* from tmp t left join tmp2 t2 on t.id = t2.id or t.id2 = t2.id2

← →
Кщд (2009-04-29 14:58) [50]

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

← →
Anatoly Podgoretsky © (2009-04-29 16:16) [51]

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

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

← →
Игорь Шевченко © (2009-04-29 19:17) [52]

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

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

← →
Кщд (2009-04-30 09:17) [53]

>Игорь Шевченко © (29.04.09 19:17) [52]
затруднит)
SQL> select t.*, t2.* 2 from tmp t, tmp2 t2 3 where t.id = t2.id(+) 4 or t.id2 = t2.id2(+) 5 ; ORA-01719: оператор внешнего соединения (+) не разрешен в операндах OR или IN
собственно, об этом и говорил

или ещё пример:
как переписать на (+)-синтаксисе такой запрос
select t.*, t2.* from tmp t left join tmp2 t2 on t.id = t2.id and t.id2 = 2

← →
Игорь Шевченко © (2009-04-30 14:06) [54]

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

Уел :)

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

← →
ANB (2009-05-04 09:57) [55]

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

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

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

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

← →
Кщд (2009-05-04 10:17) [56]

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

← →
Кщд (2009-05-04 10:18) [57]

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

← →
ANB (2009-05-04 15:15) [58]

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

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

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

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

← →
PEAKTOP © (2009-05-04 16:57) [59]

>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, дабы отключить использование индекса. Такой изврат на данный момент времени дает похожий план запроса на разных версиях оптимизатора.

← →
sniknik © (2009-05-04 20:03) [60]

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

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

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

← →
Кщд (2009-05-05 07:11) [61]

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

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

← →
Кщд (2009-05-05 07:16) [62]

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

← →
Игорь Шевченко © (2009-05-05 12:41) [63]

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

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

← →
Кщд (2009-05-05 13:33) [64]

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

← →
Игорь Шевченко © (2009-05-05 13:50) [65]

Кщд (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

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

← →
ANB (2009-05-05 14:05) [66]

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

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

← →
ANB (2009-05-05 14:10) [67]

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

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

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

← →
ANB (2009-05-05 14:30) [68]

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

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

← →
ANB (2009-05-05 14:33) [69]

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

План покажи

> План покажи

Смотри

-------------------------------------------------------------------------------- |Id |Operation | Name | Rows | Bytes | Cost | -------------------------------------------------------------------------------- | 0 |SELECT STATEMENT | | 1940 | 251K| 30528 | | 1 | HASH JOIN | | 1940 | 251K| 30528 | | 2 | TABLE ACCESS FULL |SMALLT1 | 9 | 117 | 2 | | 3 | NESTED LOOPS ANTI | | 2511 | 294K| 30525 | | 4 | HASH JOIN | | 2582 | 252K| 30524 | | 5 | NESTED LOOPS | | 2582 | 226K| 30519 | | 6 | HASH JOIN | | 17517 | 821K| 23499 | | 7 | INDEX FULL SCAN |IX_SMA_T2 | 80 | 2080 | 2 | | 8 | TABLE ACCESS FULL | BIG_T3 | 50M| 1069M| 22488 | | 9 | TABLE ACCESS BY INDEX ROWID| MED_T4 | 1 | 42 | 1 | | 10 | INDEX UNIQUE SCAN | PK_MED_T4 | 1 | | 1 | | 11 | TABLE ACCESS FULL | SMA_T5 | 13014 | 127K| 5 | | 12 | INDEX UNIQUE SCAN | IX_MED_T6 | 655 | 13100 | 1 | --------------------------------------------------------------------------------

← →
Кщд (2009-05-05 14:51) [71]

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

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

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

← →
Кщд (2009-05-05 14:54) [72]

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

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

CREATE INDEX ix_ ON bigtable (field2, (CASE WHEN TRANSLATE(TRIM(field1),"x01234567890", "x") IS NULL THEN TO_NUMBER(field1) ELSE 999999 END),field3)

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

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

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

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

db_file_sequential_read она ждет

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

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

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

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

------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1940 | 251K| 7384 | | 1 | HASH JOIN | | 1940 | 251K| 7384 | | 2 | TABLE ACCESS FULL | SMALLT1 | 9 | 117 | 2 | | 3 | NESTED LOOPS ANTI | | 2511 | 294K| 7381 | | 4 | HASH JOIN | | 2582 | 252K| 7380 | | 5 | NESTED LOOPS | | 2582 | 226K| 7375 | | 6 | NESTED LOOPS | | 17517 | 821K| 355 | | 7 | INDEX FULL SCAN | IX_SMA_T2 | 80 | 2080 | 2 | | 8 | TABLE ACCESS BY INDEX ROWID| BIG_T3 | 219 | 4818 | 4 | | 9 | INDEX RANGE SCAN | IX_ | 15 | | 1 | | 10 | TABLE ACCESS BY INDEX ROWID | MED_T4 | 1 | 42 | 1 | | 11 | INDEX UNIQUE SCAN | PK_MED_T4 | 1 | | 1 | | 12 | TABLE ACCESS FULL | SMA_T5 | 13014 | 127K| 5 | | 13 | INDEX UNIQUE SCAN | IX_MED_T6 | 655 | 13100 | 1 | -------------------------------------------------------------------------------

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

← →
ANB (2009-05-05 15:43) [74]

| 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

← →
Кщд (2009-05-06 07:56) [79]

← →
Кщд (2009-05-06 07:59) [80]

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

Как составить запрос к базе Найти похожие ветки