Some uncommon but important tips for using Oracle databases

Oracle DatabaseThere are some skills that need to be understood and mastered during use. To master these skills, we can avoid many detours and improve query efficiency in the future database use process. This article mainly introduces the seven-point skills and hopes to help you. Next we will introduce them one by one.

TIPS:

1. Do not include variables after select in procedure and function; otherwise, it will greatly affectSQL Efficiency.

2. TRUNCATE cannot be used in procedure. It can be written:

 
 

  
  
EXECUTE IMMEDIATE 'truncate table smic_rtd_bullet_lot_tmp'; 
 
 

3. when you run the DML data monopoly language, add, delete, modify, and query statements, PL/SQL opens a built-in cursor and processes the results. The cursor is a region in the memory that maintains the query results, the cursor is open when running the DML statement. Implicit cursors only use SQL % FOUND, SQL % NOTFOUND, and SQL % ROWCOUNT attributes. SQL % FOUND, SQL % NOTFOUND is a Boolean value, and SQL % ROWCOUNT is an integer. This can be used as an inference criterion to exit the loop when the loop ends.

4. In order not to repeatedly parse the same SQL statement, after the sequential parsing, ORACLE will store the SQL statement in the memory. The memory in the shared buffer pool of the SGA system global area can be shared by all database users. Therefore, when you execute an SQL statement (sometimes called a cursor), in case it is exactly the same as the previous statement, ORACLE will be able to quickly obtain the statements that have been parsed and good paths. The shared statement definitely has three conditions:

A. Character-level comparison: currently, the statements in the statement and sharing pool must be completely the same. Including all spaces and uppercase/lowercase letters.

B. The objects referred to by the two statements must be completely identical. For example, if two users have a unified table, one is the table owner and the other is a reference synonym, they cannot share SQL statements.

C. The two SQL statements certainly use the same name to bind variables. All variables have the same name. Even at runtime, the same value can be assigned to different binding variables.

Unified SQL is used in multiple sessions. In case of multiple changes, the SQL statement is not shared due to inconsistent spaces and uppercase/lowercase letters. Use WITH a AS (SELECT * FROM ......) The query name is a, and then a can be used as an SQL statement, which can ensure that multiple times of sequential parsing can improve the efficiency.

5. Whenever possible, use COMMIT as much as possible during the process, so that the features in this process can grow and the demand will also shrink due to the resources released by COMMIT:

Resources released by COMMIT:

A. The message on the rollback segment is used to repeat the data.

B. The lock obtained by the process statement.

C. Space in redo log buffer.

D. ORACLE manages the internal costs of these three types of resources.

When using COMMIT, you must always be aware of the comprehensiveness of the transaction. In practice, efficiency and comprehensiveness of the transaction are often the same as that of the fish and the bear's paw.

6. Improve SQL efficiency through internal functions.

 
 

  
  
SELECT H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC,COUNT(*)  
  
  
 
  
  
FROM HISTORY_TYPE T,EMP E,EMP_HISTORY H  
  
  
 
  
  
WHERE H.EMPNO = E.EMPNO  
  
  
 
  
  
AND H.HIST_TYPE = T.HIST_TYPE  
  
  
 
  
  
GROUP BY H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC; 
 
 

The following functions can be called to improve efficiency.

 
 

  
  
FUNCTION LOOKUP_HIST_TYPE (typ in number) RETURN VARCHAR2
  
  
 
  
  
AS
  
  
 
  
  
TDESC VARCHAR2 (30 );
  
  
 
  
  
CURSOR C1 IS
  
  
 
  
  
SELECT TYPE_DESC
  
  
 
  
  
FROM HISTORY_TYPE
  
  
 
  
  
WHERE HIST_TYPE = TYP;
  
  
 
  
  
BEGIN
  
  
 
  
  
OPEN C1;
  
  
 
  
  
FETCH C1 into tdesc;
  
  
 
  
  
CLOSE C1;
  
  
 
  
  
RETURN (NVL (TDESC, Nikon lens '? '));
  
  
 
  
  
END;
  
  
 
  
  
FUNCTION LOOKUP_EMP (emp in number) RETURN VARCHAR2
  
  
 
  
  
AS
  
  
 
  
  
ENAME VARCHAR2 (30 );
  
  
 
  
  
CURSOR C1 IS
  
  
 
  
  
SELECT ENAME
  
  
 
  
  
FROM EMP
  
  
 
  
  
Where empempno = EMP;
  
  
 
  
  
BEGIN
  
  
 
  
  
OPEN C1;
  
  
 
  
  
FETCH C1 into ename;
  
  
 
  
  
CLOSE C1;
  
  
 
  
  
RETURN (NVL (ENAME ,'? '));
  
  
 
  
  
END;
  
  
 
  
  
Select h. EMPNO, baby milk rankings LOOKUP_EMP (H. EMPNO ),
  
  
 
  
  
H. HIST_TYPE, LOOKUP_HIST_TYPE (H. HIST_TYPE), COUNT (*)
  
  
 
  
  
FROM EMP_HISTORY H
  
  
 
  
  
Group by h. EMPNO, H. HIST_TYPE;
 
 

Many people want to use an SQL statement to write the necessary data. However, they do not know that hybrid SQL statements often devote themselves to strict efficiency. Being able to master the above measures to solve problems using functions is of extreme significance in practical work.

7. In general, using UNION to rotate OR in the WHERE clause will achieve better results. Using OR to index columns will cause a full table scan. Note: The above rules are only applicable to multiple index columns. If a column is not indexed, the query efficiency may decrease because you have no choice OR choice.

This section describes how to use Oracle databases.