By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,266 Members | 1,736 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,266 IT Pros & Developers. It's quick & easy.

Question about Partitions and Indexes

P: n/a
I'm confused about something I did this week. I have an 8.1.7.4 database with tables partitioned by dates. I recently dropped my maxvalue tablespace, added a few more partitions for up-coming dates, then put the maxval partition back on. (I save the data that was in the maxval partition first and put it back in when I was done.
These tables also contains BLOBS.

All of my indexes were at a status of either "Valid" or "Usable" in the case of the partitioned indexes. I rebuilt the indexes with the "rebuild" command (not online). The indexes still showed a status of either "Valid" or "Usable". However my SQL ran slower than molasses. The explain plan said it was using the indexes, but the queries were extremely slow.

My next step was to drop and re-create the indexes. Well this solved the problem. The explain plans all looked the same as before, but not they were getting fast response.

My question is: Why did I have to drop and re-create these indexes, rather than just rebuild them. Also whey was the status of all indexes or ind_partitions "Valid" or "Usable" even when I was having the problem.

Thanks in advance for the help.

--
Paul L.

Jul 19 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.