[20230808]建立修改索引统计信息modind.sql脚本.txt

--//以前建立过修改表统计信息以及字段统计信息的脚本,参考链接:
--//[20221219]建立修改表字段统计信息modcol.sql脚本.txt => http://blog.itpub.net/267265/viewspace-2932710/
--//[20221219]建立修改表统计信息modtab.sql脚本.txt => http://blog.itpub.net/267265/viewspace-2932708/

SCOTT@test01p> @ desc_proc sys dbms_stats set_index_stats
INPUT OWNER PACKAGE_NAME OBJECT_NAME
sample : @desc_proc sys dbms_stats gather_%_stats

OWNER PACKAGE_NAME OBJECT_NAME     SEQUENCE ARGUMENT_NAME   DATA_TYPE       IN_OUT DEFAULTED
----- ------------ --------------- -------- --------------- --------------- ------ ---------
SYS   DBMS_STATS   SET_INDEX_STATS        1 OWNNAME         VARCHAR2        IN     N
                                          2 INDNAME         VARCHAR2        IN     N
                                          3 PARTNAME        VARCHAR2        IN     Y
                                          4 STATTAB         VARCHAR2        IN     Y
                                          5 STATID          VARCHAR2        IN     Y
                                          6 NUMROWS         NUMBER          IN     Y
                                          7 NUMLBLKS        NUMBER          IN     Y
                                          8 NUMDIST         NUMBER          IN     Y
                                          9 AVGLBLK         NUMBER          IN     Y
                                         10 AVGDBLK         NUMBER          IN     Y
                                         11 CLSTFCT         NUMBER          IN     Y
                                         12 INDLEVEL        NUMBER          IN     Y
                                         13 FLAGS           NUMBER          IN     Y
                                         14 STATOWN         VARCHAR2        IN     Y
                                         15 NO_INVALIDATE   PL/SQL BOOLEAN  IN     Y
                                         16 GUESSQ          NUMBER          IN     Y
                                         17 CACHEDBLK       NUMBER          IN     Y
                                         18 CACHEHIT        NUMBER          IN     Y
                                         19 FORCE           PL/SQL BOOLEAN  IN     Y
                                          1 OWNNAME         VARCHAR2        IN     N
                                          2 INDNAME         VARCHAR2        IN     N
                                          3 PARTNAME        VARCHAR2        IN     Y
                                          4 STATTAB         VARCHAR2        IN     Y
                                          5 STATID          VARCHAR2        IN     Y
                                          6 EXT_STATS       RAW             IN     N
                                          7 STATTYPOWN      VARCHAR2        IN     Y
                                          8 STATTYPNAME     VARCHAR2        IN     Y
                                          9 STATOWN         VARCHAR2        IN     Y
                                         10 NO_INVALIDATE   PL/SQL BOOLEAN  IN     Y
                                         11 FORCE           PL/SQL BOOLEAN  IN     Y
30 rows selected.


--//忘记建立1个修改索引统计信息的脚本,建立如下:

$ cat modind.sql
set verify off
set termout off

column v_owner new_value v_owner
column v_index new_value v_index
select
 upper(CASE
                    WHEN INSTR('&1','.') > 0 THEN
                        SUBSTR('&1',INSTR('&1','.')+1)
                    ELSE
                        '&1'
                    END
                     )  v_index,
 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.index_name index_stat_attribute value
prompt
prompt index_stat_attribute = numrows numlblks numdist avglblk avgdblk clstfct indlevel
prompt
prompt @desc_proc sys dbms_stats set_index_stats
prompt
prompt exec dbms_stats.set_index_stats('&v_owner','&v_index',&2=>&3,NO_INVALIDATE=>false,force=>true);;
prompt
pause press enter to continue ...
exec dbms_stats.set_index_stats('&v_owner','&v_index',&2=>&3,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> create table empx as select * from emp;
Table created.

SCOTT@test01p> @ pk empx empno
create unique index pk_empx on t1 (empno);
alter table empx add constraint pk_empx primary key (empno);

-- alter table empx modify constraint pk_empx disable;
-- alter table empx drop constraint PK_empx;
-- drop index pk_empx;
Index created.
Table altered.

SCOTT@test01p> @ ind pk_empx
Display indexes where table or index name matches %pk_empx%...
TABLE_OWNER TABLE_NAME INDEX_NAME POS# COLUMN_NAME DSC
----------- ---------- ---------- ---- ----------- ----
SCOTT       EMPX       PK_EMPX       1 EMPNO

INDEX_OWNER TABLE_NAME INDEX_NAME IDXTYPE    UNIQ STATUS PART TEMP  H LFBLKS NDK   NUM_ROWS  CLUF LAST_ANALYZED       DEGREE VISIBILIT
----------- ---------- ---------- ---------- ---- ------ ---- ---- -- ------ --- ---------- ----- ------------------- ------ ---------
SCOTT       EMPX       PK_EMPX    NORMAL     YES  VALID  NO   N     1      1  14         14     1 2023-08-18 21:09:29 1      VISIBLE

SCOTT@test01p> @ modind pk_empx indlevel 1
input argument list : owner.index_name index_stat_attribute value
index_stat_attribute = numrows numlblks numdist avglblk avgdblk clstfct indlevel
@desc_proc sys dbms_stats set_index_stats
exec dbms_stats.set_index_stats('SCOTT','PK_EMPX',indlevel=>2,NO_INVALIDATE=>false,force=>true);
press enter to continue ...
PL/SQL procedure successfully completed.

SCOTT@test01p> @ ind pk_empx
Display indexes where table or index name matches %pk_empx%...
TABLE_OWNER TABLE_NAME INDEX_NAME POS# COLUMN_NAME DSC
----------- ---------- ---------- ---- ----------- ----
SCOTT       EMPX       PK_EMPX       1 EMPNO

INDEX_OWNER TABLE_NAME INDEX_NAME IDXTYPE    UNIQ STATUS PART TEMP  H LFBLKS NDK   NUM_ROWS CLUF LAST_ANALYZED       DEGREE VISIBILIT
----------- ---------- ---------- ---------- ---- ------ ---- ---- -- ------ --- ---------- ---- ------------------- ------ ---------
SCOTT       EMPX       PK_EMPX    NORMAL     YES  VALID  NO   N     2      1  14         14    1 2023-08-18 21:12:32 1      VISIBLE
--//High=2. 索引高度比indlevel大1。

SCOTT@test01p> @ modind pk_empx numrows 100
input argument list : owner.index_name index_stat_attribute value
index_stat_attribute = numrows numlblks numdist avglblk avgdblk clstfct indlevel
@desc_proc sys dbms_stats set_index_stats
exec dbms_stats.set_index_stats('SCOTT','PK_EMPX',numrows=>100,NO_INVALIDATE=>false,force=>true);
press enter to continue ...
PL/SQL procedure successfully completed.


SCOTT@test01p> @ ind pk_empx
Display indexes where table or index name matches %pk_empx%...
TABLE_OWNER TABLE_NAME INDEX_NAME POS# COLUMN_NAME DSC
----------- ---------- ---------- ---- ----------- ----
SCOTT       EMPX       PK_EMPX       1 EMPNO

INDEX_OWNER TABLE_NAME INDEX_NAME IDXTYPE    UNIQ STATUS PART TEMP  H LFBLKS NDK   NUM_ROWS       CLUF LAST_ANALYZED       DEGREE VISIBILIT
----------- ---------- ---------- ---------- ---- ------ ---- ---- -- ------ --- ---------- ---------- ------------------- ------ ---------
SCOTT       EMPX       PK_EMPX    NORMAL     YES  VALID  NO   N     2      1  14        100          1 2023-08-18 21:18:07 1      VISIBLE

--//NUM_ROWS=100.
--//当然我估计修改索引统计信息的机会很少，我估计最大的可能用来修改调整群集因子clstfct。