Truncate table updating statistics oracle
Optimizer statistics should be gathered after any significant data change. There are exceptions but a lack of indexes is not a good enough reason.It's true that one of the main benefits of accurate statistics is deciding whether to use a full table scan or an index access.Pete Scott left a comment on a previous post stating that he rarely uses approach 1 so no doubt he'll leave another comment here expanding on his reasons What I want to show you is what happens if you do use approach 1 and introduce the _minimal_stats_aggregation hidden parameter that's been kicking around since Oracle 8i.The default setting of the parameter is TRUE, which means that Oracle automatic stats aggregation activity. First of all I'll recreate TEST_TAB1 as it was at the start of the series and add a new partition (and, by implication, the related subpartitions) and create a seperate table that I'll load the data into.Actually, before looking at any recent features, let me introduce one more aspect of the existing aggregation approach used by Oracle.The examples used to date have been based on INSERTing new rows into subpartitions and, although that's the approach used for some of our tables and will suit some systems, the likelihood is that in a near-real-time DW you will be using partition exchange at some point.This means, though that the full table scan is just as costly when you have 1000 rows of data as when you subsequently have 0 rows of data with the same high water mark.So you would expect that the cost would be the same after running the Thanks, Justin.
Although there might be other approaches, I'd say that there are two distinct approaches you are likely to use.1) Create a temporary load table, load it with data, gather statistics on it and then exchange it with the relevant subpartition in the real table.2) Create a temporary load table, load it with data, exchange it with the relevant subpartition and then gather stats on the subpartition.If what you're really talking about are tables that store transient data, though, you probably want to use global temporary tables with an aggressive dynamic sampling setting.The data set for different tasks are very different, and we will verify every task one by one(with different data set and different SQL), when running each task 1) load data set 2) gather the Oracle statistics, we want the current statistics information reflect the latest statistics 3)run the SQL for this task 4)cleanup all the data loaded in step 1. The key problem is when we are running the second tasks, how can we COB get the latest statistics for the new data set?When I treat the table I need to analyze and process every row anyway. This question clearly meets all 4 criteria listed in the Help Center.In a data warehouse, this topic is very much a programmer problem, not a DBA problem.