Re: gather stats
- From: Mladen Gogala <gogala.mladen@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 11 Nov 2015 01:34:47 -0500
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
Other related posts: