The power of Oracle Table Index Rebuilding!!

 Author: Don Burleson

Page: 1 2

It is very critical question for every database administrator that when the tables and indexes should be reorganized and which one should be the first.

There should be a predictive model that suggests which tables and indexes will benefit from reorganization, predict the reduction in I/O after the reorganization, and suggest changes that will prevent a reoccurrence of the fragmentation.

Reorganization:

Oracle tables/indexes can be reorganized, sometimes resulting in faster SQL execution speeds

AWR (STATSPACK):

AWR (STATSPACK) has a history of how the tables were accessed by historical SQL ( dba_hist_sql_plan, stats$sql_plan , etc.), including the I/O and CPU costs associated with each step of SQL execution.

Internal Structure:

You can see the current internal structure of every table/index like chain_cnt, clustering_factor, etc.

Repeating Patterns:

Most databases have repeating patterns of behavior and historical SQL is usually representative of future activity. 

alter index xxx validate structure:

There are many examples of routines that can perform a alter index xxx validate structure command and store the results in a table for analysis.

stats$sql_plan table:

You can alter the plan9i.sql script to access the stats$sql_plan table and see exactly how table/indexes are accessed by our SQL, and how often.

Expert-system algorithm:

You can design an expert-system algorithm that will interrogate your data and create the list of tables/indexes, predicted I/O reductions, and suggested structural changes.

Oracle data mining tools:

The Oracle data mining tools scan historical data and identify statistically significant correlations within 2 standard deviations of the mean value.  For example, the popular MMPI test is a set of 500 true/false questions that accesses personality with remarkable validity and its results are accepted in all U.S. courts. 

Faster Index fast full scans:

Index fast full scans will run faster after index reorganization whenever the “density” of the index entries becomes greater.  In other words, it takes less time to read 100,000 entries from a 100 block index than reading the entries from a 500 block index.

The predictive model should capture the speed of index fast-full scans, multi-block index range scans , and show the degradation over time.

Faster Multi-block Index scans: 

Multi-block Index scans (INDEX_FFS), will run faster when the table data blocks are arranged in index-key order and when the data blocks have a high number of row entries.

The predictive model should be able to see the size of each index range scan (the number of rows returned), the number of blocks that were required to fetch the rows, and the number of logical and physical I/O.

Page: 1 2

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Best Practice for Multiple Oracle Homes!
   Beware of major Oracle Failures: Are you Safe??
   Data warehouse vs. Database - Similar – different – better?
   Some Exciting Oracle Advisory Utilities you should not miss!!