Home » RDBMS Server » Performance Tuning » information about indexes
information about indexes [message #164545] Thu, 23 March 2006 22:48 Go to next message
kinjal
Messages: 124
Registered: June 2001
Senior Member
Hello,

Where can I find the information about whether the index on particular table is rebuil or not. And if its rebuild then when it was rebuilt?

Kinjal

Report message to a moderator

Re: information about indexes [message #164562 is a reply to message #164545] Fri, 24 March 2006 00:20 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
 
select last_ddl_time
from user_objects
where object_name = 'MY_INDEX_NAME'
/

_____________
Ross Leishman

Report message to a moderator

Re: information about indexes [message #164567 is a reply to message #164562] Fri, 24 March 2006 00:29 Go to previous messageGo to next message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member

Nice little query Ross. I was just going through index reorgs and i found the following Tom kyte's link which says no need to rebuild indexes.

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:2913600659112

Can we people go through it and have a small discussion on it??


Warm regards,
Tarun Dua

Report message to a moderator

Re: information about indexes [message #164573 is a reply to message #164567] Fri, 24 March 2006 00:51 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Didn't read the whole thread, but I got the gist.

My 2c:

My only criteria for rebuild is when the index is sparse - most blocks are less than half full.

Any INSERT could cause another row to be inserted in a nearly full index block. This will cause the block to split - the two blocks will only be half full. It is normal and no cause for alarm. These blocks will fill with further inserts, and the process repeats. If INSERTs are all that happen, you should never need to rebuild.
If the indexed column is correlated with the course of time (eg. a number allocated from a sequence), then inserts tend to go at the end giving you a nice dense block utilisation with no splitting.

Any update on a non-indexed column does not affect the index. Surprise, surprise!

Deletes leave holes in indexes, but these tend to be filled back up with new inserts later on. High volume deletes can leave an index sparse. Most people actually notice that FTS is slower because of the HWM problem before they notice any problem with the index.
I say rebuild/shrink both table and index after a big delete.

Updates on indexed columns are the killer. Once again, low volume is no problem. Unlike high volume deletes, people don't think twice about high volume updates. Huge amounts of data can be migrated from one part of the index to another, leaving paddocks of blank space along the way.
I've had some indexes that are about 10x bigger than they need to be because rows are continually migrated in the index and the holes are not refilled.

So my rule: anyting with a lot of deletes or updates on indexed columns (Fast Refresh Materialized Views especially!), you need to watch out for low block-utilisation.

I wrote a SQL to find them and posted it here.
_____________
Ross Leishman

Report message to a moderator

Re: information about indexes [message #164591 is a reply to message #164567] Fri, 24 March 2006 03:44 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Tarun,
Myth is not just about rebuilding the indexes.
Myth is about rebuilding the indexes frequently as scheduled job as part of maitenance. Rebuilding may or may not fix the 'issue'. Saying that, one should understand what the 'issue' is.

Report message to a moderator

Re: information about indexes [message #164597 is a reply to message #164591] Fri, 24 March 2006 04:22 Go to previous messageGo to next message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member
Mahesh and Ross,

Thanks to you both. Why i asked this because in my new job they are rebuilding indexes on a scheduled day ?
So just being curious i raised this question. I will dig deeper into the scripts that they use for selecting the indexes that are suppose to be rebuild. And then i will come back if there is any doubt in my mind.

Thanks again,
Tarun

Report message to a moderator

Re: information about indexes [message #164616 is a reply to message #164597] Fri, 24 March 2006 06:03 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
And also please search the forum for index rebuilding.
You can see many postings.
>>they are rebuilding indexes on a scheduled day?
This is what we should be concerned about. Rebuilding indexes indiscriminately is not going to fix the so called 'issues'.

Report message to a moderator

Re: information about indexes [message #164822 is a reply to message #164616] Sun, 26 March 2006 22:33 Go to previous message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member
Sure , i will search.

Thanks,
Tarun

Report message to a moderator

Previous Topic: How increase SGA more than 25% of physical memory
Next Topic: Different Plan for Inner and Outer Query
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Apr 20 09:47:20 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.