Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Partitioning and Index Question

Partitioning and Index Question

From: Paul <paul821_at_yahoo.com>
Date: Fri, 26 Sep 2003 04:56:21 GMT
Message-ID: <VFPcb.13476$XF.10926048@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 Thu Sep 25 2003 - 23:56:21 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US