Re: Distinct values

From: Tim X <timx_at_nospam.dev.null>
Date: Fri, 06 Aug 2010 11:02:47 +1000
Message-ID: <87hbj8a6yw.fsf_at_puma.rapttech.com.au>

Mladen Gogala <no_at_email.here.invalid> writes:

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

Thanks for this. As soon as I say your post, I rememberd this fact from way back when I was first learning about CBO. It is one of those important points that is so easily forgotten. I can't even remember where I read about it, but think it was buried in some Oracle docs somewhere and then later totally forgotten.

Tim

-- 
tcross (at) rapttech dot com dot au

Received on Thu Aug 05 2010 - 20:02:47 CDT