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

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