Biology Lab Manual A Answers

How to perform update / delete mass effectively


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

Oracle 10g R2 (2005)

It increased the limit of partitions / subpartitions 65k to 4M

Oracle 11g R1 (2007)

composite partitioning: range-range, list-range, list-list and list-hash.

 
 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.

With respect to operations, the vast majority are from Oracle 8, only be added after the MERGE. An operation is very interensante EXCHANGE, with which you can share a table with a partition unpartitioned. It is precisely this operation that I use to propose an alternative fast to change or delete many rows of partitioned tables. Then somo usually do, I will show the steps in detail and compare the times and use of resources:
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')
• )
  

 
 I'm going to insert rows 10M arbitrarily distributed on the partitions:   
    insert into t select rownum,  dbms_random.string ('a', 10), sysdate-dbms_random 
 . value (-100.100), 
 chr (trunc (dbms_random.value (65.68))) 

from dual connect by rownum

• 
  

5M Insert rows on the partition on which I will work to have more rows :

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):

Explain plan for select count (1)
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

WHERE c4 = 'A';

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. 
 
 
 
 
 
 September update 
 t c3 = c3 +1 
 WHERE c4 = 'A' 
 and c2> 'R' 
 
 5610297 rows updated. After 
 
 

: 00:04:45.37

 
 
 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' 

round (ms.value/1024/1024) value from v $

MYSTAT ms, v $ statname

WHERE sn = sn.STATISTIC ms.STATISTIC # #

 and sn.NAME = '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;         
 
 

REDO_SIZE

2489 Mb 885 Mb

 UNDO_SIZE 
 
 
 
 
 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' 
 
 

Elapsed: 00:00:22.04

 
 
 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: 
 
 
 
 
 
 
 ALTER TABLE t EXCHANGE PARTITION WITH t_a 
 t_a_aux table; 
 
 
 After: 00:00:11.46 
 

 
 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.