RE: LIKE OPERATOR and Index usage

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Mon, 12 Sep 2022 08:09:11 -0400
Message-ID: <27f901d8c6a0$78bb7dd0$6a327970$_at_rsiz.com>

As usual Sayan nailed this in one.  

I do have some advice spurred by your apparent feature research.  

It appears you are building an id tag as <some alpha string> <underbar> <monotonically increasing number>. (Why Oracle used the optically nice underbar as a wildcard single char instead of question mark is a question for the Sphinx.)  

If you insist on that pattern, pick an out of reason upper bound for the number (always difficult, but consider building in cycling at the top and purging old) and left pad the numbers with zero), so 1 is 0000001, 2 is 0000002, and so forth. This will be useful in future when you want to query a range that uses the index.  

I would use two columns and a two column index: One option is ID_label and ID_number, but usually this sort of thing is usually best done with ID_label, ID_time (you sort out which time exactly between date and timestamp.)  

I know, two columns instead of one might seem like a pain in the ass at the moment, but if you are checking performance I immediately suspect that something large is brewing.  

IF this going to be “BIG,” then you probably want to date partition for both performance and future purging. That will also make it easy and performant to query a time range for all ID_label values (which is awkward if each has an independently monotonically increasing number, if you want gapless numbers, and easy to use for ordering anything by time.)  

Now, IF this is a coordinating tag to link together rows from more than one logically* grouped attribute tables of an event, then this table becomes: ID_time, ID_label, ID_unique_id, with the ID_unique_id probably generated from a sequence. (* logically can include frequency of population, type of information, or simply be a work around for Oracle’s suboptimal performance [but transparent ease of use for very large numbers of columns], and probably some more things that just are not in mind at the moment.)  

Sigh. Now it has grown to three columns. But IF it is big and there are multiple tables that becomes fast, smaller in aggregate (the label and time are only in the coordination table), and easy to manage.  

Good luck. Beware the literal underbar in search strings AND beware overloading string id, unique tag in a single column.  

mwf  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sayan Malakshinov Sent: Monday, September 12, 2022 3:05 AM To: Krishnaprasad Yadav
Cc: Oracle L
Subject: 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

Received on Mon Sep 12 2022 - 14:09:11 CEST