Oracle is not using a unique index for a long key

Question

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);. You can even see that the index names are consecutive. 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. (The original table has been shrinked since the last big deletion, and there hasn't been a single delete after that.)

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.):

(In this example, there are 1867 rows that start with the identifier that is black brushed. A 2-row query produces a cardinality of 1867*2, a 3-row query produces a cardinality of 1867*3, etc. Can't be a coincidence, Oracle seems to not care about the end of the identifiers.)

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

USER_TABLES: https://i.stack.imgur.com/nDWze.jpg USER_INDEXES: https://i.stack.imgur.com/DG9um.jpg I only changed the schema and tablespace name. You can see that the table and index names are the same as on the query plan screenshot.

Answer 1

(This answers the other question about why the histograms are different.)

Histograms are created by default based on column skew and whether the column was used in a relevant predicate. Copying the DDL and the data is not enough, the workload information is also important.

According to the Performance Tuning Guide:

When you drop a table, workload information used by the auto-histogram gathering feature and saved statistics history used by the RESTORE_*_STATS procedures is lost. Without this data, these features do not function properly.

For example, here is a table with skewed data but no histogram:

drop table test1;
create table test1(a date);
insert into test1 select date '2000-01-01'+level from dual connect by level <= 10;
insert into test1 select date '2000-01-01' from dual connect by level <= 1000;
begin
    dbms_stats.gather_table_stats(user, 'TEST1');
end;
/
select histogram from user_tab_columns where table_name = 'TEST1';

HISTOGRAM
---------
NONE

Running the same thing, but with a query before the statistics are gathered, will generate a histogram.

drop table test1;
create table test1(a date);
insert into test1 select date '2000-01-01'+level from dual connect by level <= 10;
insert into test1 select date '2000-01-01' from dual connect by level <= 1000;
select count(*) from test1 where a = sysdate; --Only new line
begin
    dbms_stats.gather_table_stats(user, 'TEST1');
end;
/
select histogram from user_tab_columns where table_name = 'TEST1';

HISTOGRAM
---------
FREQUENCY

Answer 2

I found the solution! It is so beautiful and I actually learned a LOT about Oracle.

In one word: histograms.

I started reading a lot about how Oracle's CBO works and I stumbled upon histograms. I didn't fully understand so I took a look at the USER_HISTOGRAMS table, and voilá. There were several rows for the sick table, and practically nothing for the cloned table. For the sick table, there was one row for each of the 8 different identifier-starting-parts. And this is the key: they were cut off at 32 characters, before the @ sign. As I said, the first part of keys is highly repetitive, they become different after the @ sign.

It seems that histograms can be more powerful than the simple fact that a unique index always has a cardinality of 0 or 1 for a given value. When I was querying for 2+ rows, Oracle looked at the histogram, it thought that there could be tens of thousands of values for that identifier-starting-part, and it threw the CBO off course.

I deleted the histograms for that column in the old table and the problem went away!

More reading: https://blogs.oracle.com/optimizer/entry/how_do_i_drop_an_existing_histogram_on_a_column_and_stop_the_auto_stats_gathering_job_from_creating

Answer 3

I emailed Jonathan Lewis about this and got a very helpful reply:

The oddity in the calculation is a consequence of the limits on character-based histograms, see particularly:

http://jonathanlewis.wordpress.com/2010/10/13/frequency-histogram-5/ http://jonathanlewis.wordpress.com/2010/10/19/frequency-histograms-6/

Looking at the example, the query is for an IN list, not for a single row, so my initial guess would be that the optimizer has used a generic strategy for calculating multi-row selectivity rather than having a special case piece of code for an IN list on a primary key. I guess it wouldn't be too hard for them to recognise this case, but the developers have probably not considered it worth the effort.

I highly recommended reading the blog posts he links, they describe in detail the limitation of histograms you are running in to, eg:

Conclusion: If you have fairly long, and similar, strings in a column that is a good candidate for a frequency histogram (e.g. a very descriptive status column) then you have a problem if a value that is very rare looks identical to a very popular value up to the first 32 characters. You may find that the only solution is to change the list of legal values (although various strategies involving virtual columns or function-based indexes can bypass the problem).