Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Last time indexes were rebuilt

Posted on 2014-12-10
4
Medium Priority
?
309 Views
Last Modified: 2014-12-14
Hi,

 I will like to know when was the last time indexes were rebuilt.  

 I am using this query  select distinct (LAST_ddL_TIME) as last  from dba_objects, but DDL could mean some else besides rebuild for indexes ?

 There is some automatic way to keep the indexes rebuild up to date automatically  ?
0
Comment
Question by:joe_echavarria
  • 2
4 Comments
 
LVL 35

Accepted Solution

by:
johnsone earned 668 total points
ID: 40491304
If you use DROP and CREATE on the indexes to rebuild them, then the CREATED column in DBA_OBJECTS should show you  the last time it was rebuilt.  If you use ALTER INDEX...REBUILD, then there isn't a column that I am aware of that would tell you the last time it was rebuilt.

The bigger question is why do you need to rebuild the indexes?  Typically indexes would only be rebuilt if a massive delete has taken place.
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 668 total points
ID: 40491317
the last_ddl could be when the index was marked unusable/usable etc, so not necessarily when it was rebuild

anyhow, the "last time" it was done is not really the key (unless you want to avoid rebuilding it all day long....)
in which case last_ddl_time still is useful

refer to this page to review some considerations about rebuilding indexes
http://www.dba-oracle.com/art_index1.htm
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 664 total points
ID: 40491536
If you rebuild your indexes using "ALTER INDEX yourindex REBUILD COMPUTE STATISTICS", you can issue the following query to determine when the index was rebuilt:
select last_analyzed from dba_indexes where index_name = 'YOURINDEX';

You might also create a procedure to automate this somewhat -
create or replace procedure rebuild_idx (varchar2 in idxname) is
v_sql varchar2(255);
v_lastanal date;
begin
select last_analyzed into v_lastanal from user_indexes where index_name = upper(idxname);
if v_lastanal < sysdate - 2 --in this example more than two days ago but you pick the timeframe
then
v_sql := 'alter index '||idxname||' rebuild compute statistics';
execute immediate v_sql;
end if;
end;
/
0
 
LVL 35

Expert Comment

by:johnsone
ID: 40491573
LAST_ANALYZED would work only if that is the only way you gather statistics.  If you gather statistics another way outside that command (i.e. DBMS_STATS), then that column wouldn't be accurate.
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

783 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question