The defects of using the skewonly parameter to collect histograms under 11G have been fixed, 11.
We have previously verified the defect of using the skewonly parameter to collect histograms in 10 GB and fixed it in 11 GB. The histogram is becoming more and more important in large data volumes. If not, the execution plan is likely to be inaccurate.
SQL> select * from v $ version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production
PL/SQL Release 11.2.0.1.0-Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0-Production
NLSRTL Version 11.2.0.1.0-Production
SQL> drop table test purge;
SQL> create table test
(
Id varchar2 (60) primary key,
Name varchar2 (100)
);
SQL> insert into test
Select rpad ('A', 40, 'A') | to_char (rownum), object_name
From dba_objects;
You have created 69617 rows.
SQL> commit;
SQL> select count (1), count (distinct id), count (distinct name) from test;
COUNT (1) COUNT (DISTINCTID) COUNT (DISTINCTNAME)
----------------------------------------------
69617 69617 41622
SQL> exec dbms_stats.gather_table_stats (user, 'test', METHOD_OPT => 'for all columns size skewonly ');
SQL> select COLUMN_NAME, NUM_DISTINCT from user_tab_columns where TABLE_NAME = 'test ';
COLUMN_NAME NUM_DISTINCT
------------------------------------------
ID 69617.
NAME 40904
SQL> exec dbms_stats.gather_table_stats (user, 'test', METHOD_OPT => 'for all columns size auto ');
SQL> select COLUMN_NAME, NUM_DISTINCT from user_tab_columns where TABLE_NAME = 'test ';
COLUMN_NAME NUM_DISTINCT
------------------------------------------
ID 69617.
NAME 40904
We can see that the two collections are accurate.
