Solved

Last time indexes were rebuilt

Posted on 2014-12-10
4
278 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 35

Accepted Solution

by:
johnsone earned 167 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 167 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 166 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
update statement in oracle 9 53
Age Calculation from specific date 19 59
Oracle perfomance tuneing. 3 47
ORA-01843: not a valid month - use NVL 14 21
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

752 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