Re: LIKE OPERATOR and Index usage

Hi Mark,

Appologies, i missed your mail.

I really want to Thank you for reverting me.

I certainly look into your suggestion and work on them

Regards,
Krishna

On Mon, 12 Sep, 2022, 17:39 Mark W. Farnham, <mwf_at_rsiz.com> wrote:

> 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