Re: Optimization of a query with NVL in the where part

Antonio,

You have probably already recognized that the NVL function in your WHERE clause invalidates using any index. If you are on Oracle 8i, then you can use Function Based Indexes. Read the Oracle documentation on the usage. They will do what you want.

Additionally, I've never seen NVL used as you have it. Your usage says "if column1 is NULL, then use the contents of column2". Normally (but not always), NVL is used to say "if column1 is NULL, then use this default value", i.e. NVL(c1,'some value').

If your usage is correct for particular situation, then pardon my ramblings.

HTH,
Brian

Antonio Sant wrote:
>
> Hi there!
>
> Suppose we have tab_a and tab_b: 2 column the first 4 the second and we want
> to do this query:
>
> Select tab_b.c3,tab_a.c2
> From tab_a,tab_b
> Where nvl(tab_a.c1,tab_a.c2) = nvl(tab_b.c1,tab_b.c2)
>
> What can we do?
> It's quite simple to write, but when the table become HUGE...
>
> Hope for help!
 

-- 
========================================
Brian Peasland
Raytheons Systems at
  USGS EROS Data Center
These opinions are my own and do not
necessarily reflect the opinions of my 
company!
========================================