select sum(decode( d.doctype, 'CS', 1,-1)*s.totalprice), count( distinct d.rowid), count(s.rowid)
from smdocuments d
,smspec s
where d.doctype=s.doctype
and d.id=s.docid
and d.doctype in ('CS' ,'CR')
and d.createdat between '01.12.2007' and '30.12.2007'
------------------------------------------------------------
Statement Id=6 Type=INDEX
Cost=12 TimeStamp=29-12-07::07::49:11
(1) SELECT STATEMENT CHOOSE
Est. Rows: 1 Cost: 230
(8) SORT GROUP BY
Est. Rows: 1
(7) FILTER
(6) TABLE ACCESS BY INDEX ROWID SUPERMAG.SMSPEC [Analyzed]
(6) Blocks: 520*363 Est. Rows: 3 of 64*542*693 Cost: 1
Tablespace: USERS
(5) NESTED LOOPS
Est. Rows: 1*750 Cost: 230
(3) TABLE ACCESS BY INDEX ROWID SUPERMAG.SMDOCUMENTS [Analyzed]
(3) Blocks: 21*968 Est. Rows: 538 of 1*984*123 Cost: 69
Tablespace: USERS
(2) NON-UNIQUE INDEX RANGE SCAN SUPERMAG.SMDOCUMENTS_CREATEDAT [Analyzed]
Est. Rows: 5*381 Cost: 12
(4) UNIQUE INDEX RANGE SCAN SUPERMAG.SMCSPEC_PK [Analyzed]
Est. Rows: 1 Cost: 1 SQL Statement from editor:
SELECT SUM (DECODE (d.doctype, 'CS', 1, -1) * s.totalprice),
COUNT (DISTINCT d.ROWID), COUNT (s.ROWID)
FROM smdocuments d, smspec s
WHERE d.doctype = s.doctype
AND d.ID = s.docid
AND d.doctype IN ('CS', 'CR')
AND d.createdat BETWEEN TO_DATE ('01112007', 'DDMMYYYY')
AND TO_DATE ('30112007', 'DDMMYYYY')
------------------------------------------------------------
Statement Id=7 Type=INDEX
Cost=2 TimeStamp=29-12-07::10::26:33
(1) SELECT STATEMENT CHOOSE
Est. Rows: 1 Cost: 1 858
(8) SORT GROUP BY
Est. Rows: 1
(7) TABLE ACCESS BY LOCAL INDEX ROWID SUPERMAG.SMSPEC [Analyzed]
Blocks: 2 420 476 Est. Rows: 24 of 292 460 555 Cost: 2
(6) NESTED LOOPS
Est. Rows: 286 913 Cost: 1 858
(3) TABLE ACCESS BY INDEX ROWID SUPERMAG.SMDOCUMENTS [Analyzed]
(3) Blocks: 94 588 Est. Rows: 11 839 of 7 551 318 Cost: 1 146
Tablespace: USERS
(2) NON-UNIQUE INDEX RANGE SCAN SUPERMAG.SMDOCUMENTS_CREATEDAT [Analyzed]
Est. Rows: 587 325 Cost: 1 605
(5) PARTITION LIST INLIST
(4) UNIQUE INDEX RANGE SCAN SUPERMAG.SMCSPEC_PK [Analyzed]
Est. Rows: 2 Cost: 2 SQL> SELECT SUM (DECODE (d.doctype, 'CS', 1, -1) * s.totalprice),
2 COUNT (DISTINCT d.ROWID), COUNT (s.ROWID)
3 FROM smdocuments d, smspec s
4 WHERE d.doctype = s.doctype
5 AND d.ID = s.docid
6 AND d.doctype IN ('CS', 'CR')
7 AND d.createdat BETWEEN TO_DATE ('01112007', 'DDMMYYYY')
8 AND TO_DATE ('30112007', 'DDMMYYYY');
SUM(DECODE(D.DOCTYPE,'CS',1,-1)
*S.TOTALPRICE) COUNT(DISTINCTD.ROWID) COUNT(S.ROWID)
-------------- ----------------------------- -----------------
531796183 5787 7176119
Затрач.время: 00:01:57.09
План выполнения
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1858 Card=1 Bytes=61)
1 0 SORT (GROUP BY)
2 1 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'SMSPEC' (Cost=2 Card=24 Bytes=744)
3 2 NESTED LOOPS (Cost=1858 Card=286913 Bytes=17501693)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'SMDOCUMENTS' (Cost=1146 Card=11839 Bytes=355170)
5 4 INDEX (RANGE SCAN) OF 'SMDOCUMENTS_CREATEDAT' (NON-UNIQUE) (Cost=1605 Card=587325)
6 3 PARTITION LIST (INLIST)
7 6 INDEX (RANGE SCAN) OF 'SMCSPEC_PK' (UNIQUE) (Cost=2 Card=2) create table smspec_part
tablespace ffdata
PARTITION by list (doctype)
(
PARTITION PRT_AC VALUES ('AC'),
PARTITION PRT_AD VALUES ('AD'),
PARTITION PRT_BI VALUES ('BI'),
PARTITION PRT_CA VALUES ('CA'),
PARTITION PRT_CC VALUES ('CC'),
PARTITION PRT_CI VALUES ('CI'),
PARTITION PRT_CN VALUES ('CN'),
PARTITION PRT_CO VALUES ('CO'),
PARTITION PRT_CR VALUES ('CR'),
PARTITION PRT_CS VALUES ('CS'),
PARTITION PRT_EO VALUES ('EO'),
PARTITION PRT_FA VALUES ('FA'),
PARTITION PRT_GT VALUES ('GT'),
PARTITION PRT_IL VALUES ('IL'),
PARTITION PRT_IW VALUES ('IW'),
PARTITION PRT_LA VALUES ('LA'),
PARTITION PRT_MA VALUES ('MA'),
PARTITION PRT_ME VALUES ('ME'),
PARTITION PRT_OC VALUES ('OC'),
PARTITION PRT_OR VALUES ('OR'),
PARTITION PRT_PD VALUES ('PD'),
PARTITION PRT_PE VALUES ('PE'),
PARTITION PRT_PL VALUES ('PL'),
PARTITION PRT_PN VALUES ('PN'),
PARTITION PRT_PO VALUES ('PO'),
PARTITION PRT_RL VALUES ('RL'),
PARTITION PRT_RO VALUES ('RO'),
PARTITION PRT_RP VALUES ('RP'),
PARTITION PRT_SL VALUES ('SL'),
PARTITION PRT_SO VALUES ('SO'),
PARTITION PRT_SR VALUES ('SR'),
PARTITION PRT_WI VALUES ('WI'),
PARTITION PRT_WO VALUES ('WO')
)
as select * from smspec alter table smspec_part add constraint smspec_part_pk primary key (doctype, docid, specitem) using index tablespace ffdata
create index smspec_part_pid on smspec_part (doctype, docid) tablespace ffdata
-------------------------------------------------------------------------------- | Id | Operation | Name | Rows | B -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 | SORT GROUP BY | | 1 | | 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| SMSPEC_PART | 4 | | 3 | NESTED LOOPS | | 9333 | | 4 | TABLE ACCESS BY INDEX ROWID | SMDOCUMENTS | 2491 | 7 | 5 | INDEX RANGE SCAN | SMDOCUMENTS_CREATEDAT | 18681 | | 6 | INDEX RANGE SCAN | SMSPEC_PART_PID | 1 | --------------------------------------------------------------------------------