Hi Guys,
Today I will show you how simple is to check the Index creation date.
Index Creation Date or Index Rebuild date Both are same because when we rebuild the indexes oracle will drop the existing index and recreate freshly..
Steps :-
- We can see here Last Index Creation date is 18-Dec-16
SQL> select table_name,index_name,last_analyzed from dba_indexes where table_name='OTORDERS';
TABLE_NAME INDEX_NAME LAST_ANALYZED
----------------------------------------------------------------------
OTORDERS ORMEDN_IND 18-DEC-16
OTORDERS OREPRC_IDX 18-DEC-16
OTORDERS PK_ORDERS 18-DEC-16
- Now i will analyze the first index (ORMEDN_IND) and check the last_analyzed date.
SQL> analyze index ormedn_ind validate structure;
Index analyzed.
- We can see Index Last_Analyzed date has not changed even after Analyzing this index.
SQL> select table_name,index_name,last_analyzed from dba_indexes where table_name='OTORDERS';
TABLE_NAME INDEX_NAME LAST_ANALYZED
-------------- ----------------- --------------------------------------
OTORDERS ORMEDN_IND 18-DEC-16
OTORDERS OREPRC_IDX 18-DEC-16
OTORDERS PK_ORDERS 18-DEC-16
- Now i will rebuild the index and execute the same query.
SQL> Alter index ORMEDN_IND rebuild online;
- Now date has changed....
SQL> Select table_name,index_name,last_analyzed from dba_indexes where table_name='OTORDERS';
TABLE_NAME INDEX_NAME LAST_ANALYZED
-----------------------------------------------------------------------
OTORDERS ORMEDN_IND 04-JAN-17
OTORDERS OREPRC_IDX 18-DEC-16
OTORDERS PK_ORDERS 18-DEC-16
- You can also check index creation date by using this query also...
SQL> select last_ddl_time from user_objects where Object_name='ORMEDN_IND';
LAST_DDL_TIME
--------------------------
04-JAN-17
- You can observe here in Last_Analyzed column the date has changed, it means oracle will change the date of an Index when you rebuild it...
Happy Learning...
How can we rebuild the index in table ????
ReplyDeleteWhen you rebuild the index you need to give the Index name.
DeleteJust try to rebuild more than one indexes belong's to various tables you will
get the idea..
But try rebuilding indexes on Test database.
DeleteI don't understand, you say "We can see here Last Index Creation date is 18-Dec-16", but you're referring to LAST_ANALYZED, if you want to check index creation you should query the column CREATED in the USER_OBJECTS view
ReplyDeleteselect * from dba_objects where Object_type like 'INDEX%'
ReplyDeleteand object_name like '
HDR_INT%';
Nice article..It helped me to check index on a table in Oracle
ReplyDeleteA helpful Article. Thanks for sharing it brother.
ReplyDelete