А Ораклоиды здесь есть?

← →
Рваный Башмак (2008-05-10 19:52) [0]

Таблица движения товара
CREATE TABLE wh_goods (dtsave DATE, id_sys_operations NUMBER, id_goods NUMBER, nvalue NUMBER NOT NULL, nsum NUMBER(10,2), id_doc NUMBER NOT NULL, id_discountsreasons NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLESPACE system STORAGE ( INITIAL 458752 MINEXTENTS 1 MAXEXTENTS 2147483645 ) NOCACHE MONITORING / -- Indexes for WH_GOODS CREATE INDEX i_wh_goods ON wh_goods ( id_goods ASC, dtsave ASC ) PCTFREE 10 INITRANS 2 MAXTRANS 255 TABLESPACE system STORAGE ( INITIAL 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 ) / -- Foreign Key ALTER TABLE wh_goods ADD CONSTRAINT fk_wh_goods_goods FOREIGN KEY (id_goods) REFERENCES goods (id) ON DELETE CASCADE /

Функция для получения остатков на дату:
CREATE OR REPLACE FUNCTION get_goods_rest (pid_goods IN NUMBER, pdt IN DATE) RETURN NUMBER IS aresult NUMBER; BEGIN SELECT NVL (SUM (nvalue), 0) INTO aresult FROM wh_goods WHERE id_goods = pid_goods AND dtsave <= pdt; RETURN aresult; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN 0; END; /

16 тыс. позиций в номенклатуре, запрос на получение остатков работает не так уж и быстро.

Возможно ли это соптимизировать каким-либо образом? Переписать функцию получения остатков или неправильное проектирование?

← →
Loginov Dmitry © (2008-05-10 22:09) [1]

Для каждого из 16 тыс. товаров отдельно пересчитывать остаток - жжесть!

← →
Loginov Dmitry © (2008-05-10 22:26) [2]

Если для каждого из 16 тыс товаров нужно получить остаток, то может что-то типа этого:

SELECT g.id_goods, SUM(g.nvalue), SUM(g.nvalue * g.nsum) FROM wh_goods g WHERE g.dtsave <= pdt GROUP BY g.id_goods

Но в любом случае непонятно, где же тут запрос на получение остатков? Где учитывается вид движения (приход/расход)?

← →
Рваный Башмак (2008-05-11 03:54) [3]

[1] А какие есть варианты?
[2] В зависимости от ID_SYS_OPERATIONS в поле NVALUE будет положительная или отрицательная величина

← →
sniknik © (2008-05-11 09:37) [4]

> [1] А какие есть варианты?
после [2] это наверное шутка? ха-ха. смешно.

> [2] В зависимости от ID_SYS_OPERATIONS в поле NVALUE будет положительная или отрицательная величина
не верю, что в оракле нет аналога access-ового iif/mssql-ного case, для условия по которому можно "добавить" минус к NVALUE.

кстати чегото не вижу в функции условия по ID_SYS_OPERATIONS... посему тоже недоумение
> Где учитывается вид движения (приход/расход)?
или оно учитывается "сверху" в запросе типа get_goods_rest для плюсовых - get_goods_rest для отрицательных? тогда для каждого товара пересчитывается отдельно по 2 раза, а не
> Для каждого из 16 тыс. товаров отдельно пересчитывать остаток - жжесть!
двойная жжесть!!
:)))

← →
Рваный Башмак (2008-05-11 10:02) [5]

> после [2] это наверное шутка? ха-ха. смешно.

Нет, просто сначала не прочел, а потом не удалил. Не принимайте во внимание

> не верю, что в оракле нет аналога access-ового iif/mssql-
> ного case, для условия по которому можно "добавить" минус
> к NVALUE.

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

> или оно учитывается "сверху" в запросе типа get_goods_rest
> для плюсовых - get_goods_rest для отрицательных?

Чего?

← →
sniknik © (2008-05-11 10:24) [6]

> Таким образом SUM(nvalue) даст суммирование движений, т.е. остатки
так в nvalue отрицательные значения (часть), вне зависимости от ID_SYS_OPERATIONS?.... так чего же ты им мозг "париш", если оно несущественно (т.е. пункт 2 в [3] можно откинуть. пункт 1 изза непрочтения -> [2] = 100% ответ).

> Чего?
покажи вызов где используется get_goods_rest с учетом ID_SYS_OPERATIONS. (внутри функции его нет, значит должен быть "сверху" вне ее)

← →
Рваный Башмак (2008-05-11 11:17) [7]

DTSAVE | ID_SYS_OPERATIONS | ID_GOODS | NVALUE | NSUM | ID_DOC | ID_DISCOUNTREASONS 09-май-2008 11:58:49 | 2 | 12968 | 5 | 12,5 | 1 | NULL 10-май-2008 10:06:19 | 1 | 12968 | -1 | -1620 | 3 | 148

ID_SYS_OPERATIONS - Код операции (в данном случае "1" - это реализация, "2" - это поступление).
Для вычисления остатков он не важен, используется он в случае, когда например необходимо учесть только Объем Возвратов (при этому не учитывая Поступления)

Конкретно, в двух записях WH_GOODS говорится:
- Пришел товар в размере 5 шт. на сумму 12,5 условных единиц (рубли, доллары, евро, йены, тугрики - в данный момент нас не волнуют)
- Ушел товар в размере 1 шт. на сумму 1620 условных единиц (опять же в контексте вопроса топика валюта нас не интересует)

Т.о., на остатке 4 шт.

SELECT get_goods_rest(12968, SYSDATE) FROM dual

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

SELECT a.name, get_goods_rest(a.id, SYSDATE) FROM goods_view a

> покажи вызов где используется get_goods_rest с учетом ID_SYS_OPERATIONS.
> (внутри функции его нет, значит должен быть "сверху" вне
> ее)

Не используется GET_GOODS_REST с учетом ID_SYS_OPERATIONS
ID_SYS_OPERATIONS, как уже говорилось, используется в других целях.
Например, нас интересует объем возвратов (ID_SYS_OPERATIONS = 3) за какой-то период:

SELECT SUM(ncount) FROM wh_goods WHERE id_sys_operations = 3 AND TRUNC(dtsave) BETWEEN ... AND ...

← →
Loginov Dmitry © (2008-05-11 11:25) [8]

> Если необходимо получить остатки на складе, выполняется
> запрос типа
>
> SELECT a.name, get_goods_rest(a.id, SYSDATE) FROM goods_view
> a

тройная жжесть! :)

← →
Рваный Башмак (2008-05-11 11:31) [9]

Т.е. если сделать JOIN с
SELECT g.id_goods, SUM(g.nvalue), SUM(g.nvalue * g.nsum) FROM wh_goods g WHERE g.dtsave <= pdt GROUP BY g.id_goods

это намного будет быстрее?

← →
Loginov Dmitry © (2008-05-11 11:40) [10]

Так должно быть намного быстрее:

SELECT gv.name, gv.id_goods, SUM(wh.nvalue), SUM(wh.nvalue * wh.nsum) FROM wh_goods wh {LEFT} JOIN goods_view gv ON gv.id_goods = wh.id_goods GROUP BY gv.name, gv.id_goods

После JOIN - табличка с заведомо меньшим числом записей (справочник).
WHERE отпадает.

Попробуй с LEFT и без него. Логически - разницы нет. По производительности - может быть.

← →
sniknik © (2008-05-11 11:41) [11]

> это намного будет быстрее?
странный вопрос. проверь, узнаешь.

← →
sniknik © (2008-05-11 11:44) [12]

Loginov Dmitry © (11.05.08 11:40) [10]
лучше, имхо, обьеденять таблицу имен с результатом (подзапросом с показанной уже группировкой).

← →
Рваный Башмак (2008-05-11 11:52) [13]

[11] Обязательно попробую, пока собираю информацию
Спасибо обоим за помощь. Появятся еще умные мысли - пишите :)))

← →
Loginov Dmitry © (2008-05-11 12:03) [14]

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

Кстати да. Не учел, что не все товары обязаны быть в wh_goods, а вывести остатки нужно по всем...

← →
sniknik © (2008-05-11 12:55) [15]

> Появятся еще умные мысли - пишите :)))
у меня мысли идут сплошным потоком, сплошь умные (по ночам гениальные, как у Менделеева). все писать не могу, руки "отвалятся", присылай секретаршу записывать...

← →
Рваный Башмак (2008-05-11 13:30) [16]

> по ночам

> присылай секретаршу записывать...

Это уже не секретарша получается :))))

← →
ZeroDivide © (2008-05-12 02:03) [17]

Чета какой-то пипец... вообще...

1. Я как увижу таблицу без поля ID, мне сразу становится хреновато... ну или хотя бы, для таблицы wh_goods оно должно быть wh_goods_id, ну или на крайняк id_wh_goods

2. Delete cascade меня тоже вгоняет в краску....

3.
CREATE INDEX i_wh_goods ON wh_goods
(
id_goods ASC,
dtsave ASC
)

Это тоже какая-то задница, FK c ключем по дате в одном составном индексе... подскажите, кто вас этому научил?

4. SELECT NVL (SUM (nvalue), 0) честно говоря, даже я, несмотря на то, что постоянно работаю с Oracle... переодически проверяю как он работает с null"ами... стоило бы все таки написать здесь: SUM(NVL(nvalue, 0))

5.
Если необходимо получить остатки на складе, выполняется запрос типа
SELECT a.name, get_goods_rest(a.id, SYSDATE) FROM goods_view a

Вызов PL/SQL из SQL в виде поля - всегда тормоз. Машина SQL и машина PL/SQL в Oracle - совершенно разные вещи. Переключение в машину PL/SQL из SQL обычно вызывает грандиозные тормоза... тем более для вашего, простейшего запроса.

Пишите просто:

SELECT a.name,
(SELECT SUM(NVL(nvalue, 0))
INTO aresult
FROM wh_goods
WHERE id_goods = a.id AND dtsave <= :P_DATE) result_value
FROM goods_view a

Индекс по дате и индекс по внешнему ключу сделайте отдельно. Все должно выполниться моментально.

← →
Германн © (2008-05-12 02:15) [18]

> sniknik © (11.05.08 12:55) [15]
>
> > Появятся еще умные мысли - пишите :)))
> у меня мысли идут сплошным потоком, сплошь умные (по ночам
> гениальные, как у Менделеева). все писать не могу, руки
> "отвалятся", присылай секретаршу записывать...
>

Блин! И сколько потеряно гениальных мыслей из-за отсутствия "ночной секретарши"! :)

← →
Рваный Башмак (2008-05-12 11:31) [19]

[17] Сразу скажу спасибо за весь ваш пост
1. А зачем для этой таблицы ID ? Плюс лишний сиквенс и лишний триггер (опционально)
2. Вот здесь вы правы, я поторопился
3. Эээ... Никто не учил. Самоучка я. Вот такой вот хреновый самоучка :(
4. И здесь вы правы. Однако в свое оправдание скажу, что именно так и хотел написать, да перепутал местами, а затем пропустил мимо. Грешен я...
5. А вот за эту строчку "Вызов PL/SQL из SQL в виде поля - всегда тормоз. Машина SQL и машина PL/SQL в Oracle - совершенно разные вещи. Переключение в машину PL/SQL из SQL обычно вызывает грандиозные тормоза... тем более для вашего, простейшего запроса" огромное спасибо. Буду теперь знать

> Индекс по дате и индекс по внешнему ключу сделайте отдельно.
> Все должно выполниться моментально.

Хорошо. Попробую.
Еще раз спасибо

← →
Игорь Шевченко © (2008-05-12 12:48) [20]

> Вызов PL/SQL из SQL в виде поля - всегда тормоз. Машина
> SQL и машина PL/SQL в Oracle - совершенно разные вещи. Переключение
> в машину PL/SQL из SQL обычно вызывает грандиозные тормоза.
> ..

Не всегда. Оптимизатор не совсем дураки писали

← →
Рваный Башмак (2008-05-12 15:35) [21]

[20] Было бы интересно услышать ваше мнение по топику

← →
Сергей М. © (2008-05-12 16:41) [22]

> Появятся еще умные мысли - пишите

> Код операции (в данном случае "1" - это реализация, "2"
> - это поступление)

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

2. Возвращенный В.Пупкиным товар необходимо вернуть поставщику.

Что делать, если НЕТ таких операций, а отразить в движении эти факты нужно обязательно, если все это делается "белым" образом ?

Рваный Башмак (12.05.08 15:35) [21]

Первое мнение по топику - это то, что агрегатная функция не возбуждает исключения NO_DATA_FOUND, следовательно блок EXCEPTION можно убрать.

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

← →
Рваный Башмак (2008-05-13 01:48) [24]

[22] Данная ситуация оформляется возвратом товара от покупателя и списанием товара (брак). Эти операции существуют. Возврата товара поставщику не может быть, но кто мешает завести возврат поставщику как новый вид операции?

[23] Где используется функция:
- в справочнике номенклатуры необходимо видеть актуальные остатки (пожелание заказчика)
- в таблицах товаров некоторых документов
- отчет по остаткам на складе
- отчет по движениям на складе за период (остаток на начало периода, приход, расход, остаток на конец периода)
- при проведении складских расходных документов (проверка остатков со списываемым количеством)

Собственно данный подход заметно тормозит при выводе справочника. При небольшом количестве движений.

> Эти операции существуют

Ну тогда я спокоен)

> - в справочнике номенклатуры необходимо видеть актуальные
> остатки (пожелание заказчика)

Как я понимаю, заказчик одновременно не видит 16 тыс. записей - феномены с такими глазами еще не родились. Я бы сделал вычисляемое поле, которое по необходимости заполнял бы запросом. Почему не хотелось бы использовать отдельный запрос с группировкой в данном случае - потому что я не уверен в незыблемости структуры данных, и потому что я не уверен, что всегда хочется видеть остатки только на текущую дату. Впрочем, всегда есть два пути - пользовать быстрый запрос, который придется менять в дополнение к самой функции при каких-либо изменениях или сложнее попрограммировать, но иметь во-первых одно место для изменений, во-вторых, чуть большую гибкость.

← →
Рваный Башмак (2008-05-13 10:05) [27]

[25] Хоть где то хорошо :)

← →
Рваный Башмак (2008-05-13 10:17) [28]

[26] По поводу
> Впрочем, всегда есть два пути - пользовать быстрый запрос,
> который придется менять в дополнение к самой функции при
> каких-либо изменениях или сложнее попрограммировать, но
> иметь во-первых одно место для изменений, во-вторых, чуть
> большую гибкость.

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

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

Да, вы абсолютно правы, заказчик не видит одновременно 16 тыс. записей. Однако этот справочник - основной в системе, и с ним ведется очень активная работа. Частичная загрузка и постепенная подгрузка записей, например, при скроллинге не устроят. Скажу сразу, остатки в данном случае (если брать только справочник номенклатуры) интересуют только актуальные, т.е. на текущую дату. Вычисляемое поле с заполнением... Хм, а может быть и мысль. Только в какой момент делать вычисление поля? При показе строки? А будет ли такой подход быстрей загрузки все за один раз?

> Только в какой момент делать вычисление поля? При показе
> строки? А будет ли такой подход быстрей загрузки все за
> один раз?

Практика - критерий истины

← →
Рваный Башмак (2008-05-13 10:54) [30]

[29] Да уж, с этим сложно спорить

> - в справочнике номенклатуры необходимо видеть актуальные
> остатки (пожелание заказчика)

Почему бы не добавить в справочник номенклатуры поле "Остаток" и не корректировать его после каждой операции (приход/возврат/внутренний приход/внутренний расход/продажа/возврат от покупателя/списание/оприходование)

При таком подходе все просто летает!

А Ораклоиды здесь есть? Найти похожие ветки