Re: Index Usage on column with (+,-) operators

Hi Krishna,

To use an index you must be comparing a value to the pure indexed expression. Oracle does not simplify equations for you.

You can’t index A and expect it to be used for anything other than A [=,<>,>,>=,<,<=] value.

There are a couple exceptions to the rule, eg the CBO is able to apply substr, trunk, or upper/lower to both sides of an expression to take advantage of an FBI. But in general, it’s not going to be doing math.

Thanks,
Andy

On Thu, May 18, 2023 at 9:31 PM, Krishnaprasad Yadav <chrishna0007_at_gmail.com> wrote:

> Dear Gurus,
>
> currently we are trying to know , index usage on queries which are
> having +,- operators in where clause
>
> for example :
>
> SQL> create table t1 (id number, id1 number);
>
> Table created.
>
> SQL> begin
> 2 for i in 1..1000 loop
> 3 insert into t1 values(i,i+1);
> 4 end loop;
> 5 end ;
> 6 /
>
> PL/SQL procedure successfully completed.
>
> Now , when we fire the query like :
>
> SQL> select count(*) from t1 where id+1 =999 ;
>
> COUNT(*)
> ----------
> 1
>
>
> Execution Plan
> ----------------------------------------------------------
> Plan hash value: 3724264953
>
> ---------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
> ---------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
> | 1 | SORT AGGREGATE | | 1 | 13 | | |
> |* 2 | TABLE ACCESS FULL| T1 | 1 | 13 | 2 (0)| 00:00:01 |
> ---------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 2 - filter("ID"+1=999)
>
> Note
> -----
> - dynamic statistics used: dynamic sampling (level=2)
>
> From above clearly index is not used , but when i try to have functional
> index like below :
>
> SQL> create index ind4 on t1(id+1);
>
> Index created.
>
> SQL> select count(*) from t1 where id+1 =999
> 2 ;
>
> COUNT(*)
> ----------
> 1
>
>
> Execution Plan
> ----------------------------------------------------------
> Plan hash value: 3569200259
>
> --------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
> --------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
> | 1 | SORT AGGREGATE | | 1 | 13 | | |
> |* 2 | INDEX RANGE SCAN| IND4 | 1 | 13 | 1 (0)| 00:00:01 |
> --------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 2 - access("ID"+1=999)
>
> Above it uses the index , we understand is this because column get treated
> as functional column when any operator is used along with a number or even
> by providing column name .
>
> select count(*) from t1 where id-id1>100;
>
> COUNT(*)
> ----------
> 0
>
>
> Execution Plan
> ----------------------------------------------------------
> Plan hash value: 3724264953
>
> ---------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
> ---------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| 00:00:01 |
> | 1 | SORT AGGREGATE | | 1 | 26 | | |
> |* 2 | TABLE ACCESS FULL| T1 | 1 | 26 | 2 (0)| 00:00:01 |
> ---------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 2 - filter("ID"-"ID1">100)
>
>
> Above also doesn't work , so in such situation apart from index usage
> ability any other workaround can help or extended stats on both column can
> help .
>
> Regards,
> Krishna
>
>

--
http://www.freelists.org/webpage/oracle-l