Re: Indexing for LIKE selection

2007/3/5, Stephane Faroult <sfaroult@xxxxxxxxxxxx>:

  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

Other related posts: