Solved

Rebuilding Index and  ANALYZE

Posted on 2004-08-18
13
2,177 Views
Last Modified: 2009-12-16
Hi experts
can u tell me rebuilding index and analyzing will increase our database performance
or analyzing will  slow down the database .
actually after rebuilding indexes when ever i am analyzing it I am  facing lots of trouble  because its  making verrrrrrrrrrry slow  to database.
0
Comment
Question by:rehman123
13 Comments
 
LVL 12

Accepted Solution

by:
catchmeifuwant earned 50 total points
ID: 11838301
0
 
LVL 6

Assisted Solution

by:Jankovsky
Jankovsky earned 45 total points
ID: 11839686
It depends ...
analyzis should in most cases improve execution, but there are cases where it makes trables. There are several ways to solve it:
1. add hints into touched statements to force them work ok even on analyzed tables, because it is not primaryli matter of analyzis (recommended)
2. delete analysis on given table, if statements work better without analysis (like rule based) - it's a bit dirty
3. optimize set of indexes on given tables.

To solve it You should evaluate execution plans of run statements.
0
 
LVL 13

Assisted Solution

by:riazpk
riazpk earned 45 total points
ID: 11839883
So my first question to Rehman is "Why do you rebuild the indexes" ???

about 90% og time, i found people rebuilding indexes uselessly and thus causing resources and time wastage.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Assisted Solution

by:Ajay_Gupta
Ajay_Gupta earned 45 total points
ID: 11840169
We rebuild indexes to maintain the integerity of indexes and as they become unbalanced because of updations/deletions/insertions on the table.
0
 

Assisted Solution

by:sonjasperson
sonjasperson earned 45 total points
ID: 11840882
Read the asktom article mentioned in the first post.   It is really very good.

The database probably gets really sloooooowwwww after you rebuild the indexes because it takes Oracle a while to get them back to the shape that works for the data.
0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 45 total points
ID: 11843874
An unbalanced index is not the same as an invalid index (or one that has integrity problems).  Just because an index is unbalanced, you may not need to, or benefit much from, an index rebuild.   If you are convinced that rebuilding indexes is a good thing to do, this process can be automated to occur at off-peak times (assuming that there are some off-peak times in your database).
0
 
LVL 18

Assisted Solution

by:JR2003
JR2003 earned 45 total points
ID: 11847299
If you add records and delete them on a regular basis the indexes will grow and grow and become very slow.
Rebuilding an index is much quicker than dropping and recreating the index as rebuilding takes all its information from the old index.
0
 
LVL 7

Assisted Solution

by:BobMc
BobMc earned 45 total points
ID: 11853700
Analyzing objects is a very resource intensive operation. In earlier versions of Oracle it would require exclusive locks, which obviously hits performance. I believe these restrictions have been relaxes slightly, but you will still notice the hit.

If you must rebuild and reanalyze indexes regularly, schedule it to run overnight when no one will notice.

I have to agree with several earlier comments - manual maintenance on correctly implemented indexes is not normally required.
0
 
LVL 3

Assisted Solution

by:aalapsharma
aalapsharma earned 45 total points
ID: 11869027
Hey rehman123,

When the size of database is very large then the  ANALYSE statement itself takes a lot of time, but it does help in later queries.
But you should use DBMS_STATS package to compute statistics. This package is far better than ANALYZE statement.
It has lots of other facilities available to manipulate your database analysis.

http://builder.com.com/5100-6388_14-5057857.html

Regarding rebuilding Index, I must say, you should not rebuild indexes very frequently. This also takes a lot of time, and does not help more to later queries. The best way is to rebuild the indexes once a week or month.

Regards,

Aalap Sharma :)
0
 
LVL 1

Assisted Solution

by:luttappi123
luttappi123 earned 45 total points
ID: 11892324
Hi,

     There is no point in rebuilting the index at regular intervals unless and otherwise there is defenit need for it. If there is frequent deletions on column which is incremented sequentially, coelasing is a better option. But Y u want to rebult and analyze the index?

Rgds
Luttappi
0
 
LVL 9

Assisted Solution

by:konektor
konektor earned 45 total points
ID: 11912787
some hints :
- rebuild indexex on tables, to which are often inserted or deleted rows.
- do not use analye tables, or indexes on such tables. it goes rapidly old and if it is old, SQL optimizer can chhose slow ways to execute statements
- be carefull, when joining analyzed and unanalyzed table. SQL optimizer chooses analyzed table firstly, so if you are joining some transaction table (nonanalyzed) with some reference table (analyzed), use hint /*+ RULE */ in select statement to make optimizer browse transaction table firstly and join with reference table afterthat
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
clob to char in oracle 3 89
form builder not starting 3 71
how to find out the count of records based on the subfolders paths 11 39
oracle date format checking 7 32
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

713 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