8. Use the Decode function to reduce processing time
Use the DECode function to avoid repeated scans the same record or repeat the same table.
E.g:
SELECT Count (*), SUM (SAL)
From EMP
WHERE DEPT_NO = 0020
And ename Like 'Smith%';
SELECT Count (*), SUM (SAL)
From EMP
WHERE DEPT_NO = 0030
And ename Like 'Smith%';
You can use the decode function to get the same result.
Select Count (Decode (DEPT_NO, 0020, 'X', NULL) D0020_Count,
Count (DEPT_NO, 0030, 'X', NULL)) D0030_Count,
SUM (DEPT_NO, 0020, SAL, NULL) D0020_sal,
SUM (decode (DEPT_NO, 0030, SAL, NULL) D0030_SAL
From Emp where ename Like 'smith%';
Similarly, the DECode function can also be used in Group By and Order By clauses.
9. Integrate simple, unrelated database access
If you have a few simple database query statements, you can integrate them into a query (even if there is no relationship)
E.g:
Select Name
From EMP
WHERE EMP_NO = 1234;
Select Name
From DPT
WHERE DPT_NO = 10;
Select Name
From cat
WHERE CAT_TYPE = 'RD';
The above three queries can be merged into one:
Select E.NAME, D.NAME, C.NAME
From Cat C, DPT D, EMP E, DUAL X
WHERE NVL ('x', x.dummy) = NVL ('x', E.ROWID ( ))
And NVL ('x', x.dummy) = NVL ('x', D.RowID ( ))
And NVL ('x', x.dummy) = nVL ('x', C.RowID ( ))
And E.EMP_NO ( ) = 1234
And D.DEPT_NO ( ) = 10
And c.cat_type ( ) = 'rd';
(Translator Press: Although this method is taken, efficiency is improved, but the reader's reader is greatly reduced, so readers still have to weigh the pros and cons.
10. Delete Record
The most efficient deletion of repeated recording methods (because RowID is used)
Delete from EMP E
WHERE E.ROWID> (Select Min (X.RowID)
From EMP X
Where x.emp_no = E.EMP_NO);
11. Replace Delete with Truncate
When the recording in the table is deleted, in general, the rollback segments is used to store information that can be recovered. If you don't have a commit transaction, Oracle restores the status before the data is deleted (accurately said
Restore the situation before performing the deletion command)
And when using Truncate, the rollback segment no longer stores any information that can be recovered. When the command is run, the data cannot be recovered. Therefore, few resources are called, the execution time will be short.
(Translator Press: Truncate is only deleted full table, truncate is DDL is not DML)
12. Try to use Commit as much as possible
As long as it is possible, try to use Commit as much as possible in the program so that the performance of the program is improved, and the demand will also decrease due to the resources released by Commit:
Resources released by Commit:
a. Information on the recovery of data is returned.
b. Locks obtained by the program statement
c. Space D. Oracle is internal cost in the above three resources
(Translator Press: You must pay attention to the integrity of transactions when using commits. The efficiency and transaction integrity of realities are often fish and bear's own.
Comments on this article Popularity: 4369
Black_snail (2003-9-7 19:16:56)
If DECODE is NULL, the value of SUM (NULL) is null -> If all the values are null, sum (null) = null But as long as one value is not null, SUM () <> null, the original SQL should No logical problem
Menlion (2003-9-4 12:38:01)
Personal view on the eighth point: If the decode is null, the value of SUM (NULL) is NULL, which is not normal. It can be changed as shown below: SELECT COUNT (DEPT_NO, 0020, 'X', NULL) D0020_Count, Count (DEPT_NO, 0030, 'X', NULL) D0030_Count, SUM (DEPT_NO , 0020, SAL, 0) D0020_SAL, SUM (DECODE (DEPT_NO, 0030, SAL, 0)) D0030_sal from Emp where ename Like 'Smith%'
