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

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Fri, 19 May 2023 09:53:23 -0400
Message-ID: <114101d98a59$48009360$d801ba20$_at_rsiz.com>

AND (not but)  

in your first example of adding a constant to a column to create a function based index, usually it is best to simply alter the query, for example:  

select count(*) from t1 where id+1 =999 ;  

becomes  

select count(*) from t1 where id = 999-1; (oracle will do that math for you, or you could just write it as 998).  

If you need to use an index on the difference between two columns as in your query  

select count(*) from t1 where id-id1>100;  

then you could create that function based index:  

create index t1_id_minus_id1 on t1(id-id1);  

I’m not sure what you are trying to accomplish. Andy was exactly correct, and this is just a little more information guessing you are trying to understand something.  

mwf    

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Andy Sayer Sent: Friday, May 19, 2023 1:43 AM
To: chrishna0007_at_gmail.com
Cc: Oracle L
Subject: 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

Received on Fri May 19 2023 - 15:53:23 CEST