I have a table with 200K rows in my test database (few hundred millions in production). A table has an nvarchar2(50) string identifier with a unique index on it (it's not the PK). Some identifiers are very similar, like 'aaaaaaaaaaaaaaaaaaaa@10' and 'aaaaaaaaaaaaaaaaaaaa@20'. If I want to query more than one row based on these unique identifiers, the index is not used. I think it is because a long part of the keys are usually similar in the beginning and only differ in the last two characters. My investigation follows.

When I do a select based on a single identifier value (`select * from tab where id = 'xxxxxxxxxxx1'`), an index scan and a full table scan look like this:

    Description                       Object owner  Object name  Cost  Cardinality  Bytes
    SELECT STATEMENT, GOAL = ALL_ROWS                            3     1            72
     TABLE ACCESS BY INDEX ROWID      XXXX_DEV      TABLENAME    3     1            72
      INDEX UNIQUE SCAN               XXXX_DEV      UX_IDENT     2     1
    
    Description                       Object owner  Object name  Cost  Cardinality  Bytes
    SELECT STATEMENT, GOAL = ALL_ROWS                            653   1867         134424
     TABLE ACCESS FULL                XXXX_DEV      TABLENAME    653   1867         134424

With the index scan, the cardinality is 1, it has a low cost, so it is used, perfect.

When I do a select based on two identifiers with an IN expression (`select * from tab where id in ('xxxxxxxxxxx1', 'xxxxxxxxxxx2')`), an index scan and a full table scan look like this:

    Description                       Object owner  Object name  Cost  Cardinality  Bytes
    SELECT STATEMENT, GOAL = ALL_ROWS                            784   3734         268848
     INLIST ITERATOR
      TABLE ACCESS BY INDEX ROWID     XXXX_DEV      TABLENAME    784   3734         268848
       INDEX UNIQUE SCAN              XXXX_DEV      UX_IDENT     46    3734 
    
    Description                       Object owner  Object name  Cost  Cardinality  Bytes
    SELECT STATEMENT, GOAL = ALL_ROWS                            654   3734         268848
     TABLE ACCESS FULL                XXXX_DEV      TABLENAME    654   3734         268848

The index scan computations blow up and full table scan is used instead. In reality, the indexed query is still an order of magnitude faster, so I am forced to use index hints. When I add more identifiers into a single query, the indexed computations keep increasing drastically.

Statistics are up-to-date without estimation, 100% sample size (`  DBMS_STATS.GATHER_SCHEMA_STATS('schemaname',estimate_percent=>100,cascade=>true)`).

To me, it seems simple: there is a unique index, so every identifier can have a cardinality of at most 1. Oracle is acting totally crazy instead. What could be the problem?

Oracle version is: 11.2.0.1.0