SQL> create table small as select rownum id, lpad ('*',  100 , '*') data from dual connect by level <=  1000 ;

Table created

SQL> create table big as select rownum id, lpad ('*',  100 , '*') data from dual connect by level <=  1000000 ;

Table created

SQL> alter table small add primary key (id);

Table altered

SQL> alter table big add primary key (id);

Table altered

SQL> set timing on;

SQL> declare d small.data%type;
   2   begin
   3     for i in  1 .. 1000  loop
   4       select data into d from small where id = i;
   5     end loop;
   6   end;
   7   /

PL/SQL procedure successfully completed

Executed in  0 , 079  seconds

SQL> declare d big.data%type;
   2   begin
   3     for i in  1 .. 1000  loop
   4       select data into d from big where id =  1000  * i;
   5     end loop;
   6   end;
   7   /

PL/SQL procedure successfully completed

Executed in  14 , 563  seconds

SQL> declare d big.data%type;
   2   begin
   3     for i in  1 .. 1000  loop
   4       select data into d from big where id =  1000  * i;
   5     end loop;
   6   end;
   7   /

PL/SQL procedure successfully completed

Executed in  0 , 078  seconds

SQL> 
SQL> declare d small.data%type;
   2   begin
   3     for i in  1 .. 1000  loop
   4       select data into d from small where id = i;
   5     end loop;
   6   end;
   7   /

PL/SQL procedure successfully completed

Executed in  0 , 047  seconds

SQL> 
SQL> declare d big.data%type;
   2   begin
   3     for i in  1 .. 1000  loop
   4       select data into d from big where id =  1000  * i;
   5     end loop;
   6   end;
   7   /

PL/SQL procedure successfully completed

Executed in  0 , 047  seconds

SQL> 
SQL> declare d small.data%type;
   2   begin
   3     for i in  1 .. 1000  loop
   4       select data into d from small where id = i;
   5     end loop;
   6   end;
   7   /

PL/SQL procedure successfully completed

Executed in  0 , 047  seconds

SQL> 
SQL> declare d big.data%type;
   2   begin
   3     for i in  1 .. 1000  loop
   4       select data into d from big where id =  1000  * i;
   5     end loop;
   6   end;
   7   /

PL/SQL procedure successfully completed

Executed in  0 , 046  seconds

SELECT *
FROM big
WHERE TYPE_ID =  1 
  and ... условия...

  1  

select /*+ first_rows */ field1, field2, ....
from table1 [, table2 ...]
where <условия>
order by date_field desc 

  2 

select * from table where id = :id

  3 

select * from table where parent_id = :id order by something

  4 

/* нечто навороченное сложное расчетное */