Distinct values

From: Mladen Gogala <no_at_email.here.invalid>
Date: Wed, 4 Aug 2010 15:44:06 +0000 (UTC)
Message-ID: <pan.2010.08.04.15.44.06_at_email.here.invalid>

I have recently had a conversation with an Oracle support engineer who told me that, when analyzing VARCHAR2 columns, Oracle only counts the first 32 characters. I checked the statement and it is true:

SQL> create table test1(col varchar2(40));

Table created.

Elapsed: 00:00:00.07
SQL> insert into test1 values('01235678901234567890123456789012A');

1 row created.

Elapsed: 00:00:00.07
SQL> insert into test1 values('01235678901234567890123456789012B');

1 row created.

Elapsed: 00:00:00.08
SQL> commit;

Commit complete.

Elapsed: 00:00:00.06
SQL> analyze table test1 compute statistics   2 for table for all columns size 254;

Table analyzed.

Elapsed: 00:00:00.07
SQL> select column_name,num_distinct from user_tab_columns   2 where table_name='TEST1';

COLUMN_NAME NUM_DISTINCT

------------------------------ ------------
COL					  1

Elapsed: 00:00:00.11
SQL> In other words, if you are storing FS paths into the database and analyzing the table, stats will show significantly smaller number of the distinct values than there really are. Queries may be messed up because of that. I tested on 10.2.0.5 and 11.2.0.1, the behavior is the same. Did anybody else notice this?

-- 
http://mgogala.byethost5.com

Received on Wed Aug 04 2010 - 10:44:06 CDT