query1: ====== select item_id, comp_id, rownum as proc_sqn, decode(rownum, 1, 21, 25) as status_num, systimestamp, 1, 3 from (select cf.comp_id, cf.item_id from company cf where cf.item_id in (select column_value from the(select cast(:v_arr as long_array) from dual)) group by cf.comp_id, cf.item_id order by max(cf.company_yr), max(decode(cf.period, 27, 2300, 26, 2200, 25, 2100, 24, 2000, 23, 1900, 22, 1800, 21, 1700, 20, 1600, 19, 1500, 18, 1400, 17, 1300, 16, 1200, 1, 1100, 7, 1000, 11, 900, 5, 800, 10, 700, 4, 600, 6, 500, 9, 400, 3, 300, 8, 200, 2, 100, cf.period ) ), cf.item_id ); plan1: ===== select statement all_rowscost: 129,553,642 bytes: 899,548 cardinality: 34,598 8 count 7 view bdapapp. cost: 129,553,642 bytes: 899,548 cardinality: 34,598 6 sort order by cost: 129,553,642 bytes: 622,764 cardinality: 34,598 5 hash group by cost: 129,553,642 bytes: 622,764 cardinality: 34,598 4 nested loops cost: 129,553,634 bytes: 622,764 cardinality: 34,598 1 table access full table company cost: 25,393 bytes: 93,605,600 cardinality: 5,850,350 3 collection iterator pickler fetch 2 fast dual cost: 2 cardinality: 1 ----------------------------------------------*************----------------------------------------------------- query2: ====== select item_id, comp_id, rownum as proc_sqn, decode(rownum, 1, 21, 25) as status_num, systimestamp, 1, 3 from (select cf.comp_id, cf.item_id from company cf where cf.item_id in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10) group by cf.comp_id, cf.item_id order by max(cf.company_yr), max(decode(cf.period, 27, 2300, 26, 2200, 25, 2100, 24, 2000, 23, 1900, 22, 1800, 21, 1700, 20, 1600, 19, 1500, 18, 1400, 17, 1300, 16, 1200, 1, 1100, 7, 1000, 11, 900, 5, 800, 10, 700, 4, 600, 6, 500, 9, 400, 3, 300, 8, 200, 2, 100, cf.period ) ), cf.item_id ); plan2: ===== select statement all_rowscost: 30 bytes: 468 cardinality: 18 7 count 6 view bdapapp. cost: 30 bytes: 468 cardinality: 18 5 sort order by cost: 30 bytes: 288 cardinality: 18 4 hash group by cost: 30 bytes: 288 cardinality: 18 3 inlist iterator 2 table access by index rowid table company cost: 28 bytes: 288 cardinality: 18 1 index range scan index fk9_company cost: 12 cardinality: 18