If a text column is indexed with a CTXSYS.CONTEXT index it can return a score showing how well the text matches the query.

create index ix on t(text) indextype is ctxsys.context parameters ('');
select id, score(1) from t where contains(text,'my query',1) > 0;

How can I see the score for text columns index with a CTXSYS.CTXCAT index?

create index ix on t(text) indextype is ctxsys.ctxcat parameters ('');
select id, ??? from t where catsearch(text, 'my query',null) > 0;
| improve this question | | | | |
  • I see nothing in the documentation that states it's possible – Philᵀᴹ Mar 14 '15 at 8:50

The score can only be accessed with a CONTENT index using a query like this

SELECT ID, TEXT,SCORE(1) FROM t WHERE CONTAINS(text, 'my query',1) > 0

By Oracle 11 everything you can do with a CTXCAT index you can do more with a CONTENT index. Why not use that?

Edit: The op asks if CONTENT indexes must be synced. Yes, this is correct. I find the time and load factor to do this is entirely acceptable for smaller records sets. Your mileage may vary based on record size. Further reference:

| improve this answer | | | | |
  • My understanding is that a CTXCAT index doesn't need to call CTX_DDL.SYNC_INDEX while a CONTEXT index does. Is this obsolete information? – Mark Harrison Mar 16 '15 at 18:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.