Demantra TABLE_REORG procedure.
Did you know that TABLE_REORG has replace REBUILD_SCHEMA mad REBUILD_TABLES? TABLE_REORG
can addressed partitioned tables and is more efficient! Get the latest release at My Oracle Support using bug 17640575.
TABLE_REORG has really replaced and improved the functionality of REBUILD_SCHEMA and REBUILD_TABLES.
It rebuilds the table which is done in primary key order and it moves empty columns to the end of the row.
REBUILD_SCHEMA uses ALTER TABLE MOVE tablespace to reduce chained rows of all tables in the schema.
However, it does not support partitioned tables.
REBUILD_TABLES is the similar. It was originally designed for MDP_MATRIX / SALES_DATA, but it can run for all tables and
also for a specific table. From 2010 it does support partitioned tables.
The procedure MOVE_TABLE was fixed to handle partitioned tables. It is also out of date, I see ANALYZE TABLE has used parts of the
code (eg for SALES_DATA and MDP_MATRIX). For an "all tables run" is uses ANALYZE_SCHEMA that does use dbms_stats.GATHER_TABLE_STATS
All tables - Where the stats value chain_cnt > 0, it does not automatically include SALES_DATA unless 'sys_params','Rebuild_Sales_Table' = 1.
REBUILD_TABLES ( table namel, stats check, sales, all tables flag)
exec REBUILD_TABLES ( null, 1, null, 1) ; -- With ANALYZE_SCHEMA(100000) = for none or really old stats
exec REBUILD_TABLES ( null, 0, null, 1) ;
exec REBUILD_TABLES ( null, 0, 1, 1) ; -- Will include SALES_DATA
For more information see: Troubleshooting TABLE_REORG Package issues - RDF Snapshot drop when process fails + TABLE_REORG Guide MOS Note 1964291.1
Gathering statistics on partitioned tables. Best practice:
For all 11gr2 environments with large partitioned or subpartitioned objects turn on incremental statistics using this command:
Once this is set for any given table, gather statistics on that table using the normal tool (fnd_stats in ebs or dbms_stats elsewhere).
This first gather after turning it on will take longer than previous analyzes. Then going forward we will see the following:
1. The normal dbms_stats or fnd_stats, will only gather statistics on lower level partitions if the statistics on that partition are stale. This is a significant change. That is going forward using the default options of a gather command may in fact perform no re-analyze on the actual data if the modifications to the table do not warrant it.
2. If a subpartition is stale the normal stats will ONLY gather statistics on that subpartition. The partition for that subpartition will be re-derived as will the global
statistics, no other statistics will be gathered.
Making this change promises to reduce gather stats by hours in some cases.
For more information: Demantra Gathering Statistics on Partitioned Objects Oracle RDBMS 11gR2, MOS Note 1601596.1