Question about Partitions and Indexes

From: Paul <paul821_at_yahoo.com>
Date: Fri, 26 Sep 2003 04:57:07 GMT
Message-ID: <DGPcb.13482$XF.10932903_at_news4.srv.hcvlny.cv.net>


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. 

--
Received on Fri Sep 26 2003 - 06:57:07 CEST

Original text of this message