[20221216]建立修改表統計資訊minmaxtab.sql指令碼.txt

[20221216]建立修改表統計資訊minmaxtab.sql指令碼.txt

--//修改表欄位的最大最小為NULL.
$ cat minmaxtab.sql

set verify off
set termout off

column v_owner new_value v_owner
column v_table new_value v_table
select
 upper(CASE
                    WHEN INSTR('&1','.') > 0 THEN
                        SUBSTR('&1',INSTR('&1','.')+1)
                    ELSE
                        '&1'
                    END
                     )  v_table,
 nvl(upper(CASE WHEN INSTR('&1','.') > 0 THEN
            UPPER(SUBSTR('&1',1,INSTR('&1','.')-1))
        ELSE
            user
        END),user) v_owner
from dual;

set termout on


prompt
prompt modify table:&1 column=&2 stats min and max = NULL
prompt input argument list : owner.table_name column_name
prompt

DECLARE
   CURSOR c1
   IS
      SELECT *
        FROM all_tab_col_statistics
       WHERE     owner = upper('&&v_owner')
             AND table_name = upper('&&v_table')
             AND column_name = upper('&&2')
             AND histogram = 'NONE'
             AND last_analyzed IS NOT NULL;

   v_num_distinct   all_tab_col_statistics.num_distinct%TYPE;
   z_distcnt        NUMBER;
   z_density        NUMBER;
   z_nullcnt        NUMBER;
   z_srec           DBMS_STATS.statrec;
   z_avgclen        NUMBER;
BEGIN
   FOR r IN c1
   LOOP
      DBMS_STATS.get_column_stats
      (
         ownname   => r.owner
        ,tabname   => r.table_name
        ,colname   => r.column_name
        ,distcnt   => z_distcnt
        ,density   => z_density
        ,nullcnt   => z_nullcnt
        ,srec      => z_srec
        ,avgclen   => z_avgclen
      );

      DBMS_STATS.delete_column_stats
      (
         ownname         => r.owner
        ,tabname         => r.table_name
        ,colname         => r.column_name
        ,cascade_parts   => TRUE
        ,no_invalidate   => TRUE
        ,force           => TRUE
      );

      z_srec.minval := NULL;
      z_srec.maxval := NULL;

      IF r.num_distinct = 1
      THEN
         v_num_distinct := 1 + 1e-14;
      ELSE
         v_num_distinct := r.num_distinct;
      END IF;

      IF r.num_distinct <> 0
      THEN
         DBMS_STATS.set_column_stats
         (
            ownname         => r.owner
           ,tabname         => r.table_name
           ,colname         => r.column_name
           ,distcnt         => v_num_distinct
           ,density         => 1 / v_num_distinct
           ,nullcnt         => r.num_nulls
           ,srec            => z_srec               -- No HIGH_VALUE/LOW_VALUE
           ,avgclen         => r.avg_col_len
           ,no_invalidate   => FALSE
           ,force           => TRUE
         );
      END IF;
   END LOOP;
END;
/

1.環境:
SCOTT@test01p> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

2.測試:
SCOTT@test01p> @ descz scott.empx HIREDATE
eXtended describe of scott.empx

DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION.
INPUT   OWNER.TABLE_NAME  <filters>
SAMPLE  : @ desczz TABLE_NAME column_name1,column_name2
IF NOT INPUT <filters> ,USE "" or 1=1 or 1.

                                        Sample                                                                                   Distinct                    Number                  Number
Owner      Table_Name                     Size LAST_ANALYZED       Col# Column Name          Null?      Type                       Values        Density      Nulls HISTOGRAM       Buckets Low_value                                High_value
---------- -------------------- -------------- ------------------- ---- -------------------- ---------- -------------------- ------------ -------------- ---------- --------------- ------- ---------------------------------------- ----------------------------------------
SCOTT      EMPX                             14 2022-10-10 20:40:34    5 HIREDATE                        TIMESTAMP(6)(11)               13   .07692307692          0                       1 1972-10-22 20:39:27.                     2022-10-10 20:35:42.536000000


SCOTT@test01p> @ minmaxtab scott.empx hiredate
modify table:scott.empx column=hiredate stats min and max = NULL
input argument list : owner.table_name column_name

SCOTT@test01p> @ descz scott.empx HIREDATE
eXtended describe of scott.empx

DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION.
INPUT   OWNER.TABLE_NAME  <filters>
SAMPLE  : @ desczz TABLE_NAME column_name1,column_name2
IF NOT INPUT <filters> ,USE "" or 1=1 or 1.

                                        Sample                                                                                   Distinct                    Number                  Number
Owner      Table_Name                     Size LAST_ANALYZED       Col# Column Name          Null?      Type                       Values        Density      Nulls HISTOGRAM       Buckets Low_value                                High_value
---------- -------------------- -------------- ------------------- ---- -------------------- ---------- -------------------- ------------ -------------- ---------- --------------- ------- ---------------------------------------- ----------------------------------------
SCOTT      EMPX                                2023-01-17 22:08:06    5 HIREDATE                        TIMESTAMP(6)(11)               13   .07692307692          0                       1 -- ::.                                   -- ::.

--//已經清空了最大最小值.