Re: LIKE OPERATOR and Index usage

Hi Sayan,

Thanks for revert !!

I tried to check test case without wildcard , still situation is same .

SQL> insert into test003 values('ABCDEFG'   2 );

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test003 where id like 'ABCDEF%';

ID

     2 |

PLAN_TABLE_OUTPUT

Predicate Information (identified by operation id):

   1 - access("ID" LIKE 'ABCDEF%')

       filter("ID" LIKE 'ABCDEF%')

So as you mentioned it is expected behaviour with or without wildcard

Regards,
Krishna

On Mon, 12 Sept 2022 at 12:35, Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:

> 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