Six principles of experience in Oracle Database Systems

The following articles mainly focus on the relevant empirical principles frequently used in Oracle database systems. This article introduces these principles in six parts, including the usage of having clauses, external join "+" and how to delete repeated records in a table.

1. having clause usage

The having clause controls the row groups determined by the group by clause. The having clause conditions allow only constants, clustering functions, or columns in the group by clause.

2. Usage of external join "+"

External join "+" Join by left or right of "=. if a row in a table without the "+" operator does not directly match any row in the table with the "+" budget operator, then the row of the former matches an empty row in the latter and is returned. if neither of them has '+', none of the matching results will be returned. using External join "+" can replace the inefficient not in operation, greatly improving the running speed. for example, the following command is very slow to execute.

 
 

  
  
select a.empno from emp a where a.empno not in   
  
  
(select empno from emp1 where job=’SALE’);   
 
 

If an external connection is used, the rewrite command is as follows:

 
 

  
  
select a.empno from emp a ,emp1 b   
  
  
where a.empno=b.empno(+)   
  
  
and b.empno is null   
  
  
and b.job=’SALE’;   
 
 

It can be found that the running speed is significantly improved.

3. How to delete repeated records in a table

You can use this command to delete repeated records in a table:

 
 

  
  
delete from table_name a   
  
  
where rowid< (select max(rowid) from table_name   
  
  
where column1=a.column1 and column2=a.column2   
  
  
and colum3=a.colum3 and ...);   
 
 

However, when the table is large (for example, more than 0.5 million rows), the efficiency of this method is unacceptable, you need to find another solution (see the technical handling of long-distance repeated phone numbers in China Telecom, computer and communication, 1999-07 ).

4. usage of the set transaction command

Sometimes oracle reports the following error when executing a large transaction:

 
 

  
  
ORA-01555:snapshot too old (rollback segment too small)  
 
 

This indicates that the random rollback segment allocated by oracle to this transaction is too small. In this case, you can specify a large rollback segment to ensure the successful execution of this transaction. For example:

 
 

  
  
set transaction use rollback segment roll_abc;   
  
  
delete from table_name where ...   
  
  
commit;   
 
 

The rollback segment roll_abc is specified for this delete transaction. The commit command cancels the rollback segment after the transaction ends.

5. Notes for using Indexes

Subqueries in select, update, and delete statements should regularly find less than 20% of table rows. if the number of rows queried by a statement exceeds 20% of the total number of rows, it cannot improve the performance through the Oracle Database System Using indexes.

The index may produce fragments, because when the record is deleted from the table, it is also deleted from the table index. the space released by the table can be reused, but the space released by the index cannot be reused. index reconstruction should be performed on indexed tables that frequently perform deletion operations to avoid space fragmentation and performance impact. the truncate command can also be used to delete all records in the table or index fragments in a staged truncate table.

6. Precautions for database Reconstruction

Some views may cause problems when using import to reconstruct the Oracle database, because the order of Structure Input may lead to the input of views before its low-level tables, in this way, creating a view will fail. to solve this problem, you can take two steps: first enter the structure and then enter the data. command example (uesrname: jfcl, password: hfjf, host sting: ora1, data file: expdata. dmp ):

 
 

  
  
imp jfcl/hfjf@ora1 file=empdata.dmp rows=N   
  
  
imp jfcl/hfjf@ora1 file=empdata.dmp full=Y buffer=64000   
  
  
commit=Y ignore=Y   
 
 

The first command input all Oracle database structures, but no records. the second input structure and data is submitted in 64000 bytes. the ignore = Y option ensures that the second input succeeds even if the object exists.

Article by: http://www.programbbs.com/doc/class10-3.htm