Re: gather stats

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Wed, 11 Nov 2015 01:34:47 -0500
Message-ID: <5642E187.4020405_at_gmail.com>

On 11/09/2015 01:54 PM, Jonathan Lewis wrote:
> I think "for all hidden columns size 254" might be implied by "for all columns size 254", but the way - that might only be true for relative new versions of Oracle, of course.
You are right, it is implied. I created a function based index like this:

SQL> create index scott.testind on emp (mod(sal,2))

   2 /

Index created.

After that, I analyzed the table emp:

SQL> exec
dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP',method_opt=>'FOR ALL COLUMNS SIZE 20'); PL/SQL procedure successfully completed.

After that, I checked the results:

SQL> column column_name format a20
SQL> select column_name,count(*) from dba_histograms

   2 where owner='SCOTT' and
   3 table_name='EMP'
   4 group by column_name;

COLUMN_NAME COUNT(*)

-------------------- ----------
SAL                 12
SYS_NC00009$              2
HIREDATE             13
MGR                  6
COMM                  4
DEPTNO                  3
ENAME                 14
EMPNO                 14
JOB                  5

9 rows selected.

And yes, histograms on the new virtual column are there.

-- 
Mladen Gogala
Oracle DBA
http://mgogala.freehostia.com


--
http://www.freelists.org/webpage/oracle-l

Received on Wed Nov 11 2015 - 07:34:47 CET