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

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

--//最近經常要修改表欄位統計資訊,需要一個修改表欄位的統計資訊的指令碼,建立如下:

$ cat modcol.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 input argument list : owner.table_name column_name column_stat_attribute value
prompt
prompt column_stat_attribute = density distcnt nullcnt avgclen
prompt
prompt @desc_proc sys dbms_stats set_column_stats
prompt
prompt exec dbms_stats.SET_COLUMN_STATS('&v_owner','&v_table','&2',&3=>&4,NO_INVALIDATE=>false,force=>true);;
prompt
pause press enter to continue ...
exec dbms_stats.SET_COLUMN_STATS('&v_owner','&v_table','&2',&3=>&4,NO_INVALIDATE=>false,force=>true);
prompt

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> @ tab_lh scott empx  ''

DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION.
INPUT   OWNER TABLE_NAME COLUMN
SAMPLE  : @ TAB_LH TABLE_NAME [COLUMN_NAME]
IF NOT INPUT COLUMN_NAME ,USE "" .

COLUMN_NAME DATA_TYPE     DATA_LENGTH N NUM_DISTINCT    DENSITY SAMPLE_SIZE Low_value  High_value  NUM_NULLS NUM_BUCKETS LAST_ANALYZED       HISTOGRAM       DATA_DEFAULT
----------- ------------- ----------- - ------------ ---------- ----------- ---------- ----------- --------- ----------- ------------------- --------------- --------------------
EMPNO       NUMBER                 22 Y           14 .071428571          14 7369       7934                0           1 2022-10-10 20:40:34 NONE
ENAME       VARCHAR2               10 Y           14 .071428571          14 ADAMS      WARD                0           1 2022-10-10 20:40:34 NONE
JOB         VARCHAR2                9 Y            5         .2          14 ANALYST    SALESMAN            0           1 2022-10-10 20:40:34 NONE
MGR         NUMBER                 22 Y            6 .166666667          13 7566       7902                1           1 2022-10-10 20:40:34 NONE
HIREDATE    TIMESTAMP(6)           11 Y           13 .076923077          14                                0           1 2022-10-10 20:40:34 NONE
SAL         NUMBER                 22 Y           12 .083333333          14 800        5000                0           1 2022-10-10 20:40:34 NONE
COMM        NUMBER                 22 Y            4        .25           4 0          1400               10           1 2022-10-10 20:40:34 NONE
DEPTNO      NUMBER                 22 Y            3 .333333333          14 10         30                  0           1 2022-10-10 20:40:34 NONE
8 rows selected.

SCOTT@test01p> @ modcol scott.empx comm nullcnt 9
input argument list : owner.table_name column_name column_stat_attribute value
column_stat_attribute = density distcnt nullcnt avgclen
@desc_proc sys dbms_stats set_column_stats
exec dbms_stats.SET_COLUMN_STATS('SCOTT','EMPX','comm',nullcnt=>9,NO_INVALIDATE=>false,force=>true);
press enter to continue ...
PL/SQL procedure successfully completed.

SCOTT@test01p> @ modcol scott.empx comm density 0.251
input argument list : owner.table_name column_name column_stat_attribute value
column_stat_attribute = density distcnt nullcnt avgclen
@desc_proc sys dbms_stats set_column_stats
exec dbms_stats.SET_COLUMN_STATS('SCOTT','EMPX','comm',density=>0.251,NO_INVALIDATE=>false,force=>true);
press enter to continue ...
PL/SQL procedure successfully completed.

SCOTT@test01p> @ tab_lh scott empx  comm

DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION.
INPUT   OWNER TABLE_NAME COLUMN
SAMPLE  : @ TAB_LH TABLE_NAME [COLUMN_NAME]
IF NOT INPUT COLUMN_NAME ,USE "" .
COLUMN_NAME DATA_TYPE DATA_LENGTH N NUM_DISTINCT    DENSITY SAMPLE_SIZE Low_value High_value NUM_NULLS NUM_BUCKETS LAST_ANALYZED       HISTOGRAM       DATA_DEFAULT
----------- --------- ----------- - ------------ ---------- ----------- --------- -------------------- ----------- ------------------- --------------- --------------------
COMM        NUMBER             22 Y            4       .251           4 0         1400               9           1 2023-01-17 21:56:12 NONE

--//ok!!