Tag Archives: tuning

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

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 ;)