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

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms