Oracle Customization / Analysis Tables

Question

What are the means of planning automatic "analytical tables". Is it possible to query automatic "analytical tables" when a lot of data is changed by inserting and deleting data? What are the means of parameterizing the process of automatic table analysis, i.e. Set rules when they should run.

Answer 1

“Is there a way to start this work when a significant part of the data has changed” Theoretically, you can make an AFTER INSERT trigger on a table that automatically sets DBMS_STATS.

But be careful what you want. If you are in the middle of a large ETL job by simply pasting a million rows into a table, you do not need to automatically run the DBMS_STATS job automatically. Similarly, when you are in the busiest online processing mode (like lunch), you don’t want to slow it down while collecting statistics.

Usually you want these statistics to collect jobs that work during periods of low usage (nights, weekends). If you have a large batch run that loads into many tables, I would create DBMS_STATS in the batch.

Answer 2

Oracle ? 10.1, Oracle , , ( , , , ~ 15%), . , , , .

DBMS_STATS , DBMS_JOB ( DBMS_SCHEDULER), . ETL

BEGIN
  dbms_stats.gather_table_stats( 
    ownname => <<schema name>>,
    tabname => <<table name>> 
  );
END;

DECLARE
  l_jobno INTEGER;
BEGIN
  dbms_job.submit(
    l_jobno,
    'BEGIN dbms_stats.gather_table_stats( ''<<schema name>>'', ''<<table name>>'' ); END;',
    sysdate + interval '1' minute
  );
END;