If there is a global index, however, that index can become unusable when you drop the partition.To prevent the index from becoming unusable, in Oracle9i Database and later, you can update the global index when you drop the partition. Starting from Oracle 10g, Oracle introduced an automated task gathers statistics on all objects in the database that having [stale or missing] statistics, To check the status of that task: SQL> select status from dba_autotask_client where client_name = 'auto optimizer stats collection'; In older versions of the database this execution plan could be generated using one of …
in 11g you have a new granularity option: ‘APPROX_GLOBAL AND PARTITION’, this one will take the global stats by using aggregation. EXEC DBMS_STATS.delete_fixed_objects_stats;-- To delete statistics Locking Stats: To prevent statistics being overwritten , you can lock the stats at schema, table or partition level. References. When a valid SQL statement is sent to the server for the first time, Oracle produces an execution plan that describes how to retrieve the necessary data. 3 | understanding optimizer statistics with oracle database 12c release 2 returned - to be the number of rows in the table divided by the number of distinct values for the column or 100/10 = 10. That’s why the trend is almost linear without incremental statistics.
When a partition is dropped, the corresponding partition of any local index is also dropped. department_id a no_stats b 27 .037037037 no 0 4 c10b c2034 27 department_name a no_stats b 27 .037037037 no 0 12 41636 54726 27 location_id a no_stats b 7 .018518518 yes 0 3 c20f c21c 27 manager_id a no_stats b 11 .090909090 no 16 3 c202 c2030 11 ~~~~~ index / (sub)partition statistics … This significantly increases the speed of statistics collection on large tables where some of the partitions contain static data. In standard (default case) Oracle would regather the whole table, so all partitions to generate the global statistics. Cost-Based Optimizer (CBO) And Database Statistics. Oracle 11g includes improvements to statistics collection for partitioned objects so untouched partitions are not rescanned.
: Delete the global stats and retake all the stats for all the partitions (granularity=>’PARTITION’) and the GLOBAL will be aggregated, then keep taking only PARTITION stats.
Regards Stefan. If Oracle creates that invalid histogram and you try to copy the (local) partition statistics from that "target partition" (in my case P4_L5) without explicitly setting va_srec.epc and va_srec.bkvals once again – you will receive that "ORA-20001: Invalid or inconsistent input values" by executing prepare_column_stats.
P.S.