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

Categories
sql

How to tune something doing nothing?

To end this year in glory, I tried to replace a row-level trigger (LSC_TR1) into a statement level trigger (LSC_TR2)


SQL> create global temporary table lsc_t1(x number primary key)
2 on commit delete rows;
Table created.
SQL> create table lsc_t2(x number primary key);
Table created.
SQL> create table lsc_t3(x number primary key);
Table created.
SQL> create or replace function f return number deterministic is
2 begin sys.dbms_lock.sleep(1); return 10; end;
3 /
Function created.
SQL> create or replace trigger lsc_tr1 after insert on lsc_t1 for each row
2 begin
3 if (:new.x > f)
4 then
5 insert into lsc_t2(x) values (:new.x);
6 end if;
7 end;
8 /
Trigger created.
SQL> create or replace trigger lsc_tr2 after insert on lsc_t1
2 begin
3 insert into lsc_t2(x) select x from lsc_t1 where x > f;
4 end;
5 /
Trigger created.
SQL> insert into lsc_t3 values(1);
1 row created.
SQL> insert into lsc_t3 values(5);
1 row created.
SQL> insert into lsc_t3 values(20);
1 row created.
SQL> insert into lsc_t3 values(50);
1 row created.
SQL> insert into lsc_t3 values(100);
1 row created.
SQL> insert into lsc_t3 values(200);
1 row created.
SQL> insert into lsc_t3 values(1000);
1 row created.
SQL> commit;
Commit complete.
SQL> alter trigger lsc_tr1 enable;
Trigger altered.
SQL> alter trigger lsc_tr2 disable;
Trigger altered.
SQL> insert into lsc_t1(x) select x from lsc_t3;
7 rows created.
Elapsed: 00:00:07.05

The current trigger LSC_TR1 is very slow, it is doing some processing after each row. Since it is a temporary staging table, it makes no sense to process each row, I have written the trigger LSC_TR2 to boost the performance.

Have a look.


SQL> truncate table lsc_t2;
Table truncated.
SQL> alter trigger lsc_tr1 disable;
Trigger altered.
SQL> alter trigger lsc_tr2 enable;
Trigger altered.
SQL> insert into lsc_t1(x) select x from lsc_t3;
7 rows created.
Elapsed: 00:00:01.03

Much faster ! LSC_TR1 just takes ages. What a glorious year 2008 🙂

Well, the table LSC_T3 is empty most of the time, and the insert is running again and again…


SQL> truncate table lsc_t3;
Table truncated.
SQL> alter trigger lsc_tr1 enable;
Trigger altered.
SQL> alter trigger lsc_tr2 disable;
Trigger altered.
SQL> insert into lsc_t1(x) select x from lsc_t3;
0 rows created.
Elapsed: 00:00:00.01
SQL> alter trigger lsc_tr1 disable;
Trigger altered.
SQL> alter trigger lsc_tr2 enable;
Trigger altered.
SQL> insert into lsc_t1(x) select x from lsc_t3;
0 rows created.
Elapsed: 00:00:01.03

Suddenly my tuning exercice turned to be a disaster, in this example it is about 63 times slower 🙁

I could possibly count the rows in the LSC_TR2 trigger, but then it will still be a bit slower when processing very few rows (in this example with 1 row).

So I will keep this AFTER EACH ROW trigger until next year 😉