26.03.2008 12:53
Sullen
 
Олег! Причину-то откопал, или как?
Может поделишься (соображениями)?
26.03.2008 13:21
OlegON
 
Хм... Что-то давно было, уже не помню :) Но сейчас (аптайм базы около недели из-за вырубания электричества в офисе)
Цитата:
SQL Statement from editor:


INSERT INTO supermag.tttovreport
(doctype, opcode, useropcode, paycash, taxgroupid, taxid, taxsum)
SELECT m.saletype, m.saleop, NVL (m.saleuserop, -:"SYS_B_00"),
m.salepaycash, a.taxgroupid, t.taxid,
SUM (DECODE (m.saleq,
:"SYS_B_01", :"SYS_B_02",
t.taxsum * m.quantity / m.saleq
)
)
FROM supermag.fvmaprep m, supermag.smcardtax a, supermag.smspectax t
WHERE TO_DATE (:"SYS_B_03", :"SYS_B_04") BETWEEN a.datefrom AND a.dateto
AND a.article = m.article
AND NVL (m.salelocationfrom, m.salelocationto) = :"SYS_B_05"
AND m.saledate BETWEEN TO_DATE (:"SYS_B_06", :"SYS_B_07")
AND TO_DATE (:"SYS_B_08", :"SYS_B_09")
AND m.saletype IN (:"SYS_B_10", :"SYS_B_11")
AND m.saleop IN (:"SYS_B_12", :"SYS_B_13")
AND t.doctype = m.saletype
AND t.docid = m.saleid
AND t.specitem = m.salespecitem
GROUP BY m.saletype,
m.saleop,
NVL (m.saleuserop, -:"SYS_B_00"),
m.salepaycash,
a.taxgroupid,
t.taxid
------------------------------------------------------------

Statement Id=25 Type=BITMAP INDEX
Cost=0 TimeStamp=26-03-08::13::19:08

(1) INSERT STATEMENT CHOOSE
Est. Rows: 1 Cost: 41
(29) SORT GROUP BY
Est. Rows: 1 Cost: 41
(28) FILTER
(27) TABLE ACCESS BY INDEX ROWID SUPERMAG.SMSPECTAX [Analyzed]
(27) Blocks: 24 864 Est. Rows: 1 of 20 816 476 Cost: 1
Tablespace: BIG_USERS
(26) NESTED LOOPS
Est. Rows: 1 Cost: 30
(24) NESTED LOOPS
Est. Rows: 1 Cost: 29
(21) VIEW (Embedded SQL)
Est. Rows: 1 Cost: 27
(20) UNION-ALL PARTITION
(10) FILTER
(9) TABLE ACCESS BY INDEX ROWID SUPERMAG.FFMAPREP [Analyzed]
(9) Blocks: 62 073 Est. Rows: 23 of 12 795 877 Cost: 52
Tablespace: FF_ANAL
(8) BITMAP CONVERSION TO ROWIDS
(7) BITMAP AND
(3) BITMAP MERGE
(2) BITMAP INDEX RANGE SCAN SUPERMAG.FFMAPREP_SALEDATE
(6) BITMAP OR
(4) BITMAP INDEX SINGLE VALUE SUPERMAG.FFMAPREP_SALETYPE
(5) BITMAP INDEX SINGLE VALUE SUPERMAG.FFMAPREP_SALETYPE
(19) FILTER
(18) TABLE ACCESS BY INDEX ROWID SUPERMAG.FFMAPREP_ [Analyzed]
(18) Blocks: 36 895 Est. Rows: 14 of 7 649 669 Cost: 33
Tablespace: FF_ANAL
(17) BITMAP CONVERSION TO ROWIDS
(16) BITMAP AND
(12) BITMAP MERGE
(11) BITMAP INDEX RANGE SCAN SUPERMAG.FFMAPREP_SALEDATE_
(15) BITMAP OR
(13) BITMAP INDEX SINGLE VALUE SUPERMAG.FFMAPREP_SALETYPE_
(14) BITMAP INDEX SINGLE VALUE SUPERMAG.FFMAPREP_SALETYPE_
(23) TABLE ACCESS BY INDEX ROWID SUPERMAG.SMCARDTAX [Analyzed]
(23) Blocks: 184 Est. Rows: 1 of 37 606 Cost: 1
Tablespace: USERS
(22) UNIQUE INDEX RANGE SCAN SUPERMAG.SMCCARDTAX_PK [Analyzed]
Est. Rows: 1 Cost: 1
(25) UNIQUE INDEX RANGE SCAN SUPERMAG.SMCSPECTAX_PK [Analyzed]
Est. Rows: 1 Cost: 1
28.03.2008 05:43
Sullen
 
Спасибо. Хотя и не совсем понятно, но думаю - разберусь...
22.07.2013 15:13
baggio
 
во блин... те же грабли на 9.2.0.8
после переезда на 1.29.3

:( и статистику собрал...
и индексы перестроил...

а гад всё одно...
22 INSERT STATEMENT
21 SORT [GROUP BY]
20 HASH JOIN
5 HASH JOIN
3 . VIEW
2 SORT [GROUP BY]
1 SUPERMAG.TTCCRDTAX_PK INDEX [FAST FULL SCAN]
4 SUPERMAG.TTCRDTAX TABLE ACCESS [FULL]
19 . VIEW
18 UNION-ALL
10 . VIEW
9 UNION-ALL
6 SUPERMAG.FFMAPREP TABLE ACCESS [FULL]
8 SUPERMAG.FFMAPREP_ TABLE ACCESS [BY INDEX ROWID]
7 SUPERMAG.FFMAPREP_SALEDATE_ INDEX [RANGE SCAN]
17 SUPERMAG.SMSPEC TABLE ACCESS [BY INDEX ROWID]
16 NESTED LOOPS
14 NESTED LOOPS
11 SUPERMAG.SMCBOOKKEEPINGREF_PK INDEX [RANGE SCAN]
13 SUPERMAG.SMDOCUMENTS TABLE ACCESS [BY INDEX ROWID]
12 SUPERMAG.SMCDOCUMENTS_PK INDEX [UNIQUE SCAN]
15 SUPERMAG.SMCSPEC_DISPLAYPOS INDEX [RANGE SCAN]
Часовой пояс GMT +3, время: 04:44.

Форум на базе vBulletin®
Copyright © Jelsoft Enterprises Ltd.
В случае заимствования информации гипертекстовая индексируемая ссылка на Форум обязательна.