Oracle Text: CONTAINS is not returning any values, even though they exist in the index

Question

I'm very new to Oracle, and I've created a basic application which uses Oracle Text to perform a text search on an index.

My table structure is as follows:

[  Table: Stores ]
------------------
store_id        PK
name           VC2
description    VC2

My description field then has an index assigned against it:

CREATE INDEX stores_desc_ctx_idx  ON stores(description) INDEXTYPE IS ctxsys.context;

I've validated in SQLDeveloper that the INDEX exists under my Index tab, however when I run a query the results returned are always null, even when I can clearly see that the data in any given row matches the input string.

Let description A:

Local GAME store in Plymouth, selling all the latest titles as well as legacy ones!

let description B:

Local Morrison's store in Plymouth, selling all the food you could possibly want!

Let query:

SELECT * FROM stores WHERE contains(description, 'GAME') > 0;

I would expect the result of the query to return description A, however no results are returned...what am I doing wrong here?

Answer 1

For future users who face a similar problem.

SQLDeveloper has somehow invalidated my INDEX, I simply navigated to the INDEX tab, right clicked and selected "Rebuild". Doing this re-validated the INDEX and the code now works as expected.

Answer 2

You can specify when the full text index will be updated by the DB system using the "Parameters" syntax with the "Create index" statement. For example the following statement creates a full text index that is updated after each commit.

CREATE INDEX stores_desc_ctx_idx ON stores(description) INDEXTYPE IS ctxsys.context PARAMETERS ('SYNC(ON COMMIT)');

See the oracle docs for all possible "SYNC" options