Re: Misbehaving select

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: _at_csierra_usa

Life's Good!

On Nov 4, 2013, at 7:56 PM, Jack van Zanen <jack_at_vanzanen.com> 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

--
http://www.freelists.org/webpage/oracle-l