My Oracle Support Banner

WRONG RESULT AFTER SYNC INDEX ON 18C (Doc ID 2687539.1)

Last updated on JULY 12, 2020

Applies to:

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

Symptoms

Wrong result from 18c when creating index with NOPOPULATE, populate_pending and SYNC,


It worked fine when create the index with SYNC(MANUAL) only and call SYNC_INDEX.

 

Testcase
------------

create user BadIndex identified by BadIndex;

User created.

SQL> grant connect, resource, ctxapp, create job to BadIndex;
grant select on dba_tables to BadIndex;
grant select on dba_indexes to BadIndex;
grant select on dba_tab_columns to BadIndex;
grant select on dba_ind_columns to BadIndex;
grant select on dba_ind_expressions to BadIndex;
grant select on dba_lobs to BadIndex;
grant select on dba_triggers to BadIndex;
grant create materialized view to BadIndex;
grant create any index to BadIndex;
grant drop any index to BadIndex;
grant unlimited tablespace to BadIndex;
grant execute on dbms_monitor to BadIndex;

Grant succeeded.

SQL> conn BadIndex/BadIndex
Connected.
SQL> create table "S$ITE"
(
"UNIQUEID" raw(16) not null,
"EFFECTIVEDATEDLOG" clob,
"AUDITLOG" clob,
"PFILE" blob,
"TEXTSEARCH" clob
); 2 3 4 5 6 7 8

Table created.

SQL>Insert into S$ITE (UNIQUEID,TEXTSEARCH) values ('A6A2F1C796804000000000835CA4C083','1 ItemInventoryINV11 ');

SQL>1 row created.


Insert into S$ITE (UNIQUEID,TEXTSEARCH) values ('A6A2F1C796804000000000845CA4C093','2 ItemNonStockNS12 ');

SQL>1 row created.

 

commit;

SQL>Commit complete.

SQL> create index "ITETEXTSEARCH" on "S$ITE" ("TEXTSEARCH") indextype is CTXSYS.CONTEXT parameters(' nopopulate sync (every "FREQ=MINUTELY;INTERVAL=30" PARALLEL 2) filter ctxsys.null_filter ') PARALLEL 2;
execute ctx_ddl.populate_pending('"ITETEXTSEARCH"',null);
execute ctx_ddl.sync_index('"ITETEXTSEARCH"',null,null,2);

Index created.

SQL>PL/SQL procedure successfully completed.

SQL>PL/SQL procedure successfully completed.

SQL> select count(*)
from "S$ITE"
where (CONTAINS("TEXTSEARCH", '%Item%', 1) > 0); 2 3

COUNT(*)
----------
0

SQL> conn sys/3IelZHcH as sysdba
Connected.
SQL> create table "S$ITE"
(
"UNIQUEID" raw(16) not null,
"EFFECTIVEDATEDLOG" clob,
"AUDITLOG" clob,
"PFILE" blob,
"TEXTSEARCH" clob
); 2 3 4 5 6 7 8

Table created.

SQL> Insert into S$ITE (UNIQUEID,TEXTSEARCH) values
('A6A2F1C796804000000000835CA4C083','1 ItemInventoryINV1
1 ');
Insert into S$ITE (UNIQUEID,TEXTSEARCH) values
('A6A2F1C796804000000000845CA4C093','2 ItemNonStockNS1
2 ');
commit;

1 row created.

SQL>1 row created.

SQL>Commit complete.

SQL> create index "ITETEXTSEARCH" on "S$ITE" ("TEXTSEARCH") indextype is
CTXSYS.CONTEXT parameters(' nopopulate sync (every
"FREQ=MINUTELY;INTERVAL=30" PARALLEL 2) filter ctxsys.null_filter ') PARALLEL 2;


execute ctx_ddl.populate_pending('"ITETEXTSEARCH"',null);
execute ctx_ddl.sync_index('"ITETEXTSEARCH"',null,null,2);

Index created.

SQL>PL/SQL procedure successfully completed.

SQL>PL/SQL procedure successfully completed.

SQL> select count(*)
from "S$ITE"
where (CONTAINS("TEXTSEARCH", '%Item%', 1) > 0); 2 3

COUNT(*)
----------

2
create index "ITETEXTSEARCH" on "S$ITE" ("TEXTSEARCH") indextype is
CTXSYS.CONTEXT parameters(' sync (MANUAL)');

execute ctx_ddl.sync_index('"ITETEXTSEARCH"',null,null,2);

returned the correct result.




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


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.