select B.* from (select A.*, rownum as rn  from (select  C.COMPANYNAME, C.COMPANYID as CLIENT, P.INSTRUMENT, P.VALID_FROM, P.FREQUENCY, P.DETAILEDFREQUENCY, cf.VAL_DATE ,
(select max(vv.val_date)        from tableE vv
  where vv.REQUEST = p.PKEY and vv.VALID = 'Y' and vv.dealer=8
  ) as ASOF  from tableN c
 inner join tableD dp on c.companyId=dp.companyId and dp.dealer=8
 inner join tableC p on p.VALID = 'Y' and p.SINGLE = 'Y' and  p.OWNER = c.COMPANYID
 inner join VM_CALENDAR_FREQUENCIES cf on cf.FREQUENCY=p.FREQUENCY and cf.DETAILEDFREQUENCY=p.DETAILEDFREQUENCY and cf.VAL_DATE between '20150427' and '20150427' and cf.REGIONALHUB in ('ALL',c.REGIONALHUB)
 inner join VM_IDS i on i.ID_KEY = P.INSTRUMENT and i.VALID = 'Y'
 inner join tableA b on i.BOND = b.PKEY and b.VALID = 'Y'
 inner join tableB_DEFAULT_PERMS pd on pd.dealer=12584 and pd.VALID ='Y' and
        (pd.START_DATE is null or '20150427' > pd.START_DATE) and(pd.END_DATE is null or '20150727' < pd.END_DATE)
  and pd.client=p.owner and ( (pd.bond_type=b.type and pd.bond_type in ('Sov','Corp','StatBody')) or (  pd.bond_type = 'Unclassified' and (b.type not in ('Sov','Corp','StatBody')  or b.type is null ) ) )  left outer join tableB_REQUEST_PERMS pm on pm.instrument = p.instrument and pm.CLIENT = p.owner
      and pm.DEALER = 12584 and pm.VALID = 'Y' and pm.request_id=p.pkey  and (pm.START_DATE is null or '20150427' > pm.START_DATE) and(pm.END_DATE is null or '20150427' < pm.END_DATE) where (c.ISCURRENT = 'Y' and c.ISDEALER = 'N' and c.REGIONALHUB in ('BLA', 'BLU', 'BLE', 'BBB', 'YY')) and ((pm.permission in ('A','B','M','Y') ) or ( (pm.permission is null or pm.permission ='P') and (pd.permission in ('A','B','M','Y') ) )) order by c.COMPANYNAME Asc nulls first  , c.COMPANYNAME, cf.VAL_DATE asc ) A  where  rownum <= 30) B  where  rn > 1

	План (частичка, ибо длинный):

зиг>select B.* from (select A.*, rownum as rn  from (select  C.COMPANYNAME, C.COMPANYID as CLIENT, P.INSTRUMENT, P.VALID_FROM, P.FREQUENCY, P.DETAILEDFREQUENCY, cf.VAL_DATE ,
зиг>(select max(vv.val_date)        from tableE vv
зиг>  where vv.REQUEST = p.PKEY and vv.VALID = 'Y' and vv.dealer=8
зиг>  ) as ASOF  from tableN c
зиг> inner join tableD dp on c.companyId=dp.companyId and dp.dealer=8
зиг> inner join tableC p on p.VALID = 'Y' and p.SINGLE = 'Y' and  p.OWNER = c.COMPANYID
зиг> inner join VM_CALENDAR_FREQUENCIES cf on cf.FREQUENCY=p.FREQUENCY and cf.DETAILEDFREQUENCY=p.DETAILEDFREQUENCY and cf.VAL_DATE between '20150427' and '20150427' and cf.REGIONALHUB in ('ALL',c.REGIONALHUB)
зиг> inner join VM_IDS i on i.ID_KEY = P.INSTRUMENT and i.VALID = 'Y'
зиг> inner join tableA b on i.BOND = b.PKEY and b.VALID = 'Y'
зиг> inner join tableB_DEFAULT_PERMS pd on pd.dealer=12584 and pd.VALID ='Y' and
зиг>        (pd.START_DATE is null or '20150427' > pd.START_DATE) and(pd.END_DATE is null or '20150727' < pd.END_DATE)
зиг>  and pd.client=p.owner and ( (pd.bond_type=b.type and pd.bond_type in ('Sov','Corp','StatBody')) or (  pd.bond_type = 'Unclassified' and (b.type not in ('Sov','Corp','StatBody')  or b.type is null ) ) )  left outer join tableB_REQUEST_PERMS pm on pm.instrument = p.instrument and pm.CLIENT = p.owner
зиг>      and pm.DEALER = 12584 and pm.VALID = 'Y' and pm.request_id=p.pkey  and (pm.START_DATE is null or '20150427' > pm.START_DATE) and(pm.END_DATE is null or '20150427' < pm.END_DATE) where (c.ISCURRENT = 'Y' and c.ISDEALER = 'N' and c.REGIONALHUB in ('BLA', 'BLU', 'BLE', 'BBB', 'YY')) and ((pm.permission in ('A','B','M','Y') ) or ( (pm.permission is null or pm.permission ='P') and (pd.permission in ('A','B','M','Y') ) )) order by c.COMPANYNAME Asc nulls first  , c.COMPANYNAME, cf.VAL_DATE asc ) A  where  rownum <= 30) B  where  rn > 1

Опишу свои мысли:
Несколько таблиц, вероятно больших, джойнятся между собой, а потом результат сильно усекается условиями, т.е. получается, например, 2.000X2.000=4.000.000 строк и потом условия по этому множеству дают всего 200 строчек, то есть 99% данных, выбранных из базы, на которые было истрачено время, просто выкидываются. Вместо этого проще перебрать условиями каждую из таблиц и потом джойнить маленькие остатки. Будет в разы быстрее. Например, вот этот кусок:
[sql]
inner join tableB_DEFAULT_PERMS pd 
    on  pd.dealer=12584 
    and pd.VALID ='Y' 
    and(pd.START_DATE is null or '20150427' > pd.START_DATE) 
    and(pd.END_DATE is null or '20150727' < pd.END_DATE)
      and pd.client=p.owner 
    and (   (pd.bond_type=b.type and pd.bond_type in ('Sov','Corp','StatBody')) 
        or 
        (  pd.bond_type = 'Unclassified' and (b.type not in ('Sov','Corp','StatBody')  or b.type is null ) )

inner join 
    (
     select pd.client /* и может еще колонки, нужные для финальной таблицы */
    from tableB_DEFAULT_PERMS pd 
    on  pd.dealer=12584 
    and pd.VALID ='Y' 
    and(pd.START_DATE is null or '20150427' > pd.START_DATE) 
    and(pd.END_DATE is null or '20150727' < pd.END_DATE)
    and (   (pd.bond_type=b.type and pd.bond_type in ('Sov','Corp','StatBody')) 
        or 
        (  pd.bond_type = 'Unclassified' and (b.type not in ('Sov','Corp','StatBody')  or b.type is null ) ) 
    ) as pd  on pd.client=p.owner

( select max(vv.val_date)        from tableE vv
  where vv.REQUEST = p.PKEY and vv.VALID = 'Y' and vv.dealer=8
  ) as ASOF

(select max(vv.val_date)        from tableE vv
  where vv.REQUEST = p.PKEY and vv.VALID = 'Y' and vv.dealer=8
) as ASOF

select B.* from 
(select A.*, rownum as rn  from 
     (select  C.COMPANYNAME, C.COMPANYID as CLIENT, P.INSTRUMENT, P.VALID_FROM, P.FREQUENCY, P.DETAILEDFREQUENCY, cf.VAL_DATE ,
              (select max(vv.val_date)        
              from tableE vv
              where vv.REQUEST = p.PKEY and vv.VALID = 'Y' and vv.dealer=8
              ) as ASOF  
      from tableN c
       inner join tableD dp on c.companyId=dp.companyId and dp.dealer=8
       inner join tableC p on p.VALID = 'Y' and p.SINGLE = 'Y' and  p.OWNER = c.COMPANYID
       inner join VM_CALENDAR_FREQUENCIES cf on cf.FREQUENCY=p.FREQUENCY and cf.DETAILEDFREQUENCY=p.DETAILEDFREQUENCY and cf.VAL_DATE between '20150427' and '20150427' and cf.REGIONALHUB in ('ALL',c.REGIONALHUB)
       inner join VM_IDS i on i.ID_KEY = P.INSTRUMENT and i.VALID = 'Y'
       inner join tableA b on i.BOND = b.PKEY and b.VALID = 'Y'
       inner join tableB_DEFAULT_PERMS pd on pd.dealer=12584 and pd.VALID ='Y' and
              (pd.START_DATE is null or '20150427' > pd.START_DATE) and(pd.END_DATE is null or '20150727' < pd.END_DATE)
              and pd.client=p.owner and ( (pd.bond_type=b.type and pd.bond_type in ('Sov','Corp','StatBody')) 
                                         or (  pd.bond_type = 'Unclassified' and (b.type not in ('Sov','Corp','StatBody')  or b.type is null ) ) 
                                         )  
       left outer join tableB_REQUEST_PERMS pm on pm.instrument = p.instrument and pm.CLIENT = p.owner
                                                  and pm.DEALER = 12584 
                                                  and pm.VALID = 'Y' 
                                                  and pm.request_id=p.pkey  
                                                  and (pm.START_DATE is null or '20150427' > pm.START_DATE) 
                                                  and(pm.END_DATE is null or '20150427' < pm.END_DATE) 
        where (c.ISCURRENT = 'Y' and c.ISDEALER = 'N' and c.REGIONALHUB in ('BLA', 'BLU', 'BLE', 'BBB', 'YY')) 
        and ((pm.permission in ('A','B','M','Y') ) 
             or ( (pm.permission is null or pm.permission ='P') and (pd.permission in ('A','B','M','Y') ) )
            ) 
        order by c.COMPANYNAME Asc nulls first, c.COMPANYNAME, cf.VAL_DATE asc ) A  
  where rownum <= 30) B  
where  rn > 1;

inner join tableB_DEFAULT_PERMS pd
 on pd.dealer=12584
 and pd.VALID ='Y'
 and (pd.START_DATE is null or '20150427' > pd.START_DATE)
 and (pd.END_DATE is null or '20150727' < pd.END_DATE)
 and pd.client=p.owner
 and ((pd.bond_type=b.type and pd.bond_type in ('Sov','Corp','StatBody')) or ...... )

S>>Единственно, если кто-то сдуру заменит inner на outer... от тогда руки действительно отрывать надо

SELECT * FROM A
  INNER JOIN B ON A.Id = B.A_id
  WHERE A.Name = '123'           -- OK
-- =>
SELECT * FROM A
  LEFT JOIN B ON A.Id = B.A_id
  WHERE A.Name = '123'           -- OK


-- vs

SELECT * FROM A
  INNER JOIN B ON A.Id = B.A_id
    AND A.Name = '123'           -- so-so
-- =>
SELECT * FROM A
  LEFT JOIN B ON A.Id = B.A_id
    AND A.Name = '123'           -- oops

cf.VAL_DATE = DATE '2015-04-27'