Distinct values
in [Oracle]
|
From: Mladen Gogala on 4 Aug 2010 11:44 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
From: Michel Cadot on 4 Aug 2010 12:24 "Mladen Gogala" <no(a)email.here.invalid> a �crit dans le message de news: pan.2010.08.04.15.44.06(a)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: | <...> | | -- | http://mgogala.byethost5.com This has always been true in any version since CBO was introduced. I even wonder if it was not less in the previous versions but I can't remember. Regards Michel
From: Tim X on 5 Aug 2010 21:02 Mladen Gogala <no(a)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
|
Pages: 1 Prev: Starting Oracle Net Listener...forever!? Oracle XE Next: OCI protocol specification, where? |