Re: Indexing for LIKE selection
- From: "Gints Plivna" <gints.plivna@xxxxxxxxx>
- To: sfaroult@xxxxxxxxxxxx
- Date: Mon, 5 Mar 2007 22:13:55 +0200
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
