Automatic Index Analyze

[datamart]

Hi All,
I am using Informatica for my ETL processes. I have a couple of fact tables and 5 aggregate tables. After my daily load, I would like to analyze all the indexes in the fact and aggregate tables.
Is there a way by which I can dynamically analyze the indexes of a particular table after loading that table. I do not want to hard-code the analyze scripts for the existing indexes in the post-script of my ETL process for the fear that I may want to add or drop indexes in the future and my post-script fails.

Any help will be appreciated.

Thanks.

 

[ddiamond]

We have automated this with the following script:

SchemaMaint/oraSchemaMaint
set heading off
set pagesize 0
set feedback off
set verify off
set echo off
set embedded on
set serveroutput on
spool d:\oradata\REPT\DBAScripts\AnalyzeSchemaRun.sql
select 'set serveroutput on' from dual;
select distinct 'execute dbms_utility.analyze_schema ('''||owner||''',''COMPUTE'');'
from dba_tables
where owner in ('ELGAR_LIVE','MIGRATE_GRE','PROMETHEUS','ELGAR_TEST','MIGRATE_EXCESS');
spool off
set echo on
set feedback on
spool d:\oradata\REPT\Logs\AnalyzeSchemas.log
rem Executing Analyze for all schemas
--------pause Press any key to continue or ^C twice to abort
@d:\oradata\REPT\DBAScripts\AnalyzeSchemaRun
spool off
exit

 

[dbtoo2001]

http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76992/stats.htm#25754

I'm sure you are exporting them before you gather them so you can recover should something go horribly wrong.

 

[Beilstwh]

declare
sql_string varchar2(200);
begin
for pnt in (select owner,index_name
from all_indexes
where owner in ('ELGAR_LIVE','MIGRATE_GRE',
'PROMETHEUS','ELGAR_TEST',
'MIGRATE_EXCESS')
and table_name in ('FACT1','FACT2','FACT3','FACT4','FACT5')) loop
sql_string := 'DBMS_STATS.GATHER_INDEX_STATS ('''||pnt.owner||''','''||pnt.index_name||''')';
EXECUTE IMMEDIATE SQL_STRING;
END LOOP;
END;
/

Bill
Oracle DBA/Developer
New York State, USA

 

[ddiamond]

dbtoo,

Yes, we are doing a full export before hand. That being said, the script I listed above has run flawlessy for as long as I've been working at my present company (4+ years).

- Dan