Re: LIKE OPERATOR and Index usage

Hi Krishna,

Yes, that is an expected behaviour, because of wildcard characters. Btw '_' is also a wildcard.
In fact, the most important thing there is an ACCESS predicate: it uses a part of the pattern before the first wildcard. Compare these 3 examples:
https://gist.github.com/xtender/a7b20bf198d1a0e12021bc7d8c9d10a8

select id from krishna where id like 'krishna_2%';
select id from krishna where id like 'krishna\_2%' escape '\';
select id from krishna where id like 'krishna\_20' escape '\';

On Mon, Sep 12, 2022 at 6:33 AM Krishnaprasad Yadav <chrishna0007_at_gmail.com> wrote:

> Dear Gurus,
>
> we have tried some test case where we are using like operator in query
> and column used is index , however in execution plan we see it going to
> access-filter , wanted to know is this is expected behaviour , below is
> test case :
>
> below is tables having 18 rows :
>
> SQL> select * from test003;
>
> ID
> --------------------------------------------------
> KRISHNA_1
> KRISHNA_2
> KRISHNA_3
> KRISHNA_4
> KRISHNA_5
> KRISHNA_6
> KRISHNA_7
> KRISHNA_8
> KRISHNA_9
> KRISHNA_10
> KRISHNA_11
> KRISHNA_12
> KRISHNA_13
> KRISHNA_14
> KRISHNA_15
> KRISHNA_16
> KRISHNA_17
> KRISHNA_18
>
> table " Test003" has one column named ID .
>
> we created index on ID column ,post below is the plan :
>
> SQL> select /*+ gather_plan_statistics TEST001 */ id from TEST003 where
> id like 'KRISHNA_2%';
>
> ID
> --------------------------------------------------
> KRISHNA_2
>
> SQL> select * from table(dbms_xplan.display_cursor(Format=>'ALLSTATS
> LASt'));
>
> PLAN_TABLE_OUTPUT
>
> ----------------------------------------------------------------------------------------------------
> SQL_ID gx7pvjjupmhyc, child number 0
> -------------------------------------
> select /*+ gather_plan_statistics TEST001 */ id from TEST003 where id
> like 'KRISHNA_2%'
>
> Plan hash value: 2926429812
>
>
> ----------------------------------------------------------------------------------------
> | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time
> | Buffers |
>
> ----------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 1 | | 1
> |00:00:00.01 | 2 |
> |* 1 | INDEX RANGE SCAN| INDEX0011 | 1 | 1 | 1
> |00:00:00.01 | 2 |
>
> ----------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
>
> * 1 - access("ID" LIKE 'KRISHNA_2%') filter("ID" LIKE
> 'KRISHNA_2%')*
>
> Now my index is getting used , but still i see filter in acess part , my
> understanding to fact now in current tables which is testing one has less
> rows so buffer gets are less ,but let assume table for 10M records we see
> buffer gets will be increased due to acess filter
>
> is this expected or can we have anyworkaround where it goes in
> acess itself so for bug tables query we can optimize the things
>
> *Regards,*
> *Krishna *
>
>

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE
http://orasql.org

--
http://www.freelists.org/webpage/oracle-l