RE: Misbehaving select

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 5 Nov 2013 08:00:19 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901DC829E_at_exmbx05.thus.corp>


Is it a hash cluster or an index cluster ?

What is the cluster key ?

What's the index definition for the index being used

Can you report the actual execution plan - including predicate section.

Regards

Jonathan Lewis



From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Jack van Zanen [jack_at_vanzanen.com] Sent: 05 November 2013 00:56
To: oracle-l_at_freelists.org
Subject: Misbehaving select

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 Received on Tue Nov 05 2013 - 09:00:19 CET

Original text of this message