SQL_ID g58at1rrnjnur -------------------- -- getSelectProductsSQL() SELECT ce.catentry_id catentry_id_parent, ce.partnumber partnumber_parent, ced.NAME name_parent, cer.catentry_id_child catentry_id_child, ce2.partnumber partnumber_child, ced2.name name_child, ced2.longdescription, ced2.published, op.price, ced2.availabilitydate, ced2.available, cer.SEQUENCE seq_1, ecer.SEQUENCE seq_2, ce2.field2, ecgr.SEQUENCE relevance , ecgr.storeent_id storeent_id_1, ecgr2.SEQUENCE seq_3, ecgr2.storeent_id storeent_id_2 , filt.filter_id, filt.name filter_name, filt.value filter_value, o.ENDDATE FROM catgpenrel cgr LEFT OUTER JOIN EXTCATGPENSTORESEQ ecgr ON cgr.catalog_id = ecgr.catalog_id AND cgr.catgroup_id = ecgr.catgroup_id AND cgr.catentry_id = ecgr.catentry_id AND ecgr.sort_type = :"SYS_B_00" AND ecgr.storeent_id IN ( :1 ,:2 ) , catgpenrel cgr2 LEFT OUTER JOIN EXTCATGPENSTORESEQ ecgr2 ON cgr2.catalog_id = ecgr2.catalog_id AND cgr2.catgroup_id = ecgr2.catgroup_id AND cgr2.catentry_id = ecgr2.catentry_id AND ecgr2.sort_type = :"SYS_B_01" AND "ecgr2.storeent_id IN ( :3 ,:4 ) , catentry ce, catentry ce2 left outer join (SELECT cef.catentry_id, " "f.filter_id, fd.value, ft.name FROM EXTCATENTFILTER cef, EXTFILTERTYPE ft, EXTFILTER f, EXTFILTERDESC fd WHERE " "f.filter_id = cef.filter_id AND f.filtertype_id = ft.filtertype_id AND fd.filter_id = f.filter_id AND fd.language_id = :5 " ") filt ON filt.catentry_id = ce2.catentry_id, catentdesc ced, catentdesc ced2, offer o, " offerprice op, catentrel cer LEFT OUTER JOIN extcatentrel ecer ON cer.catentry_id_child = ecer.catentry_id_child AND cer.catentry_id_parent = ecer.catentry_id_parent AND ecer.language_id = :6 left outer join extcatentrydesc ecdesc on cer.catentry_id_child = ecdesc.catentry_id AND ecdesc.language_id = :7 WHERE exists(select :"SYS_B_02" from storecat sc where cgr.catalog_id = sc.catalog_id AND sc.storeent_id IN ( :8 ,:9 ) ) and exists(select :"SYS_B_03" from storecgrp scg where scg.catgroup_id = cgr.catgroup_id and scg.storeent_id IN ( :10 ,:11 ) ) AND cgr2.catgroup_id = cgr.catgroup_id and exists(select :"SYS_B_04" from catgrpdesc cgd where cgd.catgroup_id = cgr.catgroup_id AND cgd.language_id = ced.language_id AND cgd.published = :"SYS_B_05") and exists(select :"SYS_B_06" from extcatgrpdesc ecgd where ecgd.catgroup_id = cgr.catgroup_id AND ecgd.language_id = ced.language_id AND ecgd.published = :"SYS_B_07" ) AND cgr2.catgroup_id = cgr.catgroup_id AND ce.catentry_id = cer.catentry_id_parent AND cgr.catentry_id = ce.catentry_id AND cgr2.catentry_id= ce2.catentry_id AND ce.catenttype_id = :"SYS_B_08" AND ced.catentry_id = ce.catentry_id AND ced.published = :"SYS_B_09" AND ce2.catentry_id = cer.catentry_id_child AND ced2.catentry_id = ce2.catentry_id AND ced2.language_id = ced.language_id AND ced2.published = :"SYS_B_10" AND ce.markfordelete = :"SYS_B_11" AND ce2.markfordelete = :"SYS_B_12" AND o.catentry_id = cer.catentry_id_child AND o.precedence IN (:"SYS_B_13",:"SYS_B_14",:"SYS_B_15") AND o.tradeposcn_id = :12 AND o.published = :"SYS_B_16" AND (o.startdate <= current_timestamp or o.startdate is null) AND (o.enddate > current_timestamp or o.enddate is null) AND op.offer_id = o.offer_id AND cgr.catalog_id = :13 AND cgr2.catalog_id= cgr.catalog_id AND ced.language_id = :14 AND "cgr.catgroup_id = :15 ORDER BY ce.catentry_id, cer.catentry_id_child, ecgr.storeent_id desc, ecgr2.storeent_id desc" Plan hash value: 607707275 ---------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 37 (100)| | | | | 1 | SORT ORDER BY | | 1 | 518 | 37 (6)| 00:00:01 | | | | 2 | NESTED LOOPS OUTER | | 1 | 518 | 36 (3)| 00:00:01 | | | | 3 | NESTED LOOPS OUTER | | 1 | 437 | 31 (0)| 00:00:01 | | | | 4 | NESTED LOOPS | | 1 | 397 | 29 (0)| 00:00:01 | | | | 5 | NESTED LOOPS | | 1 | 386 | 26 (0)| 00:00:01 | | | | 6 | NESTED LOOPS OUTER | | 1 | 364 | 24 (0)| 00:00:01 | | | | 7 | NESTED LOOPS | | 1 | 354 | 24 (0)| 00:00:01 | | | | 8 | NESTED LOOPS OUTER | | 1 | 242 | 22 (0)| 00:00:01 | | | | 9 | NESTED LOOPS | | 1 | 202 | 20 (0)| 00:00:01 | | | | 10 | NESTED LOOPS OUTER | | 1 | 157 | 17 (0)| 00:00:01 | | | | 11 | NESTED LOOPS | | 1 | 140 | 15 (0)| 00:00:01 | | | | 12 | NESTED LOOPS | | 1 | 124 | 13 (0)| 00:00:01 | | | | 13 | NESTED LOOPS | | 1 | 103 | 11 (0)| 00:00:01 | | | | 14 | NESTED LOOPS SEMI | | 1 | 74 | 9 (0)| 00:00:01 | | | | 15 | MERGE JOIN CARTESIAN | | 1 | 65 | 8 (0)| 00:00:01 | | | | 16 | NESTED LOOPS | | 1 | 49 | 6 (0)| 00:00:01 | | | | 17 | NESTED LOOPS | | 1 | 33 | 4 (0)| 00:00:01 | | | | 18 | NESTED LOOPS | | 1 | 24 | 3 (0)| 00:00:01 | | | | 19 | TABLE ACCESS BY INDEX ROWID| CATGRPDESC | 1 | 12 | 2 (0)| 00:00:01 | | | | 20 | INDEX UNIQUE SCAN | SYS_C0030074 | 1 | | 1 (0)| 00:00:01 | | | | 21 | TABLE ACCESS BY INDEX ROWID| EXTCATGRPDESC | 1 | 12 | 1 (0)| 00:00:01 | | | | 22 | INDEX UNIQUE SCAN | PK_EXTCATGRPDESC | 1 | | 0 (0)| | | | | 23 | SORT UNIQUE | | 1 | 9 | 1 (0)| 00:00:01 | | | | 24 | INLIST ITERATOR | | | | | | | | | 25 | INDEX UNIQUE SCAN | SYS_C0031935 | 1 | 9 | 1 (0)| 00:00:01 | | | | 26 | INDEX RANGE SCAN | IND_CATGPENREL_2 | 1 | 16 | 2 (0)| 00:00:01 | | | | 27 | BUFFER SORT | | 67 | 1072 | 6 (0)| 00:00:01 | | | | 28 | INDEX RANGE SCAN | IND_CATGPENREL_2 | 67 | 1072 | 2 (0)| 00:00:01 | | | | 29 | INLIST ITERATOR | | | | | | | | | 30 | INDEX UNIQUE SCAN | SYS_C0031941 | 1 | 9 | 1 (0)| 00:00:01 | | | | 31 | TABLE ACCESS BY INDEX ROWID | CATENTRY | 1 | 29 | 2 (0)| 00:00:01 | | | | 32 | INDEX UNIQUE SCAN | SYS_C0030043 | 1 | | 1 (0)| 00:00:01 | | | | 33 | TABLE ACCESS BY INDEX ROWID | CATENTRY | 1 | 21 | 2 (0)| 00:00:01 | | | | 34 | INDEX UNIQUE SCAN | SYS_C0030043 | 1 | | 1 (0)| 00:00:01 | | | | 35 | TABLE ACCESS BY INDEX ROWID | CATENTREL | 1 | 16 | 2 (0)| 00:00:01 | | | | 36 | INDEX RANGE SCAN | IND5556045 | 1 | | 1 (0)| 00:00:01 | | | | 37 | TABLE ACCESS BY INDEX ROWID | EXTCATENTREL | 1 | 17 | 2 (0)| 00:00:01 | | | | 38 | INDEX UNIQUE SCAN | EXTCATENTREL_PK | 1 | | 1 (0)| 00:00:01 | | | | 39 | TABLE ACCESS BY INDEX ROWID | OFFER | 1 | 45 | 3 (0)| 00:00:01 | | | | 40 | INDEX RANGE SCAN | I0000167 | 1 | | 2 (0)| 00:00:01 | | | | 41 | TABLE ACCESS BY INDEX ROWID | EXTCATGPENSTORESEQ | 1 | 40 | 2 (0)| 00:00:01 | | | | 42 | INDEX RANGE SCAN | EXTCATGPENSTORESEQ_IND | 1 | | 1 (0)| 00:00:01 | | | | 43 | TABLE ACCESS BY INDEX ROWID | CATENTDESC | 1 | 112 | 2 (0)| 00:00:01 | | | | 44 | INDEX UNIQUE SCAN | SYS_C0030032 | 1 | | 1 (0)| 00:00:01 | | | | 45 | PARTITION LIST SINGLE | | 1 | 10 | 0 (0)| | KEY | KEY | | 46 | INDEX UNIQUE SCAN | EXTCATENTRYDESC_IDX1 | 1 | 10 | 0 (0)| | KEY | KEY | | 47 | INDEX RANGE SCAN | I0000304 | 1 | 22 | 2 (0)| 00:00:01 | | | | 48 | TABLE ACCESS BY INDEX ROWID | OFFERPRICE | 1 | 11 | 3 (0)| 00:00:01 | | | | 49 | INDEX RANGE SCAN | SYS_C0031126 | 1 | | 2 (0)| 00:00:01 | | | | 50 | TABLE ACCESS BY INDEX ROWID | EXTCATGPENSTORESEQ | 1 | 40 | 2 (0)| 00:00:01 | | | | 51 | INDEX RANGE SCAN | EXTCATGPENSTORESEQ_IND | 1 | | 1 (0)| 00:00:01 | | | | 52 | VIEW PUSHED PREDICATE | | 1 | 81 | 5 (20)| 00:00:01 | | | | 53 | NESTED LOOPS | | 1 | 47 | 5 (20)| 00:00:01 | | | | 54 | NESTED LOOPS | | 1 | 25 | 4 (25)| 00:00:01 | | | | 55 | NESTED LOOPS | | 1 | 15 | 3 (34)| 00:00:01 | | | | 56 | VIEW | index$_join$_012 | 295 | 2065 | 3 (34)| 00:00:01 | | | | 57 | HASH JOIN | | | | | | | | | 58 | INDEX FAST FULL SCAN | EXTFILTER_PK | 295 | 2065 | 1 (0)| 00:00:01 | | | | 59 | INDEX FAST FULL SCAN | EXTFILTER_UK | 295 | 2065 | 1 (0)| 00:00:01 | | | | 60 | INDEX UNIQUE SCAN | EXTCATENTFILTER_PK | 1 | 8 | 0 (0)| | | | | 61 | TABLE ACCESS BY INDEX ROWID | EXTFILTERTYPE | 1 | 10 | 1 (0)| 00:00:01 | | | | 62 | INDEX UNIQUE SCAN | EXTFILTERTYPE_PK | 1 | | 0 (0)| | | | | 63 | TABLE ACCESS BY INDEX ROWID | EXTFILTERDESC | 1 | 22 | 1 (0)| 00:00:01 | | | | 64 | INDEX UNIQUE SCAN | EXTFILTERDESC_PK | 1 | | 0 (0)| | | | ---------------------------------------------------------------------------------------------------------------------------------------- SQL_ID g58at1rrnjnur -------------------- -- getSelectProductsSQL() SELECT ce.catentry_id catentry_id_parent, ce.partnumber partnumber_parent, ced.NAME name_parent, cer.catentry_id_child catentry_id_child, ce2.partnumber partnumber_child, ced2.name name_child, ced2.longdescription, ced2.published, op.price, ced2.availabilitydate, ced2.available, cer.SEQUENCE seq_1, ecer.SEQUENCE seq_2, ce2.field2, ecgr.SEQUENCE relevance , ecgr.storeent_id storeent_id_1, ecgr2.SEQUENCE seq_3, ecgr2.storeent_id storeent_id_2 , filt.filter_id, filt.name filter_name, filt.value filter_value, o.ENDDATE FROM catgpenrel cgr LEFT OUTER JOIN EXTCATGPENSTORESEQ ecgr ON cgr.catalog_id = ecgr.catalog_id AND cgr.catgroup_id = ecgr.catgroup_id AND cgr.catentry_id = ecgr.catentry_id AND ecgr.sort_type = :"SYS_B_00" AND ecgr.storeent_id IN ( :1 ,:2 ) , catgpenrel cgr2 LEFT OUTER JOIN EXTCATGPENSTORESEQ ecgr2 ON cgr2.catalog_id = ecgr2.catalog_id AND cgr2.catgroup_id = ecgr2.catgroup_id AND cgr2.catentry_id = ecgr2.catentry_id AND ecgr2.sort_type = :"SYS_B_01" AND "ecgr2.storeent_id IN ( :3 ,:4 ) , catentry ce, catentry ce2 left outer join (SELECT cef.catentry_id, " "f.filter_id, fd.value, ft.name FROM EXTCATENTFILTER cef, EXTFILTERTYPE ft, EXTFILTER f, EXTFILTERDESC fd WHERE " "f.filter_id = cef.filter_id AND f.filtertype_id = ft.filtertype_id AND fd.filter_id = f.filter_id AND fd.language_id = :5 " ") filt ON filt.catentry_id = ce2.catentry_id, catentdesc ced, catentdesc ced2, offer o, " offerprice op, catentrel cer LEFT OUTER JOIN extcatentrel ecer ON cer.catentry_id_child = ecer.catentry_id_child AND cer.catentry_id_parent = ecer.catentry_id_parent AND ecer.language_id = :6 left outer join extcatentrydesc ecdesc on cer.catentry_id_child = ecdesc.catentry_id AND ecdesc.language_id = :7 WHERE exists(select :"SYS_B_02" from storecat sc where cgr.catalog_id = sc.catalog_id AND sc.storeent_id IN ( :8 ,:9 ) ) and exists(select :"SYS_B_03" from storecgrp scg where scg.catgroup_id = cgr.catgroup_id and scg.storeent_id IN ( :10 ,:11 ) ) AND cgr2.catgroup_id = cgr.catgroup_id and exists(select :"SYS_B_04" from catgrpdesc cgd where cgd.catgroup_id = cgr.catgroup_id AND cgd.language_id = ced.language_id AND cgd.published = :"SYS_B_05") and exists(select :"SYS_B_06" from extcatgrpdesc ecgd where ecgd.catgroup_id = cgr.catgroup_id AND ecgd.language_id = ced.language_id AND ecgd.published = :"SYS_B_07" ) AND cgr2.catgroup_id = cgr.catgroup_id AND ce.catentry_id = cer.catentry_id_parent AND cgr.catentry_id = ce.catentry_id AND cgr2.catentry_id= ce2.catentry_id AND ce.catenttype_id = :"SYS_B_08" AND ced.catentry_id = ce.catentry_id AND ced.published = :"SYS_B_09" AND ce2.catentry_id = cer.catentry_id_child AND ced2.catentry_id = ce2.catentry_id AND ced2.language_id = ced.language_id AND ced2.published = :"SYS_B_10" AND ce.markfordelete = :"SYS_B_11" AND ce2.markfordelete = :"SYS_B_12" AND o.catentry_id = cer.catentry_id_child AND o.precedence IN (:"SYS_B_13",:"SYS_B_14",:"SYS_B_15") AND o.tradeposcn_id = :12 AND o.published = :"SYS_B_16" AND (o.startdate <= current_timestamp or o.startdate is null) AND (o.enddate > current_timestamp or o.enddate is null) AND op.offer_id = o.offer_id AND cgr.catalog_id = :13 AND cgr2.catalog_id= cgr.catalog_id AND ced.language_id = :14 AND "cgr.catgroup_id = :15 ORDER BY ce.catentry_id, cer.catentry_id_child, ecgr.storeent_id desc, ecgr2.storeent_id desc" Plan hash value: 749532653 ---------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 38 (100)| | | | | 1 | SORT ORDER BY | | 1 | 566 | 38 (6)| 00:00:01 | | | | 2 | NESTED LOOPS OUTER | | 1 | 566 | 37 (3)| 00:00:01 | | | | 3 | NESTED LOOPS | | 1 | 485 | 32 (0)| 00:00:01 | | | | 4 | NESTED LOOPS OUTER | | 1 | 325 | 30 (0)| 00:00:01 | | | | 5 | NESTED LOOPS | | 1 | 285 | 28 (0)| 00:00:01 | | | | 6 | NESTED LOOPS | | 1 | 269 | 27 (0)| 00:00:01 | | | | 7 | NESTED LOOPS | | 1 | 248 | 25 (0)| 00:00:01 | | | | 8 | NESTED LOOPS | | 1 | 237 | 22 (0)| 00:00:01 | | | | 9 | NESTED LOOPS OUTER | | 1 | 215 | 20 (0)| 00:00:01 | | | | 10 | NESTED LOOPS | | 1 | 205 | 20 (0)| 00:00:01 | | | | 11 | NESTED LOOPS OUTER | | 1 | 160 | 17 (0)| 00:00:01 | | | | 12 | NESTED LOOPS | | 1 | 143 | 15 (0)| 00:00:01 | | | | 13 | NESTED LOOPS | | 1 | 127 | 11 (0)| 00:00:01 | | | | 14 | NESTED LOOPS OUTER | | 1 | 98 | 9 (0)| 00:00:01 | | | | 15 | NESTED LOOPS SEMI | | 1 | 58 | 7 (0)| 00:00:01 | | | | 16 | NESTED LOOPS | | 1 | 49 | 6 (0)| 00:00:01 | | | | 17 | NESTED LOOPS | | 1 | 33 | 4 (0)| 00:00:01 | | | | 18 | NESTED LOOPS | | 1 | 24 | 3 (0)| 00:00:01 | | | | 19 | TABLE ACCESS BY INDEX ROWID| CATGRPDESC | 1 | 12 | 2 (0)| 00:00:01 | | | | 20 | INDEX UNIQUE SCAN | SYS_C0030074 | 1 | | 1 (0)| 00:00:01 | | | | 21 | TABLE ACCESS BY INDEX ROWID| EXTCATGRPDESC | 1 | 12 | 1 (0)| 00:00:01 | | | | 22 | INDEX UNIQUE SCAN | PK_EXTCATGRPDESC | 1 | | 0 (0)| | | | | 23 | SORT UNIQUE | | 1 | 9 | 1 (0)| 00:00:01 | | | | 24 | INLIST ITERATOR | | | | | | | | | 25 | INDEX UNIQUE SCAN | SYS_C0031935 | 1 | 9 | 1 (0)| 00:00:01 | | | | 26 | INDEX RANGE SCAN | IND_CATGPENREL_2 | 1 | 16 | 2 (0)| 00:00:01 | | | | 27 | INLIST ITERATOR | | | | | | | | | 28 | INDEX UNIQUE SCAN | SYS_C0031941 | 1 | 9 | 1 (0)| 00:00:01 | | | | 29 | TABLE ACCESS BY INDEX ROWID | EXTCATGPENSTORESEQ | 1 | 40 | 2 (0)| 00:00:01 | | | | 30 | INDEX RANGE SCAN | EXTCATGPENSTORESEQ_IND | 1 | | 1 (0)| 00:00:01 | | | | 31 | TABLE ACCESS BY INDEX ROWID | CATENTRY | 1 | 29 | 2 (0)| 00:00:01 | | | | 32 | INDEX UNIQUE SCAN | SYS_C0030043 | 1 | | 1 (0)| 00:00:01 | | | | 33 | TABLE ACCESS BY INDEX ROWID | CATENTREL | 1 | 16 | 4 (0)| 00:00:01 | | | | 34 | INDEX RANGE SCAN | IND5556040 | 13 | | 1 (0)| 00:00:01 | | | | 35 | TABLE ACCESS BY INDEX ROWID | EXTCATENTREL | 1 | 17 | 2 (0)| 00:00:01 | | | | 36 | INDEX UNIQUE SCAN | EXTCATENTREL_PK | 1 | | 1 (0)| 00:00:01 | | | | 37 | TABLE ACCESS BY INDEX ROWID | OFFER | 1 | 45 | 3 (0)| 00:00:01 | | | | 38 | INDEX RANGE SCAN | I0000167 | 1 | | 2 (0)| 00:00:01 | | | | 39 | PARTITION LIST SINGLE | | 1 | 10 | 0 (0)| | KEY | KEY | | 40 | INDEX UNIQUE SCAN | EXTCATENTRYDESC_IDX1 | 1 | 10 | 0 (0)| | KEY | KEY | | 41 | INDEX RANGE SCAN | I0000304 | 1 | 22 | 2 (0)| 00:00:01 | | | | 42 | TABLE ACCESS BY INDEX ROWID | OFFERPRICE | 1 | 11 | 3 (0)| 00:00:01 | | | | 43 | INDEX RANGE SCAN | SYS_C0031126 | 1 | | 2 (0)| 00:00:01 | | | | 44 | TABLE ACCESS BY INDEX ROWID | CATENTRY | 1 | 21 | 2 (0)| 00:00:01 | | | | 45 | INDEX UNIQUE SCAN | SYS_C0030043 | 1 | | 1 (0)| 00:00:01 | | | | 46 | INDEX UNIQUE SCAN | SYS_C0030060 | 1 | 16 | 1 (0)| 00:00:01 | | | | 47 | TABLE ACCESS BY INDEX ROWID | EXTCATGPENSTORESEQ | 1 | 40 | 2 (0)| 00:00:01 | | | | 48 | INDEX RANGE SCAN | EXTCATGPENSTORESEQ_IND | 1 | | 1 (0)| 00:00:01 | | | | 49 | TABLE ACCESS BY INDEX ROWID | CATENTDESC | 1 | 160 | 2 (0)| 00:00:01 | | | | 50 | INDEX UNIQUE SCAN | SYS_C0030032 | 1 | | 1 (0)| 00:00:01 | | | | 51 | VIEW PUSHED PREDICATE | | 1 | 81 | 5 (20)| 00:00:01 | | | | 52 | NESTED LOOPS | | 1 | 47 | 5 (20)| 00:00:01 | | | | 53 | NESTED LOOPS | | 1 | 25 | 4 (25)| 00:00:01 | | | | 54 | NESTED LOOPS | | 1 | 15 | 3 (34)| 00:00:01 | | | | 55 | VIEW | index$_join$_012 | 295 | 2065 | 3 (34)| 00:00:01 | | | | 56 | HASH JOIN | | | | | | | | | 57 | INDEX FAST FULL SCAN | EXTFILTER_PK | 295 | 2065 | 1 (0)| 00:00:01 | | | | 58 | INDEX FAST FULL SCAN | EXTFILTER_UK | 295 | 2065 | 1 (0)| 00:00:01 | | | | 59 | INDEX UNIQUE SCAN | EXTCATENTFILTER_PK | 1 | 8 | 0 (0)| | | | | 60 | TABLE ACCESS BY INDEX ROWID | EXTFILTERTYPE | 1 | 10 | 1 (0)| 00:00:01 | | | | 61 | INDEX UNIQUE SCAN | EXTFILTERTYPE_PK | 1 | | 0 (0)| | | | | 62 | TABLE ACCESS BY INDEX ROWID | EXTFILTERDESC | 1 | 22 | 1 (0)| 00:00:01 | | | | 63 | INDEX UNIQUE SCAN | EXTFILTERDESC_PK | 1 | | 0 (0)| | | | ---------------------------------------------------------------------------------------------------------------------------------------- SQL_ID g58at1rrnjnur -------------------- -- getSelectProductsSQL() SELECT ce.catentry_id catentry_id_parent, ce.partnumber partnumber_parent, ced.NAME name_parent, cer.catentry_id_child catentry_id_child, ce2.partnumber partnumber_child, ced2.name name_child, ced2.longdescription, ced2.published, op.price, ced2.availabilitydate, ced2.available, cer.SEQUENCE seq_1, ecer.SEQUENCE seq_2, ce2.field2, ecgr.SEQUENCE relevance , ecgr.storeent_id storeent_id_1, ecgr2.SEQUENCE seq_3, ecgr2.storeent_id storeent_id_2 , filt.filter_id, filt.name filter_name, filt.value filter_value, o.ENDDATE FROM catgpenrel cgr LEFT OUTER JOIN EXTCATGPENSTORESEQ ecgr ON cgr.catalog_id = ecgr.catalog_id AND cgr.catgroup_id = ecgr.catgroup_id AND cgr.catentry_id = ecgr.catentry_id AND ecgr.sort_type = :"SYS_B_00" AND ecgr.storeent_id IN ( :1 ,:2 ) , catgpenrel cgr2 LEFT OUTER JOIN EXTCATGPENSTORESEQ ecgr2 ON cgr2.catalog_id = ecgr2.catalog_id AND cgr2.catgroup_id = ecgr2.catgroup_id AND cgr2.catentry_id = ecgr2.catentry_id AND ecgr2.sort_type = :"SYS_B_01" AND "ecgr2.storeent_id IN ( :3 ,:4 ) , catentry ce, catentry ce2 left outer join (SELECT cef.catentry_id, " "f.filter_id, fd.value, ft.name FROM EXTCATENTFILTER cef, EXTFILTERTYPE ft, EXTFILTER f, EXTFILTERDESC fd WHERE " "f.filter_id = cef.filter_id AND f.filtertype_id = ft.filtertype_id AND fd.filter_id = f.filter_id AND fd.language_id = :5 " ") filt ON filt.catentry_id = ce2.catentry_id, catentdesc ced, catentdesc ced2, offer o, " offerprice op, catentrel cer LEFT OUTER JOIN extcatentrel ecer ON cer.catentry_id_child = ecer.catentry_id_child AND cer.catentry_id_parent = ecer.catentry_id_parent AND ecer.language_id = :6 left outer join extcatentrydesc ecdesc on cer.catentry_id_child = ecdesc.catentry_id AND ecdesc.language_id = :7 WHERE exists(select :"SYS_B_02" from storecat sc where cgr.catalog_id = sc.catalog_id AND sc.storeent_id IN ( :8 ,:9 ) ) and exists(select :"SYS_B_03" from storecgrp scg where scg.catgroup_id = cgr.catgroup_id and scg.storeent_id IN ( :10 ,:11 ) ) AND cgr2.catgroup_id = cgr.catgroup_id and exists(select :"SYS_B_04" from catgrpdesc cgd where cgd.catgroup_id = cgr.catgroup_id AND cgd.language_id = ced.language_id AND cgd.published = :"SYS_B_05") and exists(select :"SYS_B_06" from extcatgrpdesc ecgd where ecgd.catgroup_id = cgr.catgroup_id AND ecgd.language_id = ced.language_id AND ecgd.published = :"SYS_B_07" ) AND cgr2.catgroup_id = cgr.catgroup_id AND ce.catentry_id = cer.catentry_id_parent AND cgr.catentry_id = ce.catentry_id AND cgr2.catentry_id= ce2.catentry_id AND ce.catenttype_id = :"SYS_B_08" AND ced.catentry_id = ce.catentry_id AND ced.published = :"SYS_B_09" AND ce2.catentry_id = cer.catentry_id_child AND ced2.catentry_id = ce2.catentry_id AND ced2.language_id = ced.language_id AND ced2.published = :"SYS_B_10" AND ce.markfordelete = :"SYS_B_11" AND ce2.markfordelete = :"SYS_B_12" AND o.catentry_id = cer.catentry_id_child AND o.precedence IN (:"SYS_B_13",:"SYS_B_14",:"SYS_B_15") AND o.tradeposcn_id = :12 AND o.published = :"SYS_B_16" AND (o.startdate <= current_timestamp or o.startdate is null) AND (o.enddate > current_timestamp or o.enddate is null) AND op.offer_id = o.offer_id AND cgr.catalog_id = :13 AND cgr2.catalog_id= cgr.catalog_id AND ced.language_id = :14 AND "cgr.catgroup_id = :15 ORDER BY ce.catentry_id, cer.catentry_id_child, ecgr.storeent_id desc, ecgr2.storeent_id desc" Plan hash value: 1406267120 ---------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 38 (100)| | | | | 1 | SORT ORDER BY | | 1 | 566 | 38 (6)| 00:00:01 | | | | 2 | NESTED LOOPS OUTER | | 1 | 566 | 37 (3)| 00:00:01 | | | | 3 | NESTED LOOPS | | 1 | 485 | 32 (0)| 00:00:01 | | | | 4 | NESTED LOOPS | | 1 | 463 | 30 (0)| 00:00:01 | | | | 5 | NESTED LOOPS OUTER | | 1 | 303 | 28 (0)| 00:00:01 | | | | 6 | NESTED LOOPS | | 1 | 263 | 26 (0)| 00:00:01 | | | | 7 | NESTED LOOPS | | 1 | 247 | 25 (0)| 00:00:01 | | | | 8 | NESTED LOOPS | | 1 | 226 | 23 (0)| 00:00:01 | | | | 9 | NESTED LOOPS OUTER | | 1 | 215 | 20 (0)| 00:00:01 | | | | 10 | NESTED LOOPS | | 1 | 205 | 20 (0)| 00:00:01 | | | | 11 | NESTED LOOPS OUTER | | 1 | 160 | 17 (0)| 00:00:01 | | | | 12 | NESTED LOOPS | | 1 | 143 | 15 (0)| 00:00:01 | | | | 13 | NESTED LOOPS | | 1 | 127 | 11 (0)| 00:00:01 | | | | 14 | NESTED LOOPS OUTER | | 1 | 98 | 9 (0)| 00:00:01 | | | | 15 | NESTED LOOPS SEMI | | 1 | 58 | 7 (0)| 00:00:01 | | | | 16 | NESTED LOOPS | | 1 | 49 | 6 (0)| 00:00:01 | | | | 17 | NESTED LOOPS | | 1 | 33 | 4 (0)| 00:00:01 | | | | 18 | NESTED LOOPS | | 1 | 24 | 3 (0)| 00:00:01 | | | | 19 | TABLE ACCESS BY INDEX ROWID| CATGRPDESC | 1 | 12 | 2 (0)| 00:00:01 | | | | 20 | INDEX UNIQUE SCAN | SYS_C0030074 | 1 | | 1 (0)| 00:00:01 | | | | 21 | TABLE ACCESS BY INDEX ROWID| EXTCATGRPDESC | 1 | 12 | 1 (0)| 00:00:01 | | | | 22 | INDEX UNIQUE SCAN | PK_EXTCATGRPDESC | 1 | | 0 (0)| | | | | 23 | SORT UNIQUE | | 1 | 9 | 1 (0)| 00:00:01 | | | | 24 | INLIST ITERATOR | | | | | | | | | 25 | INDEX UNIQUE SCAN | SYS_C0031935 | 1 | 9 | 1 (0)| 00:00:01 | | | | 26 | INDEX RANGE SCAN | IND_CATGPENREL_2 | 1 | 16 | 2 (0)| 00:00:01 | | | | 27 | INLIST ITERATOR | | | | | | | | | 28 | INDEX UNIQUE SCAN | SYS_C0031941 | 1 | 9 | 1 (0)| 00:00:01 | | | | 29 | TABLE ACCESS BY INDEX ROWID | EXTCATGPENSTORESEQ | 1 | 40 | 2 (0)| 00:00:01 | | | | 30 | INDEX RANGE SCAN | EXTCATGPENSTORESEQ_IND | 1 | | 1 (0)| 00:00:01 | | | | 31 | TABLE ACCESS BY INDEX ROWID | CATENTRY | 1 | 29 | 2 (0)| 00:00:01 | | | | 32 | INDEX UNIQUE SCAN | SYS_C0030043 | 1 | | 1 (0)| 00:00:01 | | | | 33 | TABLE ACCESS BY INDEX ROWID | CATENTREL | 1 | 16 | 4 (0)| 00:00:01 | | | | 34 | INDEX RANGE SCAN | IND5556040 | 13 | | 1 (0)| 00:00:01 | | | | 35 | TABLE ACCESS BY INDEX ROWID | EXTCATENTREL | 1 | 17 | 2 (0)| 00:00:01 | | | | 36 | INDEX UNIQUE SCAN | EXTCATENTREL_PK | 1 | | 1 (0)| 00:00:01 | | | | 37 | TABLE ACCESS BY INDEX ROWID | OFFER | 1 | 45 | 3 (0)| 00:00:01 | | | | 38 | INDEX RANGE SCAN | I0000167 | 1 | | 2 (0)| 00:00:01 | | | | 39 | PARTITION LIST SINGLE | | 1 | 10 | 0 (0)| | KEY | KEY | | 40 | INDEX UNIQUE SCAN | EXTCATENTRYDESC_IDX1 | 1 | 10 | 0 (0)| | KEY | KEY | | 41 | TABLE ACCESS BY INDEX ROWID | OFFERPRICE | 1 | 11 | 3 (0)| 00:00:01 | | | | 42 | INDEX RANGE SCAN | SYS_C0031126 | 1 | | 2 (0)| 00:00:01 | | | | 43 | TABLE ACCESS BY INDEX ROWID | CATENTRY | 1 | 21 | 2 (0)| 00:00:01 | | | | 44 | INDEX UNIQUE SCAN | SYS_C0030043 | 1 | | 1 (0)| 00:00:01 | | | | 45 | INDEX UNIQUE SCAN | SYS_C0030060 | 1 | 16 | 1 (0)| 00:00:01 | | | | 46 | TABLE ACCESS BY INDEX ROWID | EXTCATGPENSTORESEQ | 1 | 40 | 2 (0)| 00:00:01 | | | | 47 | INDEX RANGE SCAN | EXTCATGPENSTORESEQ_IND | 1 | | 1 (0)| 00:00:01 | | | | 48 | TABLE ACCESS BY INDEX ROWID | CATENTDESC | 1 | 160 | 2 (0)| 00:00:01 | | | | 49 | INDEX UNIQUE SCAN | SYS_C0030032 | 1 | | 1 (0)| 00:00:01 | | | | 50 | INDEX RANGE SCAN | I0000304 | 1 | 22 | 2 (0)| 00:00:01 | | | | 51 | VIEW PUSHED PREDICATE | | 1 | 81 | 5 (20)| 00:00:01 | | | | 52 | NESTED LOOPS | | 1 | 47 | 5 (20)| 00:00:01 | | | | 53 | NESTED LOOPS | | 1 | 25 | 4 (25)| 00:00:01 | | | | 54 | NESTED LOOPS | | 1 | 15 | 3 (34)| 00:00:01 | | | | 55 | VIEW | index$_join$_012 | 295 | 2065 | 3 (34)| 00:00:01 | | | | 56 | HASH JOIN | | | | | | | | | 57 | INDEX FAST FULL SCAN | EXTFILTER_PK | 295 | 2065 | 1 (0)| 00:00:01 | | | | 58 | INDEX FAST FULL SCAN | EXTFILTER_UK | 295 | 2065 | 1 (0)| 00:00:01 | | | | 59 | INDEX UNIQUE SCAN | EXTCATENTFILTER_PK | 1 | 8 | 0 (0)| | | | | 60 | TABLE ACCESS BY INDEX ROWID | EXTFILTERTYPE | 1 | 10 | 1 (0)| 00:00:01 | | | | 61 | INDEX UNIQUE SCAN | EXTFILTERTYPE_PK | 1 | | 0 (0)| | | | | 62 | TABLE ACCESS BY INDEX ROWID | EXTFILTERDESC | 1 | 22 | 1 (0)| 00:00:01 | | | | 63 | INDEX UNIQUE SCAN | EXTFILTERDESC_PK | 1 | | 0 (0)| | | | ---------------------------------------------------------------------------------------------------------------------------------------- SQL_ID g58at1rrnjnur -------------------- -- getSelectProductsSQL() SELECT ce.catentry_id catentry_id_parent, ce.partnumber partnumber_parent, ced.NAME name_parent, cer.catentry_id_child catentry_id_child, ce2.partnumber partnumber_child, ced2.name name_child, ced2.longdescription, ced2.published, op.price, ced2.availabilitydate, ced2.available, cer.SEQUENCE seq_1, ecer.SEQUENCE seq_2, ce2.field2, ecgr.SEQUENCE relevance , ecgr.storeent_id storeent_id_1, ecgr2.SEQUENCE seq_3, ecgr2.storeent_id storeent_id_2 , filt.filter_id, filt.name filter_name, filt.value filter_value, o.ENDDATE FROM catgpenrel cgr LEFT OUTER JOIN EXTCATGPENSTORESEQ ecgr ON cgr.catalog_id = ecgr.catalog_id AND cgr.catgroup_id = ecgr.catgroup_id AND cgr.catentry_id = ecgr.catentry_id AND ecgr.sort_type = :"SYS_B_00" AND ecgr.storeent_id IN ( :1 ,:2 ) , catgpenrel cgr2 LEFT OUTER JOIN EXTCATGPENSTORESEQ ecgr2 ON cgr2.catalog_id = ecgr2.catalog_id AND cgr2.catgroup_id = ecgr2.catgroup_id AND cgr2.catentry_id = ecgr2.catentry_id AND ecgr2.sort_type = :"SYS_B_01" AND "ecgr2.storeent_id IN ( :3 ,:4 ) , catentry ce, catentry ce2 left outer join (SELECT cef.catentry_id, " "f.filter_id, fd.value, ft.name FROM EXTCATENTFILTER cef, EXTFILTERTYPE ft, EXTFILTER f, EXTFILTERDESC fd WHERE " "f.filter_id = cef.filter_id AND f.filtertype_id = ft.filtertype_id AND fd.filter_id = f.filter_id AND fd.language_id = :5 " ") filt ON filt.catentry_id = ce2.catentry_id, catentdesc ced, catentdesc ced2, offer o, " offerprice op, catentrel cer LEFT OUTER JOIN extcatentrel ecer ON cer.catentry_id_child = ecer.catentry_id_child AND cer.catentry_id_parent = ecer.catentry_id_parent AND ecer.language_id = :6 left outer join extcatentrydesc ecdesc on cer.catentry_id_child = ecdesc.catentry_id AND ecdesc.language_id = :7 WHERE exists(select :"SYS_B_02" from storecat sc where cgr.catalog_id = sc.catalog_id AND sc.storeent_id IN ( :8 ,:9 ) ) and exists(select :"SYS_B_03" from storecgrp scg where scg.catgroup_id = cgr.catgroup_id and scg.storeent_id IN ( :10 ,:11 ) ) AND cgr2.catgroup_id = cgr.catgroup_id and exists(select :"SYS_B_04" from catgrpdesc cgd where cgd.catgroup_id = cgr.catgroup_id AND cgd.language_id = ced.language_id AND cgd.published = :"SYS_B_05") and exists(select :"SYS_B_06" from extcatgrpdesc ecgd where ecgd.catgroup_id = cgr.catgroup_id AND ecgd.language_id = ced.language_id AND ecgd.published = :"SYS_B_07" ) AND cgr2.catgroup_id = cgr.catgroup_id AND ce.catentry_id = cer.catentry_id_parent AND cgr.catentry_id = ce.catentry_id AND cgr2.catentry_id= ce2.catentry_id AND ce.catenttype_id = :"SYS_B_08" AND ced.catentry_id = ce.catentry_id AND ced.published = :"SYS_B_09" AND ce2.catentry_id = cer.catentry_id_child AND ced2.catentry_id = ce2.catentry_id AND ced2.language_id = ced.language_id AND ced2.published = :"SYS_B_10" AND ce.markfordelete = :"SYS_B_11" AND ce2.markfordelete = :"SYS_B_12" AND o.catentry_id = cer.catentry_id_child AND o.precedence IN (:"SYS_B_13",:"SYS_B_14",:"SYS_B_15") AND o.tradeposcn_id = :12 AND o.published = :"SYS_B_16" AND (o.startdate <= current_timestamp or o.startdate is null) AND (o.enddate > current_timestamp or o.enddate is null) AND op.offer_id = o.offer_id AND cgr.catalog_id = :13 AND cgr2.catalog_id= cgr.catalog_id AND ced.language_id = :14 AND "cgr.catgroup_id = :15 ORDER BY ce.catentry_id, cer.catentry_id_child, ecgr.storeent_id desc, ecgr2.storeent_id desc" Plan hash value: 1569385240 ---------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 52 (100)| | | | | 1 | SORT ORDER BY | | 1 | 566 | 52 (6)| 00:00:01 | | | | 2 | NESTED LOOPS OUTER | | 1 | 566 | 51 (4)| 00:00:01 | | | | 3 | NESTED LOOPS OUTER | | 1 | 485 | 46 (3)| 00:00:01 | | | | 4 | NESTED LOOPS | | 1 | 445 | 44 (3)| 00:00:01 | | | | 5 | NESTED LOOPS OUTER | | 1 | 434 | 41 (3)| 00:00:01 | | | | 6 | NESTED LOOPS | | 1 | 424 | 41 (3)| 00:00:01 | | | | 7 | NESTED LOOPS | | 1 | 402 | 39 (3)| 00:00:01 | | | | 8 | NESTED LOOPS | | 1 | 242 | 37 (3)| 00:00:01 | | | | 9 | NESTED LOOPS OUTER | | 1 | 197 | 34 (3)| 00:00:01 | | | | 10 | NESTED LOOPS OUTER | | 1 | 157 | 32 (4)| 00:00:01 | | | | 11 | NESTED LOOPS | | 1 | 140 | 30 (4)| 00:00:01 | | | | 12 | NESTED LOOPS | | 1 | 124 | 28 (4)| 00:00:01 | | | | 13 | NESTED LOOPS | | 1 | 103 | 26 (4)| 00:00:01 | | | | 14 | NESTED LOOPS SEMI | | 1 | 74 | 24 (5)| 00:00:01 | | | | 15 | MERGE JOIN | | 2 | 130 | 22 (5)| 00:00:01 | | | | 16 | NESTED LOOPS | | 1 | 49 | 6 (0)| 00:00:01 | | | | 17 | NESTED LOOPS | | 1 | 33 | 4 (0)| 00:00:01 | | | | 18 | NESTED LOOPS | | 1 | 24 | 3 (0)| 00:00:01 | | | | 19 | TABLE ACCESS BY INDEX ROWID| CATGRPDESC | 1 | 12 | 2 (0)| 00:00:01 | | | | 20 | INDEX UNIQUE SCAN | SYS_C0030074 | 1 | | 1 (0)| 00:00:01 | | | | 21 | TABLE ACCESS BY INDEX ROWID| EXTCATGRPDESC | 1 | 12 | 1 (0)| 00:00:01 | | | | 22 | INDEX UNIQUE SCAN | PK_EXTCATGRPDESC | 1 | | 0 (0)| | | | | 23 | SORT UNIQUE | | 1 | 9 | 1 (0)| 00:00:01 | | | | 24 | INLIST ITERATOR | | | | | | | | | 25 | INDEX UNIQUE SCAN | SYS_C0031935 | 1 | 9 | 1 (0)| 00:00:01 | | | | 26 | INDEX RANGE SCAN | IND_CATGPENREL_2 | 1 | 16 | 2 (0)| 00:00:01 | | | | 27 | SORT JOIN | | 2119 | 33904 | 16 (7)| 00:00:01 | | | | 28 | INDEX RANGE SCAN | IND_CATGPENREL_2 | 2119 | 33904 | 15 (0)| 00:00:01 | | | | 29 | INLIST ITERATOR | | | | | | | | | 30 | INDEX UNIQUE SCAN | SYS_C0031941 | 1 | 9 | 1 (0)| 00:00:01 | | | | 31 | TABLE ACCESS BY INDEX ROWID | CATENTRY | 1 | 29 | 2 (0)| 00:00:01 | | | | 32 | INDEX UNIQUE SCAN | SYS_C0030043 | 1 | | 1 (0)| 00:00:01 | | | | 33 | TABLE ACCESS BY INDEX ROWID | CATENTRY | 1 | 21 | 2 (0)| 00:00:01 | | | | 34 | INDEX UNIQUE SCAN | SYS_C0030043 | 1 | | 1 (0)| 00:00:01 | | | | 35 | TABLE ACCESS BY INDEX ROWID | CATENTREL | 1 | 16 | 2 (0)| 00:00:01 | | | | 36 | INDEX RANGE SCAN | IND5556045 | 1 | | 1 (0)| 00:00:01 | | | | 37 | TABLE ACCESS BY INDEX ROWID | EXTCATENTREL | 1 | 17 | 2 (0)| 00:00:01 | | | | 38 | INDEX UNIQUE SCAN | EXTCATENTREL_PK | 1 | | 1 (0)| 00:00:01 | | | | 39 | TABLE ACCESS BY INDEX ROWID | EXTCATGPENSTORESEQ | 1 | 40 | 2 (0)| 00:00:01 | | | | 40 | INDEX RANGE SCAN | EXTCATGPENSTORESEQ_IND | 1 | | 1 (0)| 00:00:01 | | | | 41 | TABLE ACCESS BY INDEX ROWID | OFFER | 1 | 45 | 3 (0)| 00:00:01 | | | | 42 | INDEX RANGE SCAN | I0000167 | 1 | | 2 (0)| 00:00:01 | | | | 43 | TABLE ACCESS BY INDEX ROWID | CATENTDESC | 1 | 160 | 2 (0)| 00:00:01 | | | | 44 | INDEX UNIQUE SCAN | SYS_C0030032 | 1 | | 1 (0)| 00:00:01 | | | | 45 | INDEX RANGE SCAN | I0000304 | 1 | 22 | 2 (0)| 00:00:01 | | | | 46 | PARTITION LIST SINGLE | | 1 | 10 | 0 (0)| | KEY | KEY | | 47 | INDEX UNIQUE SCAN | EXTCATENTRYDESC_IDX1 | 1 | 10 | 0 (0)| | KEY | KEY | | 48 | TABLE ACCESS BY INDEX ROWID | OFFERPRICE | 1 | 11 | 3 (0)| 00:00:01 | | | | 49 | INDEX RANGE SCAN | SYS_C0031126 | 1 | | 2 (0)| 00:00:01 | | | | 50 | TABLE ACCESS BY INDEX ROWID | EXTCATGPENSTORESEQ | 1 | 40 | 2 (0)| 00:00:01 | | | | 51 | INDEX RANGE SCAN | EXTCATGPENSTORESEQ_IND | 1 | | 1 (0)| 00:00:01 | | | | 52 | VIEW PUSHED PREDICATE | | 1 | 81 | 5 (20)| 00:00:01 | | | | 53 | NESTED LOOPS | | 1 | 47 | 5 (20)| 00:00:01 | | | | 54 | NESTED LOOPS | | 1 | 25 | 4 (25)| 00:00:01 | | | | 55 | NESTED LOOPS | | 1 | 15 | 3 (34)| 00:00:01 | | | | 56 | VIEW | index$_join$_012 | 295 | 2065 | 3 (34)| 00:00:01 | | | | 57 | HASH JOIN | | | | | | | | | 58 | INDEX FAST FULL SCAN | EXTFILTER_PK | 295 | 2065 | 1 (0)| 00:00:01 | | | | 59 | INDEX FAST FULL SCAN | EXTFILTER_UK | 295 | 2065 | 1 (0)| 00:00:01 | | | | 60 | INDEX UNIQUE SCAN | EXTCATENTFILTER_PK | 1 | 8 | 0 (0)| | | | | 61 | TABLE ACCESS BY INDEX ROWID | EXTFILTERTYPE | 1 | 10 | 1 (0)| 00:00:01 | | | | 62 | INDEX UNIQUE SCAN | EXTFILTERTYPE_PK | 1 | | 0 (0)| | | | | 63 | TABLE ACCESS BY INDEX ROWID | EXTFILTERDESC | 1 | 22 | 1 (0)| 00:00:01 | | | | 64 | INDEX UNIQUE SCAN | EXTFILTERDESC_PK | 1 | | 0 (0)| | | | ---------------------------------------------------------------------------------------------------------------------------------------- Note ----- - SQL profile "PRO_g58at1rrnjnur" used for this statement SQL_ID g58at1rrnjnur -------------------- -- getSelectProductsSQL() SELECT ce.catentry_id catentry_id_parent, ce.partnumber partnumber_parent, ced.NAME name_parent, cer.catentry_id_child catentry_id_child, ce2.partnumber partnumber_child, ced2.name name_child, ced2.longdescription, ced2.published, op.price, ced2.availabilitydate, ced2.available, cer.SEQUENCE seq_1, ecer.SEQUENCE seq_2, ce2.field2, ecgr.SEQUENCE relevance , ecgr.storeent_id storeent_id_1, ecgr2.SEQUENCE seq_3, ecgr2.storeent_id storeent_id_2 , filt.filter_id, filt.name filter_name, filt.value filter_value, o.ENDDATE FROM catgpenrel cgr LEFT OUTER JOIN EXTCATGPENSTORESEQ ecgr ON cgr.catalog_id = ecgr.catalog_id AND cgr.catgroup_id = ecgr.catgroup_id AND cgr.catentry_id = ecgr.catentry_id AND ecgr.sort_type = :"SYS_B_00" AND ecgr.storeent_id IN ( :1 ,:2 ) , catgpenrel cgr2 LEFT OUTER JOIN EXTCATGPENSTORESEQ ecgr2 ON cgr2.catalog_id = ecgr2.catalog_id AND cgr2.catgroup_id = ecgr2.catgroup_id AND cgr2.catentry_id = ecgr2.catentry_id AND ecgr2.sort_type = :"SYS_B_01" AND "ecgr2.storeent_id IN ( :3 ,:4 ) , catentry ce, catentry ce2 left outer join (SELECT cef.catentry_id, " "f.filter_id, fd.value, ft.name FROM EXTCATENTFILTER cef, EXTFILTERTYPE ft, EXTFILTER f, EXTFILTERDESC fd WHERE " "f.filter_id = cef.filter_id AND f.filtertype_id = ft.filtertype_id AND fd.filter_id = f.filter_id AND fd.language_id = :5 " ") filt ON filt.catentry_id = ce2.catentry_id, catentdesc ced, catentdesc ced2, offer o, " offerprice op, catentrel cer LEFT OUTER JOIN extcatentrel ecer ON cer.catentry_id_child = ecer.catentry_id_child AND cer.catentry_id_parent = ecer.catentry_id_parent AND ecer.language_id = :6 left outer join extcatentrydesc ecdesc on cer.catentry_id_child = ecdesc.catentry_id AND ecdesc.language_id = :7 WHERE exists(select :"SYS_B_02" from storecat sc where cgr.catalog_id = sc.catalog_id AND sc.storeent_id IN ( :8 ,:9 ) ) and exists(select :"SYS_B_03" from storecgrp scg where scg.catgroup_id = cgr.catgroup_id and scg.storeent_id IN ( :10 ,:11 ) ) AND cgr2.catgroup_id = cgr.catgroup_id and exists(select :"SYS_B_04" from catgrpdesc cgd where cgd.catgroup_id = cgr.catgroup_id AND cgd.language_id = ced.language_id AND cgd.published = :"SYS_B_05") and exists(select :"SYS_B_06" from extcatgrpdesc ecgd where ecgd.catgroup_id = cgr.catgroup_id AND ecgd.language_id = ced.language_id AND ecgd.published = :"SYS_B_07" ) AND cgr2.catgroup_id = cgr.catgroup_id AND ce.catentry_id = cer.catentry_id_parent AND cgr.catentry_id = ce.catentry_id AND cgr2.catentry_id= ce2.catentry_id AND ce.catenttype_id = :"SYS_B_08" AND ced.catentry_id = ce.catentry_id AND ced.published = :"SYS_B_09" AND ce2.catentry_id = cer.catentry_id_child AND ced2.catentry_id = ce2.catentry_id AND ced2.language_id = ced.language_id AND ced2.published = :"SYS_B_10" AND ce.markfordelete = :"SYS_B_11" AND ce2.markfordelete = :"SYS_B_12" AND o.catentry_id = cer.catentry_id_child AND o.precedence IN (:"SYS_B_13",:"SYS_B_14",:"SYS_B_15") AND o.tradeposcn_id = :12 AND o.published = :"SYS_B_16" AND (o.startdate <= current_timestamp or o.startdate is null) AND (o.enddate > current_timestamp or o.enddate is null) AND op.offer_id = o.offer_id AND cgr.catalog_id = :13 AND cgr2.catalog_id= cgr.catalog_id AND ced.language_id = :14 AND "cgr.catgroup_id = :15 ORDER BY ce.catentry_id, cer.catentry_id_child, ecgr.storeent_id desc, ecgr2.storeent_id desc" Plan hash value: 2099019875 ---------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 37 (100)| | | | | 1 | SORT ORDER BY | | 1 | 566 | 37 (6)| 00:00:01 | | | | 2 | NESTED LOOPS OUTER | | 1 | 566 | 36 (3)| 00:00:01 | | | | 3 | NESTED LOOPS OUTER | | 1 | 485 | 31 (0)| 00:00:01 | | | | 4 | NESTED LOOPS | | 1 | 445 | 29 (0)| 00:00:01 | | | | 5 | NESTED LOOPS OUTER | | 1 | 434 | 26 (0)| 00:00:01 | | | | 6 | NESTED LOOPS | | 1 | 424 | 26 (0)| 00:00:01 | | | | 7 | NESTED LOOPS | | 1 | 402 | 24 (0)| 00:00:01 | | | | 8 | NESTED LOOPS OUTER | | 1 | 242 | 22 (0)| 00:00:01 | | | | 9 | NESTED LOOPS | | 1 | 202 | 20 (0)| 00:00:01 | | | | 10 | NESTED LOOPS OUTER | | 1 | 157 | 17 (0)| 00:00:01 | | | | 11 | NESTED LOOPS | | 1 | 140 | 15 (0)| 00:00:01 | | | | 12 | NESTED LOOPS | | 1 | 124 | 13 (0)| 00:00:01 | | | | 13 | NESTED LOOPS | | 1 | 103 | 11 (0)| 00:00:01 | | | | 14 | NESTED LOOPS SEMI | | 1 | 74 | 9 (0)| 00:00:01 | | | | 15 | MERGE JOIN CARTESIAN | | 1 | 65 | 8 (0)| 00:00:01 | | | | 16 | NESTED LOOPS | | 1 | 49 | 6 (0)| 00:00:01 | | | | 17 | NESTED LOOPS | | 1 | 33 | 4 (0)| 00:00:01 | | | | 18 | NESTED LOOPS | | 1 | 24 | 3 (0)| 00:00:01 | | | | 19 | TABLE ACCESS BY INDEX ROWID| CATGRPDESC | 1 | 12 | 2 (0)| 00:00:01 | | | | 20 | INDEX UNIQUE SCAN | SYS_C0030074 | 1 | | 1 (0)| 00:00:01 | | | | 21 | TABLE ACCESS BY INDEX ROWID| EXTCATGRPDESC | 1 | 12 | 1 (0)| 00:00:01 | | | | 22 | INDEX UNIQUE SCAN | PK_EXTCATGRPDESC | 1 | | 0 (0)| | | | | 23 | SORT UNIQUE | | 1 | 9 | 1 (0)| 00:00:01 | | | | 24 | INLIST ITERATOR | | | | | | | | | 25 | INDEX UNIQUE SCAN | SYS_C0031935 | 1 | 9 | 1 (0)| 00:00:01 | | | | 26 | INDEX RANGE SCAN | IND_CATGPENREL_2 | 1 | 16 | 2 (0)| 00:00:01 | | | | 27 | BUFFER SORT | | 67 | 1072 | 6 (0)| 00:00:01 | | | | 28 | INDEX RANGE SCAN | IND_CATGPENREL_2 | 67 | 1072 | 2 (0)| 00:00:01 | | | | 29 | INLIST ITERATOR | | | | | | | | | 30 | INDEX UNIQUE SCAN | SYS_C0031941 | 1 | 9 | 1 (0)| 00:00:01 | | | | 31 | TABLE ACCESS BY INDEX ROWID | CATENTRY | 1 | 29 | 2 (0)| 00:00:01 | | | | 32 | INDEX UNIQUE SCAN | SYS_C0030043 | 1 | | 1 (0)| 00:00:01 | | | | 33 | TABLE ACCESS BY INDEX ROWID | CATENTRY | 1 | 21 | 2 (0)| 00:00:01 | | | | 34 | INDEX UNIQUE SCAN | SYS_C0030043 | 1 | | 1 (0)| 00:00:01 | | | | 35 | TABLE ACCESS BY INDEX ROWID | CATENTREL | 1 | 16 | 2 (0)| 00:00:01 | | | | 36 | INDEX RANGE SCAN | IND5556045 | 1 | | 1 (0)| 00:00:01 | | | | 37 | TABLE ACCESS BY INDEX ROWID | EXTCATENTREL | 1 | 17 | 2 (0)| 00:00:01 | | | | 38 | INDEX UNIQUE SCAN | EXTCATENTREL_PK | 1 | | 1 (0)| 00:00:01 | | | | 39 | TABLE ACCESS BY INDEX ROWID | OFFER | 1 | 45 | 3 (0)| 00:00:01 | | | | 40 | INDEX RANGE SCAN | I0000167 | 1 | | 2 (0)| 00:00:01 | | | | 41 | TABLE ACCESS BY INDEX ROWID | EXTCATGPENSTORESEQ | 1 | 40 | 2 (0)| 00:00:01 | | | | 42 | INDEX RANGE SCAN | EXTCATGPENSTORESEQ_IND | 1 | | 1 (0)| 00:00:01 | | | | 43 | TABLE ACCESS BY INDEX ROWID | CATENTDESC | 1 | 160 | 2 (0)| 00:00:01 | | | | 44 | INDEX UNIQUE SCAN | SYS_C0030032 | 1 | | 1 (0)| 00:00:01 | | | | 45 | INDEX RANGE SCAN | I0000304 | 1 | 22 | 2 (0)| 00:00:01 | | | | 46 | PARTITION LIST SINGLE | | 1 | 10 | 0 (0)| | KEY | KEY | | 47 | INDEX UNIQUE SCAN | EXTCATENTRYDESC_IDX1 | 1 | 10 | 0 (0)| | KEY | KEY | | 48 | TABLE ACCESS BY INDEX ROWID | OFFERPRICE | 1 | 11 | 3 (0)| 00:00:01 | | | | 49 | INDEX RANGE SCAN | SYS_C0031126 | 1 | | 2 (0)| 00:00:01 | | | | 50 | TABLE ACCESS BY INDEX ROWID | EXTCATGPENSTORESEQ | 1 | 40 | 2 (0)| 00:00:01 | | | | 51 | INDEX RANGE SCAN | EXTCATGPENSTORESEQ_IND | 1 | | 1 (0)| 00:00:01 | | | | 52 | VIEW PUSHED PREDICATE | | 1 | 81 | 5 (20)| 00:00:01 | | | | 53 | NESTED LOOPS | | 1 | 47 | 5 (20)| 00:00:01 | | | | 54 | NESTED LOOPS | | 1 | 25 | 4 (25)| 00:00:01 | | | | 55 | NESTED LOOPS | | 1 | 15 | 3 (34)| 00:00:01 | | | | 56 | VIEW | index$_join$_012 | 295 | 2065 | 3 (34)| 00:00:01 | | | | 57 | HASH JOIN | | | | | | | | | 58 | INDEX FAST FULL SCAN | EXTFILTER_PK | 295 | 2065 | 1 (0)| 00:00:01 | | | | 59 | INDEX FAST FULL SCAN | EXTFILTER_UK | 295 | 2065 | 1 (0)| 00:00:01 | | | | 60 | INDEX UNIQUE SCAN | EXTCATENTFILTER_PK | 1 | 8 | 0 (0)| | | | | 61 | TABLE ACCESS BY INDEX ROWID | EXTFILTERTYPE | 1 | 10 | 1 (0)| 00:00:01 | | | | 62 | INDEX UNIQUE SCAN | EXTFILTERTYPE_PK | 1 | | 0 (0)| | | | | 63 | TABLE ACCESS BY INDEX ROWID | EXTFILTERDESC | 1 | 22 | 1 (0)| 00:00:01 | | | | 64 | INDEX UNIQUE SCAN | EXTFILTERDESC_PK | 1 | | 0 (0)| | | | ---------------------------------------------------------------------------------------------------------------------------------------- SQL_ID g58at1rrnjnur -------------------- -- getSelectProductsSQL() SELECT ce.catentry_id catentry_id_parent, ce.partnumber partnumber_parent, ced.NAME name_parent, cer.catentry_id_child catentry_id_child, ce2.partnumber partnumber_child, ced2.name name_child, ced2.longdescription, ced2.published, op.price, ced2.availabilitydate, ced2.available, cer.SEQUENCE seq_1, ecer.SEQUENCE seq_2, ce2.field2, ecgr.SEQUENCE relevance , ecgr.storeent_id storeent_id_1, ecgr2.SEQUENCE seq_3, ecgr2.storeent_id storeent_id_2 , filt.filter_id, filt.name filter_name, filt.value filter_value, o.ENDDATE FROM catgpenrel cgr LEFT OUTER JOIN EXTCATGPENSTORESEQ ecgr ON cgr.catalog_id = ecgr.catalog_id AND cgr.catgroup_id = ecgr.catgroup_id AND cgr.catentry_id = ecgr.catentry_id AND ecgr.sort_type = :"SYS_B_00" AND ecgr.storeent_id IN ( :1 ,:2 ) , catgpenrel cgr2 LEFT OUTER JOIN EXTCATGPENSTORESEQ ecgr2 ON cgr2.catalog_id = ecgr2.catalog_id AND cgr2.catgroup_id = ecgr2.catgroup_id AND cgr2.catentry_id = ecgr2.catentry_id AND ecgr2.sort_type = :"SYS_B_01" AND "ecgr2.storeent_id IN ( :3 ,:4 ) , catentry ce, catentry ce2 left outer join (SELECT cef.catentry_id, " "f.filter_id, fd.value, ft.name FROM EXTCATENTFILTER cef, EXTFILTERTYPE ft, EXTFILTER f, EXTFILTERDESC fd WHERE " "f.filter_id = cef.filter_id AND f.filtertype_id = ft.filtertype_id AND fd.filter_id = f.filter_id AND fd.language_id = :5 " ") filt ON filt.catentry_id = ce2.catentry_id, catentdesc ced, catentdesc ced2, offer o, " offerprice op, catentrel cer LEFT OUTER JOIN extcatentrel ecer ON cer.catentry_id_child = ecer.catentry_id_child AND cer.catentry_id_parent = ecer.catentry_id_parent AND ecer.language_id = :6 left outer join extcatentrydesc ecdesc on cer.catentry_id_child = ecdesc.catentry_id AND ecdesc.language_id = :7 WHERE exists(select :"SYS_B_02" from storecat sc where cgr.catalog_id = sc.catalog_id AND sc.storeent_id IN ( :8 ,:9 ) ) and exists(select :"SYS_B_03" from storecgrp scg where scg.catgroup_id = cgr.catgroup_id and scg.storeent_id IN ( :10 ,:11 ) ) AND cgr2.catgroup_id = cgr.catgroup_id and exists(select :"SYS_B_04" from catgrpdesc cgd where cgd.catgroup_id = cgr.catgroup_id AND cgd.language_id = ced.language_id AND cgd.published = :"SYS_B_05") and exists(select :"SYS_B_06" from extcatgrpdesc ecgd where ecgd.catgroup_id = cgr.catgroup_id AND ecgd.language_id = ced.language_id AND ecgd.published = :"SYS_B_07" ) AND cgr2.catgroup_id = cgr.catgroup_id AND ce.catentry_id = cer.catentry_id_parent AND cgr.catentry_id = ce.catentry_id AND cgr2.catentry_id= ce2.catentry_id AND ce.catenttype_id = :"SYS_B_08" AND ced.catentry_id = ce.catentry_id AND ced.published = :"SYS_B_09" AND ce2.catentry_id = cer.catentry_id_child AND ced2.catentry_id = ce2.catentry_id AND ced2.language_id = ced.language_id AND ced2.published = :"SYS_B_10" AND ce.markfordelete = :"SYS_B_11" AND ce2.markfordelete = :"SYS_B_12" AND o.catentry_id = cer.catentry_id_child AND o.precedence IN (:"SYS_B_13",:"SYS_B_14",:"SYS_B_15") AND o.tradeposcn_id = :12 AND o.published = :"SYS_B_16" AND (o.startdate <= current_timestamp or o.startdate is null) AND (o.enddate > current_timestamp or o.enddate is null) AND op.offer_id = o.offer_id AND cgr.catalog_id = :13 AND cgr2.catalog_id= cgr.catalog_id AND ced.language_id = :14 AND "cgr.catgroup_id = :15 ORDER BY ce.catentry_id, cer.catentry_id_child, ecgr.storeent_id desc, ecgr2.storeent_id desc" Plan hash value: 2455530056 ---------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 37 (100)| | | | | 1 | SORT ORDER BY | | 1 | 566 | 37 (6)| 00:00:01 | | | | 2 | NESTED LOOPS OUTER | | 1 | 566 | 36 (3)| 00:00:01 | | | | 3 | NESTED LOOPS OUTER | | 1 | 485 | 31 (0)| 00:00:01 | | | | 4 | NESTED LOOPS | | 1 | 445 | 29 (0)| 00:00:01 | | | | 5 | NESTED LOOPS OUTER | | 1 | 434 | 26 (0)| 00:00:01 | | | | 6 | NESTED LOOPS | | 1 | 424 | 26 (0)| 00:00:01 | | | | 7 | NESTED LOOPS | | 1 | 402 | 24 (0)| 00:00:01 | | | | 8 | NESTED LOOPS | | 1 | 242 | 22 (0)| 00:00:01 | | | | 9 | NESTED LOOPS OUTER | | 1 | 197 | 19 (0)| 00:00:01 | | | | 10 | NESTED LOOPS OUTER | | 1 | 157 | 17 (0)| 00:00:01 | | | | 11 | NESTED LOOPS | | 1 | 140 | 15 (0)| 00:00:01 | | | | 12 | NESTED LOOPS | | 1 | 124 | 13 (0)| 00:00:01 | | | | 13 | NESTED LOOPS | | 1 | 103 | 11 (0)| 00:00:01 | | | | 14 | NESTED LOOPS SEMI | | 1 | 74 | 9 (0)| 00:00:01 | | | | 15 | MERGE JOIN CARTESIAN | | 1 | 65 | 8 (0)| 00:00:01 | | | | 16 | NESTED LOOPS | | 1 | 49 | 6 (0)| 00:00:01 | | | | 17 | NESTED LOOPS | | 1 | 33 | 4 (0)| 00:00:01 | | | | 18 | NESTED LOOPS | | 1 | 24 | 3 (0)| 00:00:01 | | | | 19 | TABLE ACCESS BY INDEX ROWID| CATGRPDESC | 1 | 12 | 2 (0)| 00:00:01 | | | | 20 | INDEX UNIQUE SCAN | SYS_C0030074 | 1 | | 1 (0)| 00:00:01 | | | | 21 | TABLE ACCESS BY INDEX ROWID| EXTCATGRPDESC | 1 | 12 | 1 (0)| 00:00:01 | | | | 22 | INDEX UNIQUE SCAN | PK_EXTCATGRPDESC | 1 | | 0 (0)| | | | | 23 | SORT UNIQUE | | 1 | 9 | 1 (0)| 00:00:01 | | | | 24 | INLIST ITERATOR | | | | | | | | | 25 | INDEX UNIQUE SCAN | SYS_C0031935 | 1 | 9 | 1 (0)| 00:00:01 | | | | 26 | INDEX RANGE SCAN | IND_CATGPENREL_2 | 1 | 16 | 2 (0)| 00:00:01 | | | | 27 | BUFFER SORT | | 65 | 1040 | 6 (0)| 00:00:01 | | | | 28 | INDEX RANGE SCAN | IND_CATGPENREL_2 | 65 | 1040 | 2 (0)| 00:00:01 | | | | 29 | INLIST ITERATOR | | | | | | | | | 30 | INDEX UNIQUE SCAN | SYS_C0031941 | 1 | 9 | 1 (0)| 00:00:01 | | | | 31 | TABLE ACCESS BY INDEX ROWID | CATENTRY | 1 | 29 | 2 (0)| 00:00:01 | | | | 32 | INDEX UNIQUE SCAN | SYS_C0030043 | 1 | | 1 (0)| 00:00:01 | | | | 33 | TABLE ACCESS BY INDEX ROWID | CATENTRY | 1 | 21 | 2 (0)| 00:00:01 | | | | 34 | INDEX UNIQUE SCAN | SYS_C0030043 | 1 | | 1 (0)| 00:00:01 | | | | 35 | TABLE ACCESS BY INDEX ROWID | CATENTREL | 1 | 16 | 2 (0)| 00:00:01 | | | | 36 | INDEX RANGE SCAN | IND5556045 | 1 | | 1 (0)| 00:00:01 | | | | 37 | TABLE ACCESS BY INDEX ROWID | EXTCATENTREL | 1 | 17 | 2 (0)| 00:00:01 | | | | 38 | INDEX UNIQUE SCAN | EXTCATENTREL_PK | 1 | | 1 (0)| 00:00:01 | | | | 39 | TABLE ACCESS BY INDEX ROWID | EXTCATGPENSTORESEQ | 1 | 40 | 2 (0)| 00:00:01 | | | | 40 | INDEX RANGE SCAN | EXTCATGPENSTORESEQ_IND | 1 | | 1 (0)| 00:00:01 | | | | 41 | TABLE ACCESS BY INDEX ROWID | OFFER | 1 | 45 | 3 (0)| 00:00:01 | | | | 42 | INDEX RANGE SCAN | I0000167 | 1 | | 2 (0)| 00:00:01 | | | | 43 | TABLE ACCESS BY INDEX ROWID | CATENTDESC | 1 | 160 | 2 (0)| 00:00:01 | | | | 44 | INDEX UNIQUE SCAN | SYS_C0030032 | 1 | | 1 (0)| 00:00:01 | | | | 45 | INDEX RANGE SCAN | I0000304 | 1 | 22 | 2 (0)| 00:00:01 | | | | 46 | PARTITION LIST SINGLE | | 1 | 10 | 0 (0)| | KEY | KEY | | 47 | INDEX UNIQUE SCAN | EXTCATENTRYDESC_IDX1 | 1 | 10 | 0 (0)| | KEY | KEY | | 48 | TABLE ACCESS BY INDEX ROWID | OFFERPRICE | 1 | 11 | 3 (0)| 00:00:01 | | | | 49 | INDEX RANGE SCAN | SYS_C0031126 | 1 | | 2 (0)| 00:00:01 | | | | 50 | TABLE ACCESS BY INDEX ROWID | EXTCATGPENSTORESEQ | 1 | 40 | 2 (0)| 00:00:01 | | | | 51 | INDEX RANGE SCAN | EXTCATGPENSTORESEQ_IND | 1 | | 1 (0)| 00:00:01 | | | | 52 | VIEW PUSHED PREDICATE | | 1 | 81 | 5 (20)| 00:00:01 | | | | 53 | NESTED LOOPS | | 1 | 47 | 5 (20)| 00:00:01 | | | | 54 | NESTED LOOPS | | 1 | 25 | 4 (25)| 00:00:01 | | | | 55 | NESTED LOOPS | | 1 | 15 | 3 (34)| 00:00:01 | | | | 56 | VIEW | index$_join$_012 | 295 | 2065 | 3 (34)| 00:00:01 | | | | 57 | HASH JOIN | | | | | | | | | 58 | INDEX FAST FULL SCAN | EXTFILTER_PK | 295 | 2065 | 1 (0)| 00:00:01 | | | | 59 | INDEX FAST FULL SCAN | EXTFILTER_UK | 295 | 2065 | 1 (0)| 00:00:01 | | | | 60 | INDEX UNIQUE SCAN | EXTCATENTFILTER_PK | 1 | 8 | 0 (0)| | | | | 61 | TABLE ACCESS BY INDEX ROWID | EXTFILTERTYPE | 1 | 10 | 1 (0)| 00:00:01 | | | | 62 | INDEX UNIQUE SCAN | EXTFILTERTYPE_PK | 1 | | 0 (0)| | | | | 63 | TABLE ACCESS BY INDEX ROWID | EXTFILTERDESC | 1 | 22 | 1 (0)| 00:00:01 | | | | 64 | INDEX UNIQUE SCAN | EXTFILTERDESC_PK | 1 | | 0 (0)| | | | ---------------------------------------------------------------------------------------------------------------------------------------- SQL_ID g58at1rrnjnur -------------------- -- getSelectProductsSQL() SELECT ce.catentry_id catentry_id_parent, ce.partnumber partnumber_parent, ced.NAME name_parent, cer.catentry_id_child catentry_id_child, ce2.partnumber partnumber_child, ced2.name name_child, ced2.longdescription, ced2.published, op.price, ced2.availabilitydate, ced2.available, cer.SEQUENCE seq_1, ecer.SEQUENCE seq_2, ce2.field2, ecgr.SEQUENCE relevance , ecgr.storeent_id storeent_id_1, ecgr2.SEQUENCE seq_3, ecgr2.storeent_id storeent_id_2 , filt.filter_id, filt.name filter_name, filt.value filter_value, o.ENDDATE FROM catgpenrel cgr LEFT OUTER JOIN EXTCATGPENSTORESEQ ecgr ON cgr.catalog_id = ecgr.catalog_id AND cgr.catgroup_id = ecgr.catgroup_id AND cgr.catentry_id = ecgr.catentry_id AND ecgr.sort_type = :"SYS_B_00" AND ecgr.storeent_id IN ( :1 ,:2 ) , catgpenrel cgr2 LEFT OUTER JOIN EXTCATGPENSTORESEQ ecgr2 ON cgr2.catalog_id = ecgr2.catalog_id AND cgr2.catgroup_id = ecgr2.catgroup_id AND cgr2.catentry_id = ecgr2.catentry_id AND ecgr2.sort_type = :"SYS_B_01" AND "ecgr2.storeent_id IN ( :3 ,:4 ) , catentry ce, catentry ce2 left outer join (SELECT cef.catentry_id, " "f.filter_id, fd.value, ft.name FROM EXTCATENTFILTER cef, EXTFILTERTYPE ft, EXTFILTER f, EXTFILTERDESC fd WHERE " "f.filter_id = cef.filter_id AND f.filtertype_id = ft.filtertype_id AND fd.filter_id = f.filter_id AND fd.language_id = :5 " ") filt ON filt.catentry_id = ce2.catentry_id, catentdesc ced, catentdesc ced2, offer o, " offerprice op, catentrel cer LEFT OUTER JOIN extcatentrel ecer ON cer.catentry_id_child = ecer.catentry_id_child AND cer.catentry_id_parent = ecer.catentry_id_parent AND ecer.language_id = :6 left outer join extcatentrydesc ecdesc on cer.catentry_id_child = ecdesc.catentry_id AND ecdesc.language_id = :7 WHERE exists(select :"SYS_B_02" from storecat sc where cgr.catalog_id = sc.catalog_id AND sc.storeent_id IN ( :8 ,:9 ) ) and exists(select :"SYS_B_03" from storecgrp scg where scg.catgroup_id = cgr.catgroup_id and scg.storeent_id IN ( :10 ,:11 ) ) AND cgr2.catgroup_id = cgr.catgroup_id and exists(select :"SYS_B_04" from catgrpdesc cgd where cgd.catgroup_id = cgr.catgroup_id AND cgd.language_id = ced.language_id AND cgd.published = :"SYS_B_05") and exists(select :"SYS_B_06" from extcatgrpdesc ecgd where ecgd.catgroup_id = cgr.catgroup_id AND ecgd.language_id = ced.language_id AND ecgd.published = :"SYS_B_07" ) AND cgr2.catgroup_id = cgr.catgroup_id AND ce.catentry_id = cer.catentry_id_parent AND cgr.catentry_id = ce.catentry_id AND cgr2.catentry_id= ce2.catentry_id AND ce.catenttype_id = :"SYS_B_08" AND ced.catentry_id = ce.catentry_id AND ced.published = :"SYS_B_09" AND ce2.catentry_id = cer.catentry_id_child AND ced2.catentry_id = ce2.catentry_id AND ced2.language_id = ced.language_id AND ced2.published = :"SYS_B_10" AND ce.markfordelete = :"SYS_B_11" AND ce2.markfordelete = :"SYS_B_12" AND o.catentry_id = cer.catentry_id_child AND o.precedence IN (:"SYS_B_13",:"SYS_B_14",:"SYS_B_15") AND o.tradeposcn_id = :12 AND o.published = :"SYS_B_16" AND (o.startdate <= current_timestamp or o.startdate is null) AND (o.enddate > current_timestamp or o.enddate is null) AND op.offer_id = o.offer_id AND cgr.catalog_id = :13 AND cgr2.catalog_id= cgr.catalog_id AND ced.language_id = :14 AND "cgr.catgroup_id = :15 ORDER BY ce.catentry_id, cer.catentry_id_child, ecgr.storeent_id desc, ecgr2.storeent_id desc" Plan hash value: 2475161106 ---------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 37 (100)| | | | | 1 | SORT ORDER BY | | 1 | 566 | 37 (6)| 00:00:01 | | | | 2 | NESTED LOOPS OUTER | | 1 | 566 | 36 (3)| 00:00:01 | | | | 3 | NESTED LOOPS OUTER | | 1 | 485 | 31 (0)| 00:00:01 | | | | 4 | NESTED LOOPS | | 1 | 445 | 29 (0)| 00:00:01 | | | | 5 | NESTED LOOPS | | 1 | 434 | 26 (0)| 00:00:01 | | | | 6 | NESTED LOOPS OUTER | | 1 | 412 | 24 (0)| 00:00:01 | | | | 7 | NESTED LOOPS | | 1 | 402 | 24 (0)| 00:00:01 | | | | 8 | NESTED LOOPS | | 1 | 242 | 22 (0)| 00:00:01 | | | | 9 | NESTED LOOPS OUTER | | 1 | 197 | 19 (0)| 00:00:01 | | | | 10 | NESTED LOOPS OUTER | | 1 | 157 | 17 (0)| 00:00:01 | | | | 11 | NESTED LOOPS | | 1 | 140 | 15 (0)| 00:00:01 | | | | 12 | NESTED LOOPS | | 1 | 124 | 13 (0)| 00:00:01 | | | | 13 | NESTED LOOPS | | 1 | 103 | 11 (0)| 00:00:01 | | | | 14 | NESTED LOOPS SEMI | | 1 | 74 | 9 (0)| 00:00:01 | | | | 15 | MERGE JOIN CARTESIAN | | 1 | 65 | 8 (0)| 00:00:01 | | | | 16 | NESTED LOOPS | | 1 | 49 | 6 (0)| 00:00:01 | | | | 17 | NESTED LOOPS | | 1 | 33 | 4 (0)| 00:00:01 | | | | 18 | NESTED LOOPS | | 1 | 24 | 3 (0)| 00:00:01 | | | | 19 | TABLE ACCESS BY INDEX ROWID| CATGRPDESC | 1 | 12 | 2 (0)| 00:00:01 | | | | 20 | INDEX UNIQUE SCAN | SYS_C0030074 | 1 | | 1 (0)| 00:00:01 | | | | 21 | TABLE ACCESS BY INDEX ROWID| EXTCATGRPDESC | 1 | 12 | 1 (0)| 00:00:01 | | | | 22 | INDEX UNIQUE SCAN | PK_EXTCATGRPDESC | 1 | | 0 (0)| | | | | 23 | SORT UNIQUE | | 1 | 9 | 1 (0)| 00:00:01 | | | | 24 | INLIST ITERATOR | | | | | | | | | 25 | INDEX UNIQUE SCAN | SYS_C0031935 | 1 | 9 | 1 (0)| 00:00:01 | | | | 26 | INDEX RANGE SCAN | IND_CATGPENREL_2 | 1 | 16 | 2 (0)| 00:00:01 | | | | 27 | BUFFER SORT | | 65 | 1040 | 6 (0)| 00:00:01 | | | | 28 | INDEX RANGE SCAN | IND_CATGPENREL_2 | 65 | 1040 | 2 (0)| 00:00:01 | | | | 29 | INLIST ITERATOR | | | | | | | | | 30 | INDEX UNIQUE SCAN | SYS_C0031941 | 1 | 9 | 1 (0)| 00:00:01 | | | | 31 | TABLE ACCESS BY INDEX ROWID | CATENTRY | 1 | 29 | 2 (0)| 00:00:01 | | | | 32 | INDEX UNIQUE SCAN | SYS_C0030043 | 1 | | 1 (0)| 00:00:01 | | | | 33 | TABLE ACCESS BY INDEX ROWID | CATENTRY | 1 | 21 | 2 (0)| 00:00:01 | | | | 34 | INDEX UNIQUE SCAN | SYS_C0030043 | 1 | | 1 (0)| 00:00:01 | | | | 35 | TABLE ACCESS BY INDEX ROWID | CATENTREL | 1 | 16 | 2 (0)| 00:00:01 | | | | 36 | INDEX RANGE SCAN | IND5556045 | 1 | | 1 (0)| 00:00:01 | | | | 37 | TABLE ACCESS BY INDEX ROWID | EXTCATENTREL | 1 | 17 | 2 (0)| 00:00:01 | | | | 38 | INDEX UNIQUE SCAN | EXTCATENTREL_PK | 1 | | 1 (0)| 00:00:01 | | | | 39 | TABLE ACCESS BY INDEX ROWID | EXTCATGPENSTORESEQ | 1 | 40 | 2 (0)| 00:00:01 | | | | 40 | INDEX RANGE SCAN | EXTCATGPENSTORESEQ_IND | 1 | | 1 (0)| 00:00:01 | | | | 41 | TABLE ACCESS BY INDEX ROWID | OFFER | 1 | 45 | 3 (0)| 00:00:01 | | | | 42 | INDEX RANGE SCAN | I0000167 | 1 | | 2 (0)| 00:00:01 | | | | 43 | TABLE ACCESS BY INDEX ROWID | CATENTDESC | 1 | 160 | 2 (0)| 00:00:01 | | | | 44 | INDEX UNIQUE SCAN | SYS_C0030032 | 1 | | 1 (0)| 00:00:01 | | | | 45 | PARTITION LIST SINGLE | | 1 | 10 | 0 (0)| | KEY | KEY | | 46 | INDEX UNIQUE SCAN | EXTCATENTRYDESC_IDX1 | 1 | 10 | 0 (0)| | KEY | KEY | | 47 | INDEX RANGE SCAN | I0000304 | 1 | 22 | 2 (0)| 00:00:01 | | | | 48 | TABLE ACCESS BY INDEX ROWID | OFFERPRICE | 1 | 11 | 3 (0)| 00:00:01 | | | | 49 | INDEX RANGE SCAN | SYS_C0031126 | 1 | | 2 (0)| 00:00:01 | | | | 50 | TABLE ACCESS BY INDEX ROWID | EXTCATGPENSTORESEQ | 1 | 40 | 2 (0)| 00:00:01 | | | | 51 | INDEX RANGE SCAN | EXTCATGPENSTORESEQ_IND | 1 | | 1 (0)| 00:00:01 | | | | 52 | VIEW PUSHED PREDICATE | | 1 | 81 | 5 (20)| 00:00:01 | | | | 53 | NESTED LOOPS | | 1 | 47 | 5 (20)| 00:00:01 | | | | 54 | NESTED LOOPS | | 1 | 25 | 4 (25)| 00:00:01 | | | | 55 | NESTED LOOPS | | 1 | 15 | 3 (34)| 00:00:01 | | | | 56 | VIEW | index$_join$_012 | 295 | 2065 | 3 (34)| 00:00:01 | | | | 57 | HASH JOIN | | | | | | | | | 58 | INDEX FAST FULL SCAN | EXTFILTER_PK | 295 | 2065 | 1 (0)| 00:00:01 | | | | 59 | INDEX FAST FULL SCAN | EXTFILTER_UK | 295 | 2065 | 1 (0)| 00:00:01 | | | | 60 | INDEX UNIQUE SCAN | EXTCATENTFILTER_PK | 1 | 8 | 0 (0)| | | | | 61 | TABLE ACCESS BY INDEX ROWID | EXTFILTERTYPE | 1 | 10 | 1 (0)| 00:00:01 | | | | 62 | INDEX UNIQUE SCAN | EXTFILTERTYPE_PK | 1 | | 0 (0)| | | | | 63 | TABLE ACCESS BY INDEX ROWID | EXTFILTERDESC | 1 | 22 | 1 (0)| 00:00:01 | | | | 64 | INDEX UNIQUE SCAN | EXTFILTERDESC_PK | 1 | | 0 (0)| | | | ---------------------------------------------------------------------------------------------------------------------------------------- SQL_ID g58at1rrnjnur -------------------- -- getSelectProductsSQL() SELECT ce.catentry_id catentry_id_parent, ce.partnumber partnumber_parent, ced.NAME name_parent, cer.catentry_id_child catentry_id_child, ce2.partnumber partnumber_child, ced2.name name_child, ced2.longdescription, ced2.published, op.price, ced2.availabilitydate, ced2.available, cer.SEQUENCE seq_1, ecer.SEQUENCE seq_2, ce2.field2, ecgr.SEQUENCE relevance , ecgr.storeent_id storeent_id_1, ecgr2.SEQUENCE seq_3, ecgr2.storeent_id storeent_id_2 , filt.filter_id, filt.name filter_name, filt.value filter_value, o.ENDDATE FROM catgpenrel cgr LEFT OUTER JOIN EXTCATGPENSTORESEQ ecgr ON cgr.catalog_id = ecgr.catalog_id AND cgr.catgroup_id = ecgr.catgroup_id AND cgr.catentry_id = ecgr.catentry_id AND ecgr.sort_type = :"SYS_B_00" AND ecgr.storeent_id IN ( :1 ,:2 ) , catgpenrel cgr2 LEFT OUTER JOIN EXTCATGPENSTORESEQ ecgr2 ON cgr2.catalog_id = ecgr2.catalog_id AND cgr2.catgroup_id = ecgr2.catgroup_id AND cgr2.catentry_id = ecgr2.catentry_id AND ecgr2.sort_type = :"SYS_B_01" AND "ecgr2.storeent_id IN ( :3 ,:4 ) , catentry ce, catentry ce2 left outer join (SELECT cef.catentry_id, " "f.filter_id, fd.value, ft.name FROM EXTCATENTFILTER cef, EXTFILTERTYPE ft, EXTFILTER f, EXTFILTERDESC fd WHERE " "f.filter_id = cef.filter_id AND f.filtertype_id = ft.filtertype_id AND fd.filter_id = f.filter_id AND fd.language_id = :5 " ") filt ON filt.catentry_id = ce2.catentry_id, catentdesc ced, catentdesc ced2, offer o, " offerprice op, catentrel cer LEFT OUTER JOIN extcatentrel ecer ON cer.catentry_id_child = ecer.catentry_id_child AND cer.catentry_id_parent = ecer.catentry_id_parent AND ecer.language_id = :6 left outer join extcatentrydesc ecdesc on cer.catentry_id_child = ecdesc.catentry_id AND ecdesc.language_id = :7 WHERE exists(select :"SYS_B_02" from storecat sc where cgr.catalog_id = sc.catalog_id AND sc.storeent_id IN ( :8 ,:9 ) ) and exists(select :"SYS_B_03" from storecgrp scg where scg.catgroup_id = cgr.catgroup_id and scg.storeent_id IN ( :10 ,:11 ) ) AND cgr2.catgroup_id = cgr.catgroup_id and exists(select :"SYS_B_04" from catgrpdesc cgd where cgd.catgroup_id = cgr.catgroup_id AND cgd.language_id = ced.language_id AND cgd.published = :"SYS_B_05") and exists(select :"SYS_B_06" from extcatgrpdesc ecgd where ecgd.catgroup_id = cgr.catgroup_id AND ecgd.language_id = ced.language_id AND ecgd.published = :"SYS_B_07" ) AND cgr2.catgroup_id = cgr.catgroup_id AND ce.catentry_id = cer.catentry_id_parent AND cgr.catentry_id = ce.catentry_id AND cgr2.catentry_id= ce2.catentry_id AND ce.catenttype_id = :"SYS_B_08" AND ced.catentry_id = ce.catentry_id AND ced.published = :"SYS_B_09" AND ce2.catentry_id = cer.catentry_id_child AND ced2.catentry_id = ce2.catentry_id AND ced2.language_id = ced.language_id AND ced2.published = :"SYS_B_10" AND ce.markfordelete = :"SYS_B_11" AND ce2.markfordelete = :"SYS_B_12" AND o.catentry_id = cer.catentry_id_child AND o.precedence IN (:"SYS_B_13",:"SYS_B_14",:"SYS_B_15") AND o.tradeposcn_id = :12 AND o.published = :"SYS_B_16" AND (o.startdate <= current_timestamp or o.startdate is null) AND (o.enddate > current_timestamp or o.enddate is null) AND op.offer_id = o.offer_id AND cgr.catalog_id = :13 AND cgr2.catalog_id= cgr.catalog_id AND ced.language_id = :14 AND "cgr.catgroup_id = :15 ORDER BY ce.catentry_id, cer.catentry_id_child, ecgr.storeent_id desc, ecgr2.storeent_id desc" Plan hash value: 3229430820 ---------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 48 (100)| | | | | 1 | SORT ORDER BY | | 1 | 566 | 48 (7)| 00:00:01 | | | | 2 | NESTED LOOPS OUTER | | 1 | 566 | 47 (5)| 00:00:01 | | | | 3 | NESTED LOOPS SEMI | | 1 | 485 | 42 (3)| 00:00:01 | | | | 4 | NESTED LOOPS | | 1 | 476 | 41 (3)| 00:00:01 | | | | 5 | NESTED LOOPS | | 1 | 465 | 38 (3)| 00:00:01 | | | | 6 | NESTED LOOPS OUTER | | 1 | 420 | 35 (3)| 00:00:01 | | | | 7 | NESTED LOOPS OUTER | | 1 | 403 | 33 (4)| 00:00:01 | | | | 8 | NESTED LOOPS | | 1 | 393 | 33 (4)| 00:00:01 | | | | 9 | NESTED LOOPS OUTER | | 1 | 377 | 31 (4)| 00:00:01 | | | | 10 | NESTED LOOPS | | 1 | 337 | 29 (4)| 00:00:01 | | | | 11 | NESTED LOOPS | | 1 | 315 | 27 (4)| 00:00:01 | | | | 12 | NESTED LOOPS OUTER | | 1 | 286 | 25 (4)| 00:00:01 | | | | 13 | NESTED LOOPS | | 1 | 246 | 23 (5)| 00:00:01 | | | | 14 | NESTED LOOPS | | 1 | 86 | 21 (5)| 00:00:01 | | | | 15 | MERGE JOIN | | 1 | 65 | 19 (6)| 00:00:01 | | | | 16 | NESTED LOOPS | | 1 | 49 | 6 (0)| 00:00:01 | | | | 17 | NESTED LOOPS | | 1 | 33 | 4 (0)| 00:00:01 | | | | 18 | NESTED LOOPS | | 1 | 24 | 3 (0)| 00:00:01 | | | | 19 | TABLE ACCESS BY INDEX ROWID| CATGRPDESC | 1 | 12 | 2 (0)| 00:00:01 | | | | 20 | INDEX UNIQUE SCAN | SYS_C0030074 | 1 | | 1 (0)| 00:00:01 | | | | 21 | TABLE ACCESS BY INDEX ROWID| EXTCATGRPDESC | 1 | 12 | 1 (0)| 00:00:01 | | | | 22 | INDEX UNIQUE SCAN | PK_EXTCATGRPDESC | 1 | | 0 (0)| | | | | 23 | SORT UNIQUE | | 1 | 9 | 1 (0)| 00:00:01 | | | | 24 | INLIST ITERATOR | | | | | | | | | 25 | INDEX UNIQUE SCAN | SYS_C0031941 | 1 | 9 | 1 (0)| 00:00:01 | | | | 26 | INDEX RANGE SCAN | IND_CATGPENREL_2 | 1 | 16 | 2 (0)| 00:00:01 | | | | 27 | SORT JOIN | | 1542 | 24672 | 13 (8)| 00:00:01 | | | | 28 | INDEX RANGE SCAN | IND_CATGPENREL_2 | 1542 | 24672 | 12 (0)| 00:00:01 | | | | 29 | TABLE ACCESS BY INDEX ROWID | CATENTRY | 1 | 21 | 2 (0)| 00:00:01 | | | | 30 | INDEX UNIQUE SCAN | SYS_C0030043 | 1 | | 1 (0)| 00:00:01 | | | | 31 | TABLE ACCESS BY INDEX ROWID | CATENTDESC | 1 | 160 | 2 (0)| 00:00:01 | | | | 32 | INDEX UNIQUE SCAN | SYS_C0030032 | 1 | | 1 (0)| 00:00:01 | | | | 33 | TABLE ACCESS BY INDEX ROWID | EXTCATGPENSTORESEQ | 1 | 40 | 2 (0)| 00:00:01 | | | | 34 | INDEX RANGE SCAN | EXTCATGPENSTORESEQ_IND | 1 | | 1 (0)| 00:00:01 | | | | 35 | TABLE ACCESS BY INDEX ROWID | CATENTRY | 1 | 29 | 2 (0)| 00:00:01 | | | | 36 | INDEX UNIQUE SCAN | SYS_C0030043 | 1 | | 1 (0)| 00:00:01 | | | | 37 | INDEX RANGE SCAN | I0000304 | 1 | 22 | 2 (0)| 00:00:01 | | | | 38 | TABLE ACCESS BY INDEX ROWID | EXTCATGPENSTORESEQ | 1 | 40 | 2 (0)| 00:00:01 | | | | 39 | INDEX RANGE SCAN | EXTCATGPENSTORESEQ_IND | 1 | | 1 (0)| 00:00:01 | | | | 40 | TABLE ACCESS BY INDEX ROWID | CATENTREL | 1 | 16 | 2 (0)| 00:00:01 | | | | 41 | INDEX RANGE SCAN | IND5556045 | 1 | | 1 (0)| 00:00:01 | | | | 42 | PARTITION LIST SINGLE | | 1 | 10 | 0 (0)| | KEY | KEY | | 43 | INDEX UNIQUE SCAN | EXTCATENTRYDESC_IDX1 | 1 | 10 | 0 (0)| | KEY | KEY | | 44 | TABLE ACCESS BY INDEX ROWID | EXTCATENTREL | 1 | 17 | 2 (0)| 00:00:01 | | | | 45 | INDEX UNIQUE SCAN | EXTCATENTREL_PK | 1 | | 1 (0)| 00:00:01 | | | | 46 | TABLE ACCESS BY INDEX ROWID | OFFER | 1 | 45 | 3 (0)| 00:00:01 | | | | 47 | INDEX RANGE SCAN | I0000167 | 1 | | 2 (0)| 00:00:01 | | | | 48 | TABLE ACCESS BY INDEX ROWID | OFFERPRICE | 1 | 11 | 3 (0)| 00:00:01 | | | | 49 | INDEX RANGE SCAN | SYS_C0031126 | 1 | | 2 (0)| 00:00:01 | | | | 50 | INLIST ITERATOR | | | | | | | | | 51 | INDEX UNIQUE SCAN | SYS_C0031935 | 1 | 9 | 1 (0)| 00:00:01 | | | | 52 | VIEW PUSHED PREDICATE | | 1 | 81 | 5 (20)| 00:00:01 | | | | 53 | NESTED LOOPS | | 1 | 47 | 5 (20)| 00:00:01 | | | | 54 | NESTED LOOPS | | 1 | 25 | 4 (25)| 00:00:01 | | | | 55 | NESTED LOOPS | | 1 | 15 | 3 (34)| 00:00:01 | | | | 56 | VIEW | index$_join$_012 | 295 | 2065 | 3 (34)| 00:00:01 | | | | 57 | HASH JOIN | | | | | | | | | 58 | INDEX FAST FULL SCAN | EXTFILTER_PK | 295 | 2065 | 1 (0)| 00:00:01 | | | | 59 | INDEX FAST FULL SCAN | EXTFILTER_UK | 295 | 2065 | 1 (0)| 00:00:01 | | | | 60 | INDEX UNIQUE SCAN | EXTCATENTFILTER_PK | 1 | 8 | 0 (0)| | | | | 61 | TABLE ACCESS BY INDEX ROWID | EXTFILTERTYPE | 1 | 10 | 1 (0)| 00:00:01 | | | | 62 | INDEX UNIQUE SCAN | EXTFILTERTYPE_PK | 1 | | 0 (0)| | | | | 63 | TABLE ACCESS BY INDEX ROWID | EXTFILTERDESC | 1 | 22 | 1 (0)| 00:00:01 | | | | 64 | INDEX UNIQUE SCAN | EXTFILTERDESC_PK | 1 | | 0 (0)| | | | ---------------------------------------------------------------------------------------------------------------------------------------- Note ----- - SQL profile "PROF_g58at1rrnjnur" used for this statement SQL_ID g58at1rrnjnur -------------------- -- getSelectProductsSQL() SELECT ce.catentry_id catentry_id_parent, ce.partnumber partnumber_parent, ced.NAME name_parent, cer.catentry_id_child catentry_id_child, ce2.partnumber partnumber_child, ced2.name name_child, ced2.longdescription, ced2.published, op.price, ced2.availabilitydate, ced2.available, cer.SEQUENCE seq_1, ecer.SEQUENCE seq_2, ce2.field2, ecgr.SEQUENCE relevance , ecgr.storeent_id storeent_id_1, ecgr2.SEQUENCE seq_3, ecgr2.storeent_id storeent_id_2 , filt.filter_id, filt.name filter_name, filt.value filter_value, o.ENDDATE FROM catgpenrel cgr LEFT OUTER JOIN EXTCATGPENSTORESEQ ecgr ON cgr.catalog_id = ecgr.catalog_id AND cgr.catgroup_id = ecgr.catgroup_id AND cgr.catentry_id = ecgr.catentry_id AND ecgr.sort_type = :"SYS_B_00" AND ecgr.storeent_id IN ( :1 ,:2 ) , catgpenrel cgr2 LEFT OUTER JOIN EXTCATGPENSTORESEQ ecgr2 ON cgr2.catalog_id = ecgr2.catalog_id AND cgr2.catgroup_id = ecgr2.catgroup_id AND cgr2.catentry_id = ecgr2.catentry_id AND ecgr2.sort_type = :"SYS_B_01" AND "ecgr2.storeent_id IN ( :3 ,:4 ) , catentry ce, catentry ce2 left outer join (SELECT cef.catentry_id, " "f.filter_id, fd.value, ft.name FROM EXTCATENTFILTER cef, EXTFILTERTYPE ft, EXTFILTER f, EXTFILTERDESC fd WHERE " "f.filter_id = cef.filter_id AND f.filtertype_id = ft.filtertype_id AND fd.filter_id = f.filter_id AND fd.language_id = :5 " ") filt ON filt.catentry_id = ce2.catentry_id, catentdesc ced, catentdesc ced2, offer o, " offerprice op, catentrel cer LEFT OUTER JOIN extcatentrel ecer ON cer.catentry_id_child = ecer.catentry_id_child AND cer.catentry_id_parent = ecer.catentry_id_parent AND ecer.language_id = :6 left outer join extcatentrydesc ecdesc on cer.catentry_id_child = ecdesc.catentry_id AND ecdesc.language_id = :7 WHERE exists(select :"SYS_B_02" from storecat sc where cgr.catalog_id = sc.catalog_id AND sc.storeent_id IN ( :8 ,:9 ) ) and exists(select :"SYS_B_03" from storecgrp scg where scg.catgroup_id = cgr.catgroup_id and scg.storeent_id IN ( :10 ,:11 ) ) AND cgr2.catgroup_id = cgr.catgroup_id and exists(select :"SYS_B_04" from catgrpdesc cgd where cgd.catgroup_id = cgr.catgroup_id AND cgd.language_id = ced.language_id AND cgd.published = :"SYS_B_05") and exists(select :"SYS_B_06" from extcatgrpdesc ecgd where ecgd.catgroup_id = cgr.catgroup_id AND ecgd.language_id = ced.language_id AND ecgd.published = :"SYS_B_07" ) AND cgr2.catgroup_id = cgr.catgroup_id AND ce.catentry_id = cer.catentry_id_parent AND cgr.catentry_id = ce.catentry_id AND cgr2.catentry_id= ce2.catentry_id AND ce.catenttype_id = :"SYS_B_08" AND ced.catentry_id = ce.catentry_id AND ced.published = :"SYS_B_09" AND ce2.catentry_id = cer.catentry_id_child AND ced2.catentry_id = ce2.catentry_id AND ced2.language_id = ced.language_id AND ced2.published = :"SYS_B_10" AND ce.markfordelete = :"SYS_B_11" AND ce2.markfordelete = :"SYS_B_12" AND o.catentry_id = cer.catentry_id_child AND o.precedence IN (:"SYS_B_13",:"SYS_B_14",:"SYS_B_15") AND o.tradeposcn_id = :12 AND o.published = :"SYS_B_16" AND (o.startdate <= current_timestamp or o.startdate is null) AND (o.enddate > current_timestamp or o.enddate is null) AND op.offer_id = o.offer_id AND cgr.catalog_id = :13 AND cgr2.catalog_id= cgr.catalog_id AND ced.language_id = :14 AND "cgr.catgroup_id = :15 ORDER BY ce.catentry_id, cer.catentry_id_child, ecgr.storeent_id desc, ecgr2.storeent_id desc" Plan hash value: 3837534193 ---------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 38 (100)| | | | | 1 | SORT ORDER BY | | 1 | 566 | 38 (6)| 00:00:01 | | | | 2 | NESTED LOOPS OUTER | | 1 | 566 | 37 (3)| 00:00:01 | | | | 3 | NESTED LOOPS | | 1 | 485 | 32 (0)| 00:00:01 | | | | 4 | NESTED LOOPS OUTER | | 1 | 325 | 30 (0)| 00:00:01 | | | | 5 | NESTED LOOPS | | 1 | 285 | 28 (0)| 00:00:01 | | | | 6 | NESTED LOOPS | | 1 | 269 | 27 (0)| 00:00:01 | | | | 7 | NESTED LOOPS | | 1 | 248 | 25 (0)| 00:00:01 | | | | 8 | NESTED LOOPS | | 1 | 226 | 23 (0)| 00:00:01 | | | | 9 | NESTED LOOPS OUTER | | 1 | 215 | 20 (0)| 00:00:01 | | | | 10 | NESTED LOOPS | | 1 | 205 | 20 (0)| 00:00:01 | | | | 11 | NESTED LOOPS OUTER | | 1 | 160 | 17 (0)| 00:00:01 | | | | 12 | NESTED LOOPS | | 1 | 143 | 15 (0)| 00:00:01 | | | | 13 | NESTED LOOPS | | 1 | 127 | 12 (0)| 00:00:01 | | | | 14 | NESTED LOOPS SEMI | | 1 | 98 | 10 (0)| 00:00:01 | | | | 15 | NESTED LOOPS OUTER | | 1 | 89 | 9 (0)| 00:00:01 | | | | 16 | NESTED LOOPS | | 1 | 49 | 7 (0)| 00:00:01 | | | | 17 | NESTED LOOPS | | 1 | 33 | 4 (0)| 00:00:01 | | | | 18 | NESTED LOOPS | | 1 | 24 | 3 (0)| 00:00:01 | | | | 19 | TABLE ACCESS BY INDEX ROWID| CATGRPDESC | 1 | 12 | 2 (0)| 00:00:01 | | | | 20 | INDEX UNIQUE SCAN | SYS_C0030074 | 1 | | 1 (0)| 00:00:01 | | | | 21 | TABLE ACCESS BY INDEX ROWID| EXTCATGRPDESC | 1 | 12 | 1 (0)| 00:00:01 | | | | 22 | INDEX UNIQUE SCAN | PK_EXTCATGRPDESC | 1 | | 0 (0)| | | | | 23 | SORT UNIQUE | | 1 | 9 | 1 (0)| 00:00:01 | | | | 24 | INLIST ITERATOR | | | | | | | | | 25 | INDEX UNIQUE SCAN | SYS_C0031941 | 1 | 9 | 1 (0)| 00:00:01 | | | | 26 | INDEX RANGE SCAN | IND_CATGPENREL_2 | 1 | 16 | 3 (0)| 00:00:01 | | | | 27 | TABLE ACCESS BY INDEX ROWID | EXTCATGPENSTORESEQ | 1 | 40 | 2 (0)| 00:00:01 | | | | 28 | INDEX RANGE SCAN | EXTCATGPENSTORESEQ_IND | 1 | | 1 (0)| 00:00:01 | | | | 29 | INLIST ITERATOR | | | | | | | | | 30 | INDEX UNIQUE SCAN | SYS_C0031935 | 1 | 9 | 1 (0)| 00:00:01 | | | | 31 | TABLE ACCESS BY INDEX ROWID | CATENTRY | 1 | 29 | 2 (0)| 00:00:01 | | | | 32 | INDEX UNIQUE SCAN | SYS_C0030043 | 1 | | 1 (0)| 00:00:01 | | | | 33 | TABLE ACCESS BY INDEX ROWID | CATENTREL | 1 | 16 | 3 (0)| 00:00:01 | | | | 34 | INDEX RANGE SCAN | IND5556040 | 6 | | 1 (0)| 00:00:01 | | | | 35 | TABLE ACCESS BY INDEX ROWID | EXTCATENTREL | 1 | 17 | 2 (0)| 00:00:01 | | | | 36 | INDEX UNIQUE SCAN | EXTCATENTREL_PK | 1 | | 1 (0)| 00:00:01 | | | | 37 | TABLE ACCESS BY INDEX ROWID | OFFER | 1 | 45 | 3 (0)| 00:00:01 | | | | 38 | INDEX RANGE SCAN | I0000167 | 1 | | 2 (0)| 00:00:01 | | | | 39 | PARTITION LIST SINGLE | | 1 | 10 | 0 (0)| | KEY | KEY | | 40 | INDEX UNIQUE SCAN | EXTCATENTRYDESC_IDX1 | 1 | 10 | 0 (0)| | KEY | KEY | | 41 | TABLE ACCESS BY INDEX ROWID | OFFERPRICE | 1 | 11 | 3 (0)| 00:00:01 | | | | 42 | INDEX RANGE SCAN | SYS_C0031126 | 1 | | 2 (0)| 00:00:01 | | | | 43 | INDEX RANGE SCAN | I0000304 | 1 | 22 | 2 (0)| 00:00:01 | | | | 44 | TABLE ACCESS BY INDEX ROWID | CATENTRY | 1 | 21 | 2 (0)| 00:00:01 | | | | 45 | INDEX UNIQUE SCAN | SYS_C0030043 | 1 | | 1 (0)| 00:00:01 | | | | 46 | INDEX UNIQUE SCAN | SYS_C0030060 | 1 | 16 | 1 (0)| 00:00:01 | | | | 47 | TABLE ACCESS BY INDEX ROWID | EXTCATGPENSTORESEQ | 1 | 40 | 2 (0)| 00:00:01 | | | | 48 | INDEX RANGE SCAN | EXTCATGPENSTORESEQ_IND | 1 | | 1 (0)| 00:00:01 | | | | 49 | TABLE ACCESS BY INDEX ROWID | CATENTDESC | 1 | 160 | 2 (0)| 00:00:01 | | | | 50 | INDEX UNIQUE SCAN | SYS_C0030032 | 1 | | 1 (0)| 00:00:01 | | | | 51 | VIEW PUSHED PREDICATE | | 1 | 81 | 5 (20)| 00:00:01 | | | | 52 | NESTED LOOPS | | 1 | 47 | 5 (20)| 00:00:01 | | | | 53 | NESTED LOOPS | | 1 | 25 | 4 (25)| 00:00:01 | | | | 54 | NESTED LOOPS | | 1 | 15 | 3 (34)| 00:00:01 | | | | 55 | VIEW | index$_join$_012 | 295 | 2065 | 3 (34)| 00:00:01 | | | | 56 | HASH JOIN | | | | | | | | | 57 | INDEX FAST FULL SCAN | EXTFILTER_PK | 295 | 2065 | 1 (0)| 00:00:01 | | | | 58 | INDEX FAST FULL SCAN | EXTFILTER_UK | 295 | 2065 | 1 (0)| 00:00:01 | | | | 59 | INDEX UNIQUE SCAN | EXTCATENTFILTER_PK | 1 | 8 | 0 (0)| | | | | 60 | TABLE ACCESS BY INDEX ROWID | EXTFILTERTYPE | 1 | 10 | 1 (0)| 00:00:01 | | | | 61 | INDEX UNIQUE SCAN | EXTFILTERTYPE_PK | 1 | | 0 (0)| | | | | 62 | TABLE ACCESS BY INDEX ROWID | EXTFILTERDESC | 1 | 22 | 1 (0)| 00:00:01 | | | | 63 | INDEX UNIQUE SCAN | EXTFILTERDESC_PK | 1 | | 0 (0)| | | | ---------------------------------------------------------------------------------------------------------------------------------------- SQL_ID g58at1rrnjnur -------------------- -- getSelectProductsSQL() SELECT ce.catentry_id catentry_id_parent, ce.partnumber partnumber_parent, ced.NAME name_parent, cer.catentry_id_child catentry_id_child, ce2.partnumber partnumber_child, ced2.name name_child, ced2.longdescription, ced2.published, op.price, ced2.availabilitydate, ced2.available, cer.SEQUENCE seq_1, ecer.SEQUENCE seq_2, ce2.field2, ecgr.SEQUENCE relevance , ecgr.storeent_id storeent_id_1, ecgr2.SEQUENCE seq_3, ecgr2.storeent_id storeent_id_2 , filt.filter_id, filt.name filter_name, filt.value filter_value, o.ENDDATE FROM catgpenrel cgr LEFT OUTER JOIN EXTCATGPENSTORESEQ ecgr ON cgr.catalog_id = ecgr.catalog_id AND cgr.catgroup_id = ecgr.catgroup_id AND cgr.catentry_id = ecgr.catentry_id AND ecgr.sort_type = :"SYS_B_00" AND ecgr.storeent_id IN ( :1 ,:2 ) , catgpenrel cgr2 LEFT OUTER JOIN EXTCATGPENSTORESEQ ecgr2 ON cgr2.catalog_id = ecgr2.catalog_id AND cgr2.catgroup_id = ecgr2.catgroup_id AND cgr2.catentry_id = ecgr2.catentry_id AND ecgr2.sort_type = :"SYS_B_01" AND "ecgr2.storeent_id IN ( :3 ,:4 ) , catentry ce, catentry ce2 left outer join (SELECT cef.catentry_id, " "f.filter_id, fd.value, ft.name FROM EXTCATENTFILTER cef, EXTFILTERTYPE ft, EXTFILTER f, EXTFILTERDESC fd WHERE " "f.filter_id = cef.filter_id AND f.filtertype_id = ft.filtertype_id AND fd.filter_id = f.filter_id AND fd.language_id = :5 " ") filt ON filt.catentry_id = ce2.catentry_id, catentdesc ced, catentdesc ced2, offer o, " offerprice op, catentrel cer LEFT OUTER JOIN extcatentrel ecer ON cer.catentry_id_child = ecer.catentry_id_child AND cer.catentry_id_parent = ecer.catentry_id_parent AND ecer.language_id = :6 left outer join extcatentrydesc ecdesc on cer.catentry_id_child = ecdesc.catentry_id AND ecdesc.language_id = :7 WHERE exists(select :"SYS_B_02" from storecat sc where cgr.catalog_id = sc.catalog_id AND sc.storeent_id IN ( :8 ,:9 ) ) and exists(select :"SYS_B_03" from storecgrp scg where scg.catgroup_id = cgr.catgroup_id and scg.storeent_id IN ( :10 ,:11 ) ) AND cgr2.catgroup_id = cgr.catgroup_id and exists(select :"SYS_B_04" from catgrpdesc cgd where cgd.catgroup_id = cgr.catgroup_id AND cgd.language_id = ced.language_id AND cgd.published = :"SYS_B_05") and exists(select :"SYS_B_06" from extcatgrpdesc ecgd where ecgd.catgroup_id = cgr.catgroup_id AND ecgd.language_id = ced.language_id AND ecgd.published = :"SYS_B_07" ) AND cgr2.catgroup_id = cgr.catgroup_id AND ce.catentry_id = cer.catentry_id_parent AND cgr.catentry_id = ce.catentry_id AND cgr2.catentry_id= ce2.catentry_id AND ce.catenttype_id = :"SYS_B_08" AND ced.catentry_id = ce.catentry_id AND ced.published = :"SYS_B_09" AND ce2.catentry_id = cer.catentry_id_child AND ced2.catentry_id = ce2.catentry_id AND ced2.language_id = ced.language_id AND ced2.published = :"SYS_B_10" AND ce.markfordelete = :"SYS_B_11" AND ce2.markfordelete = :"SYS_B_12" AND o.catentry_id = cer.catentry_id_child AND o.precedence IN (:"SYS_B_13",:"SYS_B_14",:"SYS_B_15") AND o.tradeposcn_id = :12 AND o.published = :"SYS_B_16" AND (o.startdate <= current_timestamp or o.startdate is null) AND (o.enddate > current_timestamp or o.enddate is null) AND op.offer_id = o.offer_id AND cgr.catalog_id = :13 AND cgr2.catalog_id= cgr.catalog_id AND ced.language_id = :14 AND "cgr.catgroup_id = :15 ORDER BY ce.catentry_id, cer.catentry_id_child, ecgr.storeent_id desc, ecgr2.storeent_id desc" Plan hash value: 3964579598 ---------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 37 (100)| | | | | 1 | SORT ORDER BY | | 1 | 518 | 37 (6)| 00:00:01 | | | | 2 | NESTED LOOPS OUTER | | 1 | 518 | 36 (3)| 00:00:01 | | | | 3 | NESTED LOOPS SEMI | | 1 | 437 | 31 (0)| 00:00:01 | | | | 4 | NESTED LOOPS | | 1 | 428 | 30 (0)| 00:00:01 | | | | 5 | NESTED LOOPS SEMI | | 1 | 417 | 27 (0)| 00:00:01 | | | | 6 | NESTED LOOPS | | 1 | 408 | 26 (0)| 00:00:01 | | | | 7 | NESTED LOOPS OUTER | | 1 | 363 | 23 (0)| 00:00:01 | | | | 8 | NESTED LOOPS OUTER | | 1 | 346 | 21 (0)| 00:00:01 | | | | 9 | NESTED LOOPS | | 1 | 336 | 21 (0)| 00:00:01 | | | | 10 | NESTED LOOPS OUTER | | 1 | 320 | 19 (0)| 00:00:01 | | | | 11 | NESTED LOOPS | | 1 | 280 | 17 (0)| 00:00:01 | | | | 12 | NESTED LOOPS | | 1 | 258 | 15 (0)| 00:00:01 | | | | 13 | NESTED LOOPS OUTER | | 1 | 229 | 13 (0)| 00:00:01 | | | | 14 | NESTED LOOPS | | 1 | 189 | 11 (0)| 00:00:01 | | | | 15 | NESTED LOOPS | | 1 | 77 | 9 (0)| 00:00:01 | | | | 16 | MERGE JOIN CARTESIAN | | 1 | 56 | 7 (0)| 00:00:01 | | | | 17 | NESTED LOOPS | | 1 | 40 | 5 (0)| 00:00:01 | | | | 18 | NESTED LOOPS | | 1 | 24 | 3 (0)| 00:00:01 | | | | 19 | TABLE ACCESS BY INDEX ROWID| CATGRPDESC | 1 | 12 | 2 (0)| 00:00:01 | | | | 20 | INDEX UNIQUE SCAN | SYS_C0030074 | 1 | | 1 (0)| 00:00:01 | | | | 21 | TABLE ACCESS BY INDEX ROWID| EXTCATGRPDESC | 1 | 12 | 1 (0)| 00:00:01 | | | | 22 | INDEX UNIQUE SCAN | PK_EXTCATGRPDESC | 1 | | 0 (0)| | | | | 23 | INDEX RANGE SCAN | IND_CATGPENREL_2 | 1 | 16 | 2 (0)| 00:00:01 | | | | 24 | BUFFER SORT | | 67 | 1072 | 5 (0)| 00:00:01 | | | | 25 | INDEX RANGE SCAN | IND_CATGPENREL_2 | 67 | 1072 | 2 (0)| 00:00:01 | | | | 26 | TABLE ACCESS BY INDEX ROWID | CATENTRY | 1 | 21 | 2 (0)| 00:00:01 | | | | 27 | INDEX UNIQUE SCAN | SYS_C0030043 | 1 | | 1 (0)| 00:00:01 | | | | 28 | TABLE ACCESS BY INDEX ROWID | CATENTDESC | 1 | 112 | 2 (0)| 00:00:01 | | | | 29 | INDEX UNIQUE SCAN | SYS_C0030032 | 1 | | 1 (0)| 00:00:01 | | | | 30 | TABLE ACCESS BY INDEX ROWID | EXTCATGPENSTORESEQ | 1 | 40 | 2 (0)| 00:00:01 | | | | 31 | INDEX RANGE SCAN | EXTCATGPENSTORESEQ_IND | 1 | | 1 (0)| 00:00:01 | | | | 32 | TABLE ACCESS BY INDEX ROWID | CATENTRY | 1 | 29 | 2 (0)| 00:00:01 | | | | 33 | INDEX UNIQUE SCAN | SYS_C0030043 | 1 | | 1 (0)| 00:00:01 | | | | 34 | INDEX RANGE SCAN | I0000304 | 1 | 22 | 2 (0)| 00:00:01 | | | | 35 | TABLE ACCESS BY INDEX ROWID | EXTCATGPENSTORESEQ | 1 | 40 | 2 (0)| 00:00:01 | | | | 36 | INDEX RANGE SCAN | EXTCATGPENSTORESEQ_IND | 1 | | 1 (0)| 00:00:01 | | | | 37 | TABLE ACCESS BY INDEX ROWID | CATENTREL | 1 | 16 | 2 (0)| 00:00:01 | | | | 38 | INDEX RANGE SCAN | IND5556045 | 1 | | 1 (0)| 00:00:01 | | | | 39 | PARTITION LIST SINGLE | | 1 | 10 | 0 (0)| | KEY | KEY | | 40 | INDEX UNIQUE SCAN | EXTCATENTRYDESC_IDX1 | 1 | 10 | 0 (0)| | KEY | KEY | | 41 | TABLE ACCESS BY INDEX ROWID | EXTCATENTREL | 1 | 17 | 2 (0)| 00:00:01 | | | | 42 | INDEX UNIQUE SCAN | EXTCATENTREL_PK | 1 | | 1 (0)| 00:00:01 | | | | 43 | TABLE ACCESS BY INDEX ROWID | OFFER | 1 | 45 | 3 (0)| 00:00:01 | | | | 44 | INDEX RANGE SCAN | I0000167 | 1 | | 2 (0)| 00:00:01 | | | | 45 | INLIST ITERATOR | | | | | | | | | 46 | INDEX UNIQUE SCAN | SYS_C0031941 | 1 | 9 | 1 (0)| 00:00:01 | | | | 47 | TABLE ACCESS BY INDEX ROWID | OFFERPRICE | 1 | 11 | 3 (0)| 00:00:01 | | | | 48 | INDEX RANGE SCAN | SYS_C0031126 | 1 | | 2 (0)| 00:00:01 | | | | 49 | INLIST ITERATOR | | | | | | | | | 50 | INDEX UNIQUE SCAN | SYS_C0031935 | 1 | 9 | 1 (0)| 00:00:01 | | | | 51 | VIEW PUSHED PREDICATE | | 1 | 81 | 5 (20)| 00:00:01 | | | | 52 | NESTED LOOPS | | 1 | 47 | 5 (20)| 00:00:01 | | | | 53 | NESTED LOOPS | | 1 | 25 | 4 (25)| 00:00:01 | | | | 54 | NESTED LOOPS | | 1 | 15 | 3 (34)| 00:00:01 | | | | 55 | VIEW | index$_join$_012 | 295 | 2065 | 3 (34)| 00:00:01 | | | | 56 | HASH JOIN | | | | | | | | | 57 | INDEX FAST FULL SCAN | EXTFILTER_PK | 295 | 2065 | 1 (0)| 00:00:01 | | | | 58 | INDEX FAST FULL SCAN | EXTFILTER_UK | 295 | 2065 | 1 (0)| 00:00:01 | | | | 59 | INDEX UNIQUE SCAN | EXTCATENTFILTER_PK | 1 | 8 | 0 (0)| | | | | 60 | TABLE ACCESS BY INDEX ROWID | EXTFILTERTYPE | 1 | 10 | 1 (0)| 00:00:01 | | | | 61 | INDEX UNIQUE SCAN | EXTFILTERTYPE_PK | 1 | | 0 (0)| | | | | 62 | TABLE ACCESS BY INDEX ROWID | EXTFILTERDESC | 1 | 22 | 1 (0)| 00:00:01 | | | | 63 | INDEX UNIQUE SCAN | EXTFILTERDESC_PK | 1 | | 0 (0)| | | | ---------------------------------------------------------------------------------------------------------------------------------------- SQL_ID g58at1rrnjnur -------------------- -- getSelectProductsSQL() SELECT ce.catentry_id catentry_id_parent, ce.partnumber partnumber_parent, ced.NAME name_parent, cer.catentry_id_child catentry_id_child, ce2.partnumber partnumber_child, ced2.name name_child, ced2.longdescription, ced2.published, op.price, ced2.availabilitydate, ced2.available, cer.SEQUENCE seq_1, ecer.SEQUENCE seq_2, ce2.field2, ecgr.SEQUENCE relevance , ecgr.storeent_id storeent_id_1, ecgr2.SEQUENCE seq_3, ecgr2.storeent_id storeent_id_2 , filt.filter_id, filt.name filter_name, filt.value filter_value, o.ENDDATE FROM catgpenrel cgr LEFT OUTER JOIN EXTCATGPENSTORESEQ ecgr ON cgr.catalog_id = ecgr.catalog_id AND cgr.catgroup_id = ecgr.catgroup_id AND cgr.catentry_id = ecgr.catentry_id AND ecgr.sort_type = :"SYS_B_00" AND ecgr.storeent_id IN ( :1 ,:2 ) , catgpenrel cgr2 LEFT OUTER JOIN EXTCATGPENSTORESEQ ecgr2 ON cgr2.catalog_id = ecgr2.catalog_id AND cgr2.catgroup_id = ecgr2.catgroup_id AND cgr2.catentry_id = ecgr2.catentry_id AND ecgr2.sort_type = :"SYS_B_01" AND "ecgr2.storeent_id IN ( :3 ,:4 ) , catentry ce, catentry ce2 left outer join (SELECT cef.catentry_id, " "f.filter_id, fd.value, ft.name FROM EXTCATENTFILTER cef, EXTFILTERTYPE ft, EXTFILTER f, EXTFILTERDESC fd WHERE " "f.filter_id = cef.filter_id AND f.filtertype_id = ft.filtertype_id AND fd.filter_id = f.filter_id AND fd.language_id = :5 " ") filt ON filt.catentry_id = ce2.catentry_id, catentdesc ced, catentdesc ced2, offer o, " offerprice op, catentrel cer LEFT OUTER JOIN extcatentrel ecer ON cer.catentry_id_child = ecer.catentry_id_child AND cer.catentry_id_parent = ecer.catentry_id_parent AND ecer.language_id = :6 left outer join extcatentrydesc ecdesc on cer.catentry_id_child = ecdesc.catentry_id AND ecdesc.language_id = :7 WHERE exists(select :"SYS_B_02" from storecat sc where cgr.catalog_id = sc.catalog_id AND sc.storeent_id IN ( :8 ,:9 ) ) and exists(select :"SYS_B_03" from storecgrp scg where scg.catgroup_id = cgr.catgroup_id and scg.storeent_id IN ( :10 ,:11 ) ) AND cgr2.catgroup_id = cgr.catgroup_id and exists(select :"SYS_B_04" from catgrpdesc cgd where cgd.catgroup_id = cgr.catgroup_id AND cgd.language_id = ced.language_id AND cgd.published = :"SYS_B_05") and exists(select :"SYS_B_06" from extcatgrpdesc ecgd where ecgd.catgroup_id = cgr.catgroup_id AND ecgd.language_id = ced.language_id AND ecgd.published = :"SYS_B_07" ) AND cgr2.catgroup_id = cgr.catgroup_id AND ce.catentry_id = cer.catentry_id_parent AND cgr.catentry_id = ce.catentry_id AND cgr2.catentry_id= ce2.catentry_id AND ce.catenttype_id = :"SYS_B_08" AND ced.catentry_id = ce.catentry_id AND ced.published = :"SYS_B_09" AND ce2.catentry_id = cer.catentry_id_child AND ced2.catentry_id = ce2.catentry_id AND ced2.language_id = ced.language_id AND ced2.published = :"SYS_B_10" AND ce.markfordelete = :"SYS_B_11" AND ce2.markfordelete = :"SYS_B_12" AND o.catentry_id = cer.catentry_id_child AND o.precedence IN (:"SYS_B_13",:"SYS_B_14",:"SYS_B_15") AND o.tradeposcn_id = :12 AND o.published = :"SYS_B_16" AND (o.startdate <= current_timestamp or o.startdate is null) AND (o.enddate > current_timestamp or o.enddate is null) AND op.offer_id = o.offer_id AND cgr.catalog_id = :13 AND cgr2.catalog_id= cgr.catalog_id AND ced.language_id = :14 AND "cgr.catgroup_id = :15 ORDER BY ce.catentry_id, cer.catentry_id_child, ecgr.storeent_id desc, ecgr2.storeent_id desc" Plan hash value: 3992582335 ---------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 37 (100)| | | | | 1 | SORT ORDER BY | | 1 | 518 | 37 (6)| 00:00:01 | | | | 2 | NESTED LOOPS OUTER | | 1 | 518 | 36 (3)| 00:00:01 | | | | 3 | NESTED LOOPS SEMI | | 1 | 437 | 31 (0)| 00:00:01 | | | | 4 | NESTED LOOPS | | 1 | 428 | 30 (0)| 00:00:01 | | | | 5 | NESTED LOOPS | | 1 | 417 | 27 (0)| 00:00:01 | | | | 6 | NESTED LOOPS OUTER | | 1 | 372 | 24 (0)| 00:00:01 | | | | 7 | NESTED LOOPS OUTER | | 1 | 355 | 22 (0)| 00:00:01 | | | | 8 | NESTED LOOPS | | 1 | 345 | 22 (0)| 00:00:01 | | | | 9 | NESTED LOOPS OUTER | | 1 | 329 | 20 (0)| 00:00:01 | | | | 10 | NESTED LOOPS | | 1 | 289 | 18 (0)| 00:00:01 | | | | 11 | NESTED LOOPS | | 1 | 267 | 16 (0)| 00:00:01 | | | | 12 | NESTED LOOPS OUTER | | 1 | 238 | 14 (0)| 00:00:01 | | | | 13 | NESTED LOOPS | | 1 | 198 | 12 (0)| 00:00:01 | | | | 14 | NESTED LOOPS | | 1 | 86 | 10 (0)| 00:00:01 | | | | 15 | MERGE JOIN CARTESIAN | | 1 | 65 | 8 (0)| 00:00:01 | | | | 16 | NESTED LOOPS | | 1 | 49 | 6 (0)| 00:00:01 | | | | 17 | NESTED LOOPS | | 1 | 33 | 4 (0)| 00:00:01 | | | | 18 | NESTED LOOPS | | 1 | 24 | 3 (0)| 00:00:01 | | | | 19 | TABLE ACCESS BY INDEX ROWID| CATGRPDESC | 1 | 12 | 2 (0)| 00:00:01 | | | | 20 | INDEX UNIQUE SCAN | SYS_C0030074 | 1 | | 1 (0)| 00:00:01 | | | | 21 | TABLE ACCESS BY INDEX ROWID| EXTCATGRPDESC | 1 | 12 | 1 (0)| 00:00:01 | | | | 22 | INDEX UNIQUE SCAN | PK_EXTCATGRPDESC | 1 | | 0 (0)| | | | | 23 | SORT UNIQUE | | 1 | 9 | 1 (0)| 00:00:01 | | | | 24 | INLIST ITERATOR | | | | | | | | | 25 | INDEX UNIQUE SCAN | SYS_C0031941 | 1 | 9 | 1 (0)| 00:00:01 | | | | 26 | INDEX RANGE SCAN | IND_CATGPENREL_2 | 1 | 16 | 2 (0)| 00:00:01 | | | | 27 | BUFFER SORT | | 67 | 1072 | 6 (0)| 00:00:01 | | | | 28 | INDEX RANGE SCAN | IND_CATGPENREL_2 | 67 | 1072 | 2 (0)| 00:00:01 | | | | 29 | TABLE ACCESS BY INDEX ROWID | CATENTRY | 1 | 21 | 2 (0)| 00:00:01 | | | | 30 | INDEX UNIQUE SCAN | SYS_C0030043 | 1 | | 1 (0)| 00:00:01 | | | | 31 | TABLE ACCESS BY INDEX ROWID | CATENTDESC | 1 | 112 | 2 (0)| 00:00:01 | | | | 32 | INDEX UNIQUE SCAN | SYS_C0030032 | 1 | | 1 (0)| 00:00:01 | | | | 33 | TABLE ACCESS BY INDEX ROWID | EXTCATGPENSTORESEQ | 1 | 40 | 2 (0)| 00:00:01 | | | | 34 | INDEX RANGE SCAN | EXTCATGPENSTORESEQ_IND | 1 | | 1 (0)| 00:00:01 | | | | 35 | TABLE ACCESS BY INDEX ROWID | CATENTRY | 1 | 29 | 2 (0)| 00:00:01 | | | | 36 | INDEX UNIQUE SCAN | SYS_C0030043 | 1 | | 1 (0)| 00:00:01 | | | | 37 | INDEX RANGE SCAN | I0000304 | 3 | 66 | 2 (0)| 00:00:01 | | | | 38 | TABLE ACCESS BY INDEX ROWID | EXTCATGPENSTORESEQ | 1 | 40 | 2 (0)| 00:00:01 | | | | 39 | INDEX RANGE SCAN | EXTCATGPENSTORESEQ_IND | 1 | | 1 (0)| 00:00:01 | | | | 40 | TABLE ACCESS BY INDEX ROWID | CATENTREL | 1 | 16 | 2 (0)| 00:00:01 | | | | 41 | INDEX RANGE SCAN | IND5556045 | 1 | | 1 (0)| 00:00:01 | | | | 42 | PARTITION LIST SINGLE | | 1 | 10 | 0 (0)| | KEY | KEY | | 43 | INDEX UNIQUE SCAN | EXTCATENTRYDESC_IDX1 | 1 | 10 | 0 (0)| | KEY | KEY | | 44 | TABLE ACCESS BY INDEX ROWID | EXTCATENTREL | 1 | 17 | 2 (0)| 00:00:01 | | | | 45 | INDEX UNIQUE SCAN | EXTCATENTREL_PK | 1 | | 1 (0)| 00:00:01 | | | | 46 | TABLE ACCESS BY INDEX ROWID | OFFER | 1 | 45 | 3 (0)| 00:00:01 | | | | 47 | INDEX RANGE SCAN | I0000167 | 1 | | 2 (0)| 00:00:01 | | | | 48 | TABLE ACCESS BY INDEX ROWID | OFFERPRICE | 1 | 11 | 3 (0)| 00:00:01 | | | | 49 | INDEX RANGE SCAN | SYS_C0031126 | 1 | | 2 (0)| 00:00:01 | | | | 50 | INLIST ITERATOR | | | | | | | | | 51 | INDEX UNIQUE SCAN | SYS_C0031935 | 1 | 9 | 1 (0)| 00:00:01 | | | | 52 | VIEW PUSHED PREDICATE | | 1 | 81 | 5 (20)| 00:00:01 | | | | 53 | NESTED LOOPS | | 1 | 47 | 5 (20)| 00:00:01 | | | | 54 | NESTED LOOPS | | 1 | 25 | 4 (25)| 00:00:01 | | | | 55 | NESTED LOOPS | | 1 | 15 | 3 (34)| 00:00:01 | | | | 56 | VIEW | index$_join$_012 | 295 | 2065 | 3 (34)| 00:00:01 | | | | 57 | HASH JOIN | | | | | | | | | 58 | INDEX FAST FULL SCAN | EXTFILTER_PK | 295 | 2065 | 1 (0)| 00:00:01 | | | | 59 | INDEX FAST FULL SCAN | EXTFILTER_UK | 295 | 2065 | 1 (0)| 00:00:01 | | | | 60 | INDEX UNIQUE SCAN | EXTCATENTFILTER_PK | 1 | 8 | 0 (0)| | | | | 61 | TABLE ACCESS BY INDEX ROWID | EXTFILTERTYPE | 1 | 10 | 1 (0)| 00:00:01 | | | | 62 | INDEX UNIQUE SCAN | EXTFILTERTYPE_PK | 1 | | 0 (0)| | | | | 63 | TABLE ACCESS BY INDEX ROWID | EXTFILTERDESC | 1 | 22 | 1 (0)| 00:00:01 | | | | 64 | INDEX UNIQUE SCAN | EXTFILTERDESC_PK | 1 | | 0 (0)| | | | ----------------------------------------------------------------------------------------------------------------------------------------