SELECT To_char(Ch.PRINTTIME,'YYYY') AS Year, To_char(Ch.PRINTTIME,'MM') AS Month, Ch.Locid,
ChIt.ARTICLE, Sum(case when Ch.opcode=1 then ChIt.QUANTITY else -ChIt.QUANTITY end) AS Qty,
Sum(case when Ch.opcode=1 then ChIt.TOTALSUM else -ChIt.TOTALSUM end) AS TSum FROM
supermag.SMCASHCHECKS Ch INNER JOIN supermag.SMCASHCHECKITEMS ChIt ON (Ch.CHECKNUM =
ChIt.CHECKNUM) AND (Ch.ZNUM = ChIt.ZNUM) AND (Ch.DESKNUM = ChIt.DESKNUM) AND (Ch.LOCID =
ChIt.LOCID) WHERE Ch.PRINTTIME Between '01.07.2010' and '15.07.2010' GROUP BY
To_char(Ch.PRINTTIME,'YYYY'), To_char(Ch.PRINTTIME,'MM'), Ch.LOCID, ChIt.ARTICLE
/ Description Owner Object name Cost Cardinality Bytes CPU cost Time ------------------------------------------------------------------------------------------------------ SELECT STATEMENT, GOAL = ALL_ROWS 250427 1778733 96051582 109183971818 2842 HASH GROUP BY 250427 1778733 96051582 109183971818 2842 HASH JOIN 230353 1778733 96051582 107127754245 2615 TABLE ACCESS FULL SUPERMAG SMCASHCHECKS 20637 387304 9682600 12317785852 235 TABLE ACCESS FULL SUPERMAG SMCASHCHECKITEMS 100619 164687891 4775948839 58417235335 1142
---------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 729K| 29M| | 398K (1)| 00:12:09 | | 1 | HASH GROUP BY | | 729K| 29M| 44M| 398K (1)| 00:12:09 | | 2 | TABLE ACCESS BY INDEX ROWID | SMCASHCHECKITEMS | 4 | 92 | | 2 (0)| 00:00:01 | | 3 | NESTED LOOPS | | 729K| 29M| | 381K (1)| 00:11:38 | | 4 | TABLE ACCESS BY INDEX ROWID | SMCASHCHECKS | 187K| 3659K| | 4430 (2)| 00:00:09 | | 5 | BITMAP CONVERSION TO ROWIDS| | | | | | | |* 6 | BITMAP INDEX RANGE SCAN | SMCASHCHECKS_PRNTIM | | | | | | |* 7 | INDEX RANGE SCAN | SMCCASHCHECKITEMS_PK | 1 | | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------------------
SQL> select sum(bytes)/1024/1024/1024 "G" from dba_segments where segment_name='SMCASHCHECKITEMS'; G ---------- 2,39648438 SQL> select sum(bytes)/1024/1024/1024 "G" from dba_segments where segment_name='SMCASHCHECKS'; G ---------- ,670898438 select * from aux_stats$;
G
----------
7,9375
G
----------
1,6328125
SNAME PNAME PVAL1 PVAL2
------------------------------ ------------------------------ ---------- --------------------------------------------------------------------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 06-07-2010 16:19
SYSSTATS_INFO DSTOP 06-07-2010 16:19
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 1490,826
SYSSTATS_MAIN IOSEEKTIM 10,967
SYSSTATS_MAIN IOTFRSPEED 21509,362
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR
13 rows selected 0 SELECT STATEMENT Optimizer Mode=CHOOSE (Cost=6146 Card=73 Bytes=2 K) 1 0 SORT GROUP BY (Cost=6146 Card=73 Bytes=2 K) 2 1 TABLE ACCESS BY INDEX ROWID SUPERMAG.SMCASHCHECKITEMS (Cost=2 Card=11 Bytes=253) 3 2 NESTED LOOPS (Cost=6145 Card=73 Bytes=2 K) 4 3 TABLE ACCESS FULL SUPERMAG.SMCASHCHECKS (Cost=6143 Card=7 Bytes=133) 5 3 INDEX RANGE SCAN SUPERMAG.SMCCASHCHECKITEMS_PK (Cost=2 Card=1)