That's a question in myself with Oracle JOB. Actually I have known a long time.
But I confused and asked myself. So I posted on Oracle Forum.
I have seen many people to answer my question.
I should say "Thanks" to "Donald K Burleson" for idea and reference.
http://www.dba-oracle.com/t_index_rebuilding_issues.htm
# Index rebuilds can improve SQL performance - On indexes with heavy delete activity, rebuilding has been proven to improve SQL performance for range queries. Andrew Kerber notes "our observation was that on 20 million row tables and upward, as little as 5% deletes, regardless of other factors, would cause sufficient performance degradation to justify an index rebuild."
# Index rebuilds can be safely automated - For shops that are concerned about space reclamation, batch jobs can be implemented to reliably rebuild all indexes automatically. I've personally used crontab jobs for index rebuilding when my client's insisted on scheduled index rebuilding, and there is a very low risk of causing a production problem, provided that the rebuild are performed during regularly scheduled maintenance windows using Oracle Best Practices.
# Index rebuilds can release free space - It's well documented that index rebuilding/coalesce will release lost space (see the Oracle Segment Advisor) and in "rare cases", an index rebuild may cause a performance boost, but only under certain conditions.
# Blocksize matters - It's generally accepted that a larger blocksize results in faster full scans.
Anyway I sure it improve performance when I rebuild indexes.
Let me back in my question... haha
Idea about rebuild index... I can see in metalink (Note:77574.1)
Rebuild the index when these conditions are true:- deleted entries represent 20% or more of the current entries.
- the index depth is more then 4 levels.
Guidelines on When to Rebuild an Index:=======================================
You have to periodically check your indexes to see if they become skewed and,
therefore, good candidates for rebuild.
A skewed index has many records clumped close together on the index tree due to their similar indexed values. When an index is skewed, parts of an index are accessed more frequently than others. As a result, disk contention may occur, creating a bottleneck in performance. It is important to periodically examine your indexes to determine if they have become skewed and might need to be rebuilt.
Here is a sample procedure on how to identify the skewed indexes:1. Gather statistics on your indexes. For large indexes (over one hundred thousand records in the underlying table), use ESTIMATE instead of COMPUTE STATISTICS.
For example:
SQL> analyze index A1_PK compute statistics;
Index analyzed.
2. Run the script given below - index_check.sql - to find out how skewed each index is.
This query checks on all indexes that belong to user SCOTT:
SQL> select index_name, blevel,
decode(blevel,0,'OK BLEVEL',1,'OK BLEVEL',
2,'OK BLEVEL',3,'OK BLEVEL',4,'OK BLEVEL','BLEVEL HIGH') OK
from dba_indexes
where owner='SCOTT';
INDEX_NAME BLEVEL OK
---------------------------------------- ------ ----A1_PK BLEVEL HIGH
A1_UK BLEVEL HIGH
BUDVERPORT_BUDVERPORT2_UK 1 OK BLEVEL
BUDVERPORT_BV_FK_I 2 OK BLEVEL
BUDVERPORT_CHAR_CL_FK_I 1 OK BLEVEL
BUDVERPORT_DIRCTE_FK_I 3 OK BLEVEL
BUDVERPORT_FUND_TYPE_FK_I 1 OK BLEVEL
BUDVERPORT_OMB_SUBFCT_FK_I 1 OK BLEVEL
BUDVERPORT_ORG_FK_I 0 OK BLEVEL
BUDVERPORT_PL_TITLE_FK_I 1 OK BLEVEL
BUDVERPORT_RDC_FK_I 1 OK BLEVEL
S_INVENTORY_PRODID_WARID_PK BLEVEL HIGH
S_ITEM_ORDID_ITEMID_PK BLEVEL HIGH
S_ITEM_ORDID_PRODID_UK BLEVEL HIGH
S_LONGTEXT_ID_PK BLEVEL HIGH
S_ORD_ID_PK BLEVEL HIGH
S_PRODUCT_ID_PK BLEVEL HIGH
S_PRODUCT_NAME_UK BLEVEL HIGH
S_REGION_ID_PK BLEVEL HIGH
S_REGION_NAME_UK BLEVEL HIGH
S_TITLE_TITLE_PK BLEVEL HIGH
S_WAREHOUSE_ID_PK BLEVEL HIGH
3. The BLEVEL (or branch level) is part of the B-tree index format and relates to the number of times Oracle has to narrow its search on the index while searching for a articular record. In some cases, a separate disk hit is requested for each BLEVEL. Prior to 9i, if the BLEVEL is more than 4, it is recommended to rebuild the index. As database are getting bigger and bigger, BLEVEL may not be a good indicator of time to rebuild the index. BLEVEL > 4 may suggest an evaluation of whether the partitioning option could help you.
Note: If you do not analyze the index, the index_check.sql script will
show "BLEVEL HIGH" for such an index.4. Gather more index statistics using the VALIDATE STRUCTURE option of the ANALYZE command to populate the INDEX_STATS virtual table. This table does not contain an OWNER column and assumes you are looking for statistics for indexes created by your active session only.
SQL> analyze index SCOTT.ORG_PK validate structure;
Index analyzed.
SQL> select DEL_LF_ROWS*100/decode(LF_ROWS, 0, 1, LF_ROWS) PCT_DELETED,
(LF_ROWS-DISTINCT_KEYS)*100/ decode(LF_ROWS,0,1,LF_ROWS) DISTINCTIVENESS
from index_stats
where NAME='&index_name';
Enter value for index_name: ORG_PK
PCT_DELETED DISTINCTIVENESS
----------- ---------------
0 0
The PCT_DELETED column shows what percent of leaf entries (index entries) have been deleted and remain unfilled. The more deleted entries exist on an index, the more unbalanced the index becomes. If the PCT_DELETED is 20% or higher, the index is candidate for rebuilding. If you can afford to rebuild indexes more frequently, then do so if the value is higher than 10%. Leaving indexes with high PCT_DELETED without rebuild might cause excessive redo allocation on some systems.
The DISTINCTIVENESS column shows how often a value for the column(s) of the index is repeated on average. For example, if a table has 10000 records and 9000 distinct SSN values, the formula would result in 10000-9000) x 100 / 10000 = 10. This shows a good distribution of values.
If, however, the table has 10000 records and only 2 distinct SSN values, the formula would result in (10000-2) x 100 /10000 = 99.98. This shows that there are very few distinct values as a percentage of total records in the column. Such columns are not candidates for a rebuild but good candidates for bitmapped indexes.
Posts
