My Oracle Support Banner

CONTAINS() Query gives incorrect Results when using MULTI_COLUMN_DATASTORE When Using Multi_Column_Datastore and Searching on the Column Names (Doc ID 1364654.1)

Last updated on APRIL 23, 2013

Applies to:

Oracle Text - Version 9.2.0.1 and later
Information in this document applies to any platform.

Symptoms

When setting up an Oracle Text index with Multi_Column_Datastore, when searching for the column names, you get all the records.

SQL> connect textuser/textuser

create table TEST_MULTI_COLUMN
(Id Number(7)
,CLIENT varchar2(10 char)
,ADDRESS varchar2(10 char)
,CTX_VALUE varchar2(1))
/

Table created.

insert into TEST_MULTI_COLUMN
(ID, CLIENT, ADDRESS, CTX_VALUE)
values
(1, 'ORACLE', Null, Null)
/
1 row created.

Commit
/

Commit complete.

select * from TEST_MULTI_COLUMN;

     ID CLIENT     ADDRESS    C
------- ---------- ---------- -
      1 ORACLE

begin
ctx_ddl.create_preference('TEST_MULTI', 'MULTI_COLUMN_DATASTORE');
ctx_ddl.set_attribute('TEST_MULTI', 'columns', 'CLIENT,ADDRESS');
end;
/

PL/SQL procedure successfully completed.

CREATE INDEX TEST_MULTI_COL ON TEST_MULTI_COLUMN(CTX_VALUE)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('datastore TEST_MULTI
filter ctxsys.null_filter
stoplist ctxsys.EMPTY_STOPLIST
memory 200M')
/

Index created.

-- this should return 1 record
select * from TEST_MULTI_COLUMN t
where contains(t.ctx_value, 'oracle') > 0
/

     ID CLIENT     ADDRESS    C
------- ---------- ---------- -
      1 ORACLE

-- this should return NO records
select * from TEST_MULTI_COLUMN t
where contains(t.ctx_value, 'client') > 0
/

      ID CLIENT     ADDRESS    C
-------- ---------- ---------- -
       1 ORACLE

-- this should return no records
select * from TEST_MULTI_COLUMN t
where contains(t.ctx_value, 'address') > 0
/

      ID CLIENT     ADDRESS    C
-------- ---------- ---------- -
       1 ORACLE



Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.