Truncate table updating statistics oracle

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.

Settings can be modified to reduce statistics gathering time.

I interviewed a couple of senior ETL developer candidates last week, so I shook the dust off of some of my tried-and-true probes for technical knowledge.

@user1929982 - But, like I said in my answer, if you are merely deleting data, the table is the same size whether there are 0 rows of data or 1000 rows of data.

In a data warehouse, this topic is very much a programmer problem, not a DBA problem.

Now I'll use LOAD_TAB1 to repeat the same process for the four different subpartitions - INSERT data into LOAD_TAB1, gather stats on it and then exchange it with the relevant subpartition of TEST_TAB1.

select table_name, global_stats, last_analyzed, num_rows 2 from dba_tables 3 where table_name='TEST_TAB1' 4 and owner='TESTUSER' 5 order by 1, 2, 4 desc nulls last; TABLE_NAME GLO LAST_ANALYZED NUM_ROWS ------------------------------ --- -------------------- ---------- TEST_TAB1 NO SQL select table_name, partition_name, global_stats, last_analyzed, num_rows 2 from dba_tab_partitions 3 where table_name='TEST_TAB1' 4 and table_owner='TESTUSER' 5 order by 1, 2, 4 desc nulls last; TABLE_NAME PARTITION_NAME GLO LAST_ANALYZED NUM_ROWS ------------------------------ ------------------------------ --- -------------------- ---------- TEST_TAB1 P_20100131 NO TEST_TAB1 P_20100201 NO TEST_TAB1 P_20100202 NO TEST_TAB1 P_20100203 NO TEST_TAB1 P_20100204 NO TEST_TAB1 P_20100205 NO TEST_TAB1 P_20100206 NO TEST_TAB1 P_20100207 NO TEST_TAB1 P_20100209 NO 9 rows selected.

Which means we need to understand how the stats might be gathered and then aggregated up to the partition and table-level stats.

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.

668

Leave a Reply

  1. Hot girls on wechat mobile numbers 15-Mar-2017 03:11

    Melbourne Events Sydney Events Canberra Events Brisbane Events “Thank you Blink for helping Jo and I get together!

  2. speed dating in bristol and bath 19-Jun-2017 23:14

    is one of the largest online dating sites in the world Millions of singles inside Free to join What are you waiting for? If you want to start to date singles today, it is time to get started with online dating tips Setting up an online profile is easy When you are filling out your.

  3. online dating site usernames 18-Feb-2017 02:00

    These all-or-nothing, black-and-white thought patterns often illustrate depressed thinking.