Fwd: index doesn't contain duplicates

From: Nigel Thomas <nigel.cl.thomas_at_googlemail.com>
Date: Tue, 9 Jun 2009 17:29:45 +0100
Message-ID: <53258cd50906090929l3d6f5624ibaee321712827974_at_mail.gmail.com>

(CC to list)


  • 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?

Received on Tue Jun 09 2009 - 11:29:45 CDT

Original text of this message