explain plan for SELECT SP.SPECITEM , SC.DOCTYPE , SC.DOCID , SC.SPECITEM SPECITEM_SRC , SC.ITEMPRICE , SC.ITEMPRICENOTAX FROM SMSPEC SP , SMSPEC SC , SMDOCUMENTS D WHERE SP.DOCTYPE = :b1 AND SP.DOCID = :b2 AND ( ( :b3 = '0' ) OR NVL(SP.ITEMPRICE , 0 ) = 0 ) AND SC.DOCTYPE = D.DOCTYPE AND SC.DOCID = D.ID AND D.CREATEDAT <= :b4 AND D.DOCTYPE = 'WI' AND D.DOCSTATE = 3 AND D.OPCODE = 0 AND SC.ARTICLE = SP.ARTICLE AND D.LOCATIONTO IN (SELECT ID FROM TTSHOPLIST ) AND ( :b1 != 'WI' OR D.ID != :b2 ) AND D.ID IN (SELECT MAX (D2.ID) FROM SMSPEC S2 , SMDOCUMENTS D2 WHERE S2.DOCTYPE = D2.DOCTYPE AND S2.DOCID = D2.ID AND D2.DOCTYPE = 'WI' AND D2.DOCSTATE = 3 AND D2.OPCODE = 0 AND S2.ARTICLE = SP.ARTICLE AND D2.LOCATIONTO IN (SELECT ID FROM TTSHOPLIST ) AND ( :b1 != 'WI' OR D2.ID != :b2 ) AND D2.CREATEDAT IN (SELECT MAX (D1.CREATEDAT) FROM SMSPEC S1 , SMDOCUMENTS D1 WHERE S1.DOCTYPE = D1.DOCTYPE AND S1.DOCID = D1.ID AND D1.CREATEDAT <= :b4 AND D1.DOCTYPE = 'WI' AND D1.DOCSTATE = 3 AND D1.OPCODE = 0 AND S1.ARTICLE = S2.ARTICLE AND D1.LOCATIONTO IN (SELECT ID FROM TTSHOPLIST ) AND ( :b1 != 'WI' OR D1.ID != :b2 ) ) ) AND SC.SPECITEM IN (SELECT MAX (S3.SPECITEM) FROM SMSPEC S3 WHERE S3.DOCTYPE = D.DOCTYPE AND S3.DOCID = D.ID AND S3.ARTICLE = SP.ARTICLE );
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop | -------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 112 | 12 | | | | 1 | NESTED LOOPS | | 1 | 112 | 4 | | | | 2 | NESTED LOOPS | | 1 | 76 | 3 | | | | 3 | NESTED LOOPS | | 1 | 63 | 2 | | | | 4 | PARTITION LIST SINGLE | | 1 | 30 | 1 | KEY | KEY | | 5 | TABLE ACCESS BY LOCAL INDEX ROWID | SMSPEC | 1 | 30 | 1 | KEY | KEY | | 6 | INDEX RANGE SCAN | SMCSPEC_DISPLAYPOS | 22 | | 1 | KEY | KEY | | 7 | TABLE ACCESS BY INDEX ROWID | SMDOCUMENTS | 1 | 33 | 1 | | | | 8 | INDEX UNIQUE SCAN | SMCDOCUMENTS_PK | 1 | | 1 | | | | 9 | SORT AGGREGATE | | 1 | 104 | | | | | 10 | NESTED LOOPS | | 1 | 104 | 7 | | | | 11 | NESTED LOOPS | | 1 | 91 | 6 | | | | 12 | NESTED LOOPS | | 1 | 58 | 5 | | | | 13 | VIEW | VW_SQ_1 | 1 | 36 | 4 | | | | 14 | SORT GROUP BY | | 1 | 74 | 4 | | | | 15 | NESTED LOOPS | | 1 | 74 | 4 | | | | 16 | MERGE JOIN CARTESIAN | | 1 | 41 | 3 | | | | 17 | TABLE ACCESS FULL | TTSHOPLIST | 1 | 13 | 2 | | | | 18 | BUFFER SORT | | 438 | 12264 | 1 | | | | 19 | PARTITION LIST SINGLE | | 438 | 12264 | 1 | KEY | KEY | | 20 | INDEX RANGE SCAN | SMSPEC_ART | 438 | 12264 | 1 | 6 | 6 | | 21 | TABLE ACCESS BY INDEX ROWID| SMDOCUMENTS | 1 | 33 | 1 | | | | 22 | INDEX UNIQUE SCAN | SMCDOCUMENTS_PK | 1 | | 1 | | | | 23 | PARTITION LIST SINGLE | | 1 | 22 | 1 | KEY | KEY | | 24 | INDEX RANGE SCAN | SMSPEC_ART | 1 | 22 | 1 | 6 | 6 | | 25 | TABLE ACCESS BY INDEX ROWID | SMDOCUMENTS | 1 | 33 | 1 | | | | 26 | INDEX UNIQUE SCAN | SMCDOCUMENTS_PK | 1 | | 1 | | | | 27 | INDEX UNIQUE SCAN | TTCSHOPLIST_PK | 1 | 13 | 1 | | | | 28 | INDEX UNIQUE SCAN | TTCSHOPLIST_PK | 1 | 13 | 1 | | | | 29 | PARTITION LIST SINGLE | | 1 | 36 | 1 | KEY | KEY | | 30 | TABLE ACCESS BY LOCAL INDEX ROWID | SMSPEC | 1 | 36 | 1 | 6 | 6 | | 31 | INDEX UNIQUE SCAN | SMCSPEC_PK | 1 | | 1 | 6 | 6 | | 32 | SORT AGGREGATE | | 1 | 26 | | | | | 33 | PARTITION LIST SINGLE | | 1 | 26 | 1 | KEY | KEY | | 34 | TABLE ACCESS BY LOCAL INDEX ROWID| SMSPEC | 1 | 26 | 1 | KEY | KEY | | 35 | INDEX RANGE SCAN | SMSPEC_ART | 1 | | 1 | KEY | KEY | --------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 97 | 4 | | 1 | NESTED LOOPS | | 1 | 97 | 4 | | 2 | NESTED LOOPS | | 1 | 66 | 3 | | 3 | NESTED LOOPS | | 1 | 53 | 2 | |* 4 | TABLE ACCESS BY INDEX ROWID | SMSPEC | 1 | 27 | 1 | |* 5 | INDEX RANGE SCAN | SMCSPEC_DISPLAYPOS | 1 | | 4 | |* 6 | TABLE ACCESS BY INDEX ROWID | SMDOCUMENTS | 1 | 26 | 1 | |* 7 | INDEX UNIQUE SCAN | SMCDOCUMENTS_PK | 1 | | | | 8 | SORT AGGREGATE | | 1 | 59 | | |* 9 | FILTER | | | | | | 10 | NESTED LOOPS | | 1 | 59 | 5 | | 11 | NESTED LOOPS | | 1 | 46 | 4 | |* 12 | INDEX RANGE SCAN | SMSPEC_ART | 18 | 360 | 4 | |* 13 | TABLE ACCESS BY INDEX ROWID | SMDOCUMENTS | 1 | 26 | 1 | |* 14 | INDEX UNIQUE SCAN | SMCDOCUMENTS_PK | 1 | | | |* 15 | INDEX UNIQUE SCAN | TTCSHOPLIST_PK | 233 | 3029 | | | 16 | SORT AGGREGATE | | 1 | 59 | | | 17 | NESTED LOOPS | | 1 | 59 | 5 | | 18 | NESTED LOOPS | | 1 | 46 | 4 | |* 19 | INDEX RANGE SCAN | SMSPEC_ART | 18 | 360 | 4 | |* 20 | TABLE ACCESS BY INDEX ROWID| SMDOCUMENTS | 1 | 26 | 1 | |* 21 | INDEX UNIQUE SCAN | SMCDOCUMENTS_PK | 1 | | | |* 22 | INDEX UNIQUE SCAN | TTCSHOPLIST_PK | 233 | 3029 | | |* 23 | INDEX UNIQUE SCAN | TTCSHOPLIST_PK | 233 | 3029 | | |* 24 | TABLE ACCESS BY INDEX ROWID | SMSPEC | 1 | 31 | 1 | |* 25 | INDEX UNIQUE SCAN | SMCSPEC_PK | 1 | | | | 26 | SORT AGGREGATE | | 1 | 23 | | | 27 | TABLE ACCESS BY INDEX ROWID | SMSPEC | 1 | 23 | 1 | |* 28 | INDEX RANGE SCAN | SMSPEC_ART | 1 | | 4 | ---------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 102 | 5 (20)| | |
| 1 | NESTED LOOPS | | 1 | 102 | 5 (20)| | |
| 2 | NESTED LOOPS | | 1 | 73 | 4 (25)| | |
| 3 | NESTED LOOPS | | 1 | 60 | 3 (34)| | |
|* 4 | TABLE ACCESS BY GLOBAL INDEX ROWID | SMSPEC | 1 | 33 | 2 (50)| ROWID | ROW L |
|* 5 | INDEX RANGE SCAN | SMCSPEC_DISPLAYPOS | 2 | | 5 (20)| | |
|* 6 | TABLE ACCESS BY INDEX ROWID | SMDOCUMENTS | 1 | 27 | 2 (50)| | |
|* 7 | INDEX UNIQUE SCAN | SMCDOCUMENTS_PK | 1 | | 2 (50)| | |
| 8 | SORT AGGREGATE | | 1 | 58 | | | |
|* 9 | FILTER | | | | | | |
| 10 | NESTED LOOPS | | 1 | 58 | 48 (0)| | |
| 11 | NESTED LOOPS | | 1 | 45 | 47 (0)| | |
|* 12 | INDEX RANGE SCAN | SMSPEC_ART | 223 | 4014 | 18 (6)| | |
|* 13 | TABLE ACCESS BY INDEX ROWID | SMDOCUMENTS | 1 | 27 | 2 (50)| | |
|* 14 | INDEX UNIQUE SCAN | SMCDOCUMENTS_PK | 1 | | 2 (50)| | |
|* 15 | INDEX UNIQUE SCAN | TTCSHOPLIST_PK | 194 | 2522 | | | |
| 16 | SORT AGGREGATE | | 1 | 58 | | | |
| 17 | NESTED LOOPS | | 1 | 58 | 48 (0)| | |
| 18 | NESTED LOOPS | | 1 | 45 | 47 (0)| | |
|* 19 | INDEX RANGE SCAN | SMSPEC_ART | 223 | 4014 | 18 (6)| | |
|* 20 | TABLE ACCESS BY INDEX ROWID | SMDOCUMENTS | 1 | 27 | 2 (50)| | |
|* 21 | INDEX UNIQUE SCAN | SMCDOCUMENTS_PK | 1 | | 2 (50)| | |
|* 22 | INDEX UNIQUE SCAN | TTCSHOPLIST_PK | 194 | 2522 | | | |
|* 23 | INDEX UNIQUE SCAN | TTCSHOPLIST_PK | 194 | 2522 | | | |
|* 24 | TABLE ACCESS BY GLOBAL INDEX ROWID | SMSPEC | 1 | 29 | 2 (50)| 2 | 2 |
|* 25 | INDEX UNIQUE SCAN | SMCSPEC_PK | 1 | | 3 (34)| | |
| 26 | SORT AGGREGATE | | 1 | 29 | | | |
| 27 | TABLE ACCESS BY GLOBAL INDEX ROWID| SMSPEC | 1 | 29 | 2 (50)| ROWID | ROW L |
|* 28 | INDEX RANGE SCAN | SMSPEC_ART | 1 | | 5 (20)| | |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(NVL("SYS_ALIAS_4"."ITEMPRICE",0)=0 OR :Z='0')
5 - access("SYS_ALIAS_4"."DOCTYPE"=:Z AND "SYS_ALIAS_4"."DOCID"=:Z)
6 - filter("SYS_ALIAS_3"."LOCATIONTO" IS NOT NULL AND "SYS_ALIAS_3"."OPCODE"=0 AND "SYS_ALIAS_3"."DOCSTATE"=3
AND "SYS_ALIAS_3"."CREATEDAT"<=:Z AND ("SYS_ALIAS_3"."ID"<>:Z OR :Z<>'WI'))
7 - access("SYS_ALIAS_3"."DOCTYPE"='WI' AND "SYS_ALIAS_3"."ID"= (SELECT /*+ */ MAX("D2"."ID") FROM
"SMDOCUMENTS" "D2","SMSPEC" "SYS_ALIAS_1","TTSHOPLIST" "TTSHOPLIST" WHERE "D2"."CREATEDAT"= (SELECT /*+ */
MAX("D1"."CREATEDAT") FROM "SMDOCUMENTS" "D1","SMSPEC" "S1","TTSHOPLIST" "TTSHOPLIST" WHERE
"D1"."LOCATIONTO"="TTSHOPLIST"."ID" AND "S1"."DOCTYPE"='WI' AND "S1"."ARTICLE"=:B1 AND "S1"."DOCID"="D1"."ID" AND
"D1"."DOCTYPE"='WI' AND "D1"."LOCATIONTO" IS NOT NULL AND "D1"."OPCODE"=0 AND "D1"."DOCSTATE"=3 AND
"D1"."CREATEDAT"<=:Z AND ("D1"."ID"<>:Z OR :Z<>'WI') AND "S1"."DOCTYPE"="D1"."DOCTYPE") AND
"D2"."LOCATIONTO"="TTSHOPLIST"."ID" AND "SYS_ALIAS_1"."DOCTYPE"='WI' AND "SYS_ALIAS_1"."ARTICLE"=:B2 AND
"SYS_ALIAS_1"."DOCID"="D2"."ID" AND "D2"."DOCTYPE"='WI' AND "D2"."LOCATIONTO" IS NOT NULL AND "D2"."OPCODE"=0 AND
"D2"."DOCSTATE"=3 AND ("D2"."ID"<>:Z OR :Z<>'WI') AND "SYS_ALIAS_1"."DOCTYPE"="D2"."DOCTYPE"))
9 - filter("D2"."CREATEDAT"= (SELECT /*+ */ MAX("D1"."CREATEDAT") FROM "SMDOCUMENTS" "D1","SMSPEC"
"S1","TTSHOPLIST" "TTSHOPLIST" WHERE "D1"."LOCATIONTO"="TTSHOPLIST"."ID" AND "S1"."DOCTYPE"='WI' AND
"S1"."ARTICLE"=:B1 AND "S1"."DOCID"="D1"."ID" AND "D1"."DOCTYPE"='WI' AND "D1"."LOCATIONTO" IS NOT NULL AND
"D1"."OPCODE"=0 AND "D1"."DOCSTATE"=3 AND "D1"."CREATEDAT"<=:Z AND ("D1"."ID"<>:Z OR :Z<>'WI') AND
"S1"."DOCTYPE"="D1"."DOCTYPE"))
12 - access("SYS_ALIAS_1"."ARTICLE"=:B1 AND "SYS_ALIAS_1"."DOCTYPE"='WI')
13 - filter("D2"."LOCATIONTO" IS NOT NULL AND "D2"."OPCODE"=0 AND "D2"."DOCSTATE"=3 AND ("D2"."ID"<>:Z OR
:Z<>'WI'))
14 - access("D2"."DOCTYPE"='WI' AND "SYS_ALIAS_1"."DOCID"="D2"."ID")
filter("SYS_ALIAS_1"."DOCTYPE"="D2"."DOCTYPE")
15 - access("D2"."LOCATIONTO"="TTSHOPLIST"."ID")
19 - access("S1"."ARTICLE"=:B1 AND "S1"."DOCTYPE"='WI')
20 - filter("D1"."LOCATIONTO" IS NOT NULL AND "D1"."OPCODE"=0 AND "D1"."DOCSTATE"=3 AND "D1"."CREATEDAT"<=:Z AND
("D1"."ID"<>:Z OR :Z<>'WI'))
21 - access("D1"."DOCTYPE"='WI' AND "S1"."DOCID"="D1"."ID")
filter("S1"."DOCTYPE"="D1"."DOCTYPE")
22 - access("D1"."LOCATIONTO"="TTSHOPLIST"."ID")
23 - access("SYS_ALIAS_3"."LOCATIONTO"="TTSHOPLIST"."ID")
24 - filter("SC"."ARTICLE"="SYS_ALIAS_4"."ARTICLE")
25 - access("SC"."DOCTYPE"='WI' AND "SC"."DOCID"="SYS_ALIAS_3"."ID" AND "SC"."SPECITEM"= (SELECT /*+ */
MAX("S3"."SPECITEM") FROM "SMSPEC" "S3" WHERE "S3"."DOCID"=:B1 AND "S3"."DOCTYPE"=:B2 AND "S3"."ARTICLE"=:B3))
filter("SC"."DOCTYPE"="SYS_ALIAS_3"."DOCTYPE")
28 - access("S3"."ARTICLE"=:B1 AND "S3"."DOCTYPE"=:B2 AND "S3"."DOCID"=:B3)