10g_crt_gather_table_stats（生成表的統計資訊收集指令碼，關鍵在引數）

set serverout on;
declare
  v_owner       varchar2(200) := upper('&tab_owner');
  v_table_name  varchar2(200) := upper('&tab_name');
  v_num_rows    int;
  v_message     varchar2(300);
  V_SQLTEXT     VARCHAR2(4000);
  v_degree      number;
  V_COUNT       number;
  V_METHOD_OPT  varchar2(50);
 begin
       select num_rows
        into v_num_rows
        from dba_tables
       where wner = v_owner
        AND table_name = v_table_name;
      if v_num_rows is null then
        V_METHOD_OPT:='FOR ALL COLUMNS SIZE 1';
        else
         v_METHOD_OPT:='FOR ALL COLUMNS SIZE REPEAT';
        end if;       
      if v_num_rows < 1000000  or v_num_rows is null then
        v_degree := 1;
      end if;
      if v_num_rows between 1000000 and 5000000 then
        v_degree := 2;
      end if;
      if v_num_rows between 5000000 and 10000000 then
        v_degree := 4;
      end if;
      if v_num_rows between 10000000 and 50000000 then
        v_degree := 6;
      end if;
      if v_num_rows between 50000000 and 100000000 then
        v_degree := 8;
      end if;
      if v_num_rows > 100000000 then
        v_degree := 12;
      end if;
      SELECT COUNT(*)
        INTO V_COUNT
        FROM DBA_TAB_PARTITIONS
       WHERE table_owner = v_owner
         AND table_name = v_table_name;
      if V_COUNT = 0 then
        V_SQLTEXT := 'BEGIN DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => ''' || V_OWNER || ''',' || CHR(10) ||
                     'TABNAME => ''' || V_TABLE_NAME || ''',' || CHR(10) ||
                     'METHOD_OPT => '''||V_METHOD_OPT||''',' || CHR(10) ||
                     'DEGREE => ' || v_degree || ',' || CHR(10) ||
                     'CASCADE => TRUE,' || CHR(10) ||
                     'NO_INVALIDATE => FALSE);' || CHR(10) ||
                     'END;';
        dbms_output.put_line(V_SQLTEXT);
      else
        V_SQLTEXT := 'BEGIN DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => ''' || V_OWNER || ''',' || CHR(10) ||
                     'TABNAME => ''' || V_TABLE_NAME || ''',' || CHR(10) ||
                     'METHOD_OPT => '''||V_METHOD_OPT||''',' || CHR(10) ||
                     'DEGREE => ' || v_degree || ',' || CHR(10) ||
                     'GRANULARITY => ''ALL'',' || CHR(10) ||
                     'CASCADE => TRUE,' || CHR(10) ||
                     'NO_INVALIDATE => FALSE);' || CHR(10) ||
                     'END;';
        dbms_output.put_line(V_SQLTEXT);
      end if;
exception
  when others then
    v_message := sqlerrm;
    dbms_output.put_line(v_message);
end;
/