Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Last time indexes were rebuilt

Posted on 2014-12-10
4
Medium Priority
?
294 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 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

722 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