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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Andy Sayer
Sent: Friday, May 19, 2023 1:43 AM
To: chrishna0007@xxxxxxxxx
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@xxxxxxxxx>
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
