Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

rebuilding index in oracle 10.2.0.3 online

Posted on 2009-03-30
4
933 Views
Last Modified: 2013-12-18
Experts,

i am planning to rebuild index online. my DB version is 10.2.0.3. i need some suggestion to rebuilding index.

how to find which index need to be rebuild?
what cases we need to rebuild indexes?
how to rebuild local indexes (partitioned indexes)?
how to rebuild global indexes?
what are the precautions i need to take while rebuild index online.
is there any negative impacts if i rebuild the index online?

please any one guide me to rebuild indexes.  
0
Comment
Question by:pavan27
  • 3
4 Comments
 
LVL 4

Expert Comment

by:marper
ID: 24021870

Rebuilding indexes online will slow down an access to the corresponding table so do it hen there is not much activity going on around.
To rebuild an index:
 ALTER INDEX <schema_name>.<index_name> REBUILD;
To rebuild a partitioned index:
rebuild the individual partitions:

ALTER INDEX <index-name>
  REBUILD PARTITION <partition-name>
PARALLEL(DEGREE 4, INSTANCES 3)  
0
 
LVL 1

Author Comment

by:pavan27
ID: 24022162
Thanks for quick response.

how do i find which index need to be rebuild?
is there any way to find which job /procedure/process will be slow while we are rebuilding index online?

is there any way to find resource consumption like temp space, memory utilization before rebuild the index?
0
 
LVL 4

Expert Comment

by:marper
ID: 24022258
I attached the script which determines whether an index is a good candidate for a rebuild or for a bitmap index.  All indexes for a given schema or for a subset of schema's are analyzed (except indexes under SYS and SYSTEM).


rebuild-indx.sql.txt
0
 
LVL 4

Accepted Solution

by:
marper earned 500 total points
ID: 24022304

I would also suggest that you read these articles too:
http://en.wordpress.com/tag/index-rebuild/ 
 
You will find some answers of your questions
Generally, when an index is sewed, 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.
Oracle index nodes are not physically deleted when table rows are deleted, nor are the entries removed from the index. Rather, Oracle "logically" deletes the index entry and leaves "dead" nodes in the index tree where that may be re-used if another adjacent entry is required.
 
However, when large numbers of adjacent rows are deleted, it is highly unlikely that Oracle will have an opportunity to re-use the deleted leaf rows, and these represent wasted space in the index. In addition to wasting space, large volumes of deleted leaf nodes will make index fast-full scans run for longer periods.  
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Query to identify changes between rows of two tables 8 55
Oracle DB monitor SW 21 60
Procedure syntax 5 38
Fill Date time Field 12 23
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question