Home » Server Options » Text & interMedia » Oracle text concatenated datastore
| Oracle text concatenated datastore [message #293320] |
Fri, 11 January 2008 12:19  |
xokas11
Messages: 21
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: 3940
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: 21
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: 3940
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: 21
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: 21
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: 3940
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: 21
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]
|
|
|
| Re: Oracle text concatenated datastore [message #305454 is a reply to message #295547 ] |
Mon, 10 March 2008 16:58 |
redonisc
Messages: 10
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]
|
|
|
| Re: Oracle text concatenated datastore [message #305457 is a reply to message #305454 ] |
Mon, 10 March 2008 17:13 |
xokas11
Messages: 21
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: 10
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: 3940
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: 10
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]
|
|
|
| Re: Oracle text concatenated datastore [message #305736 is a reply to message #305705 ] |
Tue, 11 March 2008 14:43 |
|
Barbara Boehmer
Messages: 3940
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: 10
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]
|
|
|
| Re: Oracle text concatenated datastore [message #306576 is a reply to message #306542 ] |
Fri, 14 March 2008 14:36 |
|
Barbara Boehmer
Messages: 3940
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: 10
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]
|
|
|
| Re: Oracle text concatenated datastore [message #306600 is a reply to message #306585 ] |
Fri, 14 March 2008 20:45 |
|
Barbara Boehmer
Messages: 3940
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: 3940
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]
|
|
| |
| Re: Oracle text concatenated datastore [message #307110 is a reply to message #307060 ] |
Mon, 17 March 2008 18:57 |
redonisc
Messages: 10
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]
|
|
|
| Re: Oracle text concatenated datastore [message #307114 is a reply to message #307110 ] |
Mon, 17 March 2008 19:18 |
|
Barbara Boehmer
Messages: 3940
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]
|
|
|
| Re: Oracle text concatenated datastore [message #307128 is a reply to message #307110 ] |
Mon, 17 March 2008 22:26 |
|
Barbara Boehmer
Messages: 3940
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 < | |
|
|
Members
Search
Help
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?