I have a table with 250K rows in my test database. (There are a few hundred millions in production, we can observe the same issue there.) The table has an nvarchar2(50) string identifier, not null, with a unique index on it (it's not the PK).

The identifiers are made up of a first part that has 8 different values in my test database (and about a thousand in production), then an @ sign, and finally a number, 1 to 6 digits long. For example there could be 50 thousand rows that start with 'ABCD_BGX1741F_2006_13_20110808.xml@', and it is followed by 50 thousand different numbers.

When I query for a single row based on its identifier, the cardinality is estimated as 1, the cost is very low, it works fine. When I query for more than one row with several identifiers in an IN expression or an OR expression, the estimations for the index are completely wrong, so a full table scan is used. If I force the index with a hint, it is very fast, the full table scan is actually executed an order of magnitude slower (and a lot more slower in production). So it is an optimizer problem.

As a test, I duplicated the table (in the same schema+tablespace) with the exact same DDL and exact same content. I recreated the unique index on the first table for good measure, and created the exact same index on the clone table. I did a `DBMS_STATS.GATHER_SCHEMA_STATS('schemaname',estimate_percent=>100,cascade=>true);`. So now the only difference between the two tables is that the first one was loaded in random order over a long time period, with blocks scattered on the disk (in a tablespace together with several other big tables), the second was loaded as one batched INSERT-SELECT. Other than that, I can't imagine any difference.

Here are query plans for the sick and the clone table (The strings under the black brush are the same all over the picture, and also under they gray brush.):

![Screenshot][1]

What could cause this behavior? Obviously it would be pretty expensive to recreate the table in production.

  [1]: https://i.stack.imgur.com/g5zbZ.jpg