Home » Server Options » Text & interMedia » Highlighting search results with user_datastore context index (Oracle Database 11g Enterprise Edition)
Highlighting search results with user_datastore context index [message #435826] Fri, 18 December 2009 04:13 Go to next message
shindi
Messages: 3
Registered: December 2009
Junior Member
I've got the following problem with highlighting results of a context search before presenting them to user, if context index is based on user_datastore.

Say, I've got the books table:

create table books (
  id           integer primary key,
  author       varchar2(100),
  title        varchar2(500),
  description  clob
);


...and i want to search against book authors, titles and descriptions. So i created a procedure that concatenates them together (perhaps adding markup) and a user_datastore based on this procedure. Then searching is done as desired.

The problem is with presenting results to the user. I'd like to show book author and title separate from book description. Also i want to highlight matches in the title, and present just a small snippet of book's description.

I tried using ctx_doc utils for this purpose. Among them ctx_doc.highlight is the most flexible.

My problem is that highlight gives offsets in the document synthesized by user_datastore procedure and i don't know how to get offsets just in the book's description to render snippet based on them.

Do you have any suggestions? My question seems rather natural, but nevertheless i didn't find the answer yet.
Re: Highlighting search results with user_datastore context index [message #435962 is a reply to message #435826] Sat, 19 December 2009 19:06 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Since you are using 11g, you can create more than one context index on a single table. So, you can use one index for your contains and other indexes for your snippets, as demonstrated below.

SCOTT@orcl_11g> create table books (
  2    id	    integer primary key,
  3    author	    varchar2(100),
  4    title	    varchar2(500),
  5    description  clob,
  6    all_cols     VARCHAR2(1)
  7  )
  8  /

Table created.

SCOTT@orcl_11g> BEGIN
  2    INSERT INTO books VALUES (1, 'test author', 'some title', 'some description', NULL);
  3    INSERT INTO books VALUES (2, 'some author', 'test title', 'some description', NULL);
  4    INSERT INTO books VALUES (3, 'some author', 'some title', 'test description', NULL);
  5    INSERT INTO books VALUES (4, 'test author', 'test title', 'test description', NULL);
  6    INSERT INTO books VALUES (5, 'some author', 'some title', 'some description', NULL);
  7  END;
  8  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE your_procedure
  2    (p_rowid IN     ROWID,
  3  	p_clob	IN OUT NOCOPY CLOB)
  4  AS
  5  BEGIN
  6    FOR b IN
  7  	 (SELECT * FROM books WHERE ROWID = p_rowid)
  8    LOOP
  9  	 DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (b.author), b.author);
 10  	 DBMS_LOB.WRITEAPPEND (p_clob, 1, ' ');
 11  	 DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (b.title), b.title);
 12  	 DBMS_LOB.WRITEAPPEND (p_clob, 1, ' ');
 13  	 DBMS_LOB.APPEND (p_clob, b.description);
 14    END LOOP;
 15  END your_procedure;
 16  /

Procedure created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> BEGIN
  2    CTX_DDL.CREATE_PREFERENCE ('your_user_datastore', 'USER_DATASTORE');
  3    CTX_DDL.SET_ATTRIBUTE ('your_user_datastore', 'PROCEDURE', 'your_procedure');
  4  END;
  5  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> CREATE INDEX all_cols_index ON books (all_cols)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  PARAMETERS ('DATASTORE your_user_datastore')
  4  /

Index created.

SCOTT@orcl_11g> CREATE INDEX title_index ON books (title)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  /

Index created.

SCOTT@orcl_11g> CREATE INDEX descr_index ON books (description)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  /

Index created.

SCOTT@orcl_11g> EXEC CTX_DOC.SET_KEY_TYPE ('ROWID')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> COLUMN author	   FORMAT A11 WORD_WRAPPED
SCOTT@orcl_11g> COLUMN title	   FORMAT A17 WORD_WRAPPED
SCOTT@orcl_11g> COLUMN description FORMAT A30 WORD_WRAPPED
SCOTT@orcl_11g> SELECT author,
  2  	    CTX_DOC.SNIPPET ('title_index', ROWID, 'test')
  3  	      AS title,
  4  	    CTX_DOC.SNIPPET ('descr_index', ROWID, 'test')
  5  	      AS description
  6  FROM   books
  7  WHERE  CONTAINS (all_cols, 'test') > 0
  8  /

AUTHOR      TITLE             DESCRIPTION
----------- ----------------- ------------------------------
test author some title        some description
some author <b>test</b> title some description
some author some title        <b>test</b> description
test author <b>test</b> title <b>test</b> description

SCOTT@orcl_11g> 

Re: Highlighting search results with user_datastore context index [message #435966 is a reply to message #435962] Sun, 20 December 2009 03:14 Go to previous messageGo to next message
shindi
Messages: 3
Registered: December 2009
Junior Member
Barbara Boehmer wrote on Sat, 19 December 2009 19:06
Since you are using 11g, you can create more than one context index on a single table. So, you can use one index for your contains and other indexes for your snippets, as demonstrated below.


Thanks much, Barbara!

It works under 10g also. As i understand, this approach requires extra space and double indexing time. But nevertheless it's far more elegant than calling highlight on all_cols, doing math with offsets, and creating a snippet using custom function (my workaround, ugly).

Thank you one more time, I'll use your solution.

And one more question about your_procedure. You advise doing this way:

Barbara Boehmer wrote on Sat, 19 December 2009 19:06

SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE your_procedure
  2    (p_rowid IN     ROWID,
  3  	p_clob	IN OUT NOCOPY CLOB)
  4  AS
  5  BEGIN
  6    FOR b IN
  7  	 (SELECT * FROM books WHERE ROWID = p_rowid)
  8    LOOP
  9  	 DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (b.author), b.author);
 10  	 DBMS_LOB.WRITEAPPEND (p_clob, 1, ' ');
 11  	 DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (b.title), b.title);
 12  	 DBMS_LOB.WRITEAPPEND (p_clob, 1, ' ');
 13  	 DBMS_LOB.APPEND (p_clob, b.description);
 14    END LOOP;
 15  END your_procedure;
 16  /



but why not just

select b.author || ' ' || b.title || ' ' || b.description
  into p_clob
  from books b
 where rowid = p_rowid;


instead of manually calling dbms_lob.append? Maybe it's even faster? (I know, we can get NO_DATA_FOUND here, but as soon as this procedure is never called directly by user, I think it's safe to do so.)
Re: Highlighting search results with user_datastore context index [message #435967 is a reply to message #435966] Sun, 20 December 2009 03:31 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
You can certainly use just a select in the procedure. The procedure that I provided was not intended as a recommendation, just an example, in order to complete the demo. Out of habit, I just threw together the type of thing that I was used to using in earlier versions with restrictions on concatenating clobs. Also, usually such a procedure with a user_datastore is used for multiple tables. If you only have one table, then you might as well use a multicolumn_datastore, eliminating the need for a procedure at all. However, I figured this might just be a simplified example.

[Updated on: Sun, 20 December 2009 03:34]

Report message to a moderator

Re: Highlighting search results with user_datastore context index [message #435968 is a reply to message #435967] Sun, 20 December 2009 04:14 Go to previous messageGo to next message
shindi
Messages: 3
Registered: December 2009
Junior Member
Thank you, Barbara!
Re: Highlighting search results with user_datastore context index [message #439562 is a reply to message #435826] Mon, 18 January 2010 06:56 Go to previous message
DennisPotter
Messages: 1
Registered: January 2010
Junior Member
Hey Barbara thanks for that. I'm doing something similar and never thought about creating more than 1 context index in a table. Anyway thanks for the code and advice.

Dennis
Previous Topic: Show Markup of Searced Multi-Column Text
Next Topic: Oracle text concatenated datastore
Goto Forum:
  


Current Time: Thu Mar 28 09:24:01 CDT 2024