I've found that even though you list the tables in a specific order, Oracle may not use that order when filtering the data. This can result in large run times. ie:

select * from item_ven where vendor = 'ACME';
results take 2 seconds

select * from item_file where lead_time >=7 and leadtime < 14;
results take 5 minutes

select i.item,
       i.description,
       v.vendor
  from item_ven v,
       item_file i
 where v.vendor = 'ACME'
   and i.item = v.item
   and i.lead_time >= 7
   and i.lead_time < 14;
results take 5 minutes

even though you, as the programmer, may know that VENDOR is more restrictive and thus put item_ven first, Oracle may decide to resequence the query and hit item_file first. Putting /*+ ordered */ tells it to hit the tables in the order you listed them - ie:

select /*+ ordered */
       i.item,
       i.description,
       v.vendor
  from item_ven v,
       item_file i
 where v.vendor = 'ACME'
   and i.item = v.item
   and i.lead_time >= 7
   and i.lead_time < 14;
results take 3 seconds

You can also create/delete indexes within PL/SQL. Useful for reporting on large active files that you don't want to bog down with extra index overhead during normal processing hours. The creation of an index is quite fast and can significantly speed up queries. I can send you sample code if you need it.