Home » Server Options » Text & interMedia » Oracle text concatenated datastore
Oracle text concatenated datastore [message #293320] Fri, 11 January 2008 12:19 Go to next message
xokas11
Messages: 28
Registered: January 2008
Location: Guadalajara, Spain
Junior Member
Hi!
Currently I am developing some PL/SQL functions to search my database. The thing is i don´t really know how to create multicolumn indexes on text columns(I want to be able to search forum titles and messages and blog titles and messages).
I found in the oracle site that you can use a concatenated data store(more specifically i found this "http://www.oracle.com/technology/sample_code/products/text/htdocs/concatenated_text_datastore/cdstore_readme.html")
It looks like a very decent solution but we haven´t been able to make it work(My DBA is trying to solve it right now). I don´t know what kind of permission or roles my user should have to be able to use this concatenated data store.
I've searched through this site an I have found this post http://www.orafaq.com/forum/t/41341/0/
It looks promising but if I have followed the steps and I wasn't able to make it work on my database tables.
So what I am looking for is the following:
- What kind of roles/permits should my user(let's call him dummy_user) have?
- In relation to the other post : Which user has to create the data store?

Thanks in advance for your time.
Greetings, Joaquin.
Re: Oracle text concatenated datastore [message #293363 is a reply to message #293320] Fri, 11 January 2008 19:25 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8024
Registered: November 2002
Location: California, USA
Senior Member
xokas11 wrote on Fri, 11 January 2008 10:19

Hi!
Currently I am developing some PL/SQL functions to search my database. The thing is i don´t really know how to create multicolumn indexes on text columns(I want to be able to search forum titles and messages and blog titles and messages).
I found in the oracle site that you can use a concatenated data store(more specifically i found this "http://www.oracle.com/technology/sample_code/products/text/htdocs/concatenated_text_datastore/cdstore_readme.html")
It looks like a very decent solution but we haven´t been able to make it work(My DBA is trying to solve it right now). I don´t know what kind of permission or roles my user should have to be able to use this concatenated data store.
I've searched through this site an I have found this post http://www.orafaq.com/forum/t/41341/0/
It looks promising but if I have followed the steps and I wasn't able to make it work on my database tables.
So what I am looking for is the following:
- What kind of roles/permits should my user(let's call him dummy_user) have?
- In relation to the other post : Which user has to create the data store?

Thanks in advance for your time.
Greetings, Joaquin.


You could use either the concatenated datastore utility or the multi_column_datastore. I have used both before. I would recommend using the multi_column_datastore. I believe the original concatenated datastore utility was created before the multi_column_datastore existed. Now that there is a built-in multi_column_datastore, there really isn't as much of a need for the concatenated datastore utility, which is not part of Oracle, has to be downloaded separately, and may not be supported in future versions. As of 10g, the privileges required have become more simple for the built-in multi_column_datastore and more complicated for the concatenated datastore. Prior to 10g, the multi_column_datastore had to be created in the ctxsys schema, but can now be created in any schema. The concatenated datastore now has some strange requirements that it be created in each user schema or something like that.

The ctxapp role is sufficient for most Oracle Text functionality. However, remember that privileges granted through roles do not count in store procedures, functions, views, and such. So, if you are trying to create functions that use Oracle Text features, you will need to grant privileges on each directly, not through a role. You will definitely need to grant execute on ctx_ddl.

Saying that you weren't able to make it work is a little vague. Can you be more specific? What error messages and/or results did you get? Please post a copy and paste that includes what you did and the results.


Re: Oracle text concatenated datastore [message #293364 is a reply to message #293363] Fri, 11 January 2008 20:16 Go to previous messageGo to next message
xokas11
Messages: 28
Registered: January 2008
Location: Guadalajara, Spain
Junior Member
Hi thanks for the answer. On monday I'll try to get the output to post it here.
Do you know of any kind of tutorial for the multicolumn datastore? or any kind of information of it? thanks in advance.
Again thanks for your time!
Greeting Joaquin
Re: Oracle text concatenated datastore [message #293365 is a reply to message #293364] Fri, 11 January 2008 20:28 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8024
Registered: November 2002
Location: California, USA
Senior Member
xokas11 wrote on Fri, 11 January 2008 18:16

Hi thanks for the answer. On monday I'll try to get the output to post it here.
Do you know of any kind of tutorial for the multicolumn datastore? or any kind of information of it? thanks in advance.
Again thanks for your time!
Greeting Joaquin



There is plenty of information and examples in the online documentation and on this forum and the OTN forum. Here is an example based on what little information you have provided. It assumes that you have a dummy_creator that will create the table and index and trigger and function and such and will require the ctxapp role. It also assumes that you will have multiple users such as dummy_user who will need to insert titles and messages, (probably through an application, not an insert statement) and will be querying by supplying values to search for to a function that returns a ref cursor, which will require insert privileges on the table (or execute privileges on your application) and execute privileges on the function. You might start by testing something simple like this, then modifying it to suit your needs, and posting what you tried if you get stuck.


SCOTT@orcl_11g> -- create dummy_creator and dummy_user and grant privileges
SCOTT@orcl_11g> -- (you will probably want to grant quotas on tablespaces, rather than use the resource role):
SCOTT@orcl_11g> CREATE USER dummy_creator IDENTIFIED BY dummy_creator
  2  /

User created.

SCOTT@orcl_11g> GRANT CONNECT, RESOURCE, CTXAPP TO dummy_creator
  2  /

Grant succeeded.

SCOTT@orcl_11g> CREATE USER dummy_user IDENTIFIED BY dummy_user
  2  /

User created.

SCOTT@orcl_11g> GRANT CONNECT, RESOURCE TO dummy_user
  2  /

Grant succeeded.

SCOTT@orcl_11g> -- connect as dummy_creator,
SCOTT@orcl_11g> -- create the table, multi_column_datastore, index, trigger, and search function
SCOTT@orcl_11g> -- and grant privileges to dummy_user:
SCOTT@orcl_11g> CONNECT dummy_creator/dummy_creator
Connected.
DUMMY_CREATOR@orcl_11g> 
DUMMY_CREATOR@orcl_11g> CREATE TABLE dummy_table
  2    (title	    VARCHAR2 (60),
  3  	message     CLOB,
  4  	any_column  VARCHAR2 (1))
  5  /

Table created.

DUMMY_CREATOR@orcl_11g> EXEC CTX_DDL.CREATE_PREFERENCE ('dummy_datastore', 'MULTI_COLUMN_DATASTORE')

PL/SQL procedure successfully completed.

DUMMY_CREATOR@orcl_11g> EXEC CTX_DDL.SET_ATTRIBUTE ('dummy_datastore', 'COLUMNS', 'title,message')

PL/SQL procedure successfully completed.

DUMMY_CREATOR@orcl_11g> CREATE INDEX dummy_index ON dummy_table (any_column)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  PARAMETERS
  4    ('DATASTORE dummy_datastore
  5  	 SYNC	   (ON COMMIT)')
  6  /

Index created.

DUMMY_CREATOR@orcl_11g> CREATE OR REPLACE TRIGGER dummy_biur
  2    BEFORE UPDATE ON dummy_table
  3    FOR EACH ROW
  4  BEGIN
  5    IF INSERTING THEN :NEW.any_column := '';
  6    ELSIF UPDATING THEN :NEW.any_column := :OLD.any_column;
  7    END IF;
  8  END dummy_biur;
  9  /

Trigger created.

DUMMY_CREATOR@orcl_11g> SHOW ERRORS
No errors.
DUMMY_CREATOR@orcl_11g> CREATE OR REPLACE FUNCTION search
  2    (p_string IN VARCHAR2)
  3    RETURN SYS_REFCURSOR
  4  AS
  5    v_results SYS_REFCURSOR;
  6  BEGIN
  7    OPEN v_results FOR
  8    SELECT title, message
  9    FROM   dummy_table
 10    WHERE  CONTAINS (any_column, p_string) > 0;
 11    RETURN v_results;
 12  END search;
 13  /

Function created.

DUMMY_CREATOR@orcl_11g> SHOW ERRORS
No errors.
DUMMY_CREATOR@orcl_11g> GRANT INSERT ON dummy_table TO dummy_user
  2  /

Grant succeeded.

DUMMY_CREATOR@orcl_11g> GRANT EXECUTE ON search TO dummy_user
  2  /

Grant succeeded.

DUMMY_CREATOR@orcl_11g> -- connect as dummy_user and test insert, select, and search function:
DUMMY_CREATOR@orcl_11g> CONNECT dummy_user/dummy_user
Connected.
DUMMY_USER@orcl_11g> 
DUMMY_USER@orcl_11g> INSERT INTO dummy_creator.dummy_table (title, message) VALUES
  2    ('Oracle Text index on multiple columns',
  3  	'question:  How do I create an Oracle Text index on multiple columns?')
  4  /

1 row created.

DUMMY_USER@orcl_11g> INSERT INTO dummy_creator.dummy_table (title, message) VALUES
  2    ('Oracle Text index on multiple columns',
  3  	'answer:  use a multi_column_datastore')
  4  /

1 row created.

DUMMY_USER@orcl_11g> INSERT INTO dummy_creator.dummy_table (title, message) VALUES
  2    ('Text index on multiple columns',
  3  	'question:  How do I create a multi_column datastore?')
  4  /

1 row created.

DUMMY_USER@orcl_11g> INSERT INTO dummy_creator.dummy_table (title, message) VALUES
  2    ('Text index on multiple columns',
  3  	'answer:  Search the online documentation for information and examples.')
  4  /

1 row created.

DUMMY_USER@orcl_11g> INSERT INTO dummy_creator.dummy_table (title, message) VALUES
  2    ('Privileges for multi_column_datastore',
  3  	'question:  What privileges are required for an Oracle multi_column_datastore?')
  4  /

1 row created.

DUMMY_USER@orcl_11g> INSERT INTO dummy_creator.dummy_table (title, message) VALUES
  2    ('Privileges for multi_column_datastore',
  3  	'answer:  at least ctxapp role and execute on ctx_ddl')
  4  /

1 row created.

DUMMY_USER@orcl_11g> COMMIT
  2  /

Commit complete.

DUMMY_USER@orcl_11g> VARIABLE words VARCHAR2 (30)
DUMMY_USER@orcl_11g> EXEC :words := 'Oracle'

PL/SQL procedure successfully completed.

DUMMY_USER@orcl_11g> VARIABLE results REFCURSOR
DUMMY_USER@orcl_11g> EXEC :results := dummy_creator.search (:words)

PL/SQL procedure successfully completed.

DUMMY_USER@orcl_11g> PRINT results

TITLE
------------------------------------------------------------
MESSAGE
--------------------------------------------------------------------------------
Oracle Text index on multiple columns
question:  How do I create an Oracle Text index on multiple columns?

Oracle Text index on multiple columns
answer:  use a multi_column_datastore

Privileges for multi_column_datastore
question:  What privileges are required for an Oracle multi_column_datastore?


DUMMY_USER@orcl_11g> 






Re: Oracle text concatenated datastore [message #293366 is a reply to message #293364] Fri, 11 January 2008 20:32 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8024
Registered: November 2002
Location: California, USA
Senior Member
Here is a link to the section on multi_column_datastore in the Text Reference of the Oracle 10g online documentation:


http://download.oracle.com/docs/cd/B19306_01/text.102/b14218/cdatadic.htm#sthref320
Re: Oracle text concatenated datastore [message #293929 is a reply to message #293366] Tue, 15 January 2008 11:41 Go to previous messageGo to next message
xokas11
Messages: 28
Registered: January 2008
Location: Guadalajara, Spain
Junior Member
Hi. Thanks For all the information but i have a problem with the example you provided me. When i try to create the preference(EXEC CTX_DDL.CREATE_PREFERENCE ('dummy_datastore','MULTI_COLUMN_DATASTORE'))
Oracle says that the package has errors.
Is there any way to fix this?
Thanks in advance.
PD:I am attaching the error although it is in spanish
Re: Oracle text concatenated datastore [message #293961 is a reply to message #293929] Tue, 15 January 2008 14:48 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8024
Registered: November 2002
Location: California, USA
Senior Member
Try the following, or have your DBA try the following, to do a manual reinstall of your Oracle Text, to see if it fixes the corrupted ctx_ddl package, substituting your own Oracle home directory.

SQL> CONNECT SYS AS SYSDBA
SQL> START <your_oracle_home>\ctx\admin\catctx.sql

Re: Oracle text concatenated datastore [message #295205 is a reply to message #293320] Mon, 21 January 2008 12:53 Go to previous messageGo to next message
xokas11
Messages: 28
Registered: January 2008
Location: Guadalajara, Spain
Junior Member
Hi! Thanks for all your help but i am not currently able to search through my index. I 'll explain :
I have created the index and the preferences. I had to do it from the ctxsys schema because my dba wasnt able to give me the neccesary roles on my schema(mercadesa).
I created the trigger (but i don´t understand it quite yet) from the mercadesa schema.
Problem:
when i do this search
SELECT title, message
FROM dummy_table
WHERE CONTAINS (ANY_COLUMN, 'Oracle') > 0;
it doesnt return anything.
Any clues on the problem?
Thanks, Joaquin
Re: Oracle text concatenated datastore [message #295217 is a reply to message #295205] Mon, 21 January 2008 15:25 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8024
Registered: November 2002
Location: California, USA
Senior Member
xokas11 wrote on Mon, 21 January 2008 10:53
Hi! Thanks for all your help but i am not currently able to search through my index. I 'll explain :
I have created the index and the preferences. I had to do it from the ctxsys schema because my dba wasnt able to give me the neccesary roles on my schema(mercadesa).
I created the trigger (but i don´t understand it quite yet) from the mercadesa schema.
Problem:
when i do this search
SELECT title, message
FROM dummy_table
WHERE CONTAINS (ANY_COLUMN, 'Oracle') > 0;
it doesnt return anything.
Any clues on the problem?
Thanks, Joaquin



It would help if you would provide a copy and paste of exactly what you did, including any error messages and results. Otherwise, all I can do is guess at what you did.

What do you mean your DBA was not able to give the necessary roles? Do you mean that there is policy prohibiting him from doing so or that he was not willing to do so or that he does not know how or what?

The trigger populates the any_column column whenever any other column is updated. Any index created on the any_column column is only updated when the any_column column is updated. So, without the trigger, your index would not be updated and you would get no rows returned on any contains query.

I have provided a reproduction of what you should have done below that works. I have also indicated things that you need to make sure were done, in order for it to work, such as:

1. Make sure you grant privileges on the dummy_table to ctxsys, so that the index can be created.

2. Make sure you preface the table name with the schema name when creating the index.

3. Make sure you use both parameters when creating the index. Without the datastore, the index would only be on the any_column column and would not search the data in the other columns. Without the sync (on commit), the index would not be updated after you insert or updated and any search with contains would result in no rows returned.

4. If you want to check what is indexed, then you will need privileges on the index table.

5. You must commit in order for the inserts to fire the trigger, which populates the any_column column, which causes the index table to be updated. Otherwise, you get no rows returned when using a contains query.


-- as some user with dba privileges:
SCOTT@orcl_11g> CREATE USER mercadesa IDENTIFIED BY mercadesa
  2  /

User created.

SCOTT@orcl_11g> GRANT CONNECT, RESOURCE TO mercadesa
  2  /

Grant succeeded.


-- as mercadesa:
MERCADESA@orcl_11g> 
MERCADESA@orcl_11g> CREATE TABLE dummy_table
  2    (title	    VARCHAR2 (60),
  3  	message     CLOB,
  4  	any_column  VARCHAR2 (1))
  5  /

Table created.

MERCADESA@orcl_11g> -- this trigger updates the any_column column when any other column is inserted or updated;
MERCADESA@orcl_11g> -- this is necessary in order to populate any index made on the any_column column:
MERCADESA@orcl_11g> CREATE OR REPLACE TRIGGER dummy_biur
  2    BEFORE UPDATE ON dummy_table
  3    FOR EACH ROW
  4  BEGIN
  5    IF INSERTING THEN :NEW.any_column := '';
  6    ELSIF UPDATING THEN :NEW.any_column := :OLD.any_column;
  7    END IF;
  8  END dummy_biur;
  9  /

Trigger created.

MERCADESA@orcl_11g> SHOW ERRORS
No errors.
MERCADESA@orcl_11g> -- ctxsys will need privileges to create the index on the dummy_table:
MERCADESA@orcl_11g> GRANT ALL ON dummy_table TO ctxsys
  2  /

Grant succeeded.


-- as ctxsys:
CTXSYS@orcl_11g> EXEC CTX_DDL.CREATE_PREFERENCE ('dummy_datastore', 'MULTI_COLUMN_DATASTORE')

PL/SQL procedure successfully completed.

CTXSYS@orcl_11g> EXEC CTX_DDL.SET_ATTRIBUTE ('dummy_datastore', 'COLUMNS', 'title,message')

PL/SQL procedure successfully completed.

CTXSYS@orcl_11g> -- you must preference the table name with the schema name:
CTXSYS@orcl_11g> CREATE INDEX dummy_index ON mercadesa.dummy_table (any_column)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  -- make sure you include both parameters below:
  4  PARAMETERS
  5    ('DATASTORE dummy_datastore
  6  	 SYNC	   (ON COMMIT)')
  7  /

Index created.

CTXSYS@orcl_11g> -- grant privileges so that mercadesa can see that the index table is populated:
CTXSYS@orcl_11g> GRANT SELECT ON dr$dummy_index$i TO mercadesa
  2  /

Grant succeeded.


-- as mercadesa:
MERCADESA@orcl_11g> INSERT INTO dummy_table (title, message) VALUES
  2    ('Oracle Text index on multiple columns',
  3  	'question:  How do I create an Oracle Text index on multiple columns?')
  4  /

1 row created.

MERCADESA@orcl_11g> INSERT INTO dummy_table (title, message) VALUES
  2    ('Oracle Text index on multiple columns',
  3  	'answer:  use a multi_column_datastore')
  4  /

1 row created.

MERCADESA@orcl_11g> INSERT INTO dummy_table (title, message) VALUES
  2    ('Text index on multiple columns',
  3  	'question:  How do I create a multi_column datastore?')
  4  /

1 row created.

MERCADESA@orcl_11g> INSERT INTO dummy_table (title, message) VALUES
  2    ('Text index on multiple columns',
  3  	'answer:  Search the online documentation for information and examples.')
  4  /

1 row created.

MERCADESA@orcl_11g> INSERT INTO dummy_table (title, message) VALUES
  2    ('Privileges for multi_column_datastore',
  3  	'question:  What privileges are required for an Oracle multi_column_datastore?')
  4  /

1 row created.

MERCADESA@orcl_11g> INSERT INTO dummy_table (title, message) VALUES
  2    ('Privileges for multi_column_datastore',
  3  	'answer:  at least ctxapp role and execute on ctx_ddl')
  4  /

1 row created.

MERCADESA@orcl_11g> -- You must commit in order for the inserts to fire the trigger and populate the
MERCADESA@orcl_11g> -- any_column column and populate the ctxsys.dr$dummy_index$i index table:
MERCADESA@orcl_11g> COMMIT
  2  /

Commit complete.

MERCADESA@orcl_11g> -- check what words are tokenized, indexed, and searchable in the index table:
MERCADESA@orcl_11g> SELECT token_text FROM ctxsys.dr$dummy_index$i
  2  /

TOKEN_TEXT
----------------------------------------------------------------
ANSWER
COLUMN
COLUMNS
CREATE
CTX
CTXAPP
DATASTORE
DDL
DOCUMENTATION
EXAMPLES
EXECUTE
INDEX
INFORMATION
LEAST
MESSAGE
MULTI
MULTIPLE
ONLINE
ORACLE
PRIVILEGES
QUESTION
REQUIRED
ROLE
SEARCH
TEXT
TITLE
USE

27 rows selected.

MERCADESA@orcl_11g> -- test query:
MERCADESA@orcl_11g> SELECT title, message
  2  FROM   dummy_table
  3  WHERE  CONTAINS (any_column, 'Oracle') > 0
  4  /

TITLE
------------------------------------------------------------
MESSAGE
--------------------------------------------------------------------------------
Oracle Text index on multiple columns
question:  How do I create an Oracle Text index on multiple columns?

Oracle Text index on multiple columns
answer:  use a multi_column_datastore

Privileges for multi_column_datastore
question:  What privileges are required for an Oracle multi_column_datastore?


MERCADESA@orcl_11g> 



Re: Oracle text concatenated datastore [message #295547 is a reply to message #295217] Tue, 22 January 2008 12:21 Go to previous messageGo to next message
xokas11
Messages: 28
Registered: January 2008
Location: Guadalajara, Spain
Junior Member
Hi! I'm following the last example you gave me but i have a problem. When i try to create the index it displays this error?
Code:
CREATE INDEX dummy_index ON mercadesa.dummy_table(any_column) indextype IS ctxsys.context
parameters('DATASTORE dummy_datastore SYNC (ON COMMIT)');

Error:
CREATE INDEX dummy_index ON mercadesa.dummy_table(any_column) indextype IS ctxsys.context -- make sure you include both parameters below:
parameters('DATASTORE dummy_datastore
SYNC (ON COMMIT)')
Error at Command Line:3 Column:13
Error report:
SQL Error: ORA-29855: se ha producido un error en la ejecución de la rutina ODCIINDEXCREATE
ORA-20000: Error de Oracle Text:
DRG-11001: falta la palabra clave cuando se esperaba una de (
ORA-06512: en "CTXSYS.DRUE", línea 160
ORA-06512: en "CTXSYS.TEXTINDEXMETHODS", línea 364
29855. 00000 - "error occurred in the execution of ODCIINDEXCREATE routine"
*Cause: Failed to successfully execute the ODCIIndexCreate routine.
*Action: Check to see if the routine has been coded correctly.

Can i give you any more information to help me?
UPDATE: I don´t know how. But I made it work.
I suppose it had something to do with the format of the text here because when I typed it by hand it worked. Thanks.

[Updated on: Tue, 22 January 2008 13:32]

Report message to a moderator

Re: Oracle text concatenated datastore [message #305454 is a reply to message #295547] Mon, 10 March 2008 16:58 Go to previous messageGo to next message
redonisc
Messages: 20
Registered: March 2008
Location: Guatemala, C.A.
Junior Member
Hi! i've searched to get this scenario going but my problem is can i use this solution? when the different columns of the index are on several different tables?

Lets say this:
Table ProdGroups(Id, Name) PK(id)
Table Product(Id, Name, group_type) FK(ProdGroups.id = group_type)

The search have to cover ProdGroups.Name and also Product.Name, i've found another solution using materialized views...

Thanks for your help.

[Updated on: Mon, 10 March 2008 17:00]

Report message to a moderator

Re: Oracle text concatenated datastore [message #305457 is a reply to message #305454] Mon, 10 March 2008 17:13 Go to previous messageGo to next message
xokas11
Messages: 28
Registered: January 2008
Location: Guadalajara, Spain
Junior Member
I've only tried it in the same table but i don't think you could pull that off look

CTXSYS@orcl_11g> -- you must preference the table name with the schema name:
CTXSYS@orcl_11g> CREATE INDEX dummy_index ON mercadesa.dummy_table (any_column)
2 INDEXTYPE IS CTXSYS.CONTEXT
3 -- make sure you include both parameters below:
4 PARAMETERS
5 ('DATASTORE dummy_datastore
6 SYNC (ON COMMIT)')
7 /
You have to create the index on a specific table and I don't know if oracle supports that. Maybe you could try using the same preference for 2 different indexes like this:
CTXSYS@orcl_11g> -- you must preference the table name with the schema name:
CTXSYS@orcl_11g> CREATE INDEX dummy_index ON mercadesa.dummy_table1 (any_column)
2 INDEXTYPE IS CTXSYS.CONTEXT
3 -- make sure you include both parameters below:
4 PARAMETERS
5 ('DATASTORE dummy_datastore
6 SYNC (ON COMMIT)')
7 /
CTXSYS@orcl_11g> -- you must preference the table name with the schema name:
CTXSYS@orcl_11g> CREATE INDEX dummy_index ON mercadesa.dummy_table2 (any_column)
2 INDEXTYPE IS CTXSYS.CONTEXT
3 -- make sure you include both parameters below:
4 PARAMETERS
5 ('DATASTORE dummy_datastore
6 SYNC (ON COMMIT)')
7 /


And when you create the preference you specify the columns in all 2 tables like this:

CTXSYS@orcl_11g> EXEC CTX_DDL.CREATE_PREFERENCE ('dummy_datastore', 'MULTI_COLUMN_DATASTORE')

PL/SQL procedure successfully completed.

CTXSYS@orcl_11g> EXEC CTX_DDL.SET_ATTRIBUTE ('dummy_datastore', 'COLUMNS', 'column1table1,column2table2')

PL/SQL procedure successfully completed.

Also you could try searching at the 2 tables at the same time on the contains clause of your select:
select * from table1,table2 where contains(table1dummy,query) > 0 or contains(table2dummy,querty) > 0

If you try anything of these solution please post your response here.
Also have you try looking at the documentation?
Look here that should help.
Greetings, Joaquin


Re: Oracle text concatenated datastore [message #305458 is a reply to message #305457] Mon, 10 March 2008 17:28 Go to previous messageGo to next message
redonisc
Messages: 20
Registered: March 2008
Location: Guatemala, C.A.
Junior Member
Thanks for your comment, currently i've that kind of solution (contains -> table1.column or contains -> table2.column) but the problem is with the score, if in some table i've a minor match(but is the best of that place) it will be on top of some other with more accurancy...

As i told, the better solution found until now is a materialized view -> index
Re: Oracle text concatenated datastore [message #305459 is a reply to message #305458] Mon, 10 March 2008 17:35 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8024
Registered: November 2002
Location: California, USA
Senior Member
The best method, when the columns are in multiple related tables, is to use a user_datastore with a procedure as demonstrated below. If you use the materialized view, you are likely to encounter issues with refreshing the view and synchronizing the index.

SCOTT@orcl_11g> CREATE Table ProdGroups
  2    (Id    NUMBER,
  3  	Name  VARCHAR2 (15),
  4  	CONSTRAINT ProdGroups_Id_pk PRIMARY KEY (Id))
  5  /

Table created.

SCOTT@orcl_11g> INSERT ALL
  2  INTO ProdGroups VALUES (1, 'Group1')
  3  INTO ProdGroups VALUES (2, 'Group2')
  4  SELECT * FROM DUAL
  5  /

2 rows created.

SCOTT@orcl_11g> CREATE Table Product
  2    (Id	    NUMBER,
  3  	Name	    VARCHAR2 (15),
  4  	group_type  NUMBER,
  5  	CONSTRAINT Product_fk FOREIGN KEY (group_type) REFERENCES ProdGroups (Id))
  6  /

Table created.

SCOTT@orcl_11g> INSERT ALL
  2  INTO Product VALUES (10, 'Product10', 1)
  3  INTO Product VALUES (11, 'Product11', 1)
  4  INTO Product VALUES (20, 'Product20', 2)
  5  INTO Product VALUES (21, 'Product21', 2)
  6  SELECT * FROM DUAL
  7  /

4 rows created.

SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE your_proc
  2    (p_rowid IN	      ROWID,
  3  	p_clob	IN OUT NOCOPY CLOB)
  4  AS
  5  BEGIN
  6    FOR r1 IN (SELECT id, name FROM ProdGroups WHERE ROWID = p_rowid) LOOP
  7  	 DBMS_LOB.WRITEAPPEND (p_clob, 12, '<group_name>');
  8  	 DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r1.name), r1.name);
  9  	 FOR r2 IN (SELECT name FROM Product WHERE group_type = r1.id) LOOP
 10  	   DBMS_LOB.WRITEAPPEND (p_clob, 14, '<product_name>');
 11  	   DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r2.name), r2.name);
 12  	   DBMS_LOB.WRITEAPPEND (p_clob, 15, '</product_name>');
 13  	 END LOOP;
 14  	 DBMS_LOB.WRITEAPPEND (p_clob, 13, '</group_name>');
 15    END LOOP;
 16  END your_proc;
 17  /

Procedure created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> BEGIN
  2    CTX_DDL.CREATE_PREFERENCE ('your_datastore', 'USER_DATASTORE');
  3    CTX_DDL.SET_ATTRIBUTE ('your_datastore', 'PROCEDURE', 'your_proc');
  4    CTX_DDL.SET_ATTRIBUTE ('your_datastore', 'OUTPUT_TYPE', 'CLOB');
  5  END;
  6  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> CREATE INDEX your_index ON prodgroups (name)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  PARAMETERS
  4    ('DATASTORE	your_datastore
  5  	 SECTION GROUP	CTXSYS.AUTO_SECTION_GROUP')
  6  /

Index created.

SCOTT@orcl_11g> SELECT token_text FROM dr$your_index$i
  2  /

TOKEN_TEXT
----------------------------------------------------------------
GROUP1
GROUP2
GROUP_NAME
PRODUCT10
PRODUCT11
PRODUCT20
PRODUCT21
PRODUCT_NAME

8 rows selected.

SCOTT@orcl_11g> SELECT g.name AS group_name, p.name AS product_name
  2  FROM   ProdGroups g, Product p
  3  WHERE  CONTAINS (g.name, 'Group1') > 0
  4  /

GROUP_NAME      PRODUCT_NAME
--------------- ---------------
Group1          Product10
Group1          Product11
Group1          Product20
Group1          Product21

SCOTT@orcl_11g> SELECT g.name AS group_name
  2  FROM   ProdGroups g
  3  WHERE  CONTAINS (g.name, 'Product10') > 0
  4  /

GROUP_NAME
---------------
Group1

SCOTT@orcl_11g> SELECT g.name AS group_name, p.name AS product_name
  2  FROM   ProdGroups g, Product p
  3  WHERE  CONTAINS (g.name, 'Group2 WITHIN group_name') > 0
  4  /

GROUP_NAME      PRODUCT_NAME
--------------- ---------------
Group2          Product10
Group2          Product11
Group2          Product20
Group2          Product21

SCOTT@orcl_11g> SELECT g.name AS group_name
  2  FROM   ProdGroups g
  3  WHERE  CONTAINS (g.name, 'Product20 WITHIN product_name') > 0
  4  /

GROUP_NAME
---------------
Group2

SCOTT@orcl_11g> 


Re: Oracle text concatenated datastore [message #305705 is a reply to message #305459] Tue, 11 March 2008 10:59 Go to previous messageGo to next message
redonisc
Messages: 20
Registered: March 2008
Location: Guatemala, C.A.
Junior Member
Thanks for your help i've tested and re-tried to get this going for a few hours now and i cant get no records in here..."SELECT token_text FROM dr$your_index$i"

In my real scenario i've this definitions:
--schema APP_XX

CREATE TABLE RUBRO
(
RUBRO NUMBER(10) NOT NULL,
NOMBRE VARCHAR2(100 BYTE) NOT NULL,
CONSTRAINT rubro_pk PRIMARY KEY (rubro))
)
CREATE TABLE TIPO_PRODUCT
(
TIPO_PRODUCT NUMBER(10) NOT NULL,
NOMBRE VARCHAR2(100 BYTE) NOT NULL,
RUBRO NUMBER(10) NOT NULL,
CONSTRAINT tipo_product_pk PRIMARY KEY (tipo_product))
)
ALTER TABLE TIPO_PRODUCT ADD (
CONSTRAINT FK_GC_TIPO_PRODUCT146
FOREIGN KEY (RUBRO)
REFERENCES RUBRO (RUBRO));

CREATE TABLE PRODUCT
(
ID NUMBER(10) NOT NULL,
MARCA VARCHAR2(100 BYTE),
TIPO_PRODUCT NUMBER(10) NOT NULL,
CONSTRAINT product_pk PRIMARY KEY (id));
ALTER TABLE PRODUCT ADD (
CONSTRAINT FK_PRODUCT148
FOREIGN KEY (TIPO_PRODUCT)
REFERENCES TIPO_PRODUCT (TIPO_PRODUCT));

CREATE OR REPLACE procedure APP_XX.product_text_concat
(p_rowid IN ROWID, p_clob IN OUT NOCOPY CLOB)
as
begin
for r1 in (SELECT rubro, nombre from rubro where rowid=p_rowid) loop
DBMS_LOB.WRITEAPPEND (p_clob, 12, '<rubro>');
DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r1.nombre), r1.nombre);
FOR r2 IN (SELECT tipo_product, nombre FROM tipo_product WHERE Rubro = r1.rubro) LOOP
DBMS_LOB.WRITEAPPEND (p_clob, 14, '<tipo_product>');
DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r2.nombre), r2.nombre);
FOR r3 IN (SELECT nuprog, marca FROM gc_product WHERE Tipo_Product= r2.Tipo_Product) LOOP
DBMS_LOB.WRITEAPPEND (p_clob, 16, '<product>');
DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r3.marca), r3.marca);
DBMS_LOB.WRITEAPPEND (p_clob, 17, '</product>');
end loop;
DBMS_LOB.WRITEAPPEND (p_clob, 15, '</tipo_product>');
end loop;
DBMS_LOB.WRITEAPPEND (p_clob, 13, '</rubro>');
end loop;
end product_text_concat;
/

BEGIN
CTX_DDL.CREATE_PREFERENCE ('test_datastore', 'USER_DATASTORE');
CTX_DDL.SET_ATTRIBUTE ('test_datastore', 'PROCEDURE', 'app_xx.product_text_concat');
CTX_DDL.SET_ATTRIBUTE ('test_datastore', 'OUTPUT_TYPE', 'CLOB');
END;

CREATE INDEX product_full ON Rubro (Nombre)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('DATASTORE test_datastore SECTION GROUP CTXSYS.AUTO_SECTION_GROUP sync (every "SYSDATE+5/1440" MEMORY 64M PARALLEL 2)')

Now when i see this: SELECT token_text FROM dr$product_full$i returns no rows even when the tables currently have data...

[Updated on: Tue, 11 March 2008 11:02]

Report message to a moderator

Re: Oracle text concatenated datastore [message #305736 is a reply to message #305705] Tue, 11 March 2008 14:43 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8024
Registered: November 2002
Location: California, USA
Senior Member
There are a bunch of errors and inconsistencies in what you have posted. You should post a copy and paste of an actual run with line numbers and error messages and your Oracle version. You have supplied ddl for a product table, but your procedure selects from a gc_product table. You have some extra parentheses where there should be a semicolon or slash. Some of your numeric second parameters to dbms_lob.writeappend are wrong.

You need to make sure that your app_xx user has all necessary privileges. Since you are scheduling synchronization, app_xx will need create job privileges.

Did you insert and commit any data prior to creating the index or synchronize the data after insertiion? Otherwise, there will be no rows in the index tables to select from.

Please see the complete example below.

-- create user and grant privileges:
SYS@orcl_11g> CREATE USER app_xx IDENTIFIED BY app_xx
  2  /

User created.

SYS@orcl_11g> GRANT CONNECT, RESOURCE, CTXAPP TO app_xx
  2  /

Grant succeeded.

SYS@orcl_11g> -- needed to schedule synchronization:
SYS@orcl_11g> GRANT CREATE JOB TO app_xx
  2  /

Grant succeeded.


-- connect and create tables:
SYS@orcl_11g> CONNECT app_xx/app_xx
Connected.
APP_XX@orcl_11g> 
APP_XX@orcl_11g> CREATE TABLE RUBRO
  2  (
  3  RUBRO NUMBER(10) NOT NULL,
  4  NOMBRE VARCHAR2(100 BYTE) NOT NULL,
  5  CONSTRAINT rubro_pk PRIMARY KEY (rubro))
  6  /

Table created.

APP_XX@orcl_11g> CREATE TABLE TIPO_PRODUCT
  2  (
  3  TIPO_PRODUCT NUMBER(10) NOT NULL,
  4  NOMBRE VARCHAR2(100 BYTE) NOT NULL,
  5  RUBRO NUMBER(10) NOT NULL,
  6  CONSTRAINT tipo_product_pk PRIMARY KEY (tipo_product))
  7  /

Table created.

APP_XX@orcl_11g> ALTER TABLE TIPO_PRODUCT ADD (
  2  CONSTRAINT FK_GC_TIPO_PRODUCT146
  3  FOREIGN KEY (RUBRO)
  4  REFERENCES RUBRO (RUBRO))
  5  /

Table altered.

APP_XX@orcl_11g> CREATE TABLE GC_PRODUCT
  2  (
  3  ID NUMBER(10) NOT NULL,
  4  MARCA VARCHAR2(100 BYTE),
  5  TIPO_PRODUCT NUMBER(10) NOT NULL,
  6  CONSTRAINT product_pk PRIMARY KEY (id))
  7  /

Table created.

APP_XX@orcl_11g> ALTER TABLE GC_PRODUCT ADD (
  2  CONSTRAINT FK_PRODUCT148
  3  FOREIGN KEY (TIPO_PRODUCT)
  4  REFERENCES TIPO_PRODUCT (TIPO_PRODUCT))
  5  /

Table altered.


-- create procedure and preferences:
APP_XX@orcl_11g> CREATE OR REPLACE procedure app_xx.product_text_concat
  2    (p_rowid IN ROWID, p_clob IN OUT CLOB)
  3  as
  4  begin
  5    for r1 in (SELECT rubro, nombre from rubro where rowid=p_rowid) loop
  6  	 DBMS_LOB.WRITEAPPEND (p_clob, 7, '<rubro>');
  7  	 DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r1.nombre), r1.nombre);
  8  	 FOR r2 IN (SELECT tipo_product, nombre FROM tipo_product WHERE Rubro = r1.rubro) LOOP
  9  	   DBMS_LOB.WRITEAPPEND (p_clob, 14, '<tipo_product>');
 10  	   DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r2.nombre), r2.nombre);
 11  	   FOR r3 IN (SELECT marca FROM gc_product WHERE Tipo_Product= r2.Tipo_Product) LOOP
 12  	     DBMS_LOB.WRITEAPPEND (p_clob, 9, '<product>');
 13  	     DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r3.marca), r3.marca);
 14  	     DBMS_LOB.WRITEAPPEND (p_clob, 10, '</product>');
 15  	   end loop;
 16  	   DBMS_LOB.WRITEAPPEND (p_clob, 15, '</tipo_product>');
 17  	 end loop;
 18  	 DBMS_LOB.WRITEAPPEND (p_clob, 8, '</rubro>');
 19    end loop;
 20  end product_text_concat;
 21  /

Procedure created.

APP_XX@orcl_11g> SHOW ERRORS
No errors.
APP_XX@orcl_11g> BEGIN
  2    CTX_DDL.CREATE_PREFERENCE ('test_datastore', 'USER_DATASTORE');
  3    CTX_DDL.SET_ATTRIBUTE ('test_datastore', 'PROCEDURE', 'app_xx.product_text_concat');
  4    CTX_DDL.SET_ATTRIBUTE ('test_datastore', 'OUTPUT_TYPE', 'CLOB');
  5  END;
  6  /

PL/SQL procedure successfully completed.


-- insert data and commit:
APP_XX@orcl_11g> INSERT INTO rubro VALUES (1, 'RUBRO1')
  2  /

1 row created.

APP_XX@orcl_11g> INSERT INTO tipo_product VALUES (10, 'TIPO10', 1)
  2  /

1 row created.

APP_XX@orcl_11g> INSERT INTO gc_product VALUES (100, 'MARCA1', 10)
  2  /

1 row created.

APP_XX@orcl_11g> COMMIT
  2  /

Commit complete.


-- create index:
APP_XX@orcl_11g> CREATE INDEX product_full ON Rubro (Nombre)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  PARAMETERS
  4    ('DATASTORE	test_datastore
  5  	 SECTION GROUP	CTXSYS.AUTO_SECTION_GROUP
  6  	 sync (every "SYSDATE+5/1440" MEMORY 64M PARALLEL 2)')
  7  /

Index created.


-- results:
APP_XX@orcl_11g> SELECT token_text FROM dr$product_full$i
  2  /

TOKEN_TEXT
----------------------------------------------------------------
MARCA1
PRODUCT
RUBRO
RUBRO1
TIPO10
TIPO_PRODUCT

6 rows selected.

APP_XX@orcl_11g> SELECT rubro.nombre, tipo_product.nombre, gc_product.marca
  2  FROM   rubro, tipo_product, gc_product
  3  WHERE  CONTAINS (rubro.nombre, 'marca1') > 0
  4  AND    rubro.rubro = tipo_product.rubro
  5  AND    gc_product.tipo_product = tipo_product.tipo_product
  6  /

NOMBRE
--------------------------------------------------------------------------------
NOMBRE
--------------------------------------------------------------------------------
MARCA
--------------------------------------------------------------------------------
RUBRO1
TIPO10
MARCA1


APP_XX@orcl_11g> 

Re: Oracle text concatenated datastore [message #306542 is a reply to message #293320] Fri, 14 March 2008 11:11 Go to previous messageGo to next message
redonisc
Messages: 20
Registered: March 2008
Location: Guatemala, C.A.
Junior Member
Thanks Barbara, this is working now, as you said the problem was on the procedure. The index is created.

One last question, previously i had a query sequence, something like this:
SELECT *
  FROM gc_product b, tipo_producto c, rubro d
 WHERE c.tipo_product = b.tipo_product
   AND d.rubro = c.rubro
   AND (   (contains
               (b.marca,
                '<query><textquery> MICROSOFT WORD<progression><seq><rewrite>transform((TOKENS, "?{", "}", " AND "))</rewrite>/seq><seq><rewrite>transform((TOKENS, "{", "}", " AND "))</rewrite><seq><rewrite>transform((TOKENS, "${", "}", "ACCUM"))</rewrite></seq><seq><rewrite>transform((TOKENS, "", "%", "ACCUM"))</rewrite></seq><seq><rewrite>transform((TOKENS, "{", "}%", "NEAR"))</rewrite></seq></progression></textquery></query>',
                1
               ) <> 0
           )
        OR (contains
               (c.nombre,
                '<query><textquery> MICROSOFT WORD<progression><seq><rewrite>transform((TOKENS, "?{", "}", " AND "))</rewrite>/seq><seq><rewrite>transform((TOKENS, "{", "}", " AND "))</rewrite><seq><rewrite>transform((TOKENS, "${", "}", "ACCUM"))</rewrite></seq><seq><rewrite>transform((TOKENS, "", "%", "ACCUM"))</rewrite></seq><seq><rewrite>transform((TOKENS, "{", "}%", "NEAR"))</rewrite></seq></progression></textquery></query>',
                2
               ) <> 0
           )
        OR (contains
               (d.nombre,
                '<query><textquery> MICROSOFT WORD<progression><seq><rewrite>transform((TOKENS, "?{", "}", " AND "))</rewrite>/seq><seq><rewrite>transform((TOKENS, "{", "}", " AND "))</rewrite><seq><rewrite>transform((TOKENS, "${", "}", "ACCUM"))</rewrite></seq><seq><rewrite>transform((TOKENS, "", "%", "ACCUM"))</rewrite></seq><seq><rewrite>transform((TOKENS, "{", "}%", "NEAR"))</rewrite></seq></progression></textquery></query>',
                3
               ) <> 0
           )
       )


Now i guess i've to query a xml... the previous query fails querying only the rubro.nombre it doesn't query the 3 levels, your example makes a query using WITHIN it gets a query very specific about querying a group.

But i was using this seq with very good results, can i use this query seq within the 3 levels? sorry but theres no information on this.

Again thanks Barbara.

[Updated on: Fri, 14 March 2008 11:19]

Report message to a moderator

Re: Oracle text concatenated datastore [message #306576 is a reply to message #306542] Fri, 14 March 2008 14:36 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8024
Registered: November 2002
Location: California, USA
Senior Member
If you did things properly, when you query on whatever column you created the index on, it will search all columns that are concatenated in the procedure. In my previous example, I searched rubro.nombre (the column the index was created on) for the value "marca1" and it found it in gc_product.marca (one of the columns concatenated by the procedure). You need to test to confirm whether it is working for you or not. If not, then you need to post a complete run of exactly what you did, including tables, test data, procedure, preferences, index, and search query.
Re: Oracle text concatenated datastore [message #306577 is a reply to message #306542] Fri, 14 March 2008 14:39 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8024
Registered: November 2002
Location: California, USA
Senior Member
Additional information:

If a query like the one that I posted works for you, but the progressive relaxation on the indexed column does not, then the problem may be due to something else. There was a bug in early 10g where if no rows matched the first criteria, none of the rows for the other criteria were returned. If this is the case, then check metalink for a patch.
Re: Oracle text concatenated datastore [message #306585 is a reply to message #306577] Fri, 14 March 2008 15:22 Go to previous messageGo to next message
redonisc
Messages: 20
Registered: March 2008
Location: Guatemala, C.A.
Junior Member
Ok i've applied a patch for the problem with <>0 and now using >0 in the criteria seems to improve the match, but now i think the problem here is with the relaxation, seems that there are some columns that have a some exact word match and others don't but the score seems to be higher in others that have less match...

For example:
SELECT score(1), b.id, d.nombre rubro, c.nombre tipo_product, b.marca   
  FROM gc_producto b, tipo_product c, rubro d
 WHERE 
    c.tipo_product = b.tipo_product
   AND d.rubro = c.rubro
   AND contains
          (d.nombre,
           '<query>
            <textquery>conpaca
                <progression>
                    <seq><rewrite>transform((TOKENS, "?{", "}", " AND "))</rewrite></seq>
                    <seq><rewrite>transform((TOKENS, "{", "}", " AND "))</rewrite></seq>
                    <seq><rewrite>transform((TOKENS, "", "%", "ACCUM"))</rewrite></seq>
                    <seq><rewrite>transform((TOKENS, "{", "}%", "NEAR"))</rewrite></seq>
                </progression>
            </textquery>
            </query>',
           1
          )> 0
order by score(1) desc


I get:

SCORE(1)|ID|RUBRO|TIPO_PRODUCTO|MARCA
==========================================
100|8818|PAPEL BOND|Papel Bond Blanco 75 gramos|FIRST  RESMA, CONPACA
100|8826|PAPEL BOND|Papel Bond Blanco 75 gramos, por mtr.2|APUNTA, VIVIAN
100|8931|PAPEL BOND|PAPEL BOND COLOR|MONDI / LIBRAS, MONDI
88|10855|CARTONES, CARTONCILLO Y CARTULINAS|Cartón Chip de 30" X 40", calibre 20|PLEK, PAINSA
88|11029|CARTONES, CARTONCILLO Y CARTULINAS|CARTULINA OPALINA BLANCA|FIRST  PLIEGO, CONPACA


As you see the row 1 and the last row are more accurate than others with higher score, under my own perspective a exact match on the text should weight more than a related word...

[Updated on: Fri, 14 March 2008 17:21]

Report message to a moderator

Re: Oracle text concatenated datastore [message #306600 is a reply to message #306585] Fri, 14 March 2008 20:45 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8024
Registered: November 2002
Location: California, USA
Senior Member
I think we got the relationships backwards in the original procedure. For the manner in which you want to search, we should have started with the gc_producto detail table, not the rubro table. Then we should have created the index on the gc_producto table, instead of the rubro table, and searched on the gc_producto.marca not rubro.nombre. Please see the demonstration below with revised procedure, index, and query, and modify yours accordingly. Also, notice that I switched the first and second sequences that you had in the wrong order. And, remember to gather current statistics.

SCOTT@orcl_11g> -- test data:
SCOTT@orcl_11g> SELECT b.id, d.nombre rubro, c.nombre tipo_product, b.marca
  2  FROM   gc_producto b, tipo_product c, rubro d
  3  WHERE  c.tipo_product = b.tipo_product
  4  AND    d.rubro = c.rubro
  5  /

        ID RUBRO                               TIPO_PRODUCT                             MARCA
---------- ----------------------------------- ---------------------------------------- -------------------------
      8818 PAPEL BOND                          Papel Bond Blanco 75 gramos              FIRST  RESMA, CONPACA
      8826 PAPEL BOND                          Papel Bond Blanco 75 gramos, por mtr.2   APUNTA, VIVIAN
      8931 PAPEL BOND                          PAPEL BOND COLOR                         MONDI / LIBRAS, MONDI
     10855 CARTONES, CARTONCILLO Y CARTULINAS  Cartón Chip de 30" X 40", calibre 20     PLEK, PAINSA
     11029 CARTONES, CARTONCILLO Y CARTULINAS  CARTULINA OPALINA BLANCA                 FIRST  PLIEGO, CONPACA

SCOTT@orcl_11g> -- revised procedure:
SCOTT@orcl_11g> CREATE OR REPLACE procedure product_text_concat
  2    (p_rowid IN ROWID, p_clob IN OUT CLOB)
  3  AS
  4  BEGIN
  5    FOR r1 IN (SELECT marca, tipo_product FROM gc_producto WHERE ROWID = p_rowid) LOOP
  6  	 DBMS_LOB.WRITEAPPEND (p_clob, 9, '<product>');
  7  	 DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r1.marca), r1.marca);
  8  	 DBMS_LOB.WRITEAPPEND (p_clob, 10, '</product>');
  9  	 FOR r2 IN (SELECT nombre, rubro FROM tipo_product WHERE tipo_product = r1.tipo_product) LOOP
 10  	   DBMS_LOB.WRITEAPPEND (p_clob, 14, '<tipo_product>');
 11  	   DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r2.nombre), r2.nombre);
 12  	   DBMS_LOB.WRITEAPPEND (p_clob, 15, '</tipo_product>');
 13  	   FOR r3 in (SELECT nombre from rubro WHERE rubro = r2.rubro ) loop
 14  	     DBMS_LOB.WRITEAPPEND (p_clob, 7, '<rubro>');
 15  	     DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r3.nombre), r3.nombre);
 16  	     DBMS_LOB.WRITEAPPEND (p_clob, 8, '</rubro>');
 17  	   END LOOP;
 18  	 END LOOP;
 19    END LOOP;
 20  END product_text_concat;
 21  /

Procedure created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> BEGIN
  2    CTX_DDL.CREATE_PREFERENCE ('test_datastore', 'USER_DATASTORE');
  3    CTX_DDL.SET_ATTRIBUTE ('test_datastore', 'PROCEDURE', 'product_text_concat');
  4    CTX_DDL.SET_ATTRIBUTE ('test_datastore', 'OUTPUT_TYPE', 'CLOB');
  5  END;
  6  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> -- revised index:
SCOTT@orcl_11g> CREATE INDEX product_full ON gc_producto (marca)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  PARAMETERS
  4    ('DATASTORE	test_datastore
  5  	 SECTION GROUP	CTXSYS.AUTO_SECTION_GROUP
  6  	 sync (every "SYSDATE+5/1440" MEMORY 64M PARALLEL 2)')
  7  /

Index created.

SCOTT@orcl_11g> -- gather statistics:
SCOTT@orcl_11g> EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'RUBRO')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'TIPO_PRODUCT')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'GC_PRODUCTO')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> -- revised query:
SCOTT@orcl_11g> COLUMN rubro	    FORMAT A35
SCOTT@orcl_11g> COLUMN tipo_product FORMAT A40
SCOTT@orcl_11g> COLUMN marca	    FORMAT A25
SCOTT@orcl_11g> SELECT score(1), b.id, d.nombre rubro, c.nombre tipo_product, b.marca
  2  FROM   gc_producto b, tipo_product c, rubro d
  3  WHERE  c.tipo_product = b.tipo_product
  4  AND    d.rubro = c.rubro
  5  AND    contains
  6  	       (b.marca,
  7  		'<query>
  8  		 <textquery>conpaca
  9  		     <progression>
 10  			 <seq><rewrite>transform((TOKENS, "{", "}", " AND "))</rewrite></seq>
 11  			 <seq><rewrite>transform((TOKENS, "?{", "}", " AND "))</rewrite></seq>
 12  			 <seq><rewrite>transform((TOKENS, "", "%", "AND"))</rewrite></seq>
 13  			 <seq><rewrite>transform((TOKENS, "{", "}%", "NEAR"))</rewrite></seq>
 14  		     </progression>
 15  		 </textquery>
 16  		 </query>',
 17  		1
 18  	       ) > 0
 19  order by score(1) desc
 20  /

  SCORE(1)         ID RUBRO                               TIPO_PRODUCT                             MARCA
---------- ---------- ----------------------------------- ---------------------------------------- -------------------------
        76      11029 CARTONES, CARTONCILLO Y CARTULINAS  CARTULINA OPALINA BLANCA                 FIRST  PLIEGO, CONPACA
        76       8818 PAPEL BOND                          Papel Bond Blanco 75 gramos              FIRST  RESMA, CONPACA

SCOTT@orcl_11g>

Re: Oracle text concatenated datastore [message #307045 is a reply to message #306600] Mon, 17 March 2008 12:51 Go to previous messageGo to next message
redonisc
Messages: 20
Registered: March 2008
Location: Guatemala, C.A.
Junior Member
Now its all good!!! Laughing Thanks a lot Barbara... can you tell us, some references about how the xml is working with this scenario with oracle text services?
Re: Oracle text concatenated datastore [message #307058 is a reply to message #307045] Mon, 17 March 2008 14:03 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8024
Registered: November 2002
Location: California, USA
Senior Member
I have displayed the output of the procedure below for each rowid in the gc_product table. When the index is created using a user_datastore that uses such a procedure, it is the same as if it were indexing a one-column table, with these values in that one column. This enable it to use auto_section_group based on the tags.

SCOTT@orcl_11g> DECLARE
  2    v_clob CLOB;
  3  BEGIN
  4    FOR r IN (SELECT ROWID FROM gc_producto) LOOP
  5  	 DBMS_LOB.CREATETEMPORARY (v_clob, TRUE);
  6  	 product_text_concat (r.ROWID, v_clob);
  7  	 DBMS_OUTPUT.PUT_LINE (REPLACE (v_clob, '<', CHR (10) || '<'));
  8  	 DBMS_OUTPUT.PUT_LINE ('--------------------------------------------------------');
  9  	 DBMS_LOB.FREETEMPORARY (v_clob);
 10    END LOOP;
 11  END;
 12  /

<product>FIRST  RESMA, CONPACA
</product>
<tipo_product>Papel Bond Blanco 75 gramos
</tipo_product>
<rubro>PAPEL BOND
</rubro>
--------------------------------------------------------

<product>APUNTA, VIVIAN
</product>
<tipo_product>Papel Bond Blanco 75 gramos, por mtr.2
</tipo_product>
<rubro>PAPEL BOND
</rubro>
--------------------------------------------------------

<product>MONDI / LIBRAS, MONDI
</product>
<tipo_product>PAPEL BOND COLOR
</tipo_product>
<rubro>PAPEL BOND
</rubro>
--------------------------------------------------------

<product>PLEK, PAINSA
</product>
<tipo_product>Cartón Chip de 30" X 40", calibre 20
</tipo_product>
<rubro>CARTONES, CARTONCILLO Y
CARTULINAS
</rubro>
--------------------------------------------------------

<product>FIRST  PLIEGO, CONPACA
</product>
<tipo_product>CARTULINA OPALINA BLANCA
</tipo_product>
<rubro>CARTONES, CARTONCILLO Y CARTULINAS
</rubro>
--------------------------------------------------------

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> 

[Updated on: Mon, 17 March 2008 14:06]

Report message to a moderator

Re: Oracle text concatenated datastore [message #307060 is a reply to message #307045] Mon, 17 March 2008 14:23 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8024
Registered: November 2002
Location: California, USA
Senior Member
You can find information in the section of the online documentation on the user_datastore:

http://download.oracle.com/docs/cd/B28359_01/text.111/b28304/cdatadic.htm#i1006810

You can also find some nice examples and explanations by Tom Kyte who uses this method for searching of his website:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5533095920114
Re: Oracle text concatenated datastore [message #307110 is a reply to message #307060] Mon, 17 March 2008 18:57 Go to previous messageGo to next message
redonisc
Messages: 20
Registered: March 2008
Location: Guatemala, C.A.
Junior Member
I've found a few glitches on this really good solution, data:
insert into rubro values(9,'CERAS');
insert into rubro values(10,'CEPILLOS Y ESCOBAS');
insert into rubro values(11,'TOALLA Y MECHA PARA TRAPEAR');

insert into tipo_product values(9,1,'CEPILLO DE CERDA SEMI-DURO        PARA LAVAR PISOS, DEBE COTIZ');
insert into tipo_product values(9,2,'ESCOBETA   DE   CERDA   PARA   SACUDIR,    DEBE   COTIZARSE   POR   UNIDAD.');
insert into tipo_product values(9,3,'CEPILLO   DE   CERDA   DOBLE    PARA   LIMPIAR   SANITARIOS    TIPO    GUSANO, DEBE   COTIZARSE   PO');
insert into tipo_product values(9,4,'CEPILLO   DE   CERDA   NATURAL   PARA   LAVADO   INSTRUMENTAL   MÉDICO QUIRÚRGICO   CON   ASA   DE  ');
insert into tipo_product values(9,5,'CEPILLO    DE   CERDA   DOBLE    SUAVE    PARA     BARRER,      DEBE   COTIZARSE POR   UNIDAD.');
insert into tipo_product values(10,1,'BASURERO  MEDIANO  DE PLÁSTICO,   COLOR  NEGRO,    COTIZAR  X   UNIDAD.');
insert into tipo_product values(10,2,'CUBETA  PLÁSTICA DE COLORES,  CAPACIDAD  DE 10  LITROS, COTIZAR X UNID.  ');
insert into tipo_product values(10,2,'BASURERO  MEDIANO  DE PLÁSTICO,   COLOR  NEGRO,    COTIZAR  X   UNIDAD.');
insert into tipo_product values(11,1,'ESCOPA TIPO CEPILLO, DE CERDAS SUAVES, CUALQUIER COLOR, DEBE COTIZARSE POR UNIDAD');

--product
insert into gc_product values(9,1,6777,'POPULAR, ESCOCESA, S.A');
insert into gc_product values(9,1,6769,'ESCOCESA, ESCOCESA');
insert into gc_product values(9,2,6963,'ETERNA, GOLDEN PLASTIC, S.A');
insert into gc_product values(9,3,6696,'R & R, R & R');
insert into gc_product values(9,3,6750,'MARIPOSA, DISTRIBUIDORA SAN JUAN');
insert into gc_product values(10,1,6688,'INDUPLASTIC, INDUPLASTIC, S.A');
insert into gc_product values(10,1,6661,'INDUPLASTIC, INDUPLASTIC');
insert into gc_product values(11,1,7978,'DOÑA KUKI, ESCOPLAST, S.A');


When i have this search using this keyword: cepillo cerda, it returns good results but in this condition in spanish the plural of cerda is cerdas, in this case the search using:

SELECT score(1), b.id, d.nombre rubro, c.nombre tipo_product, b.marca   
  FROM gc_product b, tipo_product c, rubro d
 WHERE 
    c.tipo_product = b.tipo_product
   AND d.rubro = c.rubro
   AND contains
          (B.marca,
           '<query>
            <textquery>cepillo cerda           
                <progression>
                   <seq><rewrite>transform((TOKENS, "{", "}", " "))</rewrite></seq>
                   <seq><rewrite>transform((TOKENS, "%", "", " "))</rewrite>/seq>
                   <seq><rewrite>transform((TOKENS, "?{", "}", " "))</rewrite>/seq>
                   <seq><rewrite>transform((TOKENS, "{", "}", "OR"))</rewrite></seq>
                   <seq><rewrite>transform((TOKENS, "?{", "}", "OR"))</rewrite></seq>
                </progression>
            </textquery>
            </query>',
           1
          )> 0
order by score(1) desc


The result is:
SCORE(1)|ID|RUBRO|TIPO_PRODUCTO|MARCA
=============================================================================
4|6777|CERAS|CEPILLO      DE      CERDA       SEMI-DURO        PARA      LAVAR       PISOS,          DEBE   COTIZ|POPULAR, ESCOCESA, S.A
4|6769|CERAS|CEPILLO      DE      CERDA       SEMI-DURO        PARA      LAVAR       PISOS,          DEBE   COTIZ|ESCOCESA, ESCOCESA
4|6750|CERAS|CEPILLO    DE     CERDA     PARA     LIMPIAR    SANITARIOS    TIPO      GUSANO, DEBE    COTIZARSE   |MARIPOSA, DISTRIBUIDORA SAN JUAN
4|6726|CERAS|CEPILLO   DE   CERDA   NATURAL   PARA   LAVADO   INSTRUMENTAL   MÉDICO QUIRÚRGICO   CON   ASA   DE  |R & R, R & R
4|6734|CERAS|CEPILLO    DE    CERDA    PARA    LIMPIAR    CIELOS    RASOS    PALO    DE    4 METROS,     DEBE   C|MARIPOSA, DISTRIBUIDORA SAN JUAN
4|6742|CERAS|CEPILLO    DE     CERDA     PARA     LIMPIAR    SANITARIOS    TIPO      GUSANO, DEBE    COTIZARSE   |ESCOCESA, ESCOCESA
4|6718|CERAS|CEPILLO    DE   CERDA   DOBLE    SUAVE    PARA     BARRER,      DEBE   COTIZARSE POR   UNIDAD.|DOÑA KUKI, ESCOPLAST, S.A
3|6688|CEPILLOS Y ESCOBAS|BASURERO  MEDIANO  DE PLÁSTICO,   COLOR  NEGRO,    COTIZAR  X   UNIDAD.|INDUPLASTIC, INDUPLASTIC, S.A
3|6653|CEPILLOS Y ESCOBAS|CUBETA  PLÁSTICA DE COLORES,  CAPACIDAD  DE 10  LITROS, COTIZAR X UNID.  |METALOPLASTICA, METALOPLASTICA, S.A
3|6661|CEPILLOS Y ESCOBAS|BASURERO  MEDIANO  DE PLÁSTICO,   COLOR  NEGRO,    COTIZAR  X   UNIDAD.|INDUPLASTIC, INDUPLASTIC
3|7978|TOALLA Y MECHA PARA TRAPEAR|ESCOPA TIPO CEPILLO, DE CERDAS SUAVES, CUALQUIER COLOR, DEBE COTIZARSE POR UNIDAD|DOÑA KUKI, ESCOPLAST, S.A
2|6807|CERAS|CEPILLO   DE    FIBRA    PARA    MÁQUINA    LUSTRADORA,     DEBE    COTIZARSE POR    UNIDAD.       (|R & R, R & R


Now do you see my point? why the id=7978 have a score of 3 if the search keyword singular "cerda" is included in "cerdas" the plural? is a spanish language bug this by the language rule of singular-plural?

[Updated on: Mon, 17 March 2008 18:58]

Report message to a moderator

Re: Oracle text concatenated datastore [message #307114 is a reply to message #307110] Mon, 17 March 2008 19:18 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8024
Registered: November 2002
Location: California, USA
Senior Member
Your insert statements are invalid. You have values for the tipo_product table that violate the primary key. Please provide a valid set of insert statements, preferably including column names, like:

insert into tipo_product (RUBRO, TIPO_PRODUCT, NOMBRE)
values(9,1,'CEPILLO DE CERDA SEMI-DURO PARA LAVAR PISOS, DEBE COTIZ');

so that I can tell what goes where.

Also, some of your gc_product.id values are in your results, but not in your insert statements and vice versa.

[Updated on: Mon, 17 March 2008 19:39]

Report message to a moderator

Re: Oracle text concatenated datastore [message #307128 is a reply to message #307110] Mon, 17 March 2008 22:26 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8024
Registered: November 2002
Location: California, USA
Senior Member
After revising your insert statements to match your query, what I found was:

Your 2nd and 3rd sequences were missing a < before the /seq, so those sequences were either being ignored or producing incorrect results from the whole query. Even after correction, only your 4th and 5th sequences are returning results. I don't know if this is what you expected, or if you intended something different with your 2nd and 3rd sequences.

If you run the queries with only one sequence at a time, you see what each sequence returns. The 4th sequence returns id's 6734, 6750, 6769, 6718, 6726, 6742, 6777, 7978, and 6807, because they each have cepillo or cerda. The 5th sequence returns those 9 and also returns 6661, 6688, and 6653, because they each have cepillos. So, the full query should be expected to list those three last, which it does in my demonstration below, using the corrected query. My scores will be slightly different due to slight differences in systems, data, statistics, and so on. You may get different scores and different order, but you should get the first 9 followed by the last 3.

SCOTT@orcl_11g> -- revised create statements to match your insert statements:
SCOTT@orcl_11g> CREATE TABLE RUBRO
  2  (
  3  RUBRO NUMBER(10) NOT NULL,
  4  NOMBRE VARCHAR2(100 BYTE) NOT NULL,
  5  CONSTRAINT rubro_pk PRIMARY KEY (rubro))
  6  /

Table created.

SCOTT@orcl_11g> CREATE TABLE TIPO_PRODUCT
  2  (
  3  RUBRO NUMBER(10) NOT NULL,
  4  TIPO_PRODUCT NUMBER(10) NOT NULL,
  5  NOMBRE VARCHAR2(100 BYTE) NOT NULL,
  6  CONSTRAINT tipo_product_pk PRIMARY KEY (tipo_product))
  7  /

Table created.

SCOTT@orcl_11g> ALTER TABLE TIPO_PRODUCT ADD (
  2  CONSTRAINT FK_GC_TIPO_PRODUCT146s
  3  FOREIGN KEY (RUBRO)
  4  REFERENCES RUBRO (RUBRO))
  5  /

Table altered.

SCOTT@orcl_11g> CREATE TABLE gc_product
  2  (
  3  RUBRO NUMBER,
  4  TIPO_PRODUCT NUMBER(10) NOT NULL,
  5  ID NUMBER(10) NOT NULL,
  6  MARCA VARCHAR2(100 BYTE),
  7  CONSTRAINT product_pk PRIMARY KEY (id))
  8  /

Table created.

SCOTT@orcl_11g> ALTER TABLE gc_product ADD (
  2  CONSTRAINT FK_PRODUCT148
  3  FOREIGN KEY (TIPO_PRODUCT)
  4  REFERENCES TIPO_PRODUCT (TIPO_PRODUCT))
  5  /

Table altered.

SCOTT@orcl_11g> -- corrected insert statements to match the result set that you provided:
SCOTT@orcl_11g> SET DEFINE OFF SCAN OFF
SCOTT@orcl_11g> begin
  2    insert into rubro values(9,'CERAS');
  3    insert into rubro values(10,'CEPILLOS Y ESCOBAS');
  4    insert into rubro values(11,'TOALLA Y MECHA PARA TRAPEAR');
  5  
  6    insert into tipo_product values(9,1,'CEPILLO DE CERDA SEMI-DURO PARA LAVAR PISOS, DEBE COTIZ');
  7    insert into tipo_product values(9,2,'ESCOBETA   DE   CERDA   PARA   SACUDIR,    DEBE   COTIZARSE   POR	UNIDAD.');
  8    insert into tipo_product values(9,3,'CEPILLO DE CERDA PARA LIMPIAR SANITARIOS TIPO GUSANO, DEBE COTIZARSE PO');
  9    insert into tipo_product values(9,4,'CEPILLO DE CERDA NATURAL PARA LAVADO INSTRUMENTAL MÉDICO QUIRÚRGICO CON ASA DE ');
 10    insert into tipo_product values(9,5,'CEPILLO DE CERDA DOBLE SUAVE PARA BARRER, DEBE COTIZARSE POR UNIDAD.');
 11    insert into tipo_product values(10,6,'BASURERO MEDIANO DE PLÁSTICO, COLOR NEGRO, COTIZAR X UNIDAD.');
 12    insert into tipo_product values(10,7,'CUBETA PLÁSTICA DE COLORES, CAPACIDAD DE 10 LITROS, COTIZAR X UNID. ');
 13    insert into tipo_product values(11,9,'ESCOPA TIPO CEPILLO, DE CERDAS SUAVES, CUALQUIER COLOR, DEBE COTIZARSE POR UNIDAD');
 14    insert into tipo_product values(9,10,'CEPILLO DE FIBRA PARA MAQUINA LUSTRADORA, DEBE COTIZARSE POR UNIDAD.');
 15    insert into tipo_product values(9,11,'CEPILLO DE CERDA PARA LIMPIAR CIELOS RASOS PALO DE 4 METROS, DEBE C');
 16  
 17    insert into gc_product values(9,1,6777,'POPULAR, ESCOCESA, S.A');
 18    insert into gc_product values(9,1,6769,'ESCOCESA, ESCOCESA');
 19    insert into gc_product values(9,3,6750,'MARIPOSA, DISTRIBUIDORA SAN JUAN');
 20    insert into gc_product values(9,4,6726,'R & R, R & R');
 21    insert into gc_product values(9,11,6734,'MARIPOSA, DISTRIBUIDORA SAN JUAN');
 22    insert into gc_product values(9,3,6742,'ESCOCESA, ESCOCESA');
 23    insert into gc_product values(9,5,6718,'DOÑA KUKI, ESCOPLAST, S.A');
 24    insert into gc_product values(10,6,6688,'INDUPLASTIC, INDUPLASTIC, S.A');
 25    insert into gc_product values(10,7,6653,'METALOPLASTICA, METALOPLASTICA, S.A');
 26    insert into gc_product values(10,6,6661,'INDUPLASTIC, INDUPLASTIC');
 27    insert into gc_product values(11,9,7978,'DOÑA KUKI, ESCOPLAST, S.A');
 28    insert into gc_product values(9,10,6807,'R & R, R & R');
 29  end;
 30  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> COMMIT
  2  /

Commit complete.

SCOTT@orcl_11g> -- test data from your revised insert statements:
SCOTT@orcl_11g> COLUMN rubro	    FORMAT A27 WORD_WRAPPED
SCOTT@orcl_11g> COLUMN tipo_product FORMAT A45 WORD_WRAPPED
SCOTT@orcl_11g> COLUMN marca	    FORMAT A35 WORD_WRAPPED
SCOTT@orcl_11g> SELECT b.id, d.nombre rubro, c.nombre tipo_product, b.marca
  2  FROM   gc_product b, tipo_product c, rubro d
  3  WHERE  c.tipo_product = b.tipo_product
  4  AND    d.rubro = c.rubro
  5  ORDER  BY DECODE -- for easy comparison to your result set
  6  	     (b.id,
  7  	      6777, 1, 6769, 2, 6750, 3, 6726, 4, 6734, 5, 6742, 6, 6718, 7,
  8  	      6688, 8, 6653, 9, 6661, 10, 7978, 11, 6807, 12, 13)
  9  /

        ID RUBRO                       TIPO_PRODUCT                                  MARCA
---------- --------------------------- --------------------------------------------- -----------------------------------
      6777 CERAS                       CEPILLO DE CERDA SEMI-DURO PARA LAVAR PISOS,  POPULAR, ESCOCESA, S.A
                                       DEBE COTIZ

      6769 CERAS                       CEPILLO DE CERDA SEMI-DURO PARA LAVAR PISOS,  ESCOCESA, ESCOCESA
                                       DEBE COTIZ

      6750 CERAS                       CEPILLO DE CERDA PARA LIMPIAR SANITARIOS TIPO MARIPOSA, DISTRIBUIDORA SAN JUAN
                                       GUSANO, DEBE COTIZARSE PO

      6726 CERAS                       CEPILLO DE CERDA NATURAL PARA LAVADO          R & R, R & R
                                       INSTRUMENTAL MÉDICO QUIRÚRGICO CON ASA DE

      6734 CERAS                       CEPILLO DE CERDA PARA LIMPIAR CIELOS RASOS    MARIPOSA, DISTRIBUIDORA SAN JUAN
                                       PALO DE 4 METROS, DEBE C

      6742 CERAS                       CEPILLO DE CERDA PARA LIMPIAR SANITARIOS TIPO ESCOCESA, ESCOCESA
                                       GUSANO, DEBE COTIZARSE PO

      6718 CERAS                       CEPILLO DE CERDA DOBLE SUAVE PARA BARRER,     DOÑA KUKI, ESCOPLAST, S.A
                                       DEBE COTIZARSE POR UNIDAD.

      6688 CEPILLOS Y ESCOBAS          BASURERO MEDIANO DE PLÁSTICO, COLOR NEGRO,    INDUPLASTIC, INDUPLASTIC, S.A
                                       COTIZAR X UNIDAD.

      6653 CEPILLOS Y ESCOBAS          CUBETA PLÁSTICA DE COLORES, CAPACIDAD DE 10   METALOPLASTICA, METALOPLASTICA, S.A
                                       LITROS, COTIZAR X UNID.

      6661 CEPILLOS Y ESCOBAS          BASURERO MEDIANO DE PLÁSTICO, COLOR NEGRO,    INDUPLASTIC, INDUPLASTIC
                                       COTIZAR X UNIDAD.

      7978 TOALLA Y MECHA PARA TRAPEAR ESCOPA TIPO CEPILLO, DE CERDAS SUAVES,        DOÑA KUKI, ESCOPLAST, S.A
                                       CUALQUIER COLOR, DEBE COTIZARSE POR UNIDAD

      6807 CERAS                       CEPILLO DE FIBRA PARA MAQUINA LUSTRADORA,     R & R, R & R
                                       DEBE COTIZARSE POR UNIDAD.


12 rows selected.

SCOTT@orcl_11g> -- procedure:
SCOTT@orcl_11g> CREATE OR REPLACE procedure product_text_concat
  2    (p_rowid IN ROWID, p_clob IN OUT CLOB)
  3  AS
  4  BEGIN
  5    FOR r1 IN (SELECT marca, tipo_product FROM gc_product WHERE ROWID = p_rowid) LOOP
  6  	 DBMS_LOB.WRITEAPPEND (p_clob, 9, '<product>');
  7  	 DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r1.marca), r1.marca);
  8  	 DBMS_LOB.WRITEAPPEND (p_clob, 10, '</product>');
  9  	 FOR r2 IN (SELECT nombre, rubro FROM tipo_product WHERE tipo_product = r1.tipo_product) LOOP
 10  	   DBMS_LOB.WRITEAPPEND (p_clob, 14, '<tipo_product>');
 11  	   DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r2.nombre), r2.nombre);
 12  	   DBMS_LOB.WRITEAPPEND (p_clob, 15, '</tipo_product>');
 13  	   FOR r3 in (SELECT nombre from rubro WHERE rubro = r2.rubro ) loop
 14  	     DBMS_LOB.WRITEAPPEND (p_clob, 7, '<rubro>');
 15  	     DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r3.nombre), r3.nombre);
 16  	     DBMS_LOB.WRITEAPPEND (p_clob, 8, '</rubro>');
 17  	   END LOOP;
 18  	 END LOOP;
 19    END LOOP;
 20  END product_text_concat;
 21  /

Procedure created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> -- datastore:
SCOTT@orcl_11g> BEGIN
  2    CTX_DDL.CREATE_PREFERENCE ('test_datastore', 'USER_DATASTORE');
  3    CTX_DDL.SET_ATTRIBUTE ('test_datastore', 'PROCEDURE', 'product_text_concat');
  4    CTX_DDL.SET_ATTRIBUTE ('test_datastore', 'OUTPUT_TYPE', 'CLOB');
  5  END;
  6  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> -- index:
SCOTT@orcl_11g> CREATE INDEX product_full ON gc_product (marca)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  PARAMETERS
  4    ('DATASTORE	test_datastore
  5  	 SECTION GROUP	CTXSYS.AUTO_SECTION_GROUP
  6  	 sync (every "SYSDATE+5/1440" MEMORY 64M PARALLEL 2)')
  7  /

Index created.

SCOTT@orcl_11g> -- gather statistics:
SCOTT@orcl_11g> EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'RUBRO')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'TIPO_PRODUCT')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'gc_product')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> -- your query with missing < before /seq added to 2nd and 3rd sequences:
SCOTT@orcl_11g> SELECT score(1), b.id, d.nombre rubro, c.nombre tipo_product, b.marca
  2    FROM gc_product b, tipo_product c, rubro d
  3   WHERE
  4  	 c.tipo_product = b.tipo_product
  5  	AND d.rubro = c.rubro
  6  	AND contains
  7  	       (B.marca,
  8  		'<query>
  9  		 <textquery>cepillo cerda
 10  		     <progression>
 11  			<seq><rewrite>transform((TOKENS,  "{", "}", " " ))</rewrite></seq>
 12  			<seq><rewrite>transform((TOKENS, "%" , "" , " " ))</rewrite></seq>
 13  			<seq><rewrite>transform((TOKENS, "?{", "}", " " ))</rewrite></seq>
 14  			<seq><rewrite>transform((TOKENS,  "{" ,"}", "OR"))</rewrite></seq>
 15  			<seq><rewrite>transform((TOKENS, "?{", "}", "OR"))</rewrite></seq>
 16  		     </progression>
 17  		 </textquery>
 18  		 </query>',
 19  		1
 20  	       )> 0
 21  order by score(1) desc
 22  /

  SCORE(1)         ID RUBRO                       TIPO_PRODUCT                                  MARCA
---------- ---------- --------------------------- --------------------------------------------- -----------------------------------
        21       6734 CERAS                       CEPILLO DE CERDA PARA LIMPIAR CIELOS RASOS    MARIPOSA, DISTRIBUIDORA SAN JUAN
                                                  PALO DE 4 METROS, DEBE C

        21       6807 CERAS                       CEPILLO DE FIBRA PARA MAQUINA LUSTRADORA,     R & R, R & R
                                                  DEBE COTIZARSE POR UNIDAD.

        21       6718 CERAS                       CEPILLO DE CERDA DOBLE SUAVE PARA BARRER,     DOÑA KUKI, ESCOPLAST, S.A
                                                  DEBE COTIZARSE POR UNIDAD.

        21       6726 CERAS                       CEPILLO DE CERDA NATURAL PARA LAVADO          R & R, R & R
                                                  INSTRUMENTAL MÉDICO QUIRÚRGICO CON ASA DE

        21       6750 CERAS                       CEPILLO DE CERDA PARA LIMPIAR SANITARIOS TIPO MARIPOSA, DISTRIBUIDORA SAN JUAN
                                                  GUSANO, DEBE COTIZARSE PO

        21       7978 TOALLA Y MECHA PARA TRAPEAR ESCOPA TIPO CEPILLO, DE CERDAS SUAVES,        DOÑA KUKI, ESCOPLAST, S.A
                                                  CUALQUIER COLOR, DEBE COTIZARSE POR UNIDAD

        21       6777 CERAS                       CEPILLO DE CERDA SEMI-DURO PARA LAVAR PISOS,  POPULAR, ESCOCESA, S.A
                                                  DEBE COTIZ

        21       6769 CERAS                       CEPILLO DE CERDA SEMI-DURO PARA LAVAR PISOS,  ESCOCESA, ESCOCESA
                                                  DEBE COTIZ

        21       6742 CERAS                       CEPILLO DE CERDA PARA LIMPIAR SANITARIOS TIPO ESCOCESA, ESCOCESA
                                                  GUSANO, DEBE COTIZARSE PO

         1       6661 CEPILLOS Y ESCOBAS          BASURERO MEDIANO DE PLÁSTICO, COLOR NEGRO,    INDUPLASTIC, INDUPLASTIC
                                                  COTIZAR X UNIDAD.

         1       6688 CEPILLOS Y ESCOBAS          BASURERO MEDIANO DE PLÁSTICO, COLOR NEGRO,    INDUPLASTIC, INDUPLASTIC, S.A
                                                  COTIZAR X UNIDAD.

         1       6653 CEPILLOS Y ESCOBAS          CUBETA PLÁSTICA DE COLORES, CAPACIDAD DE 10   METALOPLASTICA, METALOPLASTICA, S.A
                                                  LITROS, COTIZAR X UNID.


12 rows selected.

SCOTT@orcl_11g> -- one sequence at a time:
SCOTT@orcl_11g> SELECT score(1), b.id, d.nombre rubro, c.nombre tipo_product, b.marca
  2    FROM gc_product b, tipo_product c, rubro d
  3   WHERE
  4  	 c.tipo_product = b.tipo_product
  5  	AND d.rubro = c.rubro
  6  	AND contains
  7  	       (B.marca,
  8  		'<query>
  9  		 <textquery>cepillo cerda
 10  		     <progression>
 11  			<seq><rewrite>transform((TOKENS,  "{", "}", " " ))</rewrite></seq>
 12  		     </progression>
 13  		 </textquery>
 14  		 </query>',
 15  		1
 16  	       )> 0
 17  order by score(1) desc
 18  /

no rows selected

SCOTT@orcl_11g> SELECT score(1), b.id, d.nombre rubro, c.nombre tipo_product, b.marca
  2    FROM gc_product b, tipo_product c, rubro d
  3   WHERE
  4  	 c.tipo_product = b.tipo_product
  5  	AND d.rubro = c.rubro
  6  	AND contains
  7  	       (B.marca,
  8  		'<query>
  9  		 <textquery>cepillo cerda
 10  		     <progression>
 11  			<seq><rewrite>transform((TOKENS, "%" , "" , " " ))</rewrite></seq>
 12  		     </progression>
 13  		 </textquery>
 14  		 </query>',
 15  		1
 16  	       )> 0
 17  order by score(1) desc
 18  /

no rows selected

SCOTT@orcl_11g> SELECT score(1), b.id, d.nombre rubro, c.nombre tipo_product, b.marca
  2    FROM gc_product b, tipo_product c, rubro d
  3   WHERE
  4  	 c.tipo_product = b.tipo_product
  5  	AND d.rubro = c.rubro
  6  	AND contains
  7  	       (B.marca,
  8  		'<query>
  9  		 <textquery>cepillo cerda
 10  		     <progression>
 11  			<seq><rewrite>transform((TOKENS, "?{", "}", " " ))</rewrite></seq>
 12  		     </progression>
 13  		 </textquery>
 14  		 </query>',
 15  		1
 16  	       )> 0
 17  order by score(1) desc
 18  /

no rows selected

SCOTT@orcl_11g> SELECT score(1), b.id, d.nombre rubro, c.nombre tipo_product, b.marca
  2    FROM gc_product b, tipo_product c, rubro d
  3   WHERE
  4  	 c.tipo_product = b.tipo_product
  5  	AND d.rubro = c.rubro
  6  	AND contains
  7  	       (B.marca,
  8  		'<query>
  9  		 <textquery>cepillo cerda
 10  		     <progression>
 11  			<seq><rewrite>transform((TOKENS,  "{" ,"}", "OR"))</rewrite></seq>
 12  		     </progression>
 13  		 </textquery>
 14  		 </query>',
 15  		1
 16  	       )> 0
 17  order by score(1) desc
 18  /

  SCORE(1)         ID RUBRO                       TIPO_PRODUCT                                  MARCA
---------- ---------- --------------------------- --------------------------------------------- -----------------------------------
         4       6734 CERAS                       CEPILLO DE CERDA PARA LIMPIAR CIELOS RASOS    MARIPOSA, DISTRIBUIDORA SAN JUAN
                                                  PALO DE 4 METROS, DEBE C

         4       6750 CERAS                       CEPILLO DE CERDA PARA LIMPIAR SANITARIOS TIPO MARIPOSA, DISTRIBUIDORA SAN JUAN
                                                  GUSANO, DEBE COTIZARSE PO

         4       6769 CERAS                       CEPILLO DE CERDA SEMI-DURO PARA LAVAR PISOS,  ESCOCESA, ESCOCESA
                                                  DEBE COTIZ

         4       6718 CERAS                       CEPILLO DE CERDA DOBLE SUAVE PARA BARRER,     DOÑA KUKI, ESCOPLAST, S.A
                                                  DEBE COTIZARSE POR UNIDAD.

         4       6726 CERAS                       CEPILLO DE CERDA NATURAL PARA LAVADO          R & R, R & R
                                                  INSTRUMENTAL MÉDICO QUIRÚRGICO CON ASA DE

         4       6742 CERAS                       CEPILLO DE CERDA PARA LIMPIAR SANITARIOS TIPO ESCOCESA, ESCOCESA
                                                  GUSANO, DEBE COTIZARSE PO

         4       6777 CERAS                       CEPILLO DE CERDA SEMI-DURO PARA LAVAR PISOS,  POPULAR, ESCOCESA, S.A
                                                  DEBE COTIZ

         3       7978 TOALLA Y MECHA PARA TRAPEAR ESCOPA TIPO CEPILLO, DE CERDAS SUAVES,        DOÑA KUKI, ESCOPLAST, S.A
                                                  CUALQUIER COLOR, DEBE COTIZARSE POR UNIDAD

         3       6807 CERAS                       CEPILLO DE FIBRA PARA MAQUINA LUSTRADORA,     R & R, R & R
                                                  DEBE COTIZARSE POR UNIDAD.


9 rows selected.

SCOTT@orcl_11g> SELECT score(1), b.id, d.nombre rubro, c.nombre tipo_product, b.marca
  2    FROM gc_product b, tipo_product c, rubro d
  3   WHERE
  4  	 c.tipo_product = b.tipo_product
  5  	AND d.rubro = c.rubro
  6  	AND contains
  7  	       (B.marca,
  8  		'<query>
  9  		 <textquery>cepillo cerda
 10  		     <progression>
 11  			<seq><rewrite>transform((TOKENS, "?{", "}", "OR"))</rewrite></seq>
 12  		     </progression>
 13  		 </textquery>
 14  		 </query>',
 15  		1
 16  	       )> 0
 17  order by score(1) desc
 18  /

  SCORE(1)         ID RUBRO                       TIPO_PRODUCT                                  MARCA
---------- ---------- --------------------------- --------------------------------------------- -----------------------------------
         6       6734 CERAS                       CEPILLO DE CERDA PARA LIMPIAR CIELOS RASOS    MARIPOSA, DISTRIBUIDORA SAN JUAN
                                                  PALO DE 4 METROS, DEBE C

         6       6742 CERAS                       CEPILLO DE CERDA PARA LIMPIAR SANITARIOS TIPO ESCOCESA, ESCOCESA
                                                  GUSANO, DEBE COTIZARSE PO

         6       6769 CERAS                       CEPILLO DE CERDA SEMI-DURO PARA LAVAR PISOS,  ESCOCESA, ESCOCESA
                                                  DEBE COTIZ

         6       6777 CERAS                       CEPILLO DE CERDA SEMI-DURO PARA LAVAR PISOS,  POPULAR, ESCOCESA, S.A
                                                  DEBE COTIZ

         6       6750 CERAS                       CEPILLO DE CERDA PARA LIMPIAR SANITARIOS TIPO MARIPOSA, DISTRIBUIDORA SAN JUAN
                                                  GUSANO, DEBE COTIZARSE PO

         6       6726 CERAS                       CEPILLO DE CERDA NATURAL PARA LAVADO          R & R, R & R
                                                  INSTRUMENTAL MÉDICO QUIRÚRGICO CON ASA DE

         6       6718 CERAS                       CEPILLO DE CERDA DOBLE SUAVE PARA BARRER,     DOÑA KUKI, ESCOPLAST, S.A
                                                  DEBE COTIZARSE POR UNIDAD.

         3       7978 TOALLA Y MECHA PARA TRAPEAR ESCOPA TIPO CEPILLO, DE CERDAS SUAVES,        DOÑA KUKI, ESCOPLAST, S.A
                                                  CUALQUIER COLOR, DEBE COTIZARSE POR UNIDAD

         3       6653 CEPILLOS Y ESCOBAS          CUBETA PLÁSTICA DE COLORES, CAPACIDAD DE 10   METALOPLASTICA, METALOPLASTICA, S.A
                                                  LITROS, COTIZAR X UNID.

         3       6688 CEPILLOS Y ESCOBAS          BASURERO MEDIANO DE PLÁSTICO, COLOR NEGRO,    INDUPLASTIC, INDUPLASTIC, S.A
                                                  COTIZAR X UNIDAD.

         3       6661 CEPILLOS Y ESCOBAS          BASURERO MEDIANO DE PLÁSTICO, COLOR NEGRO,    INDUPLASTIC, INDUPLASTIC
                                                  COTIZAR X UNIDAD.

         3       6807 CERAS                       CEPILLO DE FIBRA PARA MAQUINA LUSTRADORA,     R & R, R & R
                                                  DEBE COTIZARSE POR UNIDAD.


12 rows selected.

SCOTT@orcl_11g>



Re: Oracle text concatenated datastore [message #307425 is a reply to message #307128] Tue, 18 March 2008 18:48 Go to previous messageGo to next message
redonisc
Messages: 20
Registered: March 2008
Location: Guatemala, C.A.
Junior Member
Grrr my bad... now the results are getting a little better, now the records are getting selected, in my tests the only failure is when i have a keyword with accent(tilde) for example a product name like: jabon in spanish the correct spelling is jabón

The query using a common mistake on the accent(jabon):
SELECT score(1), b.id, d.nombre rubro, c.nombre tipo_product, b.marca
      FROM gc_product b, tipo_product c, rubro d
     WHERE
    	 c.tipo_product = b.tipo_product
    	AND d.rubro = c.rubro
    	AND contains
    	       (B.marca,
    		'<query> 
    		 <textquery>jabon
   		     <progression>
                   <seq><rewrite>transform((TOKENS, "%", "", "AND"))</rewrite></seq>
                   <seq><rewrite>transform((TOKENS, "${", "}", " "  ))</rewrite></seq>
                   <seq><rewrite>transform((TOKENS, "?{", "}", " "))</rewrite></seq>
                   <seq><rewrite>transform((TOKENS, "{", "}", "OR"))</rewrite></seq>
                   <seq><rewrite>transform((TOKENS, "?{", "}", "OR"))</rewrite></seq>
   		     </progression>
   		 </textquery>
   		 </query>',
   		1
   	       )> 0
   order by score(1) desc


The results are:
SCORE(1)|ID|RUBRO|TIPO_PRODUCTO|MARCA
=====================================================================================
86|4219|ANTÍDOTOS, Desinfectantes/Antisépticos|CLORHEXIDINA GLUCONATO 4% JABON LITRO COTIZAR POR LITRO|BACTEREX  AL 4%, PHARMABIOS
69|4243|ANTÍDOTOS, Desinfectantes/Antisépticos|JABON QUIRÚRGICO BACTERICIDA CON GLUCONATO DE CLORHEXIDINA + PANTENOL 4%  CON DISPENSADOR EN ESPUMA |EZ-SCRUB 4%,  BECTON DICKINSON
54|2305|DERMATOLOGÍA, ANTIPSORIÁSICOS|AZUFRE Y ÁCIDO SALICÍLICO JABÓN PASTILLA 90-130 G. COTIZAR POR GRAMO.|Sastid Jabón 100 g
52|7676|JABONES|PINOLEO   TIPO   "B"    (CON    GARANTÍA    DE   ANÁLISIS     DE     LABORATORIO  Y MUESTRA),    DEB|SUPER PINO, R & R
52|7692|LIMPIADOR VARIAS CLASES|FRASCO    CON    VÁLVULA    PARA    DISPENSAR    JABÓN     DE     TOCADOR     EN  GEL;   CAPACIDAD  |MAKILGAR, MAKILGAR, S.A
35|248|GASTROENTEROLOGÍA, INHIBIDORES DE BOMBA DE PROTONES|RABEPRAZOL TABLETA O CÁPSULA 20- 40MG. COTIZAR POR TABLETA.|PARIET 2O MG., EISAI CO. LTD / JAPON


The thing that is wrong there is when i've a product that starts with the well written name(jabón 3rd row), it didn't appear in the previous output, this is a constant error on all the descriptions with a accent in the first word of the sentence:
SCORE(1)|ID|RUBRO|TIPO_PRODUCTO|MARCA
==============================================================
87|5541|SISTEMA NERVIOSO, Anestésicos Generales por inhalación|DESFLURANO LIQUIDO VOLÁTIL, FRASCO DE 240ML. COTIZAR POR FRASCO.|SUPRANE LIQUIDO PARA INHALACIÓN,FRASCO DE VIDRIO COLOR AMBAR CON 240 ML
85|4758|SISTEMA NERVIOSO, ANESTÉSICOS GENERALES POR INHALACIÓN|SEVOFLURANO LIQUIDO VOLÁTIL, FRASCO DE 250ML. COTIZAR POR FRASCO.|SEVORANE FRASCO 250ML, ABBOTT LABORATORIES DE ARGENTINA, S.A.
85|7595|ESCOBAS Y TRAPEADORES|JABÓN  LIQUIDO   DE   TOCADOR   SIMPLE,         DEBE   COTIZARSE   POR  GALÓN|HAND LUX, R & R
85|7749|LIQUIDO LIMPIADOR|LIMPIADOR DE MANTA FINA LISTADA DE 70 X 70 CENTÍMETROS, DEBE COTIZARSE POR UNIDAD.|IRIS, TEXTILES MODERNOS, S.A
85|7714|LIQUIDO LIMPIADOR|LIMPIADOR DE MANTA DE 70 X 70 CENTÍMETROS, DEBE COTIZARSE POR UNIDAD.|IRIS, TEXTILES MODERNOS, S.A


All this running with the corrected query, i've searched with no luck, the only lead right now is related with the thesaurus...

[Updated on: Tue, 18 March 2008 18:59]

Report message to a moderator

Re: Oracle text concatenated datastore [message #307445 is a reply to message #307425] Tue, 18 March 2008 22:46 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8024
Registered: November 2002
Location: California, USA
Senior Member
By default, JABON AND JABÓN are stored as two separate distinct tokens and searching for one does not find the other. If you create a lexer and set the base_letter attribute to yes and use that in your index parameters then JABÓN is converted to JABON and searching for either will find both, which I think is what you want. This will cause all diacritical marks (umlauts, cedillas, acute accents, and so on) to be converted to their base letters. I have provided a brief demonstration below using a simple table.


-- test data:
SCOTT@orcl_11g> CREATE TABLE test_tab (test_col  VARCHAR2 (30))
  2  /

Table created.

SCOTT@orcl_11g> INSERT ALL
  2  INTO test_tab VALUES ('JABON')
  3  INTO test_tab VALUES ('JABÓN')
  4  SELECT * FROM DUAL
  5  /

2 rows created.


-- without base_letter parameter:
SCOTT@orcl_11g> CREATE INDEX test_idx ON test_tab (test_col) INDEXTYPE IS CTXSYS.CONTEXT
  2  /

Index created.

SCOTT@orcl_11g> SELECT token_text FROM dr$test_idx$i
  2  /

TOKEN_TEXT
----------------------------------------------------------------
JABON
JABÓN

SCOTT@orcl_11g> SELECT * FROM test_tab WHERE CONTAINS (test_col, 'JABON') > 0
  2  /

TEST_COL
------------------------------
JABON

SCOTT@orcl_11g> SELECT * FROM test_tab WHERE CONTAINS (test_col, 'JABÓN') > 0
  2  /

TEST_COL
------------------------------
JABÓN


-- with base_letter parameter:
SCOTT@orcl_11g> DROP INDEX test_idx
  2  /

Index dropped.

SCOTT@orcl_11g> BEGIN
  2    CTX_DDL.CREATE_PREFERENCE ('test_lex', 'BASIC_LEXER');
  3    CTX_DDL.SET_ATTRIBUTE ('test_lex', 'BASE_LETTER', 'YES');
  4  END;
  5  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> CREATE INDEX test_idx ON test_tab (test_col) INDEXTYPE IS CTXSYS.CONTEXT
  2  PARAMETERS ('LEXER test_lex')
  3  /

Index created.

SCOTT@orcl_11g> SELECT token_text FROM dr$test_idx$i
  2  /

TOKEN_TEXT
----------------------------------------------------------------
JABON

SCOTT@orcl_11g> SELECT * FROM test_tab WHERE CONTAINS (test_col, 'JABON') > 0
  2  /

TEST_COL
------------------------------
JABON
JABÓN

SCOTT@orcl_11g> SELECT * FROM test_tab WHERE CONTAINS (test_col, 'JABÓN') > 0
  2  /

TEST_COL
------------------------------
JABON
JABÓN

SCOTT@orcl_11g> 

Re: Oracle text concatenated datastore [message #437301 is a reply to message #305459] Sun, 03 January 2010 09:16 Go to previous messageGo to next message
rozora
Messages: 9
Registered: January 2010
Junior Member
i have similar case only in my case i have more than 10 related tables,
some of the master table rows have multiple accurances on one or more of the referenced tables, or none. and on each table i have mutiple columns to index

the structure is like this:
create table people(pseq number primary key,id varchar2(250), name varchar2(250),description varchar2(4000));
create table people_add (pseq number, address varchar2(4000),phone varchar2(250));
create table people_mail (pseq number, email varchar2(250),email_desc varchar2(4000));
ALTER TABLE TEXT.PEOPLE_ADD ADD (FOREIGN KEY (PSEQ) REFERENCES TEXT.PEOPLE (PSEQ) ON DELETE CASCADE);
ALTER TABLE TEXT.PEOPLE_MAIL ADD (FOREIGN KEY (PSEQ) REFERENCES TEXT.PEOPLE (PSEQ) ON DELETE CASCADE);


insert into people values (1,'111111','lulu','no information');
insert into people values (2,'222222','dan','bank account');
insert into people values (3,'333333','dana','');
insert into people values (4,'444444','kuku','information');
insert into people values (5,'111111','lala','basketball');
insert into people values (6,'111111','','missing name');
insert into people values (7,'111111','nono','something something');
insert into people_add values (1,'street','012-phone');
insert into people_add values (1,'second street','');
insert into people_add values (2,'first','');
insert into people_add values (3,'5th','1357');
insert into people_add values (5,'3rd','111');
insert into people_mail values (1,'mymail@somemail.com','office email');
insert into people_mail values (4,'mymail2@somemail.com','e-mail');
insert into people_mail values (5,'','at home');
commit;

i first thought using a materialized view but it wasn't operable because of massive data.

so i'm back to check the user_data_store solution but i wonder if on such distributed structure it won't be better to use separate indexes (one for each table)
Re: Oracle text concatenated datastore [message #437314 is a reply to message #437301] Sun, 03 January 2010 16:09 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8024
Registered: November 2002
Location: California, USA
Senior Member
The user_datastore with a procedure is the best method. Oracle Text tends to have problems with multiple indexes and multiple AND and/or OR conditions using multiple contains clauses. It tends to choose only one index and be very slow and sometimes just results in an error. Tom Kytes use the user_datastore with a stored procedure for his entire asktom.oracle.com site and the searches work efficiently.
Re: Oracle text concatenated datastore [message #437317 is a reply to message #437314] Sun, 03 January 2010 18:04 Go to previous messageGo to next message
rozora
Messages: 9
Registered: January 2010
Junior Member
i tried to implement the user datastore from the examples you gave and it seems that query results even on your examples are Cartesian ? i wish to select all the text fields from all the tables in one query.
i can't use additional join condition because it will filter out all records that don't have reference in all of the tables.
and using outer join will just take a lot of resources and time.
Re: Oracle text concatenated datastore [message #437405 is a reply to message #437317] Mon, 04 January 2010 05:15 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8024
Registered: November 2002
Location: California, USA
Senior Member
The following is an example of the type of thing I think you're looking for.

SCOTT@orcl_11g> -- test tables and data that you provided (with text schema references removed):
SCOTT@orcl_11g> create table people(pseq number primary key,id varchar2(250), name varchar2(250),description varchar2(4000));

Table created.

SCOTT@orcl_11g> create table people_add (pseq number, address varchar2(4000),phone varchar2(250));

Table created.

SCOTT@orcl_11g> create table people_mail (pseq number, email varchar2(250),email_desc varchar2(4000));

Table created.

SCOTT@orcl_11g> ALTER TABLE PEOPLE_ADD ADD (FOREIGN KEY (PSEQ) REFERENCES PEOPLE (PSEQ) ON DELETE CASCADE);

Table altered.

SCOTT@orcl_11g> ALTER TABLE PEOPLE_MAIL ADD (FOREIGN KEY (PSEQ) REFERENCES PEOPLE (PSEQ) ON DELETE CASCADE);

Table altered.

SCOTT@orcl_11g> BEGIN
  2    insert into people values (1,'111111','lulu','no information');
  3    insert into people values (2,'222222','dan','bank account');
  4    insert into people values (3,'333333','dana','');
  5    insert into people values (4,'444444','kuku','information');
  6    insert into people values (5,'111111','lala','basketball');
  7    insert into people values (6,'111111','','missing name');
  8    insert into people values (7,'111111','nono','something something');
  9    insert into people_add values (1,'street','012-phone');
 10    insert into people_add values (1,'second street','');
 11    insert into people_add values (2,'first','');
 12    insert into people_add values (3,'5th','1357');
 13    insert into people_add values (5,'3rd','111');
 14    insert into people_mail values (1,'mymail@somemail.com','office email');
 15    insert into people_mail values (4,'mymail2@somemail.com','e-mail');
 16    insert into people_mail values (5,'','at home');
 17  END;
 18  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> -- additional column:
SCOTT@orcl_11g> ALTER TABLE people ADD (all_text_fields VARCHAR2(1))
  2  /

Table altered.

SCOTT@orcl_11g> -- procedure:
SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE your_procedure
  2    (p_rowid IN ROWID,
  3  	p_clob	IN OUT CLOB)
  4  AS
  5  BEGIN
  6    FOR p IN
  7  	 (SELECT * FROM people WHERE ROWID = p_rowid)
  8    LOOP
  9  	 DBMS_LOB.WRITEAPPEND (p_clob, NVL (LENGTH (p.name), 0) + 1, p.name || CHR(10));
 10  	 DBMS_LOB.WRITEAPPEND (p_clob, NVL (LENGTH (p.description), 0) + 1, p.description || CHR(10));
 11  	 FOR a IN
 12  	   (SELECT * FROM people_add WHERE pseq = p.pseq)
 13  	 LOOP
 14  	   DBMS_LOB.WRITEAPPEND (p_clob, NVL (LENGTH (a.address), 0) + 1, a.address || CHR(10));
 15  	   DBMS_LOB.WRITEAPPEND (p_clob, NVL (LENGTH (a.phone), 0) + 1, a.phone || CHR(10));
 16  	 END LOOP;
 17  	 FOR m IN
 18  	   (SELECT * FROM people_mail WHERE pseq = p.pseq)
 19  	 LOOP
 20  	   DBMS_LOB.WRITEAPPEND (p_clob, NVL (LENGTH (m.email), 0) + 1, m.email || CHR(10));
 21  	   DBMS_LOB.WRITEAPPEND (p_clob, NVL (LENGTH (m.email_desc), 0) + 1, m.email_desc || CHR(10));
 22  	 END LOOP;
 23    END LOOP;
 24    WHILE INSTR (p_clob, CHR(10) || CHR(10)) > 0 LOOP
 25  	 p_clob := REPLACE (p_clob, CHR(10) || CHR(10), CHR(10));
 26    END LOOP;
 27  END your_procedure;
 28  /

Procedure created.

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

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> -- index:
SCOTT@orcl_11g> CREATE INDEX your_index
  2  ON people (all_text_fields)
  3  INDEXTYPE IS CTXSYS.CONTEXT
  4  PARAMETERS ('DATASTORE your_datastore')
  5  /

Index created.

SCOTT@orcl_11g> -- function that returns output from procedure:
SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION your_function
  2    (p_rowid IN ROWID)
  3    RETURN	   CLOB
  4  AS
  5    v_clob	   CLOB;
  6  BEGIN
  7    DBMS_LOB.CREATETEMPORARY (v_clob, TRUE);
  8    your_procedure (p_rowid, v_clob);
  9    RETURN v_clob;
 10  END your_function;
 11  /

Function created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> -- example queries:
SCOTT@orcl_11g> SELECT id || ' ' || your_function (ROWID) AS results FROM people
  2  WHERE  CONTAINS (all_text_fields, 'second') > 0
  3  /

RESULTS
--------------------------------------------------------------------------------
111111 lulu
no information
street
012-phone
second street
mymail@somemail.com
office email


SCOTT@orcl_11g> SELECT id || ' ' || your_function (ROWID) AS results FROM people
  2  WHERE  CONTAINS (all_text_fields, 'home') > 0
  3  /

RESULTS
--------------------------------------------------------------------------------
111111 lala
basketball
3rd
111
at home


SCOTT@orcl_11g> SELECT id || ' ' || your_function (ROWID) AS results FROM people
  2  WHERE  CONTAINS (all_text_fields, 'dan%') > 0
  3  /

RESULTS
--------------------------------------------------------------------------------
222222 dan
bank account
first

333333 dana
5th
1357


SCOTT@orcl_11g> 

Re: Oracle text concatenated datastore [message #437425 is a reply to message #437405] Mon, 04 January 2010 06:32 Go to previous messageGo to next message
rozora
Messages: 9
Registered: January 2010
Junior Member
Thanks Barbara, it is a good solution because it return only the matches but this way i won't be able to tell which column ha which value, the application needs to display the real values of every column for the record i found the text in.
i guess this can be solved if my procedure will generate xml with tags to identify the columns.
what i did was something like that:

--create the table as before including dummy char column on people to create index on and to toggle index sync when other tables updated using triggers

create table people(pseq number primary key,id varchar2(250), name varchar2(250),description varchar2(4000),search char(1));
create table people_add (pseq number, address varchar2(4000),phone varchar2(250));
create table people_mail (pseq number, email varchar2(250),email_desc varchar2(4000));
ALTER TABLE PEOPLE_ADD ADD (FOREIGN KEY (PSEQ) REFERENCES PEOPLE (PSEQ) ON DELETE CASCADE);
ALTER TABLE PEOPLE_MAIL ADD (FOREIGN KEY (PSEQ) REFERENCES PEOPLE (PSEQ) ON DELETE CASCADE);

--populate tables
insert into people values (1,'111111','lulu','no information');
insert into people values (2,'222222','dan','bank account');
insert into people values (3,'333333','dana','');
insert into people values (4,'444444','kuku','information');
insert into people values (5,'111111','lala','basketball');
insert into people values (6,'111111','','missing name');
insert into people values (7,'111111','nono','something something');
insert into people_add values (1,'street','012-phone');
insert into people_add values (1,'second street','');
insert into people_add values (2,'first','');
insert into people_add values (3,'5th','1357');
insert into people_add values (5,'3rd','111');
insert into people_mail values (1,'mymail@somemail.com','office email');
insert into people_mail values (4,'mymail2@somemail.com','e-mail');
insert into people_mail values (5,'','at home');
commit;

--create my datastore procedure
create or replace procedure people_full_search  (rid  in rowid,p_clob in out NOCOPY   clob)
is
begin
  FOR r1 IN (SELECT pseq,nvl(id,' ') id,nvl(name,' ') name,nvl(description,' ') description FROM people WHERE ROWID = rid) LOOP
    	 DBMS_LOB.WRITEAPPEND (p_clob, 4, '<id>');
    	 DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r1.id), r1.id);
    	 DBMS_LOB.WRITEAPPEND (p_clob, 6, '<name>');
         DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r1.name), r1.name);
         DBMS_LOB.WRITEAPPEND (p_clob, 7, '</name>');
         DBMS_LOB.WRITEAPPEND (p_clob, 13, '<description>');
         DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r1.description), r1.description);
         DBMS_LOB.WRITEAPPEND (p_clob, 14, '</description>');
    	 FOR r2 IN (SELECT nvl(address,' ') address, nvl(phone,' ') phone FROM people_add WHERE pseq = r1.pseq) LOOP
           	   DBMS_LOB.WRITEAPPEND (p_clob, 9, '<address>');
           	   DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r2.address), r2.address);
           	   DBMS_LOB.WRITEAPPEND (p_clob, 10, '</address>');
               DBMS_LOB.WRITEAPPEND (p_clob, 7, '<phone>');
           	   DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r2.phone), r2.phone);
           	   DBMS_LOB.WRITEAPPEND (p_clob, 8, '</phone>');
   	   	 END LOOP;
         FOR r3 IN (SELECT nvl(email,' ') email, nvl(email_desc,' ') email_desc FROM people_mail WHERE pseq = r1.pseq) LOOP
           	   DBMS_LOB.WRITEAPPEND (p_clob, 7, '<email>');
           	   DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r3.email), r3.email);
           	   DBMS_LOB.WRITEAPPEND (p_clob, 8, '</email>');
               DBMS_LOB.WRITEAPPEND (p_clob, 12, '<email_desc>');
           	   DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r3.email_desc), r3.email_desc);
           	   DBMS_LOB.WRITEAPPEND (p_clob, 13, '</email_desc>');
   	   	 END LOOP;
         DBMS_LOB.WRITEAPPEND (p_clob, 5, '</id>');
  END LOOP;
end people_full_search;
/

--create datastore
BEGIN
    CTX_DDL.CREATE_PREFERENCE ('people_datastore', 'USER_DATASTORE');
     CTX_DDL.SET_ATTRIBUTE ('people_datastore', 'PROCEDURE', 'people_full_search');
     CTX_DDL.SET_ATTRIBUTE ('people_datastore', 'OUTPUT_TYPE', 'CLOB');

   END;
   /

--create index
CREATE INDEX people_search ON people (search)
    INDEXTYPE IS CTXSYS.CONTEXT
    PARAMETERS
      ('DATASTORE	people_datastore
    	 SECTION GROUP	CTXSYS.AUTO_SECTION_GROUP');

--create the function as you recommended to retrieve the data
CREATE OR REPLACE FUNCTION my_function
    (p_rowid IN ROWID)
      RETURN       CLOB
    AS
      v_clob       CLOB;
    BEGIN
      DBMS_LOB.CREATETEMPORARY (v_clob, TRUE);
      people_full_search (p_rowid, v_clob);
      RETURN v_clob;
   END my_function;
   /

--then i query
SELECT id , my_function (ROWID) AS results,score(1) FROM people
 WHERE  CONTAINS (search, 'street',1) > 0

--and the result
ID	RESULTS	SCORE(1)

111111	<id>111111<name>lulu</name><description>no information</description><address>street</address><phone>012-phone</phone><address>second street</address><phone> </phone><email>mymail@somemail.com</email><email_desc>office email</email_desc></id>	9
555555	<id>555555<name>lala</name><description>basketball</description><address>3rd street</address><phone>111</phone><email> </email><email_desc>at home</email_desc></id>	5



i'm a little worried about this performance
plus is there a way to tell me which column, or which tag on this case contained the text i searched for? something more accurate than snippet?

thanks again
Re: Oracle text concatenated datastore [message #437508 is a reply to message #437425] Mon, 04 January 2010 16:37 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8024
Registered: November 2002
Location: California, USA
Senior Member
You can use ctx_doc.markup to return the whole concatenated value from the procedure with the found values highlighted, instead of just a snippet. However, to have something that actually says which column it came from you would have to do something like what I have demonstrated below. What is the necessity for identifying which column it came from?

SCOTT@orcl_11g> -- tables and data that you provided:
SCOTT@orcl_11g> create table people(pseq number primary key,id varchar2(250), name varchar2(250),description varchar2(4000));

Table created.

SCOTT@orcl_11g> create table people_add (pseq number, address varchar2(4000),phone varchar2(250));

Table created.

SCOTT@orcl_11g> create table people_mail (pseq number, email varchar2(250),email_desc varchar2(4000));

Table created.

SCOTT@orcl_11g> ALTER TABLE PEOPLE_ADD ADD (FOREIGN KEY (PSEQ) REFERENCES PEOPLE (PSEQ) ON DELETE CASCADE);

Table altered.

SCOTT@orcl_11g> ALTER TABLE PEOPLE_MAIL ADD (FOREIGN KEY (PSEQ) REFERENCES PEOPLE (PSEQ) ON DELETE CASCADE);

Table altered.

SCOTT@orcl_11g> BEGIN
  2    insert into people values (1,'111111','lulu','no information');
  3    insert into people values (2,'222222','dan','bank account');
  4    insert into people values (3,'333333','dana','');
  5    insert into people values (4,'444444','kuku','information');
  6    insert into people values (5,'111111','lala','basketball');
  7    insert into people values (6,'111111','','missing name');
  8    insert into people values (7,'111111','nono','something something');
  9    insert into people_add values (1,'street','012-phone');
 10    insert into people_add values (1,'second street','');
 11    insert into people_add values (2,'first','');
 12    insert into people_add values (3,'5th','1357');
 13    insert into people_add values (5,'3rd','111');
 14    insert into people_mail values (1,'mymail@somemail.com','office email');
 15    insert into people_mail values (4,'mymail2@somemail.com','e-mail');
 16    insert into people_mail values (5,'','at home');
 17  END;
 18  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> -- materialized view:
SCOTT@orcl_11g> CREATE MATERIALIZED VIEW your_view AS
  2  SELECT p.pseq, p.id, p.name AS data, 'NAME' AS from_column
  3  FROM   people p
  4  UNION ALL
  5  SELECT p.pseq, p.id, p.description AS data, 'DESCRIPTION' AS from_column
  6  FROM   people p
  7  UNION ALL
  8  SELECT p.pseq, p.id, a.address AS data, 'ADDRESS' AS from_column
  9  FROM   people p, people_add a
 10  WHERE  p.pseq = a.pseq
 11  UNION ALL
 12  SELECT p.pseq, p.id, a.phone AS data, 'PHONE' AS from_column
 13  FROM   people p, people_add a
 14  WHERE  p.pseq = a.pseq
 15  UNION ALL
 16  SELECT p.pseq, p.id, m.email AS data, 'EMAIL' AS from_column
 17  FROM   people p, people_mail m
 18  WHERE  p.pseq = m.pseq
 19  UNION ALL
 20  SELECT p.pseq, p.id, m.email_desc AS data, 'EMAIL_DESC' AS from_column
 21  FROM   people p, people_mail m
 22  WHERE  p.pseq = m.pseq
 23  /

Materialized view created.

SCOTT@orcl_11g> -- index:
SCOTT@orcl_11g> CREATE INDEX people_data
  2  ON your_view (data)
  3  INDEXTYPE IS CTXSYS.CONTEXT
  4  /

Index created.

SCOTT@orcl_11g> -- query:
SCOTT@orcl_11g> COLUMN id   FORMAT A6
SCOTT@orcl_11g> COLUMN data FORMAT A15
SCOTT@orcl_11g> SELECT *
  2  FROM   your_view
  3  WHERE  CONTAINS (data, 'street',1) > 0
  4  /

      PSEQ ID     DATA            FROM_COLUMN
---------- ------ --------------- -----------
         1 111111 street          ADDRESS
         1 111111 second street   ADDRESS

SCOTT@orcl_11g>


Re: Oracle text concatenated datastore [message #437512 is a reply to message #437508] Mon, 04 January 2010 18:10 Go to previous messageGo to next message
rozora
Messages: 9
Registered: January 2010
Junior Member
Many thanks,
i'll check this solution performance impact and update the post
Re: Oracle text concatenated datastore [message #437607 is a reply to message #437508] Tue, 05 January 2010 03:42 Go to previous messageGo to next message
rozora
Messages: 9
Registered: January 2010
Junior Member
BTW, does't this mview considered complex? i mean that in cannot support fast refresh?
Re: Oracle text concatenated datastore [message #437621 is a reply to message #437607] Tue, 05 January 2010 05:09 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8024
Registered: November 2002
Location: California, USA
Senior Member
rozora wrote on Tue, 05 January 2010 01:42
BTW, does't this mview considered complex? i mean that in cannot support fast refresh?


Good point. Here is a revised example in which I eliminated some unnecessary join conditions. I did the inserts after the index creation, just to show that the view refreshes and the index synchronizes on commit.

SCOTT@orcl_11g> -- tables you provided:
SCOTT@orcl_11g> create table people(pseq number primary key,id varchar2(250), name varchar2(250),description varchar2(4000));

Table created.

SCOTT@orcl_11g> create table people_add (pseq number, address varchar2(4000),phone varchar2(250));

Table created.

SCOTT@orcl_11g> create table people_mail (pseq number, email varchar2(250),email_desc varchar2(4000));

Table created.

SCOTT@orcl_11g> ALTER TABLE PEOPLE_ADD ADD (FOREIGN KEY (PSEQ) REFERENCES PEOPLE (PSEQ) ON DELETE CASCADE);

Table altered.

SCOTT@orcl_11g> ALTER TABLE PEOPLE_MAIL ADD (FOREIGN KEY (PSEQ) REFERENCES PEOPLE (PSEQ) ON DELETE CASCADE);

Table altered.

SCOTT@orcl_11g> -- materialized view:
SCOTT@orcl_11g> CREATE MATERIALIZED VIEW LOG ON people WITH ROWID INCLUDING NEW VALUES
  2  /

Materialized view log created.

SCOTT@orcl_11g> CREATE MATERIALIZED VIEW LOG ON people_add WITH ROWID INCLUDING NEW VALUES
  2  /

Materialized view log created.

SCOTT@orcl_11g> CREATE MATERIALIZED VIEW LOG ON people_mail WITH ROWID INCLUDING NEW VALUES
  2  /

Materialized view log created.

SCOTT@orcl_11g> CREATE MATERIALIZED VIEW your_view
  2  REFRESH FAST ON COMMIT WITH ROWID
  3  AS
  4  SELECT p.ROWID AS rid, p.pseq,
  5  	    p.name AS data, 'NAME' AS from_column,
  6  	    1 AS MARKER
  7  FROM   people p
  8  UNION ALL
  9  SELECT p. ROWID AS rid, p.pseq,
 10  	    p.description AS data, 'DESCRIPTION' AS from_column,
 11  	    2 AS MARKER
 12  FROM   people p
 13  UNION ALL
 14  SELECT a. ROWID AS rid, a.pseq,
 15  	    a.address AS data, 'ADDRESS' AS from_column,
 16  	    3 AS MARKER
 17  FROM   people_add a
 18  UNION ALL
 19  SELECT a.ROWID AS rid, a.pseq,
 20  	    a.phone AS data, 'PHONE' AS from_column,
 21  	    4 AS MARKER
 22  FROM   people_add a
 23  UNION ALL
 24  SELECT m.ROWID AS rid, m.pseq,
 25  	    m.email AS data, 'EMAIL' AS from_column,
 26  	    5 AS MARKER
 27  FROM   people_mail m
 28  UNION ALL
 29  SELECT m.ROWID AS rid, m.pseq,
 30  	    m.email_desc AS data, 'EMAIL_DESC' AS from_column,
 31  	    6 AS MARKER
 32  FROM   people_mail m
 33  /

Materialized view created.

SCOTT@orcl_11g> -- index:
SCOTT@orcl_11g> CREATE INDEX people_data
  2  ON your_view (data)
  3  INDEXTYPE IS CTXSYS.CONTEXT
  4  PARAMETERS ('SYNC (ON COMMIT)')
  5  /

Index created.

SCOTT@orcl_11g> -- inserts:
SCOTT@orcl_11g> BEGIN
  2    insert into people values (1,'111111','lulu','no information');
  3    insert into people values (2,'222222','dan','bank account');
  4    insert into people values (3,'333333','dana','');
  5    insert into people values (4,'444444','kuku','information');
  6    insert into people values (5,'111111','lala','basketball');
  7    insert into people values (6,'111111','','missing name');
  8    insert into people values (7,'111111','nono','something something');
  9    insert into people_add values (1,'street','012-phone');
 10    insert into people_add values (1,'second street','');
 11    insert into people_add values (2,'first','');
 12    insert into people_add values (3,'5th','1357');
 13    insert into people_add values (5,'3rd','111');
 14    insert into people_mail values (1,'mymail@somemail.com','office email');
 15    insert into people_mail values (4,'mymail2@somemail.com','e-mail');
 16    insert into people_mail values (5,'','at home');
 17  END;
 18  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> COMMIT
  2  /

Commit complete.

SCOTT@orcl_11g> -- query:
SCOTT@orcl_11g> COLUMN id   FORMAT A6
SCOTT@orcl_11g> COLUMN data FORMAT A15
SCOTT@orcl_11g> SELECT pseq, data, from_column
  2  FROM   your_view
  3  WHERE  CONTAINS (data, 'street',1) > 0
  4  /

      PSEQ DATA            FROM_COLUMN
---------- --------------- -----------
         1 street          ADDRESS
         1 second street   ADDRESS

SCOTT@orcl_11g>

Re: Oracle text concatenated datastore [message #437644 is a reply to message #437621] Tue, 05 January 2010 06:06 Go to previous messageGo to next message
rozora
Messages: 9
Registered: January 2010
Junior Member

i'm still getting
ora-12015 cannot create a fast refresh materialized view from a complex query

even without the joins.
what is the marker column for?
if i'll be able to solve the refresh solution it'll be the best solution for me, becasue creating the text index on the mview took ~5 minutes vs 6 hours when using user datastore, so in terms of maintenance it's way better.
but why does it still think the mview query is complex?
is there a limit on amount of unions? cause in my real environment i have more than 5...
Re: Oracle text concatenated datastore [message #437691 is a reply to message #437644] Tue, 05 January 2010 09:27 Go to previous messageGo to previous message
rozora
Messages: 9
Registered: January 2010
Junior Member
my bad,
in my real environment some table have similar column names, that what cause the mview creation to fail.
i concatenated the table name on each of the union such as:
SELECT a.ROWID AS rid, a.pseq,
 a.phone AS data, 'people_add-PHONE' AS from_column,
 4 AS MARKER
 FROM   people_add a


and that did the trick.
maybe that's why you added the marker field? or thats another purpose?
Previous Topic: Highlighting search results with user_datastore context index
Next Topic: Wildcard Seraches Using CONTAINS
Goto Forum:
  


Current Time: Mon Dec 22 01:20:18 CST 2014

Total time taken to generate the page: 0.06843 seconds