Categories
dba sql

How to tune WHERE NAME LIKE ‘%ABC%’

More than once customers wanted me to tune queries where the LIKE clause made the query very slow…

The easy answer is : you cannot. If you want to search for some characters in the middle of the string, Oracle will not use an index.

Is it a correct answer? Maybe not.

Let’s imagine my application allow you to search for a string in the middle of the name, but to avoid scanning too large amount of data, the application enforces the string to be at least 3 characters long. For example ABC.

In this case, instead of doing a full table scan of the table to retrieve only a few rows, we can use an hint to tell Oracle to use an index on the name:

CREATE TABLE lsc_t AS
SELECT
ROWNUM ID,
SUBSTR(DBMS_RANDOM.STRING ('n', DBMS_RANDOM.VALUE (2, 8))
|| ' '
|| DBMS_RANDOM.STRING ('n', DBMS_RANDOM.VALUE (2, 8))
,1,17) NAME,
TRUNC(SYSDATE-ABS(10000*DBMS_RANDOM.NORMAL)) birthdate,
LPAD('X',4000,'X') address
FROM DUAL
CONNECT BY LEVEL <= 1e5; ALTER TABLE lsc_t ADD PRIMARY KEY(ID); CREATE INDEX lsc_i ON lsc_t(NAME); EXEC dbms_stats.gather_table_stats(user,'LSC_T',cascade=>true)

let’s measure the time for a full table scan
SQL> set timi on
SQL> SELECT ID, NAME, birthdate
FROM lsc_t WHERE NAME LIKE '%ABC%';

ID NAME BIRTHDATE
--------- ----------------- ---------
60249 ABCBIFAB KRKBCRN 11-MAR-90
16714 AF YABCG 09-OCT-95
55571 BABCIQ GESGLW 27-MAR-50
77561 BP GABC 24-APR-90
80027 DALSABC TZLOAWDV 05-NOV-01
49817 EABCTFIY XWB 10-FEB-88
23283 EMMOGGBF DABCB 20-DEC-87
39530 FMABCKB AB 18-SEP-87
68605 FTPGOHE ABCC 28-SEP-28
74615 KIFDWABC CSSUQ 08-AUG-82
31772 KNOABCT BO 08-SEP-77
68730 KRYIEN LMABC 10-APR-07
43317 LUFJKZJT AUABCZR 19-DEC-88
76851 MZABC TEIFG 14-SEP-92
54589 NXE YABCDX 03-MAY-88
6940 OIWABCZ DLFFXY 29-MAR-88
59070 ONIB ADGABCI 29-JUL-07
27264 PGHOABC ZY 05-OCT-90
38157 QABC OPZHE 13-JUN-87
17511 QPDKD CIABCJ 08-AUG-69
25507 RX OWULOABC 24-FEB-92
62159 SEABC DAILK 25-JUN-02
3845 SK CCABCG 22-JAN-80
50059 SPABC BVHRHW 18-MAR-86
54700 UABCPC WUHAJS 28-OCT-71
70207 UKY OIDUABC 23-APR-88
39484 WABC TJLYHVJZ 14-MAR-78
14561 WDRWABC XZKDH 29-MAR-86
61501 YBYU RYABCGI 28-JUN-78
30578 YEWENGX ABCHARA 12-SEP-67
35397 YHBEABC HFKO 25-AUG-85
26450 YOABCVG HJT 23-DEC-98
87224 ZKNLNY YAABC 13-NOV-61

33 rows selected.

Elapsed: 00:00:02.56

about 3 seconds for retrieving 33 rows out of 100000

let’s try with an index

SQL> SELECT /*+INDEX(LSC_T,LSC_I)*/ ID, NAME, birthdate
FROM lsc_t WHERE NAME LIKE '%ABC%';

ID NAME BIRTHDATE
--------- ----------------- ---------
60249 ABCBIFAB KRKBCRN 11-MAR-90
16714 AF YABCG 09-OCT-95
55571 BABCIQ GESGLW 27-MAR-50
77561 BP GABC 24-APR-90
80027 DALSABC TZLOAWDV 05-NOV-01
49817 EABCTFIY XWB 10-FEB-88
23283 EMMOGGBF DABCB 20-DEC-87
39530 FMABCKB AB 18-SEP-87
68605 FTPGOHE ABCC 28-SEP-28
74615 KIFDWABC CSSUQ 08-AUG-82
31772 KNOABCT BO 08-SEP-77
68730 KRYIEN LMABC 10-APR-07
43317 LUFJKZJT AUABCZR 19-DEC-88
76851 MZABC TEIFG 14-SEP-92
54589 NXE YABCDX 03-MAY-88
6940 OIWABCZ DLFFXY 29-MAR-88
59070 ONIB ADGABCI 29-JUL-07
27264 PGHOABC ZY 05-OCT-90
38157 QABC OPZHE 13-JUN-87
17511 QPDKD CIABCJ 08-AUG-69
25507 RX OWULOABC 24-FEB-92
62159 SEABC DAILK 25-JUN-02
3845 SK CCABCG 22-JAN-80
50059 SPABC BVHRHW 18-MAR-86
54700 UABCPC WUHAJS 28-OCT-71
70207 UKY OIDUABC 23-APR-88
39484 WABC TJLYHVJZ 14-MAR-78
14561 WDRWABC XZKDH 29-MAR-86
61501 YBYU RYABCGI 28-JUN-78
30578 YEWENGX ABCHARA 12-SEP-67
35397 YHBEABC HFKO 25-AUG-85
26450 YOABCVG HJT 23-DEC-98
87224 ZKNLNY YAABC 13-NOV-61

33 rows selected.

Elapsed: 00:00:00.06

Much better 🙂

reposted due to % in url