Oracle: how to do full text searches on an XMLType?

Question

I have an app storing XML in an Oracle table as XMLType. I want to do full text searches on that data. The Oracle documentation, in Full-Text Search Over XML Data, recommends to use the contains SQL function, which requires the data to be indexed with a context index. The trouble is that it appears that context indexes are asynchronous, which doesn't fit the use case I have where I need to be able to search through data right after it was added.

Can I make that index somehow synchronous? If not, what other technique should I use to do full text searches on an XMLType?

Answer 1

It can't be made transactional (i.e. it won't update the index so that the change is visible to a subsequent statement within the transaction). The best you can do is make it update on commit (SYNC ON COMMIT), as in:

create index your_table_x
    on your_table(your_column)
    indextype is ctxsys.context
    parameters ('sync (on commit)');

Text indexes are complex things and I'd be surprised if you could achieve a transactional / ACID compliant text index (that is, transaction A inserting documents and have those visible in the index for that transaction and not visible to transaction B until commit).