Tip of the Week
Tip for Week of December 14, 2003

Analyze a Table and All Underlying Indexes

This tip comes from Bobby Akbari, Senior DBA, at SASCO, in Santa Ana, CA.

-- This script analyzes a given table and all underlying indexes
-- within the executing schema.
-- Create this stored procedure under the executing schema (because it
-- uses user_indexes).
-- This script uses ESTIMATE STATISTICS SAMPLE 40 PERCENT which 
-- is appropriate for most situations. 
-- This script is useful right after creating or importing a large table
-- with many associated indexes where statistics either don't exist or are out of date.
-- Sample:      connect scott/password
--              exec p_analyze_table_indexes(EMP)
-- Tested on Oracle Database Release 9.2.


create or replace procedure
        p_analyze_table_indexes(a_table_name       IN
user_indexes.TABLE_NAME%type)
        as
                v_cursorid              integer;
                status                  integer;
                cursor c_user_indexes is
                        select TABLE_NAME,INDEX_NAME
                        from user_indexes
                        where status='VALID'
                        and TABLE_NAME=upper(a_table_name);
                v_user_indexes  c_user_indexes%rowtype;
                begin
                        open c_user_indexes;
                                v_cursorid:=dbms_sql.open_cursor;
                        fetch c_user_indexes into v_user_indexes;
                        while ( c_user_indexes%found ) loop
                                dbms_sql.parse(v_cursorid,
                                'analyze index
'||v_user_indexes.INDEX_NAME||' ESTIMATE STATISTICS SAMPLE 40 PERCENT
                                        ',dbms_sql.native);
                                status:=dbms_sql.execute(v_cursorid);
                                          fetch c_user_indexes into
v_user_indexes;
                        end loop;
                        dbms_sql.parse(v_cursorid,
                        'analyze table '||v_user_indexes.TABLE_NAME||'
ESTIMATE STATISTICS SAMPLE 40 PERCENT
                                        ',dbms_sql.native);
                        status:=dbms_sql.execute(v_cursorid);
                close c_user_indexes;
                dbms_sql.close_cursor(v_cursorid);
                        exception
                        when others then
                                dbms_output.put_line('Error...... ');
                                dbms_sql.close_cursor(v_cursorid);
                                raise;
        end p_analyze_table_indexes;
/