Composite Textkey Query

Composite textkey queries are queries on a base table that is indexed with a composite textkey.

Pre-8.1 Method

The first step in issuing a composite textkey query is to create a result table manually with a composite textkey consisting of two columns as follows:

create table CTX_TEMP2(
     textkey varchar2(64),
     textkey2 varchar2(64),
     score number,
     conid number);

You then join the result and base table with and AND operator in the WHERE condition. For example:

exec ctx_query.contains('ARTICLE2_POLICY','petroleum','CTX_TEMP2')
SELECT score, title 
FROM CTX_TEMP2, TEXTTAB2
WHERE texttab2.PK=ctx_temp2.textkey AND
      texttab2.PK2=ctx_temp2.textkey2
ORDER BY score DESC;

8.1 Method

The 8.1 query is a basic SELECT statement. Because no result tables are used, there is no join between a result table and a base table as in pre-8.1 CONTAINS. You thus issue queries against a composite textkey table the same way you issue a query against a table with a single column textkey.

If textab2 is the composite textkey table, the above query is written as:

SELECT SCORE(1),title FROM textab2 
  WHERE CONTAINS(text,'petroleum') > 0
  ORDER BY SCORE(1) DESC;