SQL & PL/SQL

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 load.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.