Re: index doesn't contain duplicates

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Tue, 9 Jun 2009 19:19:20 +0100
Message-ID: <7765c8970906091119y3eac029axa2fe3a7294fca849_at_mail.gmail.com>

also what the index definition is, and if there are any non-standard parameters in the execution environment, like CURSOR_SHARING etc.

Niall

On Tue, Jun 9, 2009 at 5:29 PM, Nigel Thomas <nigel.cl.thomas_at_googlemail.com
> wrote:

> (CC to list)
>
>
> Josh
>
>
> - Does your query include ANY other predicates (other than WHERE
> columnX = 'nondup value')?
> - Do you get the right results if you use an index range scan (or full
> table scan)?
> - What index is being used for the FFS - what other columns are being
> used in the index? Are any of those columns nullable? Datatypes?
> - Are you absolutely sure that you are using the FFS?
>
>
> Please post query, Oracle version, any patches applied, platform, etc etc
> (someone's bound to ask sooner or later, and it may be relevant if this is a
> bug), plus enough definition of the table, significant columns and indexes.
>
>
> Regards Nigel
>
> 2009/6/9 Josh Collier <Josh.Collier_at_banfield.net>
>
>> I have an table that has duplicate values for a column. I have a
>> non-unique index on this column. When I run a query that uses a fast full
>> scan to retrieve the rows, it only retrieves one of the two rows. When I
>> force a full tablescan both of the rows come back. I have used all my sql
>> tricks to try my best to determine if the value has trailining or leading
>> non-printable characters and it doesn’t appear so. I have also moved the
>> values into a varchar 11 field in another table, the value is 11 chars long.
>> Both of the duplicate rows where accepted. Does anyone know of a bug or a
>> reason why both of the rows wouldn’t appear in the index?
>>
>>
>
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

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

Received on Tue Jun 09 2009 - 13:19:20 CDT