В одном из магазинов начали долго (мин. 15) вычисляться остатки в сличительной ведомости. Начал разбираться. Так вот в том магазине где заполняются долго отрабатывает такой запрос:
Код:
INSERT /*+ APPEND */ INTO ttremains
(storeloc, article, quantity)
(SELECT /*+ ORDERED USE_NL (s) FULL (d) INDEX (s) */ l.id location,
s.article, sum(s.quantity * decode(l.id, d.locationto, 1,
d.locationfrom, -1, 0)) quantity
FROM smdocuments d, smstorelocations l, smspec s
WHERE d.doctype = s.doctype
AND d.id = s.docid
AND l.id IN (d.locationto, d.locationfrom)
AND d.docstate >= 2
AND d.createdat <= :i_date
AND l.id = 18
AND s.article IN (SELECT article
FROM smspec
WHERE doctype = 'RL'
AND docid = 'СВур000069')
GROUP BY l.id, s.article
HAVING sum(s.quantity * decode(l.id, d.locationto, 1, d.locationfrom,
-1, 0)) <> 0) В других магазин вычисляются немного другим запросом:
Код:
INSERT /*+ APPEND */ INTO ttremains
(storeloc, article, quantity)
(SELECT /*+ ORDERED USE_NL (s) FULL (d) INDEX (s) */ l.id location,
s.article, sum(s.quantity * decode(l.id, d.locationto, 1,
d.locationfrom, -1, 0)) quantity
FROM smdocuments d, smstorelocations l, smspec s
WHERE d.doctype = s.doctype
AND d.id = s.docid
AND l.id IN (d.locationto, d.locationfrom)
AND d.docstate >= 2
AND d.createdat <= :i_date
AND l.id = 18
AND s.article IN (SELECT article
FROM smspec
WHERE doctype = 'RL'
AND docid = 'СВур000069')
GROUP BY l.id, s.article
HAVING sum(s.quantity * decode(l.id, d.locationto, 1, d.locationfrom,
-1, 0)) <> 0) Все перепробовал и остатки рассчитать, и статистику, и все параметры сверил с другими базами, так и не понял почему разница пошла. может было у кого?
Версия СМ2000 1.23.5 Oracle 9i
Добавлено через 9 минут 44 секунды
Не тот запрос вставил вместо этого
Цитата: Vlad ➤ Код:
INSERT /*+ APPEND */ INTO ttremains
(storeloc, article, quantity)
(SELECT /*+ ORDERED USE_NL (s) FULL (d) INDEX (s) */ l.id location,
s.article, sum(s.quantity * decode(l.id, d.locationto, 1,
d.locationfrom, -1, 0)) quantity
FROM smdocuments d, smstorelocations l, smspec s
WHERE d.doctype = s.doctype
AND d.id = s.docid
AND l.id IN (d.locationto, d.locationfrom)
AND d.docstate >= 2
AND d.createdat <= :i_date
AND l.id = 18
AND s.article IN (SELECT article
FROM smspec
WHERE doctype = 'RL'
AND docid = 'СВур000069')
GROUP BY l.id, s.article
HAVING sum(s.quantity * decode(l.id, d.locationto, 1, d.locationfrom,
-1, 0)) <> 0) Вот этот
Код:
INSERT /*+ APPEND */ INTO ttremains
(storeloc, article, quantity)
(SELECT location, article, -sum(quantity) quantity
FROM (SELECT /*+ ORDERED USE_NL (s) INDEX (d smdocuments_createdat)
INDEX (s) */ l.id location, s.article, sum(s.quantity
* decode(l.id, d.locationto, 1, d.locationfrom, -1, 0))
quantity
FROM smdocuments d, smstorelocations l, smspec s
WHERE d.doctype = s.doctype
AND d.id = s.docid
AND l.id IN (d.locationto, d.locationfrom)
AND d.docstate >= 2
AND d.createdat > :i_date
AND l.id = 18
AND s.article IN (SELECT article
FROM smspec
WHERE doctype = 'RL'
AND docid = 'СВц001928')
GROUP BY l.id, s.article
HAVING sum(s.quantity * decode(l.id, d.locationto, 1,
d.locationfrom, -1, 0)) <> 0
UNION ALL
SELECT storeloc, article, -quantity
FROM smgoods
WHERE quantity <> 0
AND storeloc = 18
AND article IN (SELECT article
FROM smspec
WHERE doctype = 'RL'
AND docid = 'СВц001928'))
GROUP BY location, article
HAVING sum(quantity) <> 0)