Oracle 11g R1中的统计收集增强(1)(2)

DECLARE
  l_cg_name VARCHAR2(30);
BEGIN
  -- Explicitly created.
  l_cg_name := DBMS_STATS.create_extended_stats(ownname   => 'SCOTT',
                                                tabname   => 'EMP',
                                                extension => '(LOWER(ENAME))');

  -- Implicitly created.
  DBMS_STATS.gather_table_stats(
    'SCOTT',
    'EMP',
    method_opt => 'for columns (upper(ename))');
END;
/

BEGIN
  DBMS_STATS.gather_table_stats(
    'SCOTT',
    'EMP',
    method_opt => 'for all columns size auto');
END;
/

COLUMN extension FORMAT A30

SELECT extension_name, extension
FROM   dba_stat_extensions
WHERE  table_name = 'EMP';


EXTENSION_NAME                 EXTENSION
------------------------------ ------------------------------
SYS_STU3VG629OEYG6FN0EKTGV_HQ6 ("JOB","DEPTNO")
SYS_STULPA1A#B6YL4KQ59DQO3OADQ ("JOB","MGR")
SYS_STU2JLSDWQAFJHQST7$QK81_YB (LOWER("ENAME"))
SYS_STUOK75YSL165W#_X8GUYL0A1X (UPPER("ENAME"))

4 rows selected.

SQL>


COLUMN col_group FORMAT A30

SELECT e.extension col_group,
       t.num_distinct,
       t.histogram
FROM   dba_stat_extensions e
       JOIN dba_tab_col_statistics t ON e.extension_name=t.column_name
AND    t.table_name = 'EMP';

COL_GROUP                      NUM_DISTINCT HISTOGRAM
------------------------------ ------------ ---------------
("JOB","DEPTNO")                          9 NONE
("JOB","MGR")                             8 NONE
(LOWER("ENAME"))                         14 NONE
(UPPER("ENAME"))                         14 NONE

4 rows selected.

SQL>

-- Drop the columnn group.
BEGIN
  dbms_stats.drop_extended_stats(ownname   => 'SCOTT',
                                 tabname   => 'EMP',
                                 extension => '(UPPER(ENAME))');
END;
/

PL/SQL procedure successfully completed.

SQL>