在"Index Rebuild, the Need vs the Implications [ID 989093.1]" 中明确指出：
Due to the above it is strongly advised not to rebuild indexes on a regular basis but instead use proper diagnostics. Especially avoid to rebuild index on a regular basis as there is a reason why the index becomes in this state over-and-over again.
Please see the following note that lists a script that can be used to analyze the index structure. It does not use the 'analyze index validate structure' command but is based on the current table and index statistics to estimate the index size.
这个里面提到的脚本就在"Script to investigate a b-tree index structure [ID 989186.1]" 中，但是遗憾的是上述脚本是wrap过的。
不过没关系，我们把它unwrap掉，可以看到里面的包index_util的核心过程inspect_index的代码是：
PROCEDURE INSPECT_INDEX (AIDXOWNER IN VARCHAR2, AIDXNAME IN VARCHAR2) IS
VIDXREC ALL_INDEXES%ROWTYPE;
VTABNUMROWS ALL_TABLES.NUM_ROWS%TYPE;
VNUMNULLS ALL_TAB_COLUMNS.NUM_NULLS%TYPE;
VBLKSIZE DBA_TABLESPACES.BLOCK_SIZE%TYPE;
VIDXOBJID ALL_OBJECTS.OBJECT_ID%TYPE;
VUNIQIND NUMBER(2) := 0;
VAVGIDXROWLEN NUMBER := 0;
VAVGROWSPERBLK NUMBER := 0;
VESTSIZE NUMBER := 0;
VSQLSTR VARCHAR2(512) := '';
VBLKCNT NUMBER := 0;
VROWCNT NUMBER := 0;
VIDXSTRUCTSTR VARCHAR2(4000) := '';
VIDXHISTCNT NUMBER;
TYPE IDXRECORD IS RECORD (
ROWS_PER_BLOCK NUMBER,
BLOCKS NUMBER);
TYPE IDXARRAY IS TABLE OF IDXRECORD;
LF_DATA IDXARRAY;
VIDXLOGREC ISA_INDEX_LOG%ROWTYPE;
CURSOR CUR_INS_OR_UPD IS
SELECT *
FROM ISA_INDEX_LOG
WHERE OWNER = AIDXOWNER
AND INDEX_NAME = AIDXNAME;
BEGIN
DBMS_APPLICATION_INFO.SET_MODULE('index_util','inspect_index');
SELECT *
INTO VIDXREC
FROM ALL_INDEXES
WHERE OWNER = AIDXOWNER
AND INDEX_NAME = AIDXNAME;
SELECT NUM_ROWS
INTO VTABNUMROWS
FROM ALL_TABLES
WHERE OWNER = VIDXREC.TABLE_OWNER
AND TABLE_NAME = VIDXREC.TABLE_NAME;
SELECT BLOCK_SIZE
INTO VBLKSIZE
FROM DBA_TABLESPACES
WHERE TABLESPACE_NAME = VIDXREC.TABLESPACE_NAME;
SELECT OBJECT_ID
INTO VIDXOBJID
FROM ALL_OBJECTS
WHERE OWNER = AIDXOWNER
AND OBJECT_NAME = AIDXNAME;
SELECT SUM(TC.AVG_COL_LEN + DECODE(IC.DESCEND,'ASC',0,1)) , SUM(TC.NUM_NULLS)
INTO VAVGIDXROWLEN, VNUMNULLS
FROM ALL_IND_COLUMNS IC, ALL_TAB_COLUMNS TC
WHERE IC.COLUMN_NAME = TC.COLUMN_NAME
AND IC.TABLE_OWNER = TC.OWNER
AND IC.TABLE_NAME = TC.TABLE_NAME
AND IC.INDEX_OWNER = AIDXOWNER
AND IC.INDEX_NAME = AIDXNAME;
IF VIDXREC.UNIQUENESS = 'UNIQUE'
THEN VUNIQIND := 10;
ELSE VUNIQIND := 11;
END IF;
VESTSIZE := ROUND((1.01 * (VIDXREC.NUM_ROWS * VUNIQIND + VAVGIDXROWLEN * (VTABNUMROWS - VNUMNULLS))) / (VBLKSIZE - 100));
VAVGROWSPERBLK := ROUND ((VBLKSIZE - 100) * ((100-VIDXREC.PCT_FREE)/100) / (VAVGIDXROWLEN + VUNIQIND));
VSQLSTR := 'SELECT rows_per_block, count(*) blocks FROM (SELECT /*+ cursor_sharing_exact ' ||
'dynamic_sampling(0) no_monitoring no_expand index_ffs(' || VIDXREC.TABLE_OWNER || '.' || VIDXREC.TABLE_NAME ||
',' || AIDXNAME || ') noparallel_index(' || VIDXREC.TABLE_NAME ||
',' || AIDXNAME || ') */ sys_op_lbid(' || VIDXOBJID ||
', ''L'', ' || VIDXREC.TABLE_NAME || '.rowid) block_id, ' ||
'COUNT(*) rows_per_block FROM ' || VIDXREC.TABLE_OWNER || '.' || VIDXREC.TABLE_NAME || ' GROUP BY sys_op_lbid(' ||
VIDXOBJID || ', ''L'', ' || VIDXREC.TABLE_NAME || '.rowid)) group by rows_per_block order by rows_per_block';
EXECUTE IMMEDIATE VSQLSTR BULK COLLECT INTO LF_DATA;
FOR I IN 1..LF_DATA.LAST LOOP
VBLKCNT := VBLKCNT + LF_DATA(I).BLOCKS;
VROWCNT := VROWCNT + LF_DATA(I).ROWS_PER_BLOCK;
VIDXSTRUCTSTR := VIDXSTRUCTSTR || LF_DATA(I).ROWS_PER_BLOCK || ' - ' || LF_DATA(I).BLOCKS || CHR(10);
END
OPEN CUR_INS_OR_UPD;
FETCH CUR_INS_OR_UPD INTO VIDXLOGREC;
IF CUR_INS_OR_UPD%NOTFOUND
THEN INSERT INTO ISA_INDEX_LOG VALUES (AIDXOWNER, AIDXNAME, SYSDATE, NULL, NULL, VIDXREC.LEAF_BLOCKS, VIDXREC.PCT_FREE, VESTSIZE, (100/VIDXREC.LEAF_BLOCKS)*(VIDXREC.LEAF_BLOCKS-VBLKCNT), VAVGROWSPERBLK, NULL, VIDXSTRUCTSTR);
ELSE INSERT INTO ISA_INDEX_HIST VALUES (AIDXOWNER, AIDXNAME, VIDXLOGREC.LAST_INSPECTED, VIDXLOGREC.LEAF_BLOCKS, VIDXLOGREC.PCT_FREE, VIDXLOGREC.EST_UTILIZATION, VIDXLOGREC.EMPTY_BLOCKS, VIDXLOGREC.AVG_#ROWS_PER_BLK, VIDXLOGREC.ACTION_FLAG, VIDXLOGREC.IDX_LAYOUT);
SELECT COUNT(*) INTO VIDXHISTCNT FROM ISA_INDEX_HIST WHERE OWNER = AIDXOWNER AND INDEX_NAME = AIDXNAME;
WHILE VIDXHISTCNT > VHISTRET LOOP
DELETE FROM ISA_INDEX_HIST
WHERE OWNER = AIDXOWNER
AND INDEX_NAME = AIDXNAME
AND INSPECTED_DATE = (SELECT MIN(INSPECTED_DATE) FROM ISA_INDEX_HIST WHERE OWNER = AIDXOWNER AND INDEX_NAME = AIDXNAME);
VIDXHISTCNT := VIDXHISTCNT - 1;
END
UPDATE ISA_INDEX_LOG SET LAST_INSPECTED = SYSDATE, LAST_COALESCED = VIDXLOGREC.LAST_COALESCED, LAST_REBUILD = VIDXLOGREC.LAST_REBUILD, LEAF_BLOCKS = VIDXREC.LEAF_BLOCKS, PCT_FREE = VIDXREC.PCT_FREE, EST_UTILIZATION = VESTSIZE, EMPTY_BLOCKS = (100/VIDXREC.LEAF_BLOCKS)*(VIDXREC.LEAF_BLOCKS-VBLKCNT), AVG_#ROWS_PER_BLK = VAVGROWSPERBLK, IDX_LAYOUT= VIDXSTRUCTSTR
WHERE OWNER = AIDXOWNER
AND INDEX_NAME = AIDXNAME;
END IF;
CLOSE CUR_INS_OR_UPD;
COMMIT;
END INSPECT_INDEX;
可以看到上述代码的核心就是我用蓝色字体显示的那一段代码，其中用到了一个oracle的internal function（即SYS_OP_LBID(<object_id>, <block_type>, <table_name.rowid>），它的作用是Leaf Block ID Scanning，即
/*
Has many functional variations, but the point is to scan through index leaf blocks and calculate a measure of the quality of the index. The way it is counted, and the thing being counted, depends on the type of index (viz: bitmap, simple b-tree, local/global index, IOT, or secondary on IOT, cluster).
*/
上述过程其实就是模拟了analyze index ... validate structure，但是它的好处是不用像analyze index ... validate structure那样在目标表上加exclusive的table lock. 对这一点，"Index Rebuild, the Need vs the Implications [ID 989093.1] "中也有描述：
Most scripts around depend on the index_stats dynamic table. This is populated by the command:
analyze index ... validate structure;
不错，学习了。
另外能否说说怎么unwrap的？
你去看itpub的精华区，里面有怎样unwrap。
Please continue to keep the good work! Cheers.