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

Dear All,

Thanks for reverting back .

Thank you all for giving me understanding about this , in our environment we come across many queries where similar conditions are used in where clause. In order to tune we are trying to figure out a way out , hence i was trying to demonstrate index usage .

Thanks again to all .

Regards,
Krishna

On Sat, 20 May 2023 at 12:06, Lothar Flatz <l.flatz_at_bluewin.ch> wrote:

> 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 <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