Home » Server Options » Text & interMedia » Mixed queries with Oracle Text
Mixed queries with Oracle Text [message #448502] Tue, 23 March 2010 16:13 Go to next message
ziploc1010
Messages: 5
Registered: March 2010
Junior Member
Hi,

I am trying to perform a query that uses an Oracle Text domain index and a normal index.

I have to perform this query on two different Oracle databases.

The query works on one database, but it does not on the other.
The two database names are m1_cl and ld_cl.

When running the query on ld_cl, it is not allowing me to use filtering criteria on multiple columns when using an Oracle Text function.

For ld_cl, the query does not allow me to index on any other filtering criteria, besides the domain index:

select * from ld.staging where catsearch(msg,'117',null) > 0
and vin = '3GNEC-----6626'

Error report:

SQL Error: ORA-20000: Oracle Text error:

DRG-10849: catsearch does not support functional invocation
DRG-10599: column is not indexed
20000. 00000 - "%s"

*Cause: The stored procedure 'raise_application_error'
was called which causes this error to be generated.
*Action: Correct the problem as described in the error message or contact
the application administrator or DBA for more information.

If I run the same query on m1_cl, it does not give me this error:

select
* from ld.staging where catsearch(msg,'117',null) > 0
and vin = '3GNEC-----6626'

I saw that the explain plan is different for each query.

On ld_cl, it is:

SELECT STATEMENT

TABLE ACCESS STAGING BY GLOBAL INDEX ROWID
Filter Predicates
CTXSYS.CATSEARCH(MSG,'117',NULL)>0
INDEX IDX_STAGING_VIN RANGE SCAN
Access Predicates
VIN='3GNE------6626'

On m1_cl, it is:

SELECT STATEMENT

TABLE ACCESS STAGING BY INDEX ROWID
BITMAP CONVERSION
BITMAP AND
BITMAP CONVERSION
INDEX VIN_IDX
Access Predicates
VIN='3GNEC-------6626'
BITMAP CONVERSION
SORT
DOMAIN INDEX MSG_IDX
Access Predicates
CTXSYS.CATSEARCH(MSG,'117',NULL)>0

On ld_cl,

I can execute the query, by using an index hint:

select /*+ INDEX(lsc msg_idx) */
* from ld.staging lsc where vin = '3GNEC------6626' and
catsearch(msg,'117',null) > 0

However, it runs very slowly, because it is not using the IDX_STAGING_VIN index.

It gives me the following explain plan:

SELECT STATEMENT
TABLE ACCESS STAGING BY GLOBAL INDEX ROWID
Filter Predicates
VIN='3G------6626'
DOMAIN INDEX MSG_IDX
Access Predicates
CTXSYS.CATSEARCH(MSG,'117',NULL)>0

I have tried to use both indexes using this hint:

/*+ INDEX_COMBINE(lsc msg_idx IDX_STAGING_VIN) */

But it has not worked. It still gives me the same explain plan.
I am not sure if it is not able to use both indexes on ld_cl.staging, because the table is partitioned. The indexes msg_idx and idx_staging_vin are not partitioned on ld_cl.staging.
Re: Mixed queries with Oracle Text [message #448527 is a reply to message #448502] Wed, 24 March 2010 00:17 Go to previous messageGo to next message
BlackSwan
Messages: 22837
Registered: January 2009
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: Mixed queries with Oracle Text [message #448670 is a reply to message #448527] Wed, 24 March 2010 10:22 Go to previous messageGo to next message
ziploc1010
Messages: 5
Registered: March 2010
Junior Member

Here is information on my Oracle version:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
"CORE 10.2.0.4.0 Production"
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
Re: Mixed queries with Oracle Text [message #448673 is a reply to message #448502] Wed, 24 March 2010 10:31 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7990
Registered: November 2002
Location: California, USA
Senior Member
There is a bug with ctxcat indexes and catsearch. When your data and statistics and query are such that the structured portion of the query is very selective, the optimizer chooses an execution plan that uses functional invocation, but since catsearch does not support functional invocation it produces an error. The suggested workaround in the documentation is to use optimizer hints that force usage of an index, instead of functional invocation, but that does not always work and if it does work can result in slower execution. I would not use ctxcat and catsearch in an application. I would use context and contains. If using a newer version, you may be able to use mdata and/or sdata.

Re: Mixed queries with Oracle Text [message #448677 is a reply to message #448670] Wed, 24 March 2010 11:04 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7990
Registered: November 2002
Location: California, USA
Senior Member
One other thing that you could try is making your vin column part of an index set that is used in creation of the ctxcat index, so that you can include searching of the vin in the catsearch clause, instead of in a where clause, so that only one index hit is required for the whole query.
Re: Mixed queries with Oracle Text [message #448681 is a reply to message #448677] Wed, 24 March 2010 11:49 Go to previous messageGo to next message
ziploc1010
Messages: 5
Registered: March 2010
Junior Member

Are you sure a context index would not require too much overhead? Because my scrubber_msg column is only a text fragment with an average of 50 characters with comma-delimited numbers.

I am not sure why the m1_cl environment uses a different explain plan than the ld_cl environment.

On ml_cl, it uses a bitmap conversion that allows me to use both indexes, vin_idx and msg_idx.

On ld_cl, it only allows me to use one index, even if I use the INDEX_COMBINE hint.

I thought of using an index set for the ctxcat index, but I read in the Oracle documentation that you cannot use index sets for columns that may have null values.

Re: Mixed queries with Oracle Text [message #448688 is a reply to message #448681] Wed, 24 March 2010 12:43 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7990
Registered: November 2002
Location: California, USA
Senior Member
ziploc1010 wrote on Wed, 24 March 2010 09:49

Are you sure a context index would not require too much overhead?


What is "too much" compared to the risk of any query returning an error message and no results? The frequency of the error due to functional invocation increases as the size of the data grows. Plenty of applications with huge text data use context indexes with no problem.

Quote:

I am not sure why the m1_cl environment uses a different explain plan than the ld_cl environment.


As previously stated, the execution plan that the optimizer chooses is dependent upon the data and the statistics. With different sets of data and/or different statistics, the optimizer chooses different execution plans for the same query. It looks at the statistics and decides whether using the ctxcat index to search for the text string would likely return a smaller result or using another index to search for the vin would return a smaller result set. You can attempt to use hints to influence the optimizer but they are just hints, not commands, so you cannot force the optimizer to use the index(es) or plan that you want, only make it somewhat more likely.

Quote:

I thought of using an index set for the ctxcat index, but I read in the Oracle documentation that you cannot use index sets for columns that may have null values.


True. So, you would need to replace the null values with some sort of default, such as the word "null". However, you may still encounter the functional invocation problem, so using a context index would still be better.



Re: Mixed queries with Oracle Text [message #448692 is a reply to message #448688] Wed, 24 March 2010 13:37 Go to previous messageGo to next message
ziploc1010
Messages: 5
Registered: March 2010
Junior Member
I had tried to use an oracle context index before and it was not working, because it returned less rows than using an equivalent "like % command" in the where clause.

I read in the documentation that oracle context indexes did not work well for smaller text fragments, so I switched to using the ctxcat index.

However, I find out the context index did not work because it was not recognizing comma delimited numbers, since it looks for text separated by spaces to recognize words.

Is there a numgroup or punctuation parameter I can use to pass to the basic lexer?
Re: Mixed queries with Oracle Text [message #448693 is a reply to message #448692] Wed, 24 March 2010 14:03 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7990
Registered: November 2002
Location: California, USA
Senior Member
ziploc1010 wrote on Wed, 24 March 2010 11:37

Is there a numgroup or punctuation parameter I can use to pass to the basic lexer?


Yes, you can set the numgroup to some character to override the default value of comma, as demonstrated below. You must supply some character, since it will not accept a null value.

SCOTT@orcl_11g> CREATE TABLE test
  2    (numbers  VARCHAR2 (10))
  3  /

Table created.

SCOTT@orcl_11g> INSERT ALL
  2  INTO test VALUES ('1,2,3')
  3  INTO test VALUES ('4,5,6')
  4  SELECT * FROM DUAL
  5  /

2 rows created.

SCOTT@orcl_11g> BEGIN
  2    CTX_DDL.CREATE_PREFERENCE
  3  	 ('test_lex', 'BASIC_LEXER');
  4    CTX_DDL.SET_ATTRIBUTE
  5  	 ('test_lex', 'NUMGROUP', '@');
  6  END;
  7  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> CREATE INDEX test_idx
  2  ON test (numbers)
  3  INDEXTYPE IS CTXSYS.CONTEXT
  4  PARAMETERS ('LEXER test_lex')
  5  /

Index created.

SCOTT@orcl_11g> SELECT token_text
  2  FROM   dr$test_idx$i
  3  /

TOKEN_TEXT
----------------------------------------------------------------
1
2
3
4
5
6

6 rows selected.

SCOTT@orcl_11g> SELECT *
  2  FROM   test
  3  WHERE  CONTAINS (numbers, '2') > 0
  4  /

NUMBERS
----------
1,2,3

SCOTT@orcl_11g>

Re: Mixed queries with Oracle Text [message #450713 is a reply to message #448693] Thu, 08 April 2010 18:05 Go to previous messageGo to next message
ziploc1010
Messages: 5
Registered: March 2010
Junior Member
Hi,

I tried to set a numgroup attribute with the comma character and it did not work correctly.
I then used your suggestion with the '@' character and all of the right tokens were created.

begin
ctx_ddl.create_preference('number_lex','basic_lexer');
ctx_ddl.set_attribute('number_lex','numgroup',',');
end;

select * from dr$idx_mvw_staging_car$i

117
117,130
117,130,155
117,130,155,187
117,130,155,302

begin
ctx_ddl.create_preference('number_lex','basic_lexer');
ctx_ddl.set_attribute('number_lex','numgroup','@');
end;

select * from dr$idx_mvw_staging_car$i

117
130
155
187
302

What is the '@' character and why did the ',' character not work correctly ?





Re: Mixed queries with Oracle Text [message #450719 is a reply to message #450713] Thu, 08 April 2010 21:09 Go to previous message
Barbara Boehmer
Messages: 7990
Registered: November 2002
Location: California, USA
Senior Member
It did exactly what you asked it to in both cases. The numgroup character is the character that is used to separate hundreds from thousands and millions and so on in groups of three. So, if you tell it that the comma is the numgroup character, then it recognizes 1,200 as the single numeric value of one thousand two hundred, not two separate values of one and two hundred. If you tell it that the numgroup character is @ then it would recognize 1@200 as one thousand two hundred and 1,200 as two separate values of 1 and 200. Since you have comma-delimited strings of numbers where you want the comma recognized as a separator, not a numgroup, then you need to make the numgroup character something other than the comma. I just arbitrarily chose @ for the example. You could choose a different character, but try to select something that won't cause a conflict with something else, as many characters have special meanings.
Previous Topic: Domain Index Full Scan
Next Topic: Error during Context Index Creation for a Table of 235 Million Records (2 threads merged by bb)
Goto Forum:
  


Current Time: Tue Sep 30 04:53:45 CDT 2014

Total time taken to generate the page: 0.13055 seconds