Re: index doesn't contain duplicates
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: nigel.cl.thomas@xxxxxxxxxxxxxx
- Date: Tue, 9 Jun 2009 19:19:20 +0100
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@xxxxxxxxxxxxxx
> 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@xxxxxxxxxxxx>
>
>> 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
Other related posts:
