| speed up 'like' queries - Oracle Text API? [message #331092] |
Wed, 02 July 2008 05:23  |
smora
Messages: 59
Registered: May 2006
|
Member |
|
|
Hi
I have a table with sub_id, sub_username fields. To search for usernames, there is a query that uses the
sub_username like '%XXX%'
clause. This is extremely slow even with an index on the sub_username field, the table has ~2 million rows. Can this be speeded up somehow?
I looked into using Oracle Text API, but that seems to be for searching for a single word or phrase within a document, whereas I need to speed up searches on a column that contains a single word as its value.
Any help is appreciated.
SM
|
|
|
|
|
|
| Re: speed up 'like' queries - Oracle Text API? [message #331279 is a reply to message #331269] |
Wed, 02 July 2008 19:28  |
 |
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The ctxcat index does not support wildcard prefixes, but the context index does and the substring_index can be used to speed up such queries. On a small number of rows, a full table scan using like or instr is likely to be faster, but on a large number of rows, the substring_index will enable the search to use an index, which should be faster. Please see the demonstration below.
-- table and data:
SCOTT@orcl_11g> CREATE TABLE a_table
2 (sub_id NUMBER,
3 sub_username VARCHAR2 (30))
4 /
Table created.
SCOTT@orcl_11g> INSERT INTO a_table SELECT object_id, object_name FROM user_objects
2 /
507 rows created.
SCOTT@orcl_11g> INSERT INTO a_table VALUES (0, 'AXXXA')
2 /
1 row created.
-- context index:
SCOTT@orcl_11g> BEGIN
2 CTX_DDL.CREATE_PREFERENCE ('your_wordlist', 'BASIC_WORDLIST');
3 CTX_DDL.SET_ATTRIBUTE ('your_wordlist', 'SUBSTRING_INDEX', 'YES');
4 END;
5 /
PL/SQL procedure successfully completed.
SCOTT@orcl_11g> CREATE INDEX context_index ON a_table (sub_username) INDEXTYPE IS CTXSYS.CONTEXT
2 PARAMETERS ('WORDLIST your_wordlist')
3 /
Index created.
SCOTT@orcl_11g> EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'A_TABLE')
PL/SQL procedure successfully completed.
-- query:
SCOTT@orcl_11g> SET AUTOTRACE ON EXPLAIN
SCOTT@orcl_11g> SELECT * FROM a_table WHERE CONTAINS (sub_username, '%XXX%') > 0
2 /
SUB_ID SUB_USERNAME
---------- ------------------------------
0 AXXXA
Execution Plan
----------------------------------------------------------
Plan hash value: 1049565438
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 500 | 10 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| A_TABLE | 20 | 500 | 10 (0)| 00:00:01 |
|* 2 | DOMAIN INDEX | CONTEXT_INDEX | | | 9 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CTXSYS"."CONTAINS"("SUB_USERNAME",'%XXX%')>0)
SCOTT@orcl_11g>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
