In which scenario you would rebuild the index?
When should I rebuild the indexes in my relational database (SQL Server)?
Is there a case for rebuilding indexes on a regular basis?
The answer to your question - In which scenario you would rebuild the index is -
You should run an index maintenance process regularly. However, your index maintenance process should only rebuild/reorganize the indexes that specifically require it.
This presents the question: when does an index require to be rebuilt or reorganized? Rolando touched on this nicely. Again, I risk being extremely broad. An index requires maintenance when the fragmentation level adversely affects performance. This level of fragmentation could vary based on the size and composition of the index.
Speaking of SQL Server, I tend to choose an index size and index fragmentation level at which point I begin performing index maintenance. If an index contains less than 100 pages, I will perform no maintenance.
If an index is between 10% and 30% fragmented, I will REORGANISE the index and UPDATE the statistics. If an index is over 30% fragmented, I will REBUILD the index - with no UPDATE STATISTICS, as this is taken care of by the REBUILD. Remember though that a rebuild only updates the statistics object directly associated with the index. Other column statistics will need to be maintained separately.
This answer is really just a long way to say: Yes, you should do routine index maintenance, but only on the indexes that need it.