Необходимо сократить или оптимизировать запрос!

← →
rayrom © (2005-07-26 15:44) [0]

Необходимо сократить или оптимизировать запрос:
SELECT s.desc, to_char(t.godto,"YYYY") yearto, count(gosnum) kolvo
FROM avto a, texosm t, amt_spr s
where a.NZA = t.NZA AND a.tip_avt = s.codename and t.keyuin = (SELECT MAX(keyuin)
FROM TEXOSM
WHERE a.NZA = texosm.NZA)
AND prin_avt = 1 and akt = 1 and cod = "10"
group by s.desc, godto

где amt_spr - справочник типов
texosm - таблица техосмотров (накопительная система запрос (SELECT MAX(keyuin) FROM TEXOSM WHERE a.NZA = texosm.NZA) определяет последнюю запись)
avto – таблица автотранспорта.
Через to_char(t.godto,"YYYY") я откусываю из дата год для вывода
count(gosnum) определяю количество транспорта
s.desc – название типа транспорта (их не много 16 штук)
prin_avt = 1 – определяю принадлежность (значения 0 или 1)
act = 1 – что он активный
cod = "10" – что нашего региона.
Так вот процесс идет очень долго, особенно по личному транспорту (~2 000 000 записей), и данные которые я получаю при группировке не получаются типа:
1 – 2005 – 250
2 – 2005 – 377
2 – 2004 – 400 …
а получаю типа такого
1 – 2005 – 120
1 – 2005 – 110
1 – 2005 – 20 …
Как это, во-первых, ускорить, а во-вторых, получить полную группировку!

З.Ы. В МуСКЛе я мог группировать по названию получаемого поля (select year(godto) as yearto …. group by yearto), а Ораклы не хотят, только по оригинальному названию поля! :(

← →
ANB © (2005-07-26 15:50) [1]

1. Индексы.
2.
> Ораклы не хотят, только по оригинальному названию поля!
> :(
- используй вложенный запрос. И будет тебе счастье.

← →
Fay © (2005-07-26 15:56) [2]

2 rayrom © (26.07.05 15:44)
Дай скрипты для создания таблиц - будет проще ваять.

← →
Sergey13 © (2005-07-26 15:56) [3]

А индексы какие есть? А план что говорит?
Я бы в первую очередь вместо
FROM avto a
попробовал сделать
FROM (select * from avto where prin_avt = 1 and akt = 1 and cod = "10" ) a

← →
rayrom © (2005-07-26 16:03) [4]

К сожелению база не моя, и менять или что либо создавать не имею никакого права, только выборки и все!

← →
Sergey13 © (2005-07-26 16:05) [5]

2[4] rayrom © (26.07.05 16:03)
А кто тебя об этом просил?

← →
rayrom © (2005-07-26 16:09) [6]

2 Sergey13 © (26.07.05 16:05)
Если есть Аська кинь на мыло все объясню!!!

← →
Sergey13 © (2005-07-26 16:18) [7]

2[6] rayrom © (26.07.05 16:09)
Нет ее у меня.
У тебя похоже подзапрос с МАХ-ом делается на каждую запись из авто. Я предложил сначала ограничить ее, а потом соединять. Можно попробовать выбрать предварительно все из TEXOSM. типа
SELECT NZA,MAX(keyuin) FROM TEXOSM group by 1
и заделать это вместо TEXOSM во From головного запроса.

← →
evvcom © (2005-07-26 16:59) [8]

SELECT
s.desc,
to_char(t.godto,"YYYY") yearto,
count(gosnum) kolvo
FROM avto a
INNER JOIN texosm t
ON t.NZA = a.NZA
INNER JOIN amt_spr s
ON s.codename = a.tip_avt
INNER JOIN (
SELECT
to.NZA,
MAX(to.keyuin) keyuin
FROM TEXOSM to
GROUP BY to.NZA
) q
ON q.NZA = a.NZA AND
q.keyuin = t.keyuin
where prin_avt = 1 and akt = 1 and cod = "10" -- здесь тоже используй синонимы, нагляднее
group by s.desc, t.godto

Примерно так, писал прямо здесь, должно работать.

← →
rayrom © (2005-07-27 10:17) [9]

2 evvcom
Хорошо еслиб все так просто, только про INNER JOIN придется забыть, версия ораклов 8, там JOIN ни в каком виде нету!

← →
Sergey13 © (2005-07-27 10:24) [10]

[9] rayrom © (27.07.05 10:17)
>Хорошо еслиб все так просто, только про INNER JOIN придется забыть, версия ораклов 8, там JOIN ни в каком виде нету!

(+)

← →
rayrom © (2005-07-27 10:31) [11]

2 Sergey13
Об єтом поподробнее плз, у меня нет нормальной доки, только по версии 7.

← →
ANB © (2005-07-27 10:32) [12]

> Sergey13 © (27.07.05 10:24) [10]

И в 9 нету.

← →
Sergey13 © (2005-07-27 11:00) [13]

SELECT *
FROM avto a, texosm t
where a.NZA = t.NZA(+)

выдаст все авто (даже если они не проходили ТО) с данными о ТО (у непроходивших будут пустые ТО-шные поля).
Не то?

← →
rayrom © (2005-07-27 11:06) [14]

> Sergey13

Ну дык это уже было, то же что и просто =

← →
evvcom © (2005-07-27 11:10) [15]

> (+)

Для INNER JOIN это лишнее. (+) нужен, если {LEFT|RIGHT|FULL} JOIN.

> версия ораклов 8,

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

← →
rayrom © (2005-07-27 11:11) [16]

Вообщето я тут подумал, посмотрите такой вариант -
SELECT s.desc, to_char(t.godto,"YYYY") yearto, count(gosnum) kolvo FROM avto a, texosm t, amt_spr s where a.NZA = t.NZA AND a.tip_avt = s.codename and t.keyuin = (SELECT MAX(keyuin) FROM TEXOSM WHERE a.NZA = texosm.NZA AND а.prin_avt = 1 and а.akt = 1 and а.cod = "10" ) group by s.desc, godto
В принципе будет ли тоже самое?

← →
evvcom © (2005-07-27 11:24) [17]

Блин, ну вынеси ты этот подзапрос из where, посмотри как в [8]. То же это или не то же зависит от того, как оптимизатор твой запрос разрулит. Не знаю, но, возможно, твой код оптимизатор не может оптимизировать так, чтобы выполнить за 2 скана, поэтому приходится для каждого t.keyuin выполнять отдельно подзапрос. Я не настаиваю, но возможно это так.

← →
rayrom © (2005-07-27 11:27) [18]

> evvcom

Пробывал в таком варианте, орет что мол много значений!

← →
evvcom © (2005-07-27 11:37) [19]

Что значит "орет"? Код варианта и текст ошибки в студию. Я постоянно так делаю, и у меня не орет.

← →
rayrom © (2005-07-27 12:02) [20]

Чесно говоря не нравится мне этот запрос, если логически рассуждать то нужно сделать в таком варианте-
Выбрать все НЗА для автомобилей по необходимым условиям, потом выбрать все последние записи для техосмотров в связки отобранных автомобильных НЗА, и то что получилосьсвязать со справочником и сгруппировать, аот только как энто реализовать чтото ума не приложу (ИМХО долбанутый Оракл!)

← →
Sergey13 © (2005-07-27 12:05) [21]

2 [20] rayrom © (27.07.05 12:02)
Я тебе об этом и писАл в [3] и [7].

>(ИМХО долбанутый Оракл!)
Плохому танцору знаешь что мешает?

← →
rayrom © (2005-07-27 12:14) [22]

> Sergey13

В том то и дело что не проходит такой вариан, возмущается Оракл, что слишком много значений!
Смотря что он танцует, в МуСКЛе я очень неплохо танцую, а ограниченность Ораклов меня уже достало (к примеру чтоб из даты вытащить год в числовом параметре нужно 2 преобразования делать в МуСКЛе 1 преобразование и т.п. и т.д.)!!! То что в МуСКЛ я могу за 2-3 минуты реализовать в Ораклах днями приходится сидеть и обсасывать варианты Ж(

← →
rayrom © (2005-07-27 12:16) [23]

Тем более что я в Ораклах только 3 месяца!

← →
Sergey13 © (2005-07-27 12:18) [24]

2[22] rayrom © (27.07.05 12:14)
>В том то и дело что не проходит такой вариан
Какой "такой"?

>Смотря что он танцует, в МуСКЛе я очень неплохо танцую,
Напомнает критику классического балета исполнителем народных танцев. 8-)

← →
rayrom © (2005-07-27 12:24) [25]

> Sergey13 © (27.07.05 12:18) [24]
> Какой "такой"?

[3] + [7]!!!
Извини Модератор может чтото из офтопа скажу!

Дело в том что я с многими людьми пообщался по поводу Ораклов (которые давно работают с ним) и они согласились с моим мнением что разрекламированная марка которая не стоит того чтобы его так превозносить по 5 бальной шкале он занимает 3+ максимум!

← →
Sergey13 © (2005-07-27 12:35) [26]

2[25] rayrom © (27.07.05 12:24)
Если ты хочешь холиваров, то я не хочу. Сходи на sql.ru в форум "Сравнение СУБД" если хочешь. Там этого добра - выше крыши.

А по сабжу - раз ты свой запрос тихаришь, как ты думешь тебе помогут?

← →
rayrom © (2005-07-27 12:39) [27]

Нет Сергей просто небольшое отступление, просто пока я в Инете я не могу подрубится к базе и проверить запрос (чето ВПН подглюкивает), может мне просто поподробней описать что собственно я хочу?

← →
Sergey13 © (2005-07-27 12:44) [28]

2[27] rayrom © (27.07.05 12:39)
>просто пока я в Инете я не могу подрубится к базе и проверить запрос
Но ты же говоришь про ошибку. Тебя и просили показать запрос и ошибку которую он выдал (я подозреваю это недопеределаный подзапрос с МАХом ругнулся).
Не хочешь - не надо. Я не настаиваю.

← →
ANB © (2005-07-27 12:46) [29]

> rayrom © (27.07.05 12:24) [25]
Оракле рулит !!! Все проблемы в том, что ты на Оракле УЖЕ целых 3 месяца и его еще не выучил.

← →
rayrom © (2005-07-27 14:21) [30]

Ладно есть еще один вопрос есть ли в Ораклах инструкция еxplain (эта инструкция пере оператором select в МуСКЛе показывает что где и как будет выбиратся, сколько, и какие индексы при этом используются)? Если есть, то вопрос мой мона закрывать, сам разберусь!

← →
evvcom © (2005-07-27 14:51) [31]

> rayrom ©

Зря ты на Оракл наезжаешь. Ты его просто не знаешь. В конструкцию языка многие такие вещи добавили, о которых тебе и не снилось. Только для этого 8 версию в топку надо. Имхо. Уже 10 юзают, а ты все на 8 сидишь. Отсюда и ограничения. Уже даже 9 над 8 на порядок выше стоит, а что там в 10 даже не знаю.
А с MSSQL каким сравниваешь? А ты сравни сверстников.

← →
Sergey13 © (2005-07-27 15:07) [32]

2[30] rayrom © (27.07.05 14:21)

EXPLAIN PLAN

2[31] evvcom © (27.07.05 14:51)
А чего в 8 не хватает? И в какой 8? Даже 8 и 8i разные.

← →
Sergey13 © (2005-07-27 15:12) [33]

2[30] rayrom © (27.07.05 14:21)
А какой тулзой ты пользуешься для SQLя? В PLSQLDeveloper например такая возможность есть по кнопке. В ТОАДе тоже.

← →
ANB © (2005-07-27 15:12) [34]

> Sergey13 © (27.07.05 15:07) [32]
- хе, в 8i столько наворотов, что я до сих еще не все фичи знаю, а уж про 9-10 я вообще молчу.

← →
rayrom © (2005-07-27 15:15) [35]

На сервере в 100 км от меня база стоит Оракл версии 8.1.5.17 под Фрюхой, на двух Ксеонах, 1 ГБ озу, 60ГБх4 СКАЗИ винта в рейде, но при этом в том запросе что я сначала написал безбожно тормозит, он выполняется гдето от 3-15 минут в зависимости от загрузки сервера (клиентов много), а в мускуле такой же запрос выполняется от 20сек-2 мин (тоже от загрузки зависит) дык при етом комп на котором МуСКЛ стоит сел2.5, 512 МБ озу, винт 80 АТА 133!

← →
rayrom © (2005-07-27 15:16) [36]

Да забыл добавить операционка Фря 5.1.

← →
evvcom © (2005-07-27 15:26) [37]

> А чего в 8 не хватает? И в какой 8?

Я с 8 не работал, поэтому сужу только по "слухам". Например, нет ни в одной 8, насколько мне известно, конструкции with, нет конструкции JOIN, а (+) вроде как Оракл в нескольких строках не позволяет. Аналитических функций по одним утверждениям в 8 нет, по другим вроде есть, точно не знаю. Может я и ошибаюсь, не знаю, повторю, что с 8 я не работал.

← →
ANB © (2005-07-27 15:26) [38]

> rayrom © (27.07.05 15:15) [35]

1. Мускуль много клиентов просто не вытянет
2. Оракла оперативку любит, так что 1 гектар неплохо бы и расширить
3. Запрос нужно оптимизнуть и прохинтовать.
4. А структура базы/индексы в оракле и мс скуле одинаковые ?

← →
rayrom © (2005-07-27 15:38) [39]

> 1. Мускуль много клиентов просто не вытянет
Ага держал аж 218 штук.
> 2. Оракла оперативку любит,
Ага после 100 клиентов затыкатся начинает!
> так что 1 гектар неплохо бы и расширить
Хотелось бы тока денег не выделяютя!
> 3. Запрос нужно оптимизнуть и прохинтовать.
Во я и спросил как это сделать (вопрос по поводу explain)!
> 4. А структура базы/индексы в оракле и мс скуле одинаковые?
Полный дубликат (тока без тригерров, процедур, и т.п. что в MySQLe не поддерживается)

← →
Sergey13 © (2005-07-27 15:45) [40]

2 [35] rayrom © (27.07.05 15:15)
8.1.5 - не самый лучший релиз по отзывам.

Производительность запроса зависит не только от самого запроса, но и от того как настроен Оракл, какие индексы и т.д.

Ты так и не даешь последнюю редакцию твоего запроса.

2[39] rayrom © (27.07.05 15:38)
>> так что 1 гектар неплохо бы и расширить
>Хотелось бы тока денег не выделяютя!
Важно еще как этот гектар распахан.

← →
rayrom © (2005-07-27 15:49) [41]

Кстати EXPLAIN PLAN - используется для получения плана, которому которому следует сервер базы данных при выполнении утвеждения СКЛ. Эта команда вставляет строку, описывающую пошаговый план выполнения, в указанную таблицу. (SQL Справочник программиста).
Это не то, мне нужно чтоб сервер показал как он выполняет выборку, вот что!

← →
ANB © (2005-07-27 15:53) [42]

> Важно еще как этот гектар распахан.

> rayrom © (27.07.05 15:49) [41]

Да, кстати, настройки памяти тоже лучше выложи. На неправильных настройках можно до 50% времени потерять.

← →
rayrom © (2005-07-27 15:55) [43]

Вот последняя редакция, она не отличается от первоначального, только тем что справчник последний связывается, и ксати чуть быстрее работает:
SELECT s.name, to_char(t.god_to,"YYYY") yearto, count(gos_num) kolvo
FROM avt_new a, tex_osm t, t_amt_spr s
where a.NZA = t.NZA and t.key_nid = (SELECT MAX(key_nid)
FROM TEX_OSM
WHERE a.NZA = tex_osm.NZA) AND a.tip_amt = s.codename
AND prin_amt = 1 and act = 1 and cod_mr = "18"
group by s.name, god_to

← →
rayrom © (2005-07-27 15:57) [44]

2 ANB, каким макаром, у меня доступа к серверу та нету, что сбегать за 100 км к серверу и спросить у разработчика который за 300 км находится. Я ж говорил могу только SELECT исполнять и все мои привелегии!

← →
Sergey13 © (2005-07-27 16:44) [45]

2[43] rayrom © (27.07.05 15:55)
А если нечто такое изобразить?
SELECT s.name, to_char(t.god_to,"YYYY") yearto, count(gos_num) kolvo
FROM
(select * from avt_new where prin_amt = 1 and act = 1 and cod_mr = "18") a, (SELECT NZA,MAX(key_nid) FROM TEX_OSM group by 1) t,
t_amt_spr s
where a.NZA = t.NZA AND a.tip_amt = s.codename
group by s.name, god_to

Подзапрос к техосмотрам можно ограничить еще и по авто.

← →
rayrom © (2005-07-27 16:54) [46]

Выдает Invalid field name., ведь откуда будет братся to_char(t.god_to,"YYYY") yearto, если в выборке его нет?

← →
Sergey13 © (2005-07-27 17:04) [47]

2[46] rayrom © (27.07.05 16:54)
Я ж тебе болванку дал как идею, а не рабочий запрос.

← →
rayrom © (2005-07-27 17:06) [48]

Даа, такой бред получится, ладно потом поэкперементим (Все равно МуСКЛ рулез, а Оракл - Ракло)

← →
ANB © (2005-07-27 17:31) [49]

> rayrom © (27.07.05 17:06) [48]
Наоборот !!! Плюс у ораклистов зарплата выше. Вот ты сколько получаешь ?

← →
ANB © (2005-07-27 17:33) [50]

> Sergey13 © (27.07.05 16:44) [45]
- кстати, клевое решение проблемы, ща в копилочку себе скопирую . . .

← →
rayrom © (2005-07-28 09:54) [51]

Пришол к такому варианту но чтото не соображу где ошибка:SELECT s.name, to_char(tt.god_to,"YYYY") yearto, count(a.gos_num) kolvo FROM (select * from avt_new where prin_amt = 1 and act = 1 and cod_mr = "18") a, (SELECT NZA,MAX(key_nid) FROM TEX_OSM group by nza) t, tex_osm tt t_amt_spr s where a.NZA = t.NZA AND tt.key_nid = t.key_nid and a.tip_amt = s.codename group by s.name, tt.god_to

← →
Ольга (2005-07-28 10:04) [52]

1. select gos_num from avt_new where ...
2. group by s.name, to_char(tt.god_to,"YYYY")

← →
rayrom © (2005-07-28 10:11) [53]

Премного благодарен Ольга, ваше предложение убрало повторение годов по одному типу транспорта - group by s.name, to_char(tt.god_to,"YYYY")

2[53] rayrom © (28.07.05 10:11)
А как c производительностью? Можно попробовать еще ограничить 2 подзапрос
(SELECT to.NZA,MAX(to.key_nid) FROM TEX_OSM to,avt_new av WHERE av.NZA = to.NZA and av.prin_amt = 1 and av.act = 1 and av.cod_mr = "18"
group by nza) t
И какие есть индексы на avt_new? Иногда перестановка местами условий в where (сделать порядок как в индексе) дает ощутимые результаты.

Попробуй вставить SELECT /*+ RULE */s.name, to_char

2 Sergey13
Счас на мыло вышлю структуры авто и техосм, из Ембасадеро!

2[55] rayrom © (28.07.05 11:39)
Что такое Ембасадеро? Не надо мне ничего слать. 8-)
Просто посмотри индексы и приведи порядок условий в where в соответствие с ними если возможно.
Так что с производительностью то? Лучше, хуже, также?

Embarcadero ER/Studio, the award-winning data modeling application for logical and physical database design and construction. ER/Studio"s powerful, multi-level design environment addresses the everyday needs of database administrators, developers and data architects who build and maintain large, complex database applications. Its progressive interface and processes have been logically organized to effectively address the "ease-of-use" issues that have plagued data modeling tools for the past decade. The application facilitates your ability to create, understand and manage the mission-critical database designs within an enterprise. It offers strong logical design capabilities, bi-directional synchronization of logical and physical designs, automatic database construction and Java application generation, accurate reverse-engineering of databases, and powerful HTML-based documentation and reporting facilities.

Блин. Не шибко информативная штука.
Насколько я понял, индекс у avt_new есть только по cod_mr. Вот и попробуй его поставить первым в условии. А желателен (для этого запроса) индекс по cod_mr+prin_amt+act+NZA.
Если этот запрос частый, то может и стОит подумать о создании такого индекса.
Так что с производительностью то запроса все таки?

Запрос не частый примерно 1-3 раза вдень, ну и фиг сним, потом еще посмотрю, может чето переделаю! Спасибо всем за помощь!!!

[48] rayrom © (27.07.05 17:06)
>(Все равно МуСКЛ рулез, а Оракл - Ракло)

2[59] rayrom © (28.07.05 12:17)
По тому, что ты уже раз 5 не ответил на вопрос о производительности я заключаю, что МуСкул занял таки подобающую позицию по отношению к Ораклу. 8-)

Итог, последний вариант запроса который я оставляю -
SELECT s.name, to_number(to_char(t.god_to,"YYYY")) yearto, count(gos_num) kolvo FROM avt_new a, tex_osm t, t_amt_spr s where prin_amt = 1 and act = 1 and cod_mr = "18" AND a.NZA = t.NZA AND t.key_nid = (SELECT MAX(key_nid) FROM TEX_OSM WHERE a.NZA = tex_osm.NZA) AND a.tip_amt = s.codename group by s.name, to_number(to_char(t.god_to,"YYYY"))
В таком варианте запрос выполняется на 10-20% быстрее!
А преобразование to_number(to_char(t.god_to,"YYYY")) для правильной сортировки.

Хотя конечно преобразование to_number(to_char(t.god_to,"YYYY")) yearto выглядит как через ж..у, в МуСкуле проще Year(t.god_to) as yearto.
В принципе у любой РБД есть свои плюсы и мынусы, толького у кого чего больше и их значимость решает использование а не маркетинговая политика, и если это будет так то реляционки будут впереди планеты всей ;)

> для правильной сортировки

А где она? И почему не оставить просто t.god_to? Клиент сам сможет отобразить только год. Дополнительная потеря времени, имхо.

> where prin_amt = 1 and act = 1 and cod_mr = "18"

Почему не используешь алиасы? Ведь кто потом будет разбираться, будет искать эти поля по всем таблицам и материть тебя по чем свет!

2 evvcom Ты наверно не понял сам смысл запроса!
Запрос для начальства, ему нужно видеть (в DesicionCube), состояние прохождения техосмотров по типу транспорта и в каком году в группе типа - скока автобусов прошло в этом году и сколько еще не прошло осталось т.е. предыдущие года!
А так как таблица техосмотров накопительная то и идет выборка (SELECT MAX(key_nid) FROM TEX_OSM WHERE a.NZA = tex_osm.NZA)
для получения последнего техосмотра по данному транспорту, а как выглядит запрос ему и нафиг не надо (начальству), главное результат!!!

2[61] rayrom © (29.07.05 09:17)
>Итог, последний вариант запроса который я оставляю -
>В таком варианте запрос выполняется на 10-20% быстрее!

Неужели to_number(to_char дало прирост в 10-20%? Это интересно. Других отличий от первого варианта я не нашел.

2 Sergey13
Как я понял скорее всего это зависит от последовательности условий в WHERE, думаю что, условия обрабатываются с лева на право, т.е. соответственно изначально пошло ограничение на колличесво отобранных записей для дальнейших условий и связываний! А то что стоит в SELECT уже выполняется после условий хотя х.з.

2[66] rayrom © (29.07.05 09:44)
А что с запросом из [51]? Там как было с шустростью? Просто интересно.

ЗЫ: Для просмотра статистики, кстати, в СКЛПлюсе можно набрать
set autotrace on
Будет и план и статистика.

rayrom © (29.07.05 09:44)
А преобразование to_number(to_char(t.god_to,"YYYY")) для правильной сортировки...I>
если только для сортировки то есть ещё trunc(in_date,"RRRR") возвращает дату
вроде в восьмерке уже был?

2 k2 Да есть, уже проходили, не работает ошибку выдает!

2 Sergey13 По поводу запроса [51], не работает пишет что неверное имя поля!

2 k2, Sergey13
Не знаю, я через SQL Explorer работаю, другого нет, говорит что неверное имя и все а какое х.е.з.

Попробуй через SQLPlus
и попробуй
group by s.name, to_char(tt.god_to,"YYYY")

Да нету у меня его, я через ОДБСишный драйвер работаю, я хотел Ораклы 9 купить (3 диска), дык пиратка тоже дорогая 15 у.е. для меня это накладно при зряплате в 110 у.е.
И скорее всего остановлюсь на последнем запросе [61]!

Необходимо сократить или оптимизировать запрос! Найти похожие ветки