Re: LIKE OPERATOR and Index usage

Hi Andy,

Thanks , it made me more clear

Regards,
Krishna

On Tue, 13 Sept 2022 at 10:35, Andy Sayer <andysayer_at_gmail.com> wrote:

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