Re: FOR ALL INDEXED COLUMNS SIZE 254

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Mon, 12 Sep 2011 22:32:39 +0000 (UTC)
Message-ID: <pan.2011.09.12.22.32.38_at_gmail.com>

On Mon, 12 Sep 2011 21:15:21 +0000, Mladen Gogala wrote:

> So, why is this a bane? The explanation on the page is less than clear.
>

BTW, there is something extremely stupid about this method: it collects histograms even for the the single column primary and unique keys, which is completely pointless because the distribution is given by the very definition of the primary/unique key:

SQL> begin
  2 dbms_stats.gather_schema_stats(
  3 ownname=>'SCOTT',
  4 method_opt => 'FOR TABLE FOR ALL INDEXED COLUMNS SIZE 254',   5 estimate_percent => NULL,
  6 cascade => true);
  7 end;
  8 /

PL/SQL procedure successfully completed.

SQL> select table_name,column_name,count(*)   2 from dba_histograms
  3 where owner='SCOTT'
  4 group by table_name,column_name;

TABLE_NAME COLUMN_NAME COUNT(*)

------------------------------ -------------------- ----------
DEPT			       DEPTNO			     4
EMP			       EMPNO			    14

Those histograms are completely unnecessary. Fortunately, there is a method called SET_TABLE_PREFS which can do something like this:

SQL> begin
  2 dbms_stats.set_table_prefs(
  3 ownname=>'SCOTT',
  4 tabname => 'EMP',
  5 pname => 'METHOD_OPT',
  6 pvalue => 'FOR ALL COLUMNS SIZE 1');   7 end;
  8 /

That tells the DBMS_STATS not to collect histograms for any of the columns of the table EMP:

SQL> exec dbms_stats.delete_schema_stats(ownname=>'SCOTT');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.13

SQL> begin
  2 dbms_stats.gather_schema_stats(
  3 ownname=>'SCOTT',
  4 cascade => true);
  5 end;
  6 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.65
SQL> select table_name,column_name,count(*)   2 from dba_histograms
  3 where owner='SCOTT'
  4 group by table_name,column_name;

TABLE_NAME COLUMN_NAME COUNT(*)

------------------------------ -------------------- ----------
EMP			       ENAME			     2
EMP			       SAL			     2
EMP			       DEPTNO			     2
DEPT			       DEPTNO			     4
EMP			       COMM			     2
EMP			       JOB			     2
EMP			       EMPNO			     2
EMP			       MGR			     2
EMP			       HIREDATE 		     2

9 rows selected.

Now all columns have histograms with size 2, which means that only minimum and maximum is collected. Observe that there is still a histogram of size 4 for the DEPTNO column. There should be an automatic preference not to gather histograms for the single column primary/unique keys.

-- 
http://mgogala.byethost5.com

Received on Mon Sep 12 2011 - 17:32:39 CDT