like operator error in 9.2.0.3

From: <si_bendoviNO_S_P_A_M_at_hotmail.com>
Date: 9 Nov 2004 19:43:38 -0800
Message-ID: <1100058218.310282.170790@f14g2000cwb.googlegroups.com>

Hello,

I am curious if anyone else seen this problem. Query with 7 tables joined with equijoin. Qualified using two strings, one of them with like operator:

name like 'ABCDEFG LLC%'

Query would return 50 rows or so. When changing condition to:

name like 'ABCDEFG LL%'

Query returned 0 rows. No error, just wrong result. Tried another database with similar data. It worked initially, but when playing with string by removing and adding characters it returned 0 rows again. Running statistics fixed problem, but it would reappear again I think. Statistics is gathered for all columns. Normally statistics is running

every week. Database is about 500 GB big on 64 bit Solaris. We have TAR
opened with Oracle, but they did not find anything, so far. Query with
same plan, but rule base optimizer does return data always. Database is

upgrade from 7.3 and there are no problems like this on 7.3. Query does have first_rows(100) hint and few other hints to mimic rule base plan to make sure it is not about plan.
Anyone seen anything like this? It did significantly shake my confidence in Oracle.

Thank you,

      Stan Received on Tue Nov 09 2004 - 21:43:38 CST