Home » Server Options » Text & interMedia » Oracle Text index on multiple tables
Oracle Text index on multiple tables [message #109298] Wed, 23 February 2005 10:56 Go to next message
Animir
Messages: 1
Registered: February 2005
Junior Member
Hi there,

We are working with a standard application. This application has poor search possibilities and therefore I want to create a search function myself, directly in the database.

Because I don't want to change the database structure, I have created a new user and synonyms to the other database.

My question is, what is the best way to create 1 index on multiple columns from multiple synonyms, so that I can search through the database with 1 query.

Thank you very much!

Ray
Re: Oracle Text index on multiple tables [message #115367 is a reply to message #109298] Sun, 10 April 2005 18:07 Go to previous messageGo to next message
rhardman
Messages: 25
Registered: April 2005
Junior Member
Take a look at the multi_column_datastore. In the example below I include two columns, but you can do more than that. Check the Oracle Text app developer's guide for the disclaimers on it.

Example:
BEGIN
CTX_DDL.CREATE_PREFERENCE('EXPERT_CONCAT_DATASTORE', 'MULTI_COLUMN_DATASTORE');
CTX_DDL.SET_ATTRIBUTE('EXPERT_CONCAT_DATASTORE',
'columns',
'TITLE, SHORT_DESCRIPTION');
END;
/
Re: Oracle Text index on multiple tables [message #125204 is a reply to message #115367] Thu, 23 June 2005 15:39 Go to previous messageGo to next message
pmj1
Messages: 11
Registered: June 2005
Location: Ann Arobr, MI
Junior Member
When you go to CREATE a CONTEXT index for a multi_column_datastore, isn't there some ruse needed to identify which column will be indexed? What might a CREATE INDEX statement look like for 'EXPERT_CONCAT_DATASTORE'?
Re: Oracle Text index on multiple tables [message #125282 is a reply to message #125204] Fri, 24 June 2005 03:31 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
You would create your index on a dummy column that is not one of the columns that you want to search. The following example is for multiple columns in one table.

scott@ORA92> CONNECT CTXSYS
Connected.
scott@ORA92> @ LOGIN
scott@ORA92> SET ECHO OFF

GLOBAL_NAME
----------------------------------------------------------------------------------------------------
ctxsys@ORA92

ctxsys@ORA92> EXEC CTX_DDL.DROP_PREFERENCE ('expert_concat_datastore')

PL/SQL procedure successfully completed.

ctxsys@ORA92> BEGIN
  2    CTX_DDL.CREATE_PREFERENCE
  3  	 ('expert_concat_datastore', 'MULTI_COLUMN_DATASTORE');
  4    CTX_DDL.SET_ATTRIBUTE
  5  	 ('expert_concat_datastore', 'COLUMNS', 'title, short_description');
  6  END;
  7  /

PL/SQL procedure successfully completed.

ctxsys@ORA92> CONNECT scott
Connected.
ctxsys@ORA92> @ LOGIN
ctxsys@ORA92> SET ECHO OFF

GLOBAL_NAME
----------------------------------------------------------------------------------------------------
scott@ORA92

scott@ORA92> DROP TABLE books
  2  /

Table dropped.

scott@ORA92> CREATE TABLE books
  2    (id		  NUMBER,
  3  	title		  VARCHAR2(30),
  4  	short_description VARCHAR2(30),
  5  	dummy		  VARCHAR2(1))
  6  /

Table created.

scott@ORA92> INSERT ALL
  2  INTO books VALUES (1, 'Oracle databases'	 , 'lots of stuff'     , NULL)
  3  INTO books VALUES (2, 'Computers'		 , 'whatever'	       , NULL)
  4  INTO books VALUES (3, 'Successful companies', 'Oracle Corporation', NULL)
  5  SELECT * FROM DUAL
  6  /

3 rows created.

scott@ORA92> CREATE INDEX books_keyword_index
  2  ON books (dummy)
  3  INDEXTYPE IS CTXSYS.CONTEXT
  4  PARAMETERS ('datastore CTXSYS.expert_concat_datastore')
  5  /

Index created.

scott@ORA92> SELECT id, title, short_description
  2  FROM   books
  3  WHERE  CONTAINS (dummy, 'Oracle') > 0
  4  /

        ID TITLE                          SHORT_DESCRIPTION
---------- ------------------------------ ------------------------------
         3 Successful companies           Oracle Corporation
         1 Oracle databases               lots of stuff

scott@ORA92> 


Re: Oracle Text index on multiple tables [message #125335 is a reply to message #125282] Fri, 24 June 2005 09:53 Go to previous messageGo to next message
pmj1
Messages: 11
Registered: June 2005
Location: Ann Arobr, MI
Junior Member
Thanks. That helps.

Actually I should have read the previous posting more carefully. It does provide an answer to the question I asked.

Also I found a place where this is described in the documentation -- current (10g Release 1 (10.1)) Oracle Text Reference, Section 2.2.2.1 Indexing and DML.
Re: Oracle Text index on multiple tables [message #125556 is a reply to message #125282] Mon, 27 June 2005 10:21 Go to previous messageGo to next message
pmj1
Messages: 11
Registered: June 2005
Location: Ann Arobr, MI
Junior Member
There are two closely related approaches described above. They look to be essentially the same except for one detail -- the choice of placeholder for the create index command.

One approach uses the first of the columns being concatenated by the MUTLI_COLUMN_DATASTORE as the column in the CREATE INDEX ... TABLE(indexed_column) ....

The second (and the Oracle documentation) advocate the creation of a dummy column used only as the place holder in the CREATE INDEX statement.

They probably both work but I was wondering if people had a reason for doing it one way or the other.

Re: Oracle Text index on multiple tables [message #125564 is a reply to message #125556] Mon, 27 June 2005 12:36 Go to previous message
rhardman
Messages: 25
Registered: April 2005
Junior Member
It all depends on what gets updated when.

If you have two columns in a concatenated datastore, the one specified during create index is the one that must be modified in order for it to be picked up during a sync.

For example, with the two columns as I had it, the column specified during create index (TITLE, for example) must be changed if the SHORT_DESCRIPTION is changed. If it isn't modified in some way, nothing will be marked as pending, and a sync will not catch the fact that a mod was made. This isn't a problem if both columns will always be modified together.

If one or more columns will be modified independent of the column you specify in the create index statement then the dummy column is probably the way to go. Create another dummy column, index on that column, and update it whenever a change is made to any other column. This will force the record to be marked as pending for the next sync.

Did that clear it up a bit, or confuse it more Smile

-Ron

Previous Topic: How to Retrive Clob fields Faster
Next Topic: Image Formats
Goto Forum:
  


Current Time: Thu Mar 28 15:57:27 CDT 2024