CODEWITHSUNDEEP
sqloracle-databasefull-text-searchindexing
Jennifer
Jennifer

Reputation: 11

How to create an Inverted Index on a table using Oracle

I have a table called book with columns ISBN, Title and Description. I have been trying to create an inverted index on this table using the command:

CREATE INDEX myindex ON book(description) INDEXTYPE IS CTXSYS.CONTEXT;

and I've been trying to query it with the query below:

  SELECT score(1), title 
    FROM book b
   WHERE CONTAINS(b.description, 'Huckleberry Finn', 1) > 0 
ORDER BY score(1) DESC;

Whenever I run this query, however no rows are selected even though the query

SELECT title, description
  FROM book
 WHERE description = 'Huckleberry Finn';

returns:

Title          description
------------------------------------
'Huckleberry'  'Huckleberry Finn'

I am not sure what is wrong with my queries, this is the first time I've ever tried to create an inverted index, so if anyone could tell me either what is wrong with my query or another way to do this, I would really appreciate it.

Upvotes: 1

Views: 1789

Answers (1)

Gary Myers
Gary Myers

Reputation: 35401

Have you sync'ed your index ? In newer versions, you can construct your index so that it is updated on a commit. Otherwise you may be relying on a manual sync operation.

Don't think it is possible to get the index synchronised within a transaction.

create index search_idx
  on ctx_demo (text)
  indextype is ctxsys.context
  parameters ('sync (on commit)')
/

Upvotes: 7

Related Questions