|
How to tune WHERE NAME LIKE ‘ABC’
Article by Rampant author Laurent Schneider
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.
|
