Home » Server Options » Text & interMedia » Oracle text concatenated datastore
| Oracle text concatenated datastore [message #293320] |
Fri, 11 January 2008 12:19  |
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  |
 |
Barbara Boehmer
Messages: 9100
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 |
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 |
|
Barbara Boehmer
Messages: 9100
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 #293929 is a reply to message #293366] |
Tue, 15 January 2008 11:41 |
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 #295205 is a reply to message #293320] |
Mon, 21 January 2008 12:53 |
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 |
|
Barbara Boehmer
Messages: 9100
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 |
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 |
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 |
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 |
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 |
|
Barbara Boehmer
Messages: 9100
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 |
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 |
|
Barbara Boehmer
Messages: 9100
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 |
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 |
|
Barbara Boehmer
Messages: 9100
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 #306585 is a reply to message #306577] |
Fri, 14 March 2008 15:22 |
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 |
|
Barbara Boehmer
Messages: 9100
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 #307058 is a reply to message #307045] |
Mon, 17 March 2008 14:03 |
|
Barbara Boehmer
Messages: 9100
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 #307110 is a reply to message #307060] |
Mon, 17 March 2008 18:57 |
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 |
|
Barbara Boehmer
Messages: 9100
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 |
|
Barbara Boehmer
Messages: 9100
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 |
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 |
|
Barbara Boehmer
Messages: 9100
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 |
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 #437317 is a reply to message #437314] |
Sun, 03 January 2010 18:04 |
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 |
|
Barbara Boehmer
Messages: 9100
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 |
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 |
|
Barbara Boehmer
Messages: 9100
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 #437621 is a reply to message #437607] |
Tue, 05 January 2010 05:09 |
|
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
rozora wrote on Tue, 05 January 2010 01:42BTW, 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 |
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 |
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?
|
|
|
|
Goto Forum:
Current Time: Sat Nov 30 04:20:05 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
Thanks a lot Barbara... can you tell us, some references about how the xml is working with this scenario with oracle text services?