[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 -- ::.                                   -- ::. --//已经清空了最大最小值.