1

Scenario

Database

  • Oracle Database 11g

Table

  • Users
    • ID Number, PK
    • CODE Number, Indexed, Unique, Not null
    • FIRST_NAME VARCHAR(100), Not null
    • LAST_NAME VARCHAR(100), Not null
    • EMAIL VARCHAR(50), Not null

Search

  • Except for ID and CODE, all other columns has mixed case.
  • I need to search a user by an string. The search should be case insensitive.
  • The string is a partial value. (ex: 'foo' to search for 'foobar')
  • The pattern can be present on any part of a value on the colum. (ex: 'ob' for foobar)
  • The pattern can be present on any of the table fields.

SQL

SELECT *
FROM users u
WHERE 
u.code = COALESCE( to_number( REGEXP_REPLACE(:pattern, '[^0-9]+', '') ), -1)
OR upper(u.email) LIKE upper(:pattern)
OR upper(u.last_name) LIKE upper(:pattern) 
OR upper(u.first_name) LIKE upper(:pattern) 

Query Plan

  • Results show a full table scan with an OR Filter Predicate

Question

  1. What is the best strategy to optimize this query? Can we reduce the cost somehow?
  2. Is beneficial to use an index on the VARCHAR2 column(s)? Even with the search using the LIKE operator?
  3. Can we use a different SQL(s) to take advantage of the indexes and join the results at the end?

Text Search Engines

  • I'm aware that we could use an text search engine to handle that. But let's discuss what can be done using the database.

Related Links

  • 1
    I'm not seeing a great approach besides text search functionality. The primary problem is that you are performing an operation on the column before you can analyze it. Is your collation case sensitive? It seems like it is based on your where statement. By performing any operation on the column you are searching (like upper(u.email)), you render the index useless. If you were to create a process like a trigger or stored procedure that would perform that function on the column but insert that value as another column, you could possibly index and use that column instead. – Shaulinator Jul 9 '18 at 17:05
  • thanks for pointing that out (operation before analysis). The of other column strategy is not a viable in my case. Since it is names, the data has mixed case, but the search should be case insensitive. – linuxunil Jul 10 '18 at 8:18
  • 1
    @Shaulinator - concatinate and UPPER() the columns 8nto a Virtual Column then INDEX – Michael Kutz Jul 10 '18 at 10:13
  • That is an good idea! I'll investigate. Found an article about the Virtual Columns and Indexes (under the hood). – linuxunil Jul 10 '18 at 12:44
  • 1
    @MguerraTorres, I wish I could take credit but the brilliance comes from MichaelKutz. – Shaulinator Jul 10 '18 at 14:35
2

No, forget LIKE operator for this, conditions as first_name LIKE '%name%' will never be effective. A regular index can not be used for range scans for conditions as first_name LIKE '%name%'. If first_name is indexed, index full scan is the best the database can use. And you still need to handle case-sensitity.

(index range scan can be used for conditions as first_name like 'name%', where the beginning of the value is fixed)

This is a task for Oracle Text.

No, forget virtual columns, you can not create text indexes on virtual columns, this is what you get if you try it:

DRG-11304: function-based indexes are not supported by this indextype

I have answered a similar question: Oracle “matches” multiple columns

But here it is again, with an example:

create table t1
(
  id number primary key,
  code number not null,
  first_name varchar2(100 char) not null,
  last_name varchar2(100 char) not null,
  email varchar2(50 char) not null
);

insert into t1 values (1, 111, 'Balazs', 'Papp', 'dba@stackexchange.com');
insert into t1 values (2, 222, 'linux', 'unil', 'index@stackexchange.com');
commit;

begin
ctx_ddl.create_preference('t1_multi_column_datastore', 'multi_column_datastore');
ctx_ddl.set_attribute('t1_multi_column_datastore', 'columns', 'code, first_name, last_name, email');
ctx_ddl.create_preference('t1_lexer', 'basic_lexer');
ctx_ddl.set_attribute('t1_lexer', 'mixed_case','no');
end;
/

create index i1 on t1 (first_name) indextype is ctxsys.context
  parameters ('lexer t1_lexer datastore t1_multi_column_datastore');

Then the query:

SQL> select * from t1 where contains (first_name, '%STACK%') > 0;

        ID       CODE FIRST_NAME LAST_NAME  EMAIL
---------- ---------- ---------- ---------- -------------------------
         1        111 Balazs     Papp       dba@stackexchange.com
         2        222 linux      unil       index@stackexchange.com

SQL> select * from t1 where contains (first_name, '%11%') > 0;

        ID       CODE FIRST_NAME LAST_NAME  EMAIL
---------- ---------- ---------- ---------- -------------------------
         1        111 Balazs     Papp       dba@stackexchange.com

SQL> select * from t1 where contains (first_name, '%Uni%') > 0;

        ID       CODE FIRST_NAME LAST_NAME  EMAIL
---------- ---------- ---------- ---------- -------------------------
         2        222 linux      unil       index@stackexchange.com

Notice how I did not specify all the columns, still the query searched in all of them (enumerated in the multi_column_datastore). I specified first_name in the index and the query, yet the query returned records that matched on the email, code or last_name column.

Notice how I did not specify anything related to case-sensitivity in the query. It was specified with the mixed_case=no property (Oracle Text word searches are not case-insensitive by default: Case-Sensitive Searching).

And the query uses the index:

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID  a4vw67psmh4x3, child number 0
-------------------------------------
select * from t1 where contains (first_name, '%Uni%') > 0

Plan hash value: 1218792127

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |       |       |     4 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1   |     1 |   544 |     4   (0)| 00:00:01 |
|*  2 |   DOMAIN INDEX              | I1   |       |       |     4   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CTXSYS"."CONTAINS"("FIRST_NAME",'%Uni%')>0)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.