Home » Server Options » Text & interMedia » Searching using CATSEARCH and OR (10.2.0.4)
| Searching using CATSEARCH and OR [message #478978] |
Wed, 13 October 2010 18:26  |
lkngstr82is
Messages: 33
Registered: January 2010
Location: USA
|
Member |
|
|
I am getting function invocation error with CATSEARCH
create table mkt_track
shrt_nm varchar2(20),
accnt_id varchar2(20),
brnch_tx varchar2(3));
/
insert into mkt_track('ROYAL BANK','50057012','005');
/
insert into mkt_track('SPENCER','50057019','500');
/
insert into mkt_track('BATS','50057020','500');
/
commit;
CREATE INDEX INDX_MKT_TRACK_1
ON MKT_TRCK(SHRT_NM)
INDEXTYPE IS CTXSYS.CTXCAT PARALLEL;
/
CREATE INDEX INDX_MKT_TRACK_2
ON MKT_TRCK(BRNCH_TX)
INDEXTYPE IS CTXSYS.CTXCAT PARALLEL;
/
select * from bs_orgnl_sbmsn
WHERE (catsearch(SHRT_NM,'50057012',' ' )>0) OR (CATSEARCH(BRNCH_TX,'005',' ')>0);
/
ORA-20000: Oracle Text error:
DRG-10849: catsearch does not support functional invocation
DRG-10599: column is not indexed
Is there any way I can combine two CATSEARCH in a single query?
|
|
|
|
| Re: Searching using CATSEARCH and OR [message #478980 is a reply to message #478978] |
Wed, 13 October 2010 20:05  |
 |
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You have put a semicolon ; and a slash / after every command which causes every command to run twice. You posted a create table statement for mkt_track, created your indexes on mkt_trck, and queried bs_orgnl_sbmsn. Your table creation is missing a left parentheses. Your insert statements are missing the values keyword.
There should not be a space between the single quotes around the third parameter of the catsearch operator. It should be a null value, so either use two single quotes without a space or the word null.
There is also a problem with ctxcat indexes and catsearch where the optimizer may choose functional invocation at any time, which will always produce an error without returning any rows. This is why I always recommend context indexes and contains.
If you are querying two columns, then it is better to use a context index with a multi_column_datastore and use one contains clause. If you want to search within specific columns, then you can add a section group with field sections and use within clauses within one contains clause. It is generally a bad practice to use multiple catsearch or contains clauses in one query.
If your second column is purely structured data like numbers or dates, then you can create a ctxcat index with a sub-index, but this still does not solve the functional invocation problem.
I have corrected your code below, then offered a better method below that.
-- corrected code:
SCOTT@orcl_11gR2> create table mkt_track (
2 shrt_nm varchar2(20),
3 accnt_id varchar2(20),
4 brnch_tx varchar2(3))
5 /
Table created.
SCOTT@orcl_11gR2> insert into mkt_track VALUES ('ROYAL BANK','50057012','005')
2 /
1 row created.
SCOTT@orcl_11gR2> insert into mkt_track VALUES ('SPENCER','50057019','500')
2 /
1 row created.
SCOTT@orcl_11gR2> insert into mkt_track VALUES ('BATS','50057020','500')
2 /
1 row created.
SCOTT@orcl_11gR2> commit
2 /
Commit complete.
SCOTT@orcl_11gR2> CREATE INDEX INDX_MKT_TRACK_1
2 ON MKT_TRACK (SHRT_NM)
3 INDEXTYPE IS CTXSYS.CTXCAT PARALLEL
4 /
Index created.
SCOTT@orcl_11gR2> CREATE INDEX INDX_MKT_TRACK_2
2 ON MKT_TRACK(BRNCH_TX)
3 INDEXTYPE IS CTXSYS.CTXCAT PARALLEL
4 /
Index created.
SCOTT@orcl_11gR2> select * from mkt_track
2 WHERE (catsearch (SHRT_NM,'50057012', NULL ) > 0)
3 OR (CATSEARCH (BRNCH_TX,'005', NULL) > 0)
4 /
SHRT_NM ACCNT_ID BRN
-------------------- -------------------- ---
ROYAL BANK 50057012 005
1 row selected.
SCOTT@orcl_11gR2>
-- better method:
SCOTT@orcl_11gR2> create table mkt_track (
2 shrt_nm varchar2(20),
3 accnt_id varchar2(20),
4 brnch_tx varchar2(3),
5 search_cols varchar2(1))
6 /
Table created.
SCOTT@orcl_11gR2> begin
2 insert into mkt_track VALUES ('ROYAL BANK','50057012','005', null);
3 insert into mkt_track VALUES ('SPENCER','50057019','500', null);
4 insert into mkt_track VALUES ('BATS','50057020','500', null);
5 end;
6 /
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> BEGIN
2 CTX_DDL.CREATE_PREFERENCE ('your_datastore', 'MULTI_COLUMN_DATASTORE');
3 CTX_DDL.SET_ATTRIBUTE ('your_datastore', 'COLUMNS', 'shrt_nm, brnch_tx');
4 CTX_DDL.CREATE_SECTION_GROUP ('your_sec', 'BASIC_SECTION_GROUP');
5 CTX_DDL.ADD_FIELD_SECTION ('your_sec', 'shrt_nm', 'shrt_nm', TRUE);
6 CTX_DDL.ADD_FIELD_SECTION ('your_sec', 'brnch_tx', 'brnch_tx', TRUE);
7 END;
8 /
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> CREATE INDEX context_idx ON MKT_TRACK (search_cols)
2 INDEXTYPE IS CTXSYS.CONTEXT
3 PARAMETERS
4 ('datastore your_datastore
5 section group your_sec')
6 /
Index created.
SCOTT@orcl_11gR2> select * from mkt_track
2 WHERE CONTAINS
3 (search_cols,
4 '50057012 OR 005') > 0
5 /
SHRT_NM ACCNT_ID BRN S
-------------------- -------------------- --- -
ROYAL BANK 50057012 005
1 row selected.
SCOTT@orcl_11gR2> select * from mkt_track
2 WHERE CONTAINS
3 (search_cols,
4 '50057012 WITHIN shrt_nm OR 005 WITHIN brnch_tx') > 0
5 /
SHRT_NM ACCNT_ID BRN S
-------------------- -------------------- --- -
ROYAL BANK 50057012 005
1 row selected.
SCOTT@orcl_11gR2>
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Nov 23 05:54:33 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
