[SOLVED] Counting Hits with a Structured Predicate containing LIKE in Oracle returns wrong results

Counting Hits with a Structured Predicate containing LIKE in Oracle returns wrong results

I'm trying to use Oracle Text to perform a query where i'm searching for any OS name that starts with "AIX" and also contains the substring 'XYZ'. Somehow this formulation of the query results in 0 results, even though if I break it up into separate parts there are clearly results:

SELECT 
  COUNT(*) AS cnt
FROM 
  package_master 
WHERE 
  CONTAINS(doc,'%XYZ%',1)>0 AND UPPER(os) LIKE 'AIX%'

This returns 0 results.

But curiously if I modify it to:

SELECT 
  COUNT(*) AS cnt
FROM 
  package_master 
WHERE 
  CONTAINS(doc,'%XYZ%',1)>0 AND UPPER(os)='AIX 6.1.0.0'

it returns results, but of course only those that pertain to AIX 6.1.0.0...

I'm using Oracle 11g2.

Is it possible there is a bug in the ORACLE TEXT package?

I guess I can break into two INTERSECT queries and do a COUNT(*) of the results, but that complicates matters and seems to run for a long while... I would like to use the simple 'AND' form.... If possible...

This works but runs for a long while and is unnecessarily complex:

SELECT count(*) FROM (
SELECT 
  host, package_name
FROM 
  package_master 
WHERE 
  CONTAINS(doc,'%XYZ%',1)>0
INTERSECT 
SELECT 
  host, package_name
FROM 
  package_master 
WHERE 
  UPPER(os) LIKE 'AIX%'
)

Also note if I try to do an EXPLAIN on the original query, it's as though the "LIKE" portion of the query is not even executed at all...! This is rather bizarre:

Plan hash value: 1075233541


    ----------------------------------------------------------------------------------------
    | Id  | Operation        | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT |                     |     1 |   238 |    55   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE  |                     |     1 |   238 |            |          |
    |*  2 |   DOMAIN INDEX   | PACKAGE_MASTER_IDX7 |   100 | 23800 |    55   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------

    -


    Predicate Information (identified by operation id):
    ---------------------------------------------------

       2 - access("CTXSYS"."CONTAINS"("DOC",'%XYZ%',1)>0)
           filter(UPPER("OS") LIKE 'AIX%')

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit. NLS_COMP value is BINARY, NLS_SORT value is null. The table is only loaded once so it is not an issue with synching the index.

Solution

In a bizarre twist, I am no longer seeing this issue! I cannot reproduce the 0 result behavior and now if I perform an EXPLAIN plan, I see that the optimizer is working properly. Not much has changed. Maybe Oracle just needed a restart... I guess I will close out this question, even though there was no satisfactory reason/explanation as to how the issue resolved itself.