Noticeable difference between ANALYZE and DBMS_STATS

[Mladen Gogala]

Analyze cannot process virtual columns. I created a table like this:

CREATE TABLE "SCOTT"."TEST_EMP"
( "ENAME" VARCHAR2(20),
"HIREDATE" DATE,
"JOB" VARCHAR2(20),
"SAL" NUMBER,
"SALIND" NUMBER GENERATED ALWAYS AS
(CASE WHEN SAL BETWEEN 0 AND 1000 THEN 0
WHEN SAL BETWEEN 1000 AND 2000 THEN 1
WHEN SAL BETWEEN 2000 AND 3000) THEN 2
WHEN SAL >3000 THEN 3
ELSE -1
END) VIRTUAL
);

The table was populated like this:

SQL> insert into test_emp(ename,hiredate,job,sal)
2 select ename,hiredate,job,sal from emp;

14 rows created.

I also created an index called TEST_EMP_SALIND. The index, quite
expectedly, shows up as a function based index. Now, here is what happens
next:

SQL> analyze table test_emp delete statistics;

Table analyzed.

Elapsed: 00:00:00.02
SQL> select table_name,column_name
2 from user_tab_histograms
3 where table_name='TEST_EMP';

no rows selected

Elapsed: 00:00:00.03
SQL> save /tmp/2
Created file /tmp/2.sql
SQL> analyze table test_emp
2 compute statistics
3 for all indexed columns size 254;

Table analyzed.

Elapsed: 00:00:00.01
SQL> @/tmp/2

no rows selected

Elapsed: 00:00:00.01
SQL> get /tmp/1
1 begin
2 dbms_stats.gather_table_stats(
3 ownname=>user,
4 tabname=>'TEST_EMP',
5 method_opt => 'FOR ALL INDEXED COLUMNS SIZE 254');
6* end;
SQL> /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.08
SQL> @/tmp/2

TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
TEST_EMP
SALIND

TEST_EMP
SALIND

TEST_EMP
SALIND

TEST_EMP
SALIND


Elapsed: 00:00:00.00
SQL>


In other words, ANALYZE was unable to gather statistics on the virtual
column. DBMS_STATS did it without a problem. That is to be expected,
after all, ANALYZE is a bit older than the virtual columns.

--
http://mgogala.byethost5.com