Home » Server Options » Text & interMedia » CONTEXT INDEX and Tune LIKE clause (merged by bb and mc)
CONTEXT INDEX and Tune LIKE clause (merged by bb and mc) [message #348982] Thu, 18 September 2008 08:21 Go to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hello sir,

I created EMP table with column ENAME of type Varchar2(10) and added some records, next i created a Context index on ENAME
and executed a query

Select ENAME
From EMP
Where Contains(ENAME,'%'||'ER'||'%') > 0

I GOT CORRECT RESULT

But later i added few more records and ran the query but it didn't showed the extra rows in output which were also satisfying the query criteria

Later i came to know that Context index needs to be synchronized with CTX_DDL.SYNC_INDEX('index_name')

If so then whats the use of this index in OLTP applications where the index needs to be automatically updated.

Is there any method to automatically update Context index?

Thanks in advance
Re: CONTEXT INDEX [message #349013 is a reply to message #348982] Thu, 18 September 2008 10:02 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Different systems have different needs. There are some systems where there is a one-time load into a data warehouse that never needs any synchronization. There are some systems where information must be available immediately, so synchronization is done upon commit. There are other systems where information is synchronized once each 24-hours at night or once per week on the weekend, so that synchronization does not slow down other things. The frequency and type of synchronization can be specified either during index creation or by altering the index after creation or using a trigger or using a scheduled job. One of the simplest methods available since 10g is to specify that the index is synchronized upon commit when you create the index, like so:

SCOTT@orcl_11g> CREATE INDEX emp_ename_idx
2 ON emp (ename)
3 INDEXTYPE IS CTXSYS.CONTEXT
4 PARAMETERS ('SYNC (ON COMMIT)');

Index created.

SCOTT@orcl_11g>

However, synchronization results in index fragmentation, so you should plan to periodically either optimize the index or rebuild it or drop it and recreate it. There are many options for all of these things.

Also, you do not need to concatenate the %; You can include the % within the single quotes, as shown below:

SCOTT@orcl_11g> SELECT ename
2 FROM emp
3 WHERE CONTAINS (ename, '%ER%') > 0;

ENAME
----------
TURNER
MILLER

SCOTT@orcl_11g>

Although, if you are using a bind variable, as you should be, you will want to concatenate the % to the bind variable either before or after passing it, as shown below:

SCOTT@orcl_11g> VARIABLE bind_variable VARCHAR2(10)
SCOTT@orcl_11g> EXEC :bind_variable := 'ER'

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT ename
2 FROM emp
3 WHERE CONTAINS (ename, '%' || :bind_variable || '%') > 0;

ENAME
----------
TURNER
MILLER

SCOTT@orcl_11g> EXEC :bind_variable := '%' || :bind_variable || '%'

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> PRINT bind_variable

BIND_VARIABLE
--------------------------------
%ER%

SCOTT@orcl_11g> SELECT ename
2 FROM emp
3 WHERE CONTAINS (ename, :bind_variable) > 0;

ENAME
----------
TURNER
MILLER

SCOTT@orcl_11g>

If you create your index with sync(on commit), then information is searchable immediately after commit, as shown below:

SCOTT@orcl_11g> INSERT INTO emp (deptno, empno, ename) VALUES (40, 99, 'VERN');

1 row created.

SCOTT@orcl_11g> COMMIT;

Commit complete.

SCOTT@orcl_11g> SELECT ename FROM emp WHERE CONTAINS (ename, '%ER%') > 0;

ENAME
----------
TURNER
MILLER
VERN

SCOTT@orcl_11g>

[Updated on: Thu, 18 September 2008 10:11]

Report message to a moderator

Tune LIKE clause [message #349108 is a reply to message #348982] Thu, 18 September 2008 22:43 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hello Sir,

If there no method to automatically update Context index then
How to tune LIKE clause which occur frequently in my query

I did the below written scenario

I created EMP table with column ENAME of type Varchar2(10) and added some records, next i created a Context index on ENAME
and executed a query

Select ENAME
From EMP
Where Contains(ENAME,'%'||'ER'||'%') > 0

I GOT CORRECT RESULT

But later i added few more records and ran the query but it didn't showed the extra rows in output which were also satisfying the query criteria

Later i came to know that Context index needs to be synchronized with CTX_DDL.SYNC_INDEX('index_name')

If so then whats the use of this index in OLTP applications where the index needs to be automatically updated.

What is the solution to improve performance of query which uses more than 2 LIKE clauses.
Re: Tune LIKE clause [message #349112 is a reply to message #349108] Thu, 18 September 2008 23:24 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
As previously stated, it is very simple to automatically update your context index by adding sync(on commit) to your index parameters when you create the context index.
Previous Topic: package and oracle text search AND SOS,SOS,Search,Search,SOS (merged by bb)
Next Topic: Problem with Fuzzy Query
Goto Forum:
  


Current Time: Thu Mar 28 13:41:40 CDT 2024