Re: Indexing for LIKE selection

From: Gints Plivna <gints.plivna_at_gmail.com>
Date: Mon, 5 Mar 2007 22:13:55 +0200
Message-ID: <6e49b6d00703051213o19b5fab6ta40116e91559e49c@mail.gmail.com>

2007/3/5, Stephane Faroult <sfaroult_at_roughsea.com>:

>   In other words


>           like 'blah%' is fine,

>           like '%blah%' is not.


I'd like to add that there is possibility also search for 'blah%'

using index. Of course '%blah%' remains as bad as before. Example follows:
SQL> create table src (txt varchar2(100) NOT NULL);

Table created.

Elapsed: 00:00:00.00
SQL> insert into src select distinct object_name from dba_objects;

22474 rows created.

Elapsed: 00:00:00.08
SQL> create unique index src_uk1 on src(txt);

Index created.

Elapsed: 00:00:00.03
SQL> create unique index src_uk2 on src(reverse(txt));

Index created.

Elapsed: 00:00:00.05
SQL> exec dbms_stats.gather_table_stats(user, 'src')

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.03
SQL> set autot on explain
SQL> select * from src where txt like 'DBA_DB%'; TXT

---

DBA_DB_LINKS Elapsed: 00:00:00.00

Execution Plan

---

   0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=1 Bytes=22)

   1 0 INDEX (RANGE SCAN) OF 'SRC_UK1' (UNIQUE) (Cost=2 Card=1 Bytes=22) SQL> select * from src where reverse(txt) like reverse('%DB_LINKS');

TXT

---

ORA_KGLR7_DB_LINKS
DBA_DB_LINKS
ALL_DB_LINKS

USER_DB_LINKS Elapsed: 00:00:00.00

Execution Plan

---

   0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=3 Card=1 Bytes=22)

   1 0 TABLE ACCESS (BY INDEX ROWID) OF 'SRC' (Cost=3 Card=1 Bytes=22)

   2 1 INDEX (RANGE SCAN) OF 'SRC_UK2' (UNIQUE) (Cost=2 Card=1)

Gints Plivna
http://www.gplivna.eu

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Mar 05 2007 - 14:13:55 CST