Home » Server Options » Text & interMedia » Need help on CATSEARCH (ORACLE 10G)
| Need help on CATSEARCH [message #527519] |
Tue, 18 October 2011 08:57  |
 |
venkatlvr
Messages: 5
Registered: October 2011
Location: DUBAI
|
Junior Member |
|
|
Hi,
I am trying to implement catserach in my application. I am struggling to use this cat search. Can any one help on this.
first query working fine.
SELECT A.*
FROM Xtable A,
Ytable B,
WHERE A.ID = B.ID
AND CATSEARCH (A.COD, '**XYZ*', null) > 0 -- Hard coded
Second query : giving Oracle text error ORA : 20000
Here I am searching with second tables value
SELECT A.*
FROM Xtable A,
Ytable B,
WHERE A.ID = B.ID
AND CATSEARCH (A.TCOD, '**'||b.cod||'*', null) > 0
Thanks,
Venkataraman .L
|
|
|
|
|
|
| Re: Need help on CATSEARCH [message #527524 is a reply to message #527520] |
Tue, 18 October 2011 09:23  |
|
venkatlvr
Messages: 5
Registered: October 2011
Location: DUBAI
|
Junior Member |
|
|
Ignore my previous queries.
first query working fine. Here I am using only one table
SELECT A.*
FROM Xtable A,
WHERE CATSEARCH(A.COD, '**XYZ*', null) > 0 -- Hard coded
Second query : giving Oracle text error ORA : 20000
If I join with another table I am getting the error
SELECT A.*
FROM Xtable A, Ytable B,
WHERE A.ID = B.ID
AND CATSEARCH(A.TCOD, '**' || b.cod || '*', null) > 0
Please help on this
[Updated on: Tue, 18 October 2011 10:23] Report message to a moderator |
|
|
|
|
|
| Re: Need help on CATSEARCH [message #527555 is a reply to message #527527] |
Tue, 18 October 2011 15:02 |
 |
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You are using column cod in one query and column tcod in another query. You also have an extra comma after the table names. I will assume that both of these are typing errors. This is why you should copy and paste, instead of typing.
We also expect you to provide create table and insert statements to reproduce the problem. You also need to provide the complete stack of error message, not just one error number, as there can be different error messages with the same number and we need to see all of the messages.
Catsearch queries with ctxcat indexes have what I consider a major bug. Any time that the optimizer chooses functional invocation, the query will fail, since catsearch queries do not support functional invocation. It looks like that is what you have encountered. This may happen at any time with any catsearch query, depending on the data, statistics, and the execution plan that the optimizer chooses. Because of this, I recommend that ctxcat indexes and catsearch not be used in a production environment.
Ctxcat indexes and catsearch queries have a lot of limitations that context indexes and contains queries do not have. In the past, the major benefit to ctxcat indexes was that they are transactional. However, context indexes can now be created using the transactional parameter. So, there does not appear to be any benefit to ctxcat, but lots of drawbacks.
I have provided a demonstration below that includes the create table and insert statements and index creation statements that you should have provided, a reproduction of the problem, and a better method of obtaining the same results and performance using context and contains instead of ctxcat and catsearch.
-- create table, insert, and index creation statements that you should have provided:
SCOTT@orcl_11gR2> CREATE TABLE Xtable
2 (id NUMBER,
3 cod VARCHAR2 (10))
4 /
Table created.
SCOTT@orcl_11gR2> INSERT ALL
2 INTO Xtable VALUES (1, 'XYZ')
3 INTO Xtable VALUES (1, 'WXYZ')
4 INTO Xtable VALUES (1, 'XYZA')
5 INTO Xtable VALUES (1, 'ABC')
6 SELECT * FROM DUAL
7 /
4 rows created.
SCOTT@orcl_11gR2> CREATE TABLE Ytable
2 (id NUMBER,
3 cod VARCHAR2 (10))
4 /
Table created.
SCOTT@orcl_11gR2> INSERT ALL
2 INTO Ytable VALUES (1, 'XYZ')
3 SELECT * FROM DUAL
4 /
1 row created.
SCOTT@orcl_11gR2> CREATE INDEX Xtable_cod_idx
2 ON Xtable (cod)
3 INDEXTYPE IS CTXSYS.CTXCAT
4 /
Index created.
-- reproduction of problem:
SCOTT@orcl_11gR2> SELECT A.*
2 FROM Xtable A
3 WHERE CATSEARCH (A.COD, '**XYZ*', null) > 0
4 /
ID COD
---------- ----------
1 XYZ
1 WXYZ
1 XYZA
3 rows selected.
SCOTT@orcl_11gR2> SELECT A.*
2 FROM Xtable A, Ytable B
3 WHERE A.ID = B.ID
4 AND CATSEARCH (A.COD, '**XYZ*', null) > 0
5 /
ID COD
---------- ----------
1 XYZA
1 WXYZ
1 XYZ
3 rows selected.
SCOTT@orcl_11gR2> SELECT A.*
2 FROM Xtable A, Ytable B
3 WHERE A.ID = B.ID
4 AND CATSEARCH (A.COD, '**' || b.cod || '*', null) > 0
5 /
SELECT A.*
*
ERROR at line 1:
ORA-20000: Oracle Text error:
DRG-10849: catsearch does not support functional invocation
-- better method:
SCOTT@orcl_11gR2> DROP INDEX Xtable_cod_idx
2 /
Index dropped.
SCOTT@orcl_11gR2> CREATE INDEX Xtable_cod_idx
2 ON Xtable (cod)
3 INDEXTYPE IS CTXSYS.CONTEXT
4 PARAMETERS ('TRANSACTIONAL')
5 /
Index created.
SCOTT@orcl_11gR2> SELECT A.*
2 FROM Xtable A
3 WHERE CONTAINS (A.COD, '%XYZ%') > 0
4 /
ID COD
---------- ----------
1 XYZ
1 WXYZ
1 XYZA
3 rows selected.
SCOTT@orcl_11gR2> SELECT A.*
2 FROM Xtable A, Ytable B
3 WHERE A.ID = B.ID
4 AND CONTAINS (A.COD, '%XYZ%') > 0
5 /
ID COD
---------- ----------
1 XYZA
1 WXYZ
1 XYZ
3 rows selected.
SCOTT@orcl_11gR2> SELECT A.*
2 FROM Xtable A, Ytable B
3 WHERE A.ID = B.ID
4 AND CONTAINS (A.COD, '%' || b.cod || '%') > 0
5 /
ID COD
---------- ----------
1 XYZ
1 WXYZ
1 XYZA
3 rows selected.
SCOTT@orcl_11gR2>
[Updated on: Tue, 18 October 2011 15:06] Report message to a moderator |
|
|
|
|
|
Goto Forum:
Current Time: Sat Nov 23 06:33:26 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
