Re: nullify an index - incorrect use of operators

From: Thomas Hardy <rnield_at_vision-consulting.com>
Date: Wed, 19 Aug 1998 15:04:03 +0100
Message-ID: <35DADB53.C7DAE908@vision-consulting.com>

InfoTech Consulting, Inc. wrote:

> I've heard that you can nullify an index if you use operators "incorrectly",
> such as in the following statement:
>
> sales.amount => 40000
>
> In other words, in order to prevent nullifying the index on the sales table,
> the operators should look like this:
>
> sales.amount >= 40000
>
> Is this true?
>

>YES!. You cannot have operators or functions on a column if you wish to use
the index.
An example of how to get around other probs where you have functions acting on the columns too would be :-

An example of how to over come the trunc forcing the non-use of an index

Non-index using code

SELECT 1 INTO dummy
FROM a_table at
WHERE TRUNC(at.effective_from_date) = TO_DATE(temp_from_date,'yyyymmdd');

Index using code

SELECT 1 INTO dummy
FROM a_table at
WHERE
at.effective_from_date BETWEEN TO_DATE(temp_from_date,'yyyymmdd')

                                                         AND

TO_DATE(temp_from_date,'yyyymmdd') + .99999;

Regard
Bob. Received on Wed Aug 19 1998 - 09:04:03 CDT