analyze partitioned indexes

From: Daiminger, Helmut <Helmut.Daiminger_at_KirchGruppe.de>
Date: Wed, 24 Oct 2001 02:18:16 -0700
Message-ID: <F001.003B2C57.20011024020022@fatcity.com>

Hi,

I want to write a procedure that analyzes all my indexes. But I'm not sure whether my source code will also analyze partitioned indexes. What I'm doing is:

delete from admin.tb_index_stats
     where index_owner = '&1';

     commit;

     FOR EACH_ROW IN (SELECT OWNER || '.' || INDEX_NAME as INDEX_NAME

                      FROM DBA_INDEXES
                      WHERE OWNER = '&1')
         LOOP

             t_tables(t_tables.COUNT + 1) := EACH_ROW.INDEX_NAME;
     END LOOP;

     FOR i IN 1 .. t_tables.COUNT LOOP

         BEGIN
              EXECUTE IMMEDIATE 'ANALYZE INDEX ' ||
                  t_tables(i) || ' VALIDATE STRUCTURE';

Will this also work for all the partitions in a partitioned index?

Or what would be a way to get all the index partitions and analyze them separately?

This is 8.1.7 on Sun Solaris.

Thanks,
Helmut Received on Wed Oct 24 2001 - 04:18:16 CDT