Optimizing SQL with LIKE expression

This post is focused on talking about how to optimize several SQL statements with LIKE expression in oracle database. Now let me quote some important knowledge point about the pattern of LIKE condition on 20c.

The LIKE conditions specify a test involving pattern matching. Whereas the equality operator (=) exactly matches one character value to another, the LIKE conditions match a portion of one character value to another by searching the first value for the pattern specified by the second.

The pattern can contain special pattern-matching characters:

An underscore (_) in the pattern matches exactly one character (as opposed to one byte in a multibyte character set) in the value.

A percent sign (%) in the pattern can match zero or more characters (as opposed to bytes in a multibyte character set) in the value. The pattern ‘%’ cannot match a null.

In general a LIKE expression has four number of situation in a SQL query statement. Take, for example, some random combinations of between ‘%’ and character strings ‘qw’.

• qw%
• %qw
• %qw%
• q%w

Middle-N, Pagination Query and Optimization

So far I’ve written 3 number of notes in the series of “top-n, pagination query and optimization”, they’re as follows.

• Top-N, Pagination Query and Optimization – 1
• Top-N, Pagination Query and Optimization – 2
• Top-N, Pagination Query and Optimization – 3

A few days ago in particular Nigel Bayliss gave me some good ideas that was never in my mind (by communicating with email back and forth) so it is a reason why today I am going to write this blog note. Here’s my test demo (running on 19.3) creating table “staff”.

Top-N, Pagination Query and Optimization – 3

About how to optimize the query of Top-N and Pagination on oracle database I’ve written total two number of notes (here and here). Today this note is the last one in this series. From time to time (occasionally) there appears some strange bug on descending index that I once mentioned on the previous note. Thus I only can talk about several SQL queries with ascending index (default) and here’s my demo via creating test table and quickly inserting some random data into all columns of that table.

Top-N, Pagination Query and Optimization – 2

After sending Tweets message based on my previous blog post on Twitter, unexpectedly, Jonathan Lewis, Andrew Sayer, Patrick Jolliffe (oracle gurus all over the world) and Tiger Liu (China) all gave me so many pretty nice comments and very valuable opinions, so I have added some stuff (at that moment my confusion about whether it’s bug or not) on that blog note with [Update Mar 04, 2020: ……] and at the same time also marked those old sentences (wrong analysis process) using a conspicuous strikethrough.

According to their good suggestions I’ve done two funny test as well:

• index full scan VS index range scan
• descending index oddity (bug) VS ascending index (default)

Top-N, Pagination Query and Optimization

At the end of last month I once wrote a blog note about Classic Pagination, at that moment I just mentioned that using 3 approaches (ROWNUM, ROW_NUMBER() and OFFSET … FETCH …) to do pagination query and never made a comparison on their execution plan, so which is an intention of today this note as well.

Avoid changing the ROWNUM query result in an interval range

At the end of last month I once introduced classic pagination using the ROWNUM query on Oracle database. Today this blog note I’ll continue to introduce how to avoid changing the ROWNUM query result in an interval range according to the first two methods (rownum, row_number) on that blog note.

For vividly simultaing the case that seems like the Production System in reality. Firstly I need build a test table named “person” with 1e7 (10,000,000) lines of random data. Here’s a snippet of SQL code.

WordPress Bug

Yesterday evening I submitted an issue on WP Forums unfortunately which hasn’t been shown on the forum lists and I posted it again as a result it still has not been found by me. Hence I decide to describe this bug on today’s this blog note.

Here’s the thing I finished reading this post “Getting the most out of in-memory – part 2” from Connor McDonald I noticed that several key and important SQL code has been marked with blue color by him so that being able to attract readers’ attention, which prompts me to mark my demo code using the eye-catching color as well.