LIKE OPERATOR and Index usage
Date: Mon, 12 Sep 2022 11:02:53 +0530
Message-ID: <CAO8FHeUryCs1rmMrhd8FJtW7-S5u9rzSht=Gq3PY0_oqNY1UBQ_at_mail.gmail.com>
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 *
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Sep 12 2022 - 07:32:53 CEST