Re: HELP avoiding full table scans with NVL and DECODE

If column_i you use is always not null then you can try this:

SELECT col1, col2, col3...
FROM table
WHERE column1 like nvl(variable1,'%')
AND column2 like nvl(variable2,'%') and so on ...

Operation LIKE is a little longer than (=) in case if variable_i is null, but you can use indexes on column_i.
If variable_i is not null and doesn't contain symbols like '%' and '_' then ORACLE replaces operation
LIKE to (=).

Kel Brigman wrote <01be5c68$5ff02380$f5377a95_at_twmmpe01> ...
>I need to tune numerous queries that use NVL with DECODE to allow users to
>leave fields null when querying. This seems to force a full table scan! The
>table has grown beyond 7,000,000 rows, and performance now terrible!
>
>Works like this:
>
>SELECT col1, col2, col3...
>FROM table
>WHERE NVL(variable1, 'T') = DECODE(variable1, NULL, 'T', column1)
>AND NVL(variable2, 'T') = DECODE(variable2, NULL, 'T', column2)
>...
>
>if variable 1 is NOT NULL, but variable 2 IS NULL, then you get
>WHERE variable1 = column1
>AND 'T' = 'T' (not 'T' = column2)
Received on Sat Feb 20 1999 - 19:50:11 CET