cardinality и фильтрация через коллекцию, подсказка оптимизатору на использование индекса.

create table test(
id_pok number not null,
val number,
constraint uk_test unique(id_pok)
);

insert into test
select rownum as id_pok,
       round(dbms_random.value(0,100)) as val
  from dual
 connect by rownum<=1000000

commit;

create or replace type type_n is table of number;

begin
 dbms_stats.gather_table_stats(user,tabname => 'test', cascade => true);
end;

--slr:1800
select sum(t.val)
  from test t
 where t.id_pok in (select column_value from table(type_n(10,20,30,40,50)) f)

SELECT STATEMENT, GOAL = ALL_ROWS
 SORT AGGREGATE
  HASH JOIN RIGHT SEMI
   COLLECTION ITERATOR CONSTRUCTOR FETCH
   TABLE ACCESS FULL TEST
 
--slr:17
select sum(t.val)
  from test t
 where t.id_pok in (select /*+ cardinality(f,5) */ column_value from table(type_n(10,20,30,40,50)) f )

SELECT STATEMENT, GOAL = ALL_ROWS
 SORT AGGREGATE
  NESTED LOOPS
   NESTED LOOPS
    SORT UNIQUE
     COLLECTION ITERATOR CONSTRUCTOR FETCH
    INDEX UNIQUE SCAN UK_TEST
   TABLE ACCESS BY INDEX ROWID TEST