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

Well, sorry, as Mark pointed out, select count(*) from t1 where id-id1>100; becomes select count(*) from t1 where id->100+id1; would still scan the whole table as
id1 is not a constant, therefore 100+id1 can not be precalculated as the db needs to read the row to evaluate.

Am 19.05.2023 um 18:14 schrieb Lothar Flatz:
> 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_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