>>One question, about how many tables are we talking about, and how many rows in the big tables? Also, are you doing any partitioning?
>
>234 tables
>5 millions to 20 millions on the big tables
>No partitioning
>
>>On the 24x7, who deemed that, the client? Reason I ask, unless we are talking about a mission critical database (say, medical transactions or stock transactions), I think 24x7 is sometimes a pipe dream. Even Wells Fargo (my bank) has a brief period overnight where they are down.
>
>We need to work on that. I'll see what can be done to have a better window of such maintenance.

OK, reindexing of five million rows should take less than a minute. (Of course, I don't know how many indexes you have per table). Again, unless this is mission critical, I don't think it's unreasonable to have an off-line period, preferably during a time of night (or day?) when you have the least amount of activity.

Just an observation - it sounds like you could be in overdrive on what you're doing with your transaction tables, and might want to think about a data mart. (Not saying it would solve all issues, but something to at least consider).