Hi Krishna,
please understand that an index is precreated based on the search
condition expression.
An index consumes space, it is physical.
You either have to create a Index (id+n) for every possible n, n being
every value you might want to search on.
Your you just create an index on t(id) and rephrase your query.
e.g. select count(*) from t1 where id-id1>100; becomes select count(*)
from t1 where id->100+id1;
That way you just have on index.
The rule is : never use transformation on your indexed column if you
can avoid it.
Thanks
Lothar
m 19.05.2023 um 15:53 schrieb Mark W. Farnham:
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
