Full text index returns duplicates

Feb 8, 2022 7:47PM

We have 4.7M record table TAB with full text index created on it:

CREATE INDEX "IND_TAB_COL_FT" ON "TAB" ("TAB_COL") INDEXTYPE IS "CTXSYS"."CONTEXT" PARAMETERS ('SYNC ( ON COMMIT)');

Table has also primary key:

ALTER TABLE "TAB" ADD CONSTRAINT "PK_TAB" PRIMARY KEY ("TAB_CODE") USING INDEX "PK_TAB" ENABLE;

Select using "CONTAINS" on FT index is returning duplicates for SOME rows, for example:

SELECT rowid, lc.TAB_CODE, lc.TAB_COL FROM TAB lc WHERE CONTAINS (TAB_COL, '3300153937') > 0;

ROWID TAB_CODE TAB_COL

-----------------------------------------------------------------------------------------------------

AAFBzoAAAAAHDzgAAs 106022116 3300153937 521416441 TEST FIRM

When I select same row using primary key, I'm getting only one row, as expected:

Oracle Text (MOSC)