Solved

Last time indexes were rebuilt

Posted on 2014-12-10
4
251 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 34

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 142

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 31

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 34

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

706 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now