сейчас пробую снова.
raid intel SRCS28X .... 2 scsi винта в зеркале на 10 000 rpm
Код:
SQL Statement:
SELECT sum(quantity) AS cnt, article, type_oper
FROM (((((((SELECT b.quantity, b.article, 1 AS type_oper
FROM smdocuments a, smspec b
WHERE a.createdat BETWEEN '03.06.2013' AND '03.06.2013'
AND a.doctype = 'CS'
AND a.id = b.docid
AND a.doctype = b.doctype
AND a.docstate > 1
AND a.locationfrom IN (SELECT id
FROM smstorelocations)
UNION ALL
SELECT -b.quantity, b.article, 1 AS type_oper
FROM smdocuments a, smspec b
WHERE a.createdat BETWEEN '03.06.2013' AND '03.06.2013'
AND a.doctype = 'CR'
AND a.id = b.docid
AND a.doctype = b.doctype
AND a.docstate > 1
AND a.locationto IN (SELECT id
FROM smstorelocations))
UNION ALL
SELECT b.quantity, b.article, 2 AS type_oper
FROM smdocuments a, smspec b
WHERE a.createdat BETWEEN '03.06.2013' AND '03.06.2013'
AND a.doctype IN ('WI', 'IW')
AND a.id = b.docid
AND a.doctype = b.doctype
AND a.docstate > 1
AND a.locationto IN (SELECT id
FROM smstorelocations))
UNION ALL
SELECT b.quantity, b.article, 3 AS type_oper
FROM smdocuments a, smspec b
WHERE a.createdat BETWEEN '03.06.2013' AND '03.06.2013'
AND a.doctype IN ('WO', 'IW')
AND a.id = b.docid
AND a.doctype = b.doctype
AND a.docstate > 1
AND a.locationfrom IN (SELECT id
FROM smstorelocations))
UNION ALL
SELECT quantity, article, 0 AS type_oper
FROM smgoods
WHERE storeloc IN (SELECT id
FROM smstorelocations))
UNION ALL
SELECT -b.quantity, b.article, 4 AS type_oper
FROM smdocuments a, smspec b
WHERE a.createdat > '03.06.2013'
AND a.id = b.docid
AND a.doctype = b.doctype
AND a.docstate > 1
AND a.doctype IN ('WI', 'IW', 'CR', 'PO')
AND a.locationto IN (SELECT id
FROM smstorelocations))
UNION ALL
SELECT b.quantity, b.article, 4 AS type_oper
FROM smdocuments a, smspec b
WHERE a.createdat > '03.06.2013'
AND a.id = b.docid
AND a.doctype = b.doctype
AND a.docstate > 1
AND a.doctype IN ('WO', 'IW', 'CS', 'PE')
AND a.locationfrom IN (SELECT id
FROM smstorelocations))
UNION ALL
SELECT quantity, article, 4 AS type_oper
FROM smgoods
WHERE storeloc IN (SELECT id
FROM smstorelocations))
WHERE 1 = 1
AND article IN (SELECT b.article
FROM smdocuments a, smspec b
WHERE a.doctype = 'WI'
AND a.docstate > 1
AND a.clientindex IN (1416)
AND b.docid = a.id
AND b.doctype = a.doctype
GROUP BY b.article)
GROUP BY article, type_oper
ORDER BY article, type_oper
Optimizer Mode Used:
ALL_ROWS
Total Cost:
140
Execution Steps:
Step # Step Name
63 SELECT STATEMENT
62 SORT [ORDER BY]
61 HASH [GROUP BY]
60 FILTER
47 . VIEW
46 UNION-ALL
5 SUPERMAG.SMSPEC TABLE ACCESS [BY INDEX ROWID]
4 NESTED LOOPS
2 SUPERMAG.SMDOCUMENTS TABLE ACCESS [BY INDEX ROWID]
1 SUPERMAG.SMDOCUMENTS_CREATEDAT INDEX [RANGE SCAN]
3 SUPERMAG.SMCSPEC_PK INDEX [RANGE SCAN]
10 SUPERMAG.SMSPEC TABLE ACCESS [BY INDEX ROWID]
9 NESTED LOOPS
7 SUPERMAG.SMDOCUMENTS TABLE ACCESS [BY INDEX ROWID]
6 SUPERMAG.SMDOCUMENTS_CREATEDAT INDEX [RANGE SCAN]
8 SUPERMAG.SMCSPEC_PK INDEX [RANGE SCAN]
15 SUPERMAG.SMSPEC TABLE ACCESS [BY INDEX ROWID]
14 NESTED LOOPS
12 SUPERMAG.SMDOCUMENTS TABLE ACCESS [BY INDEX ROWID]
11 SUPERMAG.SMDOCUMENTS_CREATEDAT INDEX [RANGE SCAN]
13 SUPERMAG.SMCSPEC_PK INDEX [RANGE SCAN]
20 SUPERMAG.SMSPEC TABLE ACCESS [BY INDEX ROWID]
19 NESTED LOOPS
17 SUPERMAG.SMDOCUMENTS TABLE ACCESS [BY INDEX ROWID]
16 SUPERMAG.SMDOCUMENTS_CREATEDAT INDEX [RANGE SCAN]
18 SUPERMAG.SMCSPEC_PK INDEX [RANGE SCAN]
21 SUPERMAG.SMGOODS TABLE ACCESS [FULL]
26 SUPERMAG.SMSPEC TABLE ACCESS [BY INDEX ROWID]
25 NESTED LOOPS
23 SUPERMAG.SMDOCUMENTS TABLE ACCESS [BY INDEX ROWID]
22 SUPERMAG.SMDOCUMENTS_CREATEDAT INDEX [RANGE SCAN]
24 SUPERMAG.SMCSPEC_PK INDEX [RANGE SCAN]
44 SUPERMAG.SMSPEC TABLE ACCESS [BY INDEX ROWID]
43 NESTED LOOPS
41 SUPERMAG.SMDOCUMENTS TABLE ACCESS [BY INDEX ROWID]
40 BITMAP CONVERSION [TO ROWIDS]
39 BITMAP AND
29 BITMAP CONVERSION [FROM ROWIDS]
28 SORT [ORDER BY]
27 SUPERMAG.SMDOCUMENTS_CREATEDAT INDEX [RANGE SCAN]
38 BITMAP OR
31 BITMAP MERGE
30 SUPERMAG.OK_SMDOC_OR BITMAP INDEX [RANGE SCAN]
33 BITMAP MERGE
32 SUPERMAG.OK_SMDOC_OR BITMAP INDEX [RANGE SCAN]
35 BITMAP MERGE
34 SUPERMAG.OK_SMDOC_OR BITMAP INDEX [RANGE SCAN]
37 BITMAP MERGE
36 SUPERMAG.OK_SMDOC_OR BITMAP INDEX [RANGE SCAN]
42 SUPERMAG.SMCSPEC_PK INDEX [RANGE SCAN]
45 SUPERMAG.SMGOODS TABLE ACCESS [FULL]
59 FILTER
58 HASH [GROUP BY]
57 SUPERMAG.SMSPEC TABLE ACCESS [BY INDEX ROWID]
56 NESTED LOOPS
54 SUPERMAG.SMDOCUMENTS TABLE ACCESS [BY INDEX ROWID]
53 BITMAP CONVERSION [TO ROWIDS]
52 BITMAP AND
49 BITMAP CONVERSION [FROM ROWIDS]
48 SUPERMAG.SMDOCUMENTS_CLIENT INDEX [RANGE SCAN]
51 BITMAP MERGE
50 SUPERMAG.OK_SMDOC_OR BITMAP INDEX [RANGE SCAN]
55 SUPERMAG.SMCSPEC_PK INDEX [RANGE SCAN]
Step # Description Est. Cost Est. Rows Returned Est. KBytes Returned
1 This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index SMDOCUMENTS_CREATEDAT. 1 105 --
2 This plan step retrieves rows from table SMDOCUMENTS through ROWID(s) returned by an index. 1 1 0,031
3 This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index SMCSPEC_PK. 1 53 --
4 This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause. 2 15 0,894
5 This plan step retrieves rows from table SMSPEC through ROWID(s) returned by an index. 1 53 1,501
6 This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index SMDOCUMENTS_CREATEDAT. 1 105 --
7 This plan step retrieves rows from table SMDOCUMENTS through ROWID(s) returned by an index. 1 1 0,032
8 This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index SMCSPEC_PK. 1 1 --
9 This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause. 2 1 0,061
10 This plan step retrieves rows from table SMSPEC through ROWID(s) returned by an index. 1 1 0,028
11 This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index SMDOCUMENTS_CREATEDAT. 1 105 --
12 This plan step retrieves rows from table SMDOCUMENTS through ROWID(s) returned by an index. 1 31 0,999
13 This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index SMCSPEC_PK. 1 1 --
14 This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause. 2 222 13,441
15 This plan step retrieves rows from table SMSPEC through ROWID(s) returned by an index. 1 7 0,198
16 This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index SMDOCUMENTS_CREATEDAT. 1 105 --
17 This plan step retrieves rows from table SMDOCUMENTS through ROWID(s) returned by an index. 1 2 0,063
18 This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index SMCSPEC_PK. 1 1 --
19 This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause. 2 2 0,119
20 This plan step retrieves rows from table SMSPEC through ROWID(s) returned by an index. 1 1 0,028
21 This plan step retrieves all rows from table SMGOODS. 20 61 713 723,199
22 This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index SMDOCUMENTS_CREATEDAT. 1 8 041 --
23 This plan step retrieves rows from table SMDOCUMENTS through ROWID(s) returned by an index. 14 2 430 78,311
24 This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index SMCSPEC_PK. 1 1 --
25 This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause. 38 17 592 1 065,141
26 This plan step retrieves rows from table SMSPEC through ROWID(s) returned by an index. 1 7 0,198
27 This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index SMDOCUMENTS_CREATEDAT. 1 8 041 --
28 This plan step accepts a set of rows from its child node, and sorts them on a per-column basis using the query's ORDER BY clause. -- -- --
29 This plan step accepts a ROWID from its child node, and converts it to a bitmap representation of the index.
30 This plan step retrieves one or more ROWIDs by scanning a range of bits in the bitmap index OK_SMDOC_OR to find the rows which satisfy a condition specified in the querys WHERE clause. -- -- --
31 This plan step merges several bitmap indexes resulting from a RANGE SCAN, into a single bitmap index.
32 This plan step retrieves one or more ROWIDs by scanning a range of bits in the bitmap index OK_SMDOC_OR to find the rows which satisfy a condition specified in the querys WHERE clause. -- -- --
33 This plan step merges several bitmap indexes resulting from a RANGE SCAN, into a single bitmap index.
34 This plan step retrieves one or more ROWIDs by scanning a range of bits in the bitmap index OK_SMDOC_OR to find the rows which satisfy a condition specified in the querys WHERE clause. -- -- --
35 This plan step merges several bitmap indexes resulting from a RANGE SCAN, into a single bitmap index.
36 This plan step retrieves one or more ROWIDs by scanning a range of bits in the bitmap index OK_SMDOC_OR to find the rows which satisfy a condition specified in the querys WHERE clause. -- -- --
37 This plan step merges several bitmap indexes resulting from a RANGE SCAN, into a single bitmap index.
38 This plan step accepts bitmap representations of two indexes, performs a bitwise OR operation on them, and returns the result.
39 This plan step has no supplementary description information. -- -- --
40 This plan step accepts a bitmap representation of an index from its child node, and converts it to a ROWID that can be used to access the table.
41 This plan step retrieves rows from table SMDOCUMENTS through ROWID(s) returned by an index. 8 167 5,219
42 This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index SMCSPEC_PK. 1 4 --
43 This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause. 10 5 452 324,777
44 This plan step retrieves rows from table SMSPEC through ROWID(s) returned by an index. 1 33 0,935
45 This plan step retrieves all rows from table SMGOODS. 20 61 713 723,199
46 This plan step accepts multiple sets of rows, and combines them into one set including all duplicates.
47 This plan step represents the execution plan for the subquery defined by the view . 96 146 710 6 160,674
48 This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index SMDOCUMENTS_CLIENT. 1 4 282 --
49 This plan step accepts a ROWID from its child node, and converts it to a bitmap representation of the index.
50 This plan step retrieves one or more ROWIDs by scanning a range of bits in the bitmap index OK_SMDOC_OR to find the rows which satisfy a condition specified in the querys WHERE clause. -- -- --
51 This plan step merges several bitmap indexes resulting from a RANGE SCAN, into a single bitmap index.
52 This plan step has no supplementary description information. -- -- --
53 This plan step accepts a bitmap representation of an index from its child node, and converts it to a ROWID that can be used to access the table.
54 This plan step retrieves rows from table SMDOCUMENTS through ROWID(s) returned by an index. 6 2 287 55,835
55 This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index SMCSPEC_PK. 1 12 --
56 This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause. 29 27 541 1 344,775
57 This plan step retrieves rows from table SMSPEC through ROWID(s) returned by an index. 1 12 0,293
58 This plan step has no supplementary description information.
59 This plan step accepts a set of rows from its child node, eliminates some of them, and returns the rest.
60 This plan step accepts multiple sets of rows. Rows from the first set are eliminated using the data found in the second through n sets.
61 This plan step has no supplementary description information.
62 This plan step accepts a set of rows from its child node, and sorts them on a per-column basis using the query's ORDER BY clause. 140 46 274 1 943,146
63 This plan step designates this statement as a SELECT statement. 140 -- --