Solved

Last time indexes were rebuilt

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
form builder not starting 3 72
make null the repeated levels 2 37
ER Diagram 3 40
Oracle cursor lifecycle inside procedure. 2 25
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
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…

756 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