Home » Server Options » Text & interMedia » speed up 'like' queries - Oracle Text API? (Oracle Database 10g Release 10.2.0.3.0, Windows Server 2003)
speed up 'like' queries - Oracle Text API? [message #331092] Wed, 02 July 2008 05:23 Go to next message
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 #331269 is a reply to message #331092] Wed, 02 July 2008 16:08 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You should consider a CTXCAT index.
Re: speed up 'like' queries - Oracle Text API? [message #331279 is a reply to message #331269] Wed, 02 July 2008 19:28 Go to previous message
Barbara Boehmer
Messages: 8625
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> 

Previous Topic: Displaying search results from table and CTX_DOC.SNIPPET - Returning error messages (merged by bb)
Next Topic: Merge columns from dif tables in one index and markup the result
Goto Forum:
  


Current Time: Sun Dec 04 04:44:37 CST 2016

Total time taken to generate the page: 0.06352 seconds