INDEX 분석

INDEX 분석

20% 이상이면 성능저하 => 리빌드 필요!

SQL> create table test as select * from big_emp;
SQL> create index i_test_empno on test(empno);
SQL> select * from user_indexes where index_name = 'I_TEST_EMPNO';
SQL> ANALYZE INDEX I_TEST_EMPNO VALIDATE STRUCTURE;
SQL> SELECT (DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100 BALACE FROM INDEX_STATS;
SQL> DELETE FROM TEST WHERE EMPNO > 1 AND EMPNO < 7500;
SQL> COMMIT;
SQL> ANALYZE INDEX I_TEST_EMPNO VALIDATE STRUCTURE;  INDEX 구조 분석
SQL> SELECT DEL_LF_ROWS_LEN ,LF_ROWS_LEN, (DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100 BALACE FROM INDEX_STATS;
DEL_LF_ROWS_LEN LF_ROWS_LEN BALACE
--------------- ----------- --------------
103510 453512 22.8
SQL> ALTER INDEX I_TEST_EMPNO REBUILD NOLOGGING PARALLEL 2;
SQL> ANALYZE INDEX I_TEST_EMPNO VALIDATE STRUCTURE;
SQL> SELECT DEL_LF_ROWS_LEN ,LF_ROWS_LEN, (DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100 BALACE FROM INDEX_STATS;
DEL_LF_ROWS_LEN LF_ROWS_LEN BALACE
--------------- ----------- --------------
0 350002 0