What are precautions for Oracle?

The following articles mainly introduce the things that should be paid attention to as a novice Oracle learner. In fact, it is not difficult to learn Oracle well, as long as you have a good understanding of its main learning architecture and related precautions, the following articles mainly describe the precautions for Oracle.

1. Oracle considerations: Considerations for table Deletion

When deleting all data in a TABLE, the truncate table name must be used. Because when the drop table or DELETE * from table name is used, the occupied space of the TABLE in the TABLESPACE is not released, after repeated DROP and DELETE operations, the hundred megabytes of space on the TABLESPACE will be exhausted.

2. 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.

3. 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.

Using External join improves the query speed of table join

When connecting tables (commonly used for views), you can use the following methods to query data:

 
 

  
  
SELECT PAY_NO, PROJECT_NAME  
  
  
FROM A  
  
  
WHERE A.PAY_NO NOT IN (SELECT PAY_  
  
  
NO FROM B WHERE VALUE >=120000);  
 
 

However, if Table A has 10000 records and table B has 10000 records, it takes 30 minutes to complete the query. This is mainly because NOT IN requires A comparison, A total of 10000*10000 comparisons are required before the results can be obtained. After the external join is used, the time can be shortened to about 1 minute:

 
 

  
  
SELECT PAY_NO,PROJECT_NAME  
  
  
FROM A,B  
  
  
WHERE A.PAY_NO=B.PAY_NO(+)  
  
  
AND B.PAY_NO IS NULL  
  
  
AND B.VALUE >=12000;  
 
 

4 Oracle considerations: 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. Precautions for database Reconstruction

During database reconstruction using import, some views may cause problems, because the order of Structure Input may lead to the input of the view before its low-level table, so that the creation of the 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 inputs all database structures, but there is no record. The second input structure and data is submitted once in 64000 bytes. The ignore = Y option ensures that the second input can be successful even if the object exists.

 
 

  
  
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.

6. Oracle considerations: create another table from a known table:

 
 

  
  
Create table B
  
  
As select * (several columns in Table a can be used)
  
  
FROM
  
  
WHERE a. column = ...;
 
 

7. Find and delete duplicate records:

Method 1: Use the Group by statement to quickly find

Select count (num), max (name) from student -- find the number of duplicate records in the num column of the table, and list its name attribute

Group by num

Having count (num)> 1 -- group by num to find the num column in the table, that is, more than once

Delete from student (Select above)

In this way, all duplicates are deleted. ----- Exercise caution

Method 2: When the table is relatively large (for example, more than 0.1 million rows), the efficiency of this method is unacceptable. Another method is required:

After executing the following SQL statement, all records with the same and repeated DRAWING and DSNO can be displayed.

 
 

  
  
SELECT * FROM EM5_PIPE_PREFAB 
 
 

Where rowid! = (Select max (ROWID) FROM EM5_PIPE_PREFAB D -- D is equivalent to First, Second

 
 

  
  
WHERE EM5_PIPE_PREFAB.DRAWING=D.DRAWING AND  
  
  
EM5_PIPE_PREFAB.DSNO=D.DSNO);   
 
 

The above content is an introduction to Oracle considerations, and I hope you will get some benefits.