Re: help to improve

Alexei,

  select
  ...
  from ir, gateways, ...
  where
  ...
    and ( (ir.fiGW_src is null and gateways.fiGW_id is null)

         or (ir.fiGW_src = gateways.fiGW_id))

If ir.fiGW_src & fiGW_id will never have the value -1 you could improve performance by using function-based indexes:

  create index ir_n99 on ir (nvl(fiGW_src, -1));   create index gateways_n99 on gateways (nvl(fiGW_id, -1));

then use the following join:

  select
  ...
  from ir, gateways, ...
  where
  ...
    and nvl(ir.fiGW_src, -1) = nvl(gateways.fiGW_id, -1)

Regards

af_at_ipclearingboard.com (Alexei Fedotov) wrote in message news:<e6d56cc2.0202200612.3adbcccd_at_posting.google.com>...
> could you help me to improve the following where clause:
>
> select
> ...
> from ir, gateways, ...
> where
> ...
> and (ir.fiGW_src in (select fiGW_id from gateways) or ir.fiGW_src is null)
> and ir.fiGW_src = gateways.fiGW_id (+)
Received on Wed Feb 20 2002 - 23:57:24 CET