Re: LIKE OPERATOR and Index usage

% is a wildcard.

You shouldn’t be concerned about the filter line appearing, your wildcard is on the far right of the string and the bits to the left of it seem fairly useful for the search criteria.

Of course, the obvious thing to do to find out would be to simulate some real data in your table and see how much work actually gets done.

Thanks,
Andy

On Mon, Sep 12, 2022 at 9:29 PM, Krishnaprasad Yadav <chrishna0007_at_gmail.com> wrote:

> 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
> --------------------------------------------------
> ABCDEFG
>
> SQL> select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS
> LAST'));
>
> PLAN_TABLE_OUTPUT
>
> --------------------------------------------------------------------------------
> SQL_ID c086akpstv5j4, child number 0
> -------------------------------------
> select * from test003 where id like 'ABCDEF%'
>
> Plan hash value: 2926429812
>
>
> --------------------------------------------------------------------------------
> --------
>
> | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time
> | B
> uffers |
>
> PLAN_TABLE_OUTPUT
>
> --------------------------------------------------------------------------------
>
>
> --------------------------------------------------------------------------------
> --------
>
> | 0 | SELECT STATEMENT | | 1 | | 1
> |00:00:00.01 |
> 2 |
>
> |* 1 | INDEX RANGE SCAN| INDEX0011 | 1 | 1 | 1
> |00:00:00.01 |
> 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