Home » Server Options » Text & interMedia » Oracle Text CATSEARCH with empty field and not returning data (Oracle 11Gr2)
| Oracle Text CATSEARCH with empty field and not returning data [message #520964] |
Thu, 25 August 2011 11:25  |
 |
wpharvey
Messages: 4
Registered: August 2011
|
Junior Member |
|
|
Hello,
We have a basic Oracle Text index setup on a field.
create index search_landusex on PARCEL.address_search(land_use_zoning) indextype is CTXSYS.CTXCAT
ADDRESS_SUFFIX VARCHAR2(50 BYTE)
The distinct values in this field are:
A,ABC,B,C,D,E,F,G,H,I,J,L,M,N,Q,S and an empty value ''
Two questions/problems I am encountering.
1) From what I have researched, CATSEARCH does not support NULLS, which is fine. The columns have empty values which should be able to be queried with a ''. However, I can't get them to return and throw an error.
select objectID, address_suffix from PARCEL.address_search where CATSEARCH(address_suffix,'',NULL) > 0
ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error:
DRG-50901: text query parser syntax error on line 1, column 1
How can I return the fields that are empty?
2) The following queries returns all values:
select address_suffix from address_search where Address_Suffix = 'A'
However, the CATSEARCH returns no values. Niether does including trailing wildcard (e.g. 'A*')
select objectID, address_suffix from PARCEL.address_search where CATSEARCH(address_suffix,'A',NULL) > 0
Finally, on a hopefully not totally stupid question, I cannot find anything on what the trailing >0 does on a CATSEARCH, or why the final NULL is required in it.
CATSEARCH(address_suffix,'A',NULL) > 0
It will not work without the NULL in bold above (there are no parameters, or index sets defined) and changing the >0 to > 100000, = 0, etc. etc. do not make a difference.
Thanks very much for any help you can provide.
|
|
|
|
| Re: Oracle Text CATSEARCH with empty field and not returning data [message #520983 is a reply to message #520964] |
Thu, 25 August 2011 13:36  |
 |
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Your index creation statement shows that your index is created on the land_use_zoning column, but everything else indicates it is created on the address_suffix column, so I will assume it is the address_suffix column.
NULL and '' and no value inserted are the same thing. They all mean unknown, so they cannot be compared to anything using comparison operators like =. They can only be compared using IS NULL or IS NOT NULL. As a workaround to use them with a CTXCAT index you can replace all such null values with the word 'NULL' or whatever other value you choose, so that you can search for that. You can create a trigger to populate any null values. Also, you should be using bind variables for the value passed to catsearch as the second parameter, so you can use NVL on that to convert it as well. Using bind variables allows Oracle to re-use the query in the SGA avoiding the extra time of hard parsing, just substituting a different value for the bind variable.
When you do not specify a stoplist, by default, Oracle Text uses a default stoplist, which includes the word 'A' and various other common short words like 'THE' and does not index them. If you want to search for such words, then you need to specify a stoplist that does not contain these words. You can use an empty stoplist, so that no words are skipped during indexing.
When you create a ctxcat index, it creates various domain index tables. You can query the dr$token column of the dr$your_index_name$i table to see what tokens have been created by indexing. These are the things that you can search on.
The catsearch function has three parameters and there is no default for the third parameter, so you have to provide it, whether you use it or not. Since '' and null are the same, you can use either one for the third parameter.
Requiring some operator and some numerical value, like >0 is just how the ctxcat function was created. Only the people at Oracle who wrote it that way know why.
Please see the reproduction of your problems below, followed by the workarounds.
-- reproduction of problems:
SCOTT@orcl_11gR2> -- test table and data:
SCOTT@orcl_11gR2> CREATE TABLE address_search
2 (objectid NUMBER PRIMARY KEY,
3 address_suffix VARCHAR2 (50 BYTE))
4 /
Table created.
SCOTT@orcl_11gR2> INSERT ALL
2 INTO address_search (objectid, address_suffix) VALUES (1, 'ABC')
3 INTO address_search (objectid, address_suffix) VALUES (2, 'A')
4 INTO address_search (objectid, address_suffix) VALUES (3, '')
5 INTO address_search (objectid, address_suffix) VALUES (4, NULL)
6 INTO address_search (objectid) VALUES (5)
7 SELECT * FROM DUAL
8 /
5 rows created.
SCOTT@orcl_11gR2> COMMIT
2 /
Commit complete.
SCOTT@orcl_11gR2> -- Null and '' and no value inserted are the same
SCOTT@orcl_11gR2> -- and nothing can be compared to a null value using anything other than
SCOTT@orcl_11gR2> -- IS NULL or IS NOT NULL:
SCOTT@orcl_11gR2> SELECT * FROM address_search WHERE address_suffix = ''
2 /
no rows selected
SCOTT@orcl_11gR2> SELECT * FROM address_search WHERE address_suffix = NULL
2 /
no rows selected
SCOTT@orcl_11gR2> SELECT * FROM address_search WHERE address_suffix IS NULL
2 /
OBJECTID ADDRESS_SUFFIX
---------- --------------------------------------------------
3
4
5
3 rows selected.
SCOTT@orcl_11gR2> SELECT * FROM address_search WHERE address_suffix IS NOT NULL
2 /
OBJECTID ADDRESS_SUFFIX
---------- --------------------------------------------------
1 ABC
2 A
2 rows selected.
SCOTT@orcl_11gR2> -- index:
SCOTT@orcl_11gR2> CREATE INDEX search_landusex
2 ON address_search (address_suffix)
3 INDEXTYPE IS CTXSYS.CTXCAT
4 /
Index created.
SCOTT@orcl_11gR2> -- tokens created by index
SCOTT@orcl_11gR2> -- (these are the only things you can search on):
SCOTT@orcl_11gR2> SELECT dr$token FROM dr$search_landusex$i
2 /
DR$TOKEN
----------------------------------------------------------------
ABC
1 row selected.
SCOTT@orcl_11gR2> -- original queries that do not produce desired results:
SCOTT@orcl_11gR2> SELECT objectid, address_suffix
2 FROM address_search
3 WHERE CATSEARCH (address_suffix, '', NULL) > 0
4 /
SELECT objectid, address_suffix
*
ERROR at line 1:
ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error:
DRG-50901: text query parser syntax error on line 1, column 1
SCOTT@orcl_11gR2> SELECT objectid, address_suffix
2 FROM address_search
3 WHERE CATSEARCH (address_suffix, 'A', NULL) > 0
4 /
no rows selected
-- possible workarounds:
SCOTT@orcl_11gR2> -- drop old index:
SCOTT@orcl_11gR2> DROP INDEX search_landusex
2 /
Index dropped.
SCOTT@orcl_11gR2> -- update null values to 'NULL':
SCOTT@orcl_11gR2> UPDATE address_search
2 SET address_suffix = 'NULL'
3 WHERE address_suffix IS NULL
4 /
3 rows updated.
SCOTT@orcl_11gR2> -- recreate index adding empty stoplist:
SCOTT@orcl_11gR2> CREATE INDEX search_landusex
2 ON address_search (address_suffix)
3 INDEXTYPE IS CTXSYS.CTXCAT
4 PARAMETERS ('STOPLIST CTXSYS.EMPTY_STOPLIST')
5 /
Index created.
SCOTT@orcl_11gR2> -- add a trigger to replace all future inserted null values with 'NULL':
SCOTT@orcl_11gR2> CREATE OR REPLACE TRIGGER address_search_bir
2 BEFORE INSERT ON address_search
3 FOR EACH ROW
4 WHEN (NEW.address_suffix IS NULL)
5 BEGIN
6 :NEW.address_suffix := 'NULL';
7 END address_search_bir;
8 /
Trigger created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> -- test new inserts:
SCOTT@orcl_11gR2> INSERT ALL
2 INTO address_search (objectid, address_suffix) VALUES (6, '')
3 INTO address_search (objectid, address_suffix) VALUES (7, NULL)
4 INTO address_search (objectid) VALUES (8)
5 INTO address_search (objectid, address_suffix) VALUES (9, 'B')
6 SELECT * FROM DUAL
7 /
4 rows created.
SCOTT@orcl_11gR2> COMMIT
2 /
Commit complete.
SCOTT@orcl_11gR2> -- tokens created by index
SCOTT@orcl_11gR2> -- (these are the only things you can search on):
SCOTT@orcl_11gR2> SELECT dr$token FROM dr$search_landusex$i
2 /
DR$TOKEN
----------------------------------------------------------------
A
ABC
B
NULL
NULL
NULL
NULL
NULL
NULL
9 rows selected.
SCOTT@orcl_11gR2> -- use a bind variable for better performance
SCOTT@orcl_11gR2> VARIABLE search_string VARCHAR2 (50 BYTE)
SCOTT@orcl_11gR2> -- use nvl to convert null values to 'NULL'
SCOTT@orcl_11gR2> EXEC :search_string := ''
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> SELECT objectid, address_suffix
2 FROM address_search
3 WHERE CATSEARCH (address_suffix, NVL (:search_string, 'NULL'), NULL) > 0
4 /
OBJECTID ADDRESS_SUFFIX
---------- --------------------------------------------------
3 NULL
4 NULL
5 NULL
6 NULL
7 NULL
8 NULL
6 rows selected.
SCOTT@orcl_11gR2> -- due to empty stoplist, you can now search for 'A'
SCOTT@orcl_11gR2> EXEC :search_string := 'A'
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> SELECT objectid, address_suffix
2 FROM address_search
3 WHERE CATSEARCH (address_suffix, NVL (:search_string, 'NULL'), NULL) > 0
4 /
OBJECTID ADDRESS_SUFFIX
---------- --------------------------------------------------
2 A
1 row selected.
SCOTT@orcl_11gR2> -- The third parameter can be '' instead of NULL
SCOTT@orcl_11gR2> SELECT objectid, address_suffix
2 FROM address_search
3 WHERE CATSEARCH (address_suffix, 'NULL', '') > 0
4 /
OBJECTID ADDRESS_SUFFIX
---------- --------------------------------------------------
3 NULL
4 NULL
5 NULL
6 NULL
7 NULL
8 NULL
6 rows selected.
SCOTT@orcl_11gR2> SELECT objectid, address_suffix
2 FROM address_search
3 WHERE CATSEARCH (address_suffix, 'A', '') > 0
4 /
OBJECTID ADDRESS_SUFFIX
---------- --------------------------------------------------
2 A
1 row selected.
SCOTT@orcl_11gR2>
[Updated on: Thu, 25 August 2011 13:39] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Oracle Text CATSEARCH with empty field and not returning data [message #521157 is a reply to message #521118] |
Fri, 26 August 2011 12:30 |
|
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
wpharvey wrote on Fri, 26 August 2011 06:06
With CTXCAT indexes they are sync'd automatically during DML/DDL operations. If a column is updated, are all of the indexes defined on the talbe indexed, or just the column that is updated?
I am not sure I understand what you are asking. A ctxcat index is created on one column. When you create a ctxcat index, Oracle Text automatically creates a bunch of things, including a trigger that updates all of the domain index tables associated with the index whenever DML is done on the column that the index is created on. This does not affect other non-text indexes on other columns, but those are also maintained during DML through separate processes. The ctxcat index is not affected when DML is done on columns other than the one that the ctxcat index is created on. The following example shows the trigger that is created as a result of indexing.
-- no trigger or other objects before index creation:
SCOTT@orcl_11gR2> COLUMN object_name FORMAT A30
SCOTT@orcl_11gR2> SELECT object_type, object_name
2 FROM user_objects
3 WHERE object_name LIKE '%SEARCH_LANDUSEX%'
4 /
no rows selected
-- create table and index:
SCOTT@orcl_11gR2> CREATE TABLE address_search
2 (objectid NUMBER PRIMARY KEY,
3 address_suffix VARCHAR2 (50 BYTE))
4 /
Table created.
SCOTT@orcl_11gR2> CREATE INDEX search_landusex
2 ON address_search (address_suffix)
3 INDEXTYPE IS CTXSYS.CTXCAT
4 /
Index created.
-- objects created as a result of index creation:
SCOTT@orcl_11gR2> SELECT object_type, object_name
2 FROM user_objects
3 WHERE object_name LIKE '%SEARCH_LANDUSEX%'
4 /
OBJECT_TYPE OBJECT_NAME
------------------- ------------------------------
INDEX SEARCH_LANDUSEX
TRIGGER DR$SEARCH_LANDUSEXTC
INDEX DR$SEARCH_LANDUSEX$X
INDEX DR$SEARCH_LANDUSEX$R
TABLE DR$SEARCH_LANDUSEX$I
5 rows selected.
-- trigger that is created as a result of index creation:
SCOTT@orcl_11gR2> SELECT text
2 FROM user_source
3 WHERE name = 'DR$SEARCH_LANDUSEXTC'
4 AND type = 'TRIGGER'
5 ORDER BY line
6 /
TEXT
--------------------------------------------------------------------------------
trigger "SCOTT"."DR$SEARCH_LANDUSEXTC" after insert or update on "SCOTT"."ADDRES
S_SEARCH" for each row declare reindex boolean := FALSE; updop boolean :=
FALSE; begin ctxsys.drvdml.c_updtab.delete; ctxsys.drvdml.c_numtab.delete;
ctxsys.drvdml.c_vctab.delete; ctxsys.drvdml.c_rowid := :new.rowid; if (inse
rting or updating('ADDRESS_SUFFIX') or :new."ADDRESS_SUFFIX" <> :old."ADDR
ESS_SUFFIX") then reindex := TRUE; updop := (not inserting); ctxsys.
drvdml.c_text_vc2 := :new."ADDRESS_SUFFIX"; end if; ctxsys.drvdml.ctxcat_dml
('SCOTT','SEARCH_LANDUSEX', reindex, updop); end;
1 row selected.
SCOTT@orcl_11gR2>
-- above trigger formatted so that it is easier to read:
trigger "SCOTT"."DR$SEARCH_LANDUSEXTC"
after insert or update on "SCOTT"."ADDRESS_SEARCH"
for each row
declare
reindex boolean := FALSE;
updop boolean := FALSE;
begin
ctxsys.drvdml.c_updtab.delete;
ctxsys.drvdml.c_numtab.delete;
ctxsys.drvdml.c_vctab.delete;
ctxsys.drvdml.c_rowid := :new.rowid;
if (inserting or updating ('ADDRESS_SUFFIX')
or :new."ADDRESS_SUFFIX" <> :old."ADDRESS_SUFFIX")
then
reindex := TRUE;
updop := (not inserting);
ctxsys.drvdml.c_text_vc2 := :new."ADDRESS_SUFFIX";
end if;
ctxsys.drvdml.ctxcat_dml ('SCOTT','SEARCH_LANDUSEX', reindex, updop);
end;
|
|
|
|
|
|
| Re: Oracle Text CATSEARCH with empty field and not returning data [message #521169 is a reply to message #521158] |
Fri, 26 August 2011 12:59 |
|
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
wpharvey wrote on Fri, 26 August 2011 10:37
We have multiple CTXCAT indexes on the same table and different columns. If a DML operation is done on one column, only that index is sync'd, correct?
Correct.
If you run queries that use multiple catsearch clauses on different columns in the same query, then you might be better off with a context index with a multi_column_datastore and queries using contains.
|
|
|
|
Goto Forum:
Current Time: Thu Dec 26 23:24:41 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
