Цитата: SQL Statement from editor:
select article, clientindex,
CASE when (co=1) then paymentdelay else paymentdelay_cli END "paymentdelay",
CASE when (co=1) then flags else flags_cli END "flags",
CASE when (co=1) then kaldays else kaldays_cli END "kaldays"
from (
select article, clientindex, sum(paymentdelay) paymentdelay, sum(flags) flags, max(kaldays) kaldays, sum(co) co, sum(paymentdelay_cli) paymentdelay_cli, sum(flags_cli) flags_cli, max(kaldays_cli) kaldays_cli
from ( select dd1.article, dd1.clientindex, dd3.paymentdelay, decode(bitand(DD3.flags,1),1,0,1) flags, NVL(dd4.kaldays,0) kaldays, 1 as co,
0 as paymentdelay_cli, 0 as flags_cli, 0 as kaldays_cli
from (select distinct d1.article, d2.clientindex
from smspec d1, smdocuments d2
where d1.doctype=d2.doctype
and d1.docid=d2.id
and d2.docstate=3
and d2.doctype='WI'
and d2.opcode=0
and d2.createdat > TO_DATE('31.12.2008','DD.MM.YYYY')) dd1,
(
select d3.article, d4.clientindex, max(d3.docid) id, max(d4.createdat) createdat
from smspec d3, smdocuments d4
where d3.doctype=d4.doctype
and d4.doctype='CO'
and d4.createdat > TO_DATE('31.12.2008','DD.MM.YYYY')
and d4.docstate=2
and d3.docid=d4.id
group by d3.article, d4.clientindex
) dd2, smcontracts dd3, vito_workdays2 dd4
where dd1.article=dd2.article
and dd1.clientindex=dd2.clientindex
and dd2.id=dd3.id
and dd3.doctype='CO'
and dd3.paymentdelay=dd4.bankdays(+)
UNION ALL
select dd1.article, dd1.clientindex, 0 as paymentdelay, 0 as flags, 0 as kaldays, 0 as co,
dd4.paymentdelay paymentdelay_cli, decode(bitand(DD4.flags,1),1,0,1) flags_cli, NVL(dd5.kaldays,0) kaldays_cli
from (select distinct d1.article, d2.clientindex
from smspec d1, smdocuments d2
where d1.doctype=d2.doctype
and d1.docid=d2.id
and d2.doctype='WI'
and d2.docstate=3
and d2.opcode=0
and d2.createdat > TO_DATE('31.12.2008','DD.MM.YYYY')) dd1, SMSUPPLIERS dd4, vito_workdays2 dd5
where dd1.clientindex=dd4.id and dd4.paymentdelay=dd5.bankdays(+))
group by article, clientindex)
------------------------------------------------------------
Statement Id=88770984 Type=
Cost=2,0960068036193E-317 TimeStamp=02-09-09::17::11:44
(1) SELECT STATEMENT ALL_ROWS
Est. Rows: 104351 Cost: 56218
(38) HASH GROUP BY
Est. Rows: 104351 Cost: 56218
(37) VIEW (Embedded SQL)
Est. Rows: 104351 Cost: 56204
(36) UNION-ALL
(24) NESTED LOOPS OUTER
Est. Rows: 1 Cost: 32154
(21) NESTED LOOPS
Est. Rows: 1 Cost: 32153
(18) MERGE JOIN
Est. Rows: 1 Cost: 32152
(9) SORT JOIN
Est. Rows: 104350 Cost: 24040
(8) VIEW (Embedded SQL)
Est. Rows: 104350 Cost: 24040
(7) HASH UNIQUE
Est. Rows: 104350 Cost: 24040
(6) HASH JOIN
Est. Rows: 104350 Cost: 22797
(3) PARTITION LIST SINGLE
Est. Rows: 9318 Cost: 2447
(2) TABLE TABLE ACCESS FULL SUPERMAG.SMDOCUMENTS [Analyzed]
Blocks: 23750 Est. Rows: 9318 of 1967392 Cost: 2447
(5) PARTITION LIST SINGLE
Est. Rows: 9166647 Cost: 20190
(4) TABLE TABLE ACCESS FULL SUPERMAG.SMSPEC [Analyzed]
Blocks: 603330 Est. Rows: 9166647 of 68030600 Cost: 20190
(17) SORT JOIN
Est. Rows: 1321 Cost: 8112
(16) VIEW (Embedded SQL)
Est. Rows: 1321 Cost: 8111
(15) HASH GROUP BY
Est. Rows: 1321 Cost: 8111
(14) TABLE TABLE ACCESS BY GLOBAL INDEX ROWID SUPERMAG.SMSPEC [Analyzed]
Blocks: 603330 Est. Rows: 1 of 68030600 Cost: 4
(13) NESTED LOOPS
Est. Rows: 1321 Cost: 8110
(11) PARTITION LIST SINGLE
Est. Rows: 1287 Cost: 3180
(10) TABLE TABLE ACCESS FULL SUPERMAG.SMDOCUMENTS [Analyzed]
Blocks: 23750 Est. Rows: 1287 of 1967392 Cost: 3180
(12) INDEX INDEX RANGE SCAN SUPERMAG.SMCSPEC_DISPLAYPOS [Analyzed]
Est. Rows: 1 Cost: 3
(20) TABLE TABLE ACCESS BY INDEX ROWID SUPERMAG.SMCONTRACTS [Analyzed]
(20) Blocks: 35 Est. Rows: 1 of 1815 Cost: 1
Tablespace: USERS
(19) INDEX (UNIQUE) INDEX UNIQUE SCAN SUPERMAG.SMCCONTRACTS_PK [Analyzed]
Est. Rows: 1
(23) TABLE TABLE ACCESS BY INDEX ROWID SUPERMAG.VITO_WORKDAYS2 [Not Analyzed]
(23) Est. Rows: 1 Cost: 1
Tablespace: USERS
(22) INDEX (UNIQUE) INDEX UNIQUE SCAN SUPERMAG.SYS_C0047141 [Not Analyzed]
Est. Rows: 1
(35) HASH JOIN
Est. Rows: 104350 Cost: 24050
(27) HASH JOIN RIGHT OUTER
Est. Rows: 863 Cost: 8
(25) TABLE TABLE ACCESS FULL SUPERMAG.VITO_WORKDAYS2 [Not Analyzed]
(25) Est. Rows: 1 Cost: 2
Tablespace: USERS
(26) TABLE TABLE ACCESS FULL SUPERMAG.SMSUPPLIERS [Analyzed]
(26) Blocks: 13 Est. Rows: 863 of 863 Cost: 5
Tablespace: USERS
(34) VIEW (Embedded SQL)
Est. Rows: 104350 Cost: 24040
(33) HASH UNIQUE
Est. Rows: 104350 Cost: 24040
(32) HASH JOIN
Est. Rows: 104350 Cost: 22797
(29) PARTITION LIST SINGLE
Est. Rows: 9318 Cost: 2447
(28) TABLE TABLE ACCESS FULL SUPERMAG.SMDOCUMENTS [Analyzed]
Blocks: 23750 Est. Rows: 9318 of 1967392 Cost: 2447
(31) PARTITION LIST SINGLE
Est. Rows: 9166647 Cost: 20190
(30) TABLE TABLE ACCESS FULL SUPERMAG.SMSPEC [Analyzed]
Blocks: 603330 Est. Rows: 9166647 of 68030600 Cost: 20190