| Oracle Text index on multiple tables [message #109298] |
Wed, 23 February 2005 10:56  |
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  |
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 |
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 |
 |
Barbara Boehmer
Messages: 9104
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 |
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 |
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 |
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 
-Ron
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
