In this note I will show an effective method to modify or eliminate a large number of rows on a large table. In general, the voluminous tables are partitioned for scalability naturally. Partitioning mainly provides 3 types of benefits: 1) improves performance, 2) facilitates the management and maintenance and 3) increasing the availability of data. Resolve a query using as underlying table can be partitioned in the same way to solve a problem dividing it into parts. The familiar premise: divide and conquer is the main objective behind the partition.
Since the introduction of the feature of partitioning (Oracle 8) has greatly expanded the set of possible operations on tables and indexes to support and manage the tables / indices partitioned. With each new release options were added, and partitioning methods for handling operations segments. The various features introduced in each release are:
Oracle 8 (1997)
Partition Pruning (*)
Range Partitioning (includes operations ADD, DROP, RENAME, TRUNCATE, MODIFY, MOVE, SPLIT and EXCHANGE)
Oracle 8i ( 1999)
Hash Partitioning Composite Partitioning
: range / hash
Added
MERGE operation
Oracle 9i R2 (2002)
List Partitioning Partitioning Composite: Range / List
Clause UPDATE GLOBAL INDEXES
Oracle 10g R1 (2004)
global indices partitioned by Hash and List 
It increased the limit of partitions / subpartitions 65k to 4M
- Oracle 11g R1 (2007)
Added partitioning interval, reference and system.
Oracle 11g R2 (2009)
- virtual and primary key columns for partitioned tables referenced. Indices
-
system partitioned by list-partitioned tables. -
- As you can see, almost every new release there was any new functionality added. However, in my opinion, the main feature has existed since the first release with partitioning (1997). I mean the partition pruning and partition pruning, which allows the optimizer (CBO always talking about) choice in automatic, accurate and transparent partition or partitions where the required data. This allows you to segment the data and process only that interest us, without adding any additional intelligence in the application code.
I will create a table T partitioned 3 partitions list create table t (c1 int, c2 varchar2 (10),
date c3, c4
- char (1))
- partition by list (c4) (
- t_a partition values \u200b\u200b('A'), partition
T_B values \u200b\u200b('B'), partition T_C values \u200b\u200b('C') - )
from dual connect by rownumI'm going to insert rows 10M arbitrarily distributed on the partitions:chr (trunc (dbms_random.value (65.68)))
insert into t select rownum, dbms_random.string ('a', 10), sysdate-dbms_random
. value (-100.100),
insert into t select rownum +10000000,
- dbms_random.string ('a', 10), sysdate-dbms_random.value
- (-100.100), 'A' from dual connect by rownum
After all values \u200b\u200bare loaded confirm (commit) and then collect statistics.
Consider the plan for a query that counts rows on partition 1 (t_a):
from t WHERE c2> 'R' and c4 = 'A'; select * from table (dbms_xplan.display) ------------------------------------
PLAN_TABLE_OUTPUT
- -------------------------------------------------- -------------
- Plan hash value: 2901716037
- ----------------------------------------------------------------------------------------------- ACCESS FULL from t
COUNT (1) ----------
8333946
At this point, we already have a partition with more than 8.3M of rows which we will modify 5.6M, which is more than 67%.
First I'll test a regular update on the table T and then make the comparison with the same change but using another approach more efficient.: 00:04:45.37
September update
t c3 = c3 +1
WHERE c4 = 'A'
and c2> 'R'
5610297 rows updated. After
round (ms.value/1024/1024) value from v $
The amendment took 4 ' 45 ". We think that the database must maintain consistency to ensure consistent read (using the UNDO) and persist the changes to be able to recover if a failure event occurs during the modification (REDO). These mechanisms cause the times increase and generate additional information.
review how much UNDO and REDO space is needed to perform the update:
<= 10000000;
select 'REDO_SIZE'
MYSTAT ms, v $ statname
WHERE sn = sn.STATISTIC ms.STATISTIC # # and sn.NAME = 'redo size'REDO_SIZE
union all SELECT 'UNDO_SIZE'
t.used_ublk * 8 / 1024 value
FROM v $ transaction t, v $ session s WHERE
t.addr = s.taddr
AND s.audsid = userenv ('sessionid')
<= 5000000;
2489 Mb 885 Mb
UNDO_SIZEElapsed: 00:00:22.04
5.3m is needed to modify 2489Mb 885Mb redo and undo!. In the example, the table has no indices. If you have indexes and the modified column is part of the indexing columns generate more redo and undo, and also the sentence would have to update the indexes for each row modified which will cause the update takes a while longer. If it were a bulk processing instead of a delete update will generate more undo (the delete is dml operation that generates more undo) and will be balanced to keep the indices, which means more processing time.
there a simpler way to perform the update operation using partitioning star: EXCHANGE. Before using the exchange we need to create an auxiliary table (T_A) and to speed up the table as NOLOGGING set up and inserted directly using the APPEND hint.
create table t_a_aux
NOLOGGING as select / * + APPEND * /
c1, c2
,
case when (c2> 'R') then c3 else c3 end
+1 c3, c4
from t WHERE
c4 = 'A'
Only needed 22 "to insert the rows in the auxiliary table. With DECODE or CASE function to make a change to simulate the update. Now only remains to make the exchange between the auxiliary and the partition table with the operation t_a EXCHANGE:After: 00:00:11.46
ALTER TABLE t EXCHANGE PARTITION WITH t_a
t_a_aux table;
The exchange took place almost 12. " Adding the auxiliary table creation and exchange, all took only 44 "!!!, ie more than 6 times faster than the traditional update.
Running the query space for the redo and undo generated is obtained:
REDO_SIZE 1 Mb
UNDO_SIZE 0 Mb
Virtually no allocation for undo / redo. Therefore, for certain cases it is very useful to use this method to update since the processing times are significantly reduced and also the requirements of undo and redo are minimized almost completely.
To remove (delete) en masse, creating the auxiliary table should only be filled with rows that do not fade. If you need to delete many rows from a non-partitioned table can use the same approach, ie replacing delete an insert into a new table, recreate the indexes and rename.
Host CPU Utilization (%) 4.63 % Busy/(Idle+Busy) 
