More selective means slower?

I'm running into a problem on one of our systems where a query on dba-segments is running incredibly slow.

The full query is:

select 'alter index diamond'||segment_name||' rebuild tablespace tmp_d_indx;' from dba_segments
where owner='DIAMOND'
and
segment_type='INDEX'
and
tablespace_name='DM_DIAMOND_INDEXES';

and it takes ages (killed it after 2 hours). I know that there are 307 indexes (on less than 150 tables, I think the people (an external supplier) who wrote the app might have gone a little overboard, still it gives me something to work on when they start complaining that updates are too slow) that satisfy the where clause.

If I remove the last where clause (tablespace_name) it runs much faster (but obviously that's not the answer I want), if I remove the next clause (segment_type) then it runs faster still (a few seconds).

I would have thought that making a query more selective would, if anything, speed up the response time, not slow it down significantly. The only thing I can think of is that the optimizer is choosing really bad execution plans and using an index that it really should be or doing a crappy join due to the extra where clause.

I haven't done an explain plan as I seem to recall that it doesn't work for dictionary objects.

I've now substituted dba_indexes for dba_segments in the query and edited appropriately which works nice and quickly but I'm still curious as to whether anyone has come accross something similar before.

Incidentally the reason I'm doing this, in case anyone is wondering, is that the bright spark who set up the system in the first place misplaced a couple of zeros when sizing the tablespaces so files that were supposed to be 300Mb are now 30Gb and won't fit on the backup media. I have to move the segments off to staging tablespaces, drop the old tablespaces, recreate the old tablespaces with the correct sizes and then move the objects back (for some reason the app suppliers wrote it so it won't work if the segments aren't in tablespaces with certain specific names, I didn't actually think that that was possible). Of course they only deemed to tell me this 4 minutes before I was due to go home.

Thankyou for your attention.

Stephen

--

It's better to ask a silly question than to make a silly assumption.
--

http://www.freelists.org/webpage/oracle-l Received on Thu Jan 20 2005 - 14:24:44 CST