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

[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。