Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2186
  • Last Modified:

Rebuilding Index and ANALYZE

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
rehman123
Asked:
rehman123
11 Solutions
 
JankovskyCommented:
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
 
riazpkCommented:
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
Ajay_GuptaCommented:
We rebuild indexes to maintain the integerity of indexes and as they become unbalanced because of updations/deletions/insertions on the table.
0
 
sonjaspersonCommented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
JR2003Commented:
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
 
BobMcCommented:
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
 
aalapsharmaCommented:
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
 
luttappi123Commented:
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
 
konektorCommented:
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

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!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now