Jack, If you have the same SQL, different execution Plan, and different results, then most probably you are hitting a wrong results bug in some of the query transformations. I suggest you open an SR with Support and provide a Test Case to reproduce your issue. Cheers, Carlos Sierra blog: carlos-sierra.net twitter: @csierra_usa Life's Good! On Nov 4, 2013, at 7:56 PM, Jack van Zanen <jack@xxxxxxxxxxxx> wrote: > Hi All, > > > Oracle 10.2.0.5 > AIX 6.1 > > We have this select statement on a table that is in a cluster by itself and > has an index on it. > > > if we run the following version of our select it uses the index and returns 2 > rows > > SELECT distinct(m.channel_number) > FROM table_x m > where m.channel_number=503 > and m.metering_system_sid=53799 > AND m.gmt_day = to_date('10-09-2013','dd-mm-yyyy') > AND m.VERSION = 1 > > 501 <---- wrong result as we specifically wan t only 503 > 503 > > > If we force it to use full table scan it returns 1 row > > SELECT /*+ full(m) parallel(m 8) */ distinct(m.channel_number) > FROM table_x m > where m.channel_number=503 > and m.metering_system_sid=53799 > AND m.gmt_day = to_date('10-09-2013','dd-mm-yyyy') > AND m.VERSION = 1 > > 503 > > Which is correct. this assumes something is not quite right with index, > correct? > > > We have dropped and recreated the index but that was no joy. > I have tried to find something on metalink that would point to possible bug, > but my searches have not come up with anything worthwhile. > > Can anybody else shed some light on this > > > > > Jack van Zanen > > ------------------------- > This e-mail and any attachments may contain confidential material for the > sole use of the intended recipient. If you are not the intended recipient, > please be aware that any disclosure, copying, distribution or use of this > e-mail or any attachment is prohibited. If you have received this e-mail in > error, please contact the sender and delete all copies. > Thank you for your cooperation