Noticeable difference between ANALYZE and DBMS_STATS

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Wed, 9 May 2012 02:00:33 +0000 (UTC)
Message-ID: <pan.2012.05.09.02.00.32_at_gmail.com>

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> _at_/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> _at_/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

Received on Tue May 08 2012 - 21:00:33 CDT