Index Usage on column with (+,-) operators

From: Krishnaprasad Yadav <chrishna0007_at_gmail.com>
Date: Fri, 19 May 2023 10:00:51 +0530
Message-ID: <CAO8FHeWf9GVBkr3rZ58PkaJXOzJ+-pgdf-_d1Xwg7nTm9otT7A_at_mail.gmail.com>

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

Received on Fri May 19 2023 - 06:30:51 CEST