|Oracle8i interMedia Text Migration
Release 2 (8.1.6)
Part Number A77061-01
Querying, 4 of 12
In pre-8.1, you use a cursor query, formerly known as an in-memory query, over a Text query when you want only a small portion of a potentially large hitlist.
In 8.1, the PL/SQL interface for in-memory queries is obsolete. This means that the following procedures are obsolete in 8.1:
To migrate pre-8.1 in-memory queries, use a cursor. Use the FIRST_ROWS hint in the SELECT statement to obtain the first n hits of a potentially large hitlist.
The following in-memory query finds all documents that contain the word oracle and returns them in score sorted order. The mechanism of the query returns the hits row by row in order, thus allowing you to extract the first n hits without spending the overhead of obtaining the entire hitlist first.
declare pk varchar2(80); scr number; cur number; begin cur := ctx_query.open_con('mypolicy','oracle',TRUE); while (ctx_query.fetch_hit(cur, pk, scr) > 0) loop -- deal with hit end loop; ctx_query.close_con(cur); end;
The pre-8.1 cursor query procedures OPEN_CON, FETCH_HIT, CLOSE_CON, COUNT_LAST are obsolete in 8.1.
To obtain the first n hits of a potentially large hitlist, execute the CONTAINS query using a cursor. Use the FIRST_ROWS hint to optimize for response time in the SELECT statement as follows:
begin for c1 in (select /*+ FIRST_ROWS */ pk, score(1) scr from basetable where contains(textcol, 'oracle', 1) > 0 order by scr desc) loop -- deal with hit dbms_output.put_line('KEY is '||c1.pk); dbms_output.put_line('SCORE is '||c1.scr); end loop; end;
To learn more about using the FIRST_ROWS hint with CONTAINS queries, see the Oracle8i interMedia Text Reference.