The Oracle tutorial you are looking at is: Oracle SQL Performance Tuning Series learning three. 8. Use the Decode function to reduce processing time
Use the Decode function to avoid repeatedly scanning the same record or repeating the same table.
For example:
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 results efficiently.
SELECT COUNT (DECODE (dept_no,0020, ' X ', NULL)) D0020_count,
COUNT (DECODE (dept_no,0030, ' X ', NULL)) D0030_count,
SUM (DECODE (dept_no,0020,sal,null)) D0020_sal,
SUM (DECODE (dept_no,0030,sal,null)) d0030_sal
From the EMP WHERE ename like ' smith% ';
Similarly, the Decode function can also be applied to the group BY and ORDER BY clauses.
9. Simple integration, no associated database access
If you have a few simple database query statements, you can integrate them into a single query (even if there is no relationship between them).
For example:
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 3 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: Although this approach to improve efficiency, but the readability of the program is greatly reduced, so the reader still have to weigh the pros and cons)
10. Delete duplicate records
The most efficient way to delete a duplicate record (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 you delete records in a table, in general, the rollback segment (rollback segments) is used to hold information that can be recovered. If you do not commit a transaction, Oracle restores the data to the state before it was deleted (accurately, before the deletion was performed), and when the truncate is applied, the rollback segment no longer holds any recoverable information. When the command is run, The data cannot be recovered. Therefore, very few resources are invoked and the execution time is short. (Note: Truncate only applies if the entire table is deleted, truncate is DDL is not DML)
12. Use of commit as much as possible
Whenever possible, use a commit in the program as much as possible, so that the performance of the program is improved and the requirements are reduced by the resources released by the commit:
Resources released by commit:
A. The information used to recover data on the rollback segment.
B. Locks obtained by program statements
C. Space in the Redo log buffer
D. Oracle to manage the internal costs of the 3 resources mentioned above
(Note: The integrity of the transaction must be noted when using a commit, and the actual efficiency and transactional integrity are often not the same as the fish and the paws.)
If the value null,sum (NULL) for decode is null--> if all values are null, SUM (NULL) = null but only one value is not null,sum () <> NULL So there should be no logical problem with the original SQL
Personal opinion on the 8th: If the value of decode null,sum (NULL) is NULL, the sum is not normal. You can change it as follows: SELECT count (DECODE (dept_no,0020, ' x ', null)) D0020_count, Count (DECODE (dept_no,0030, ' x ', null)) d0030_ COUNT, SUM (DECODE (dept_no,0020,sal,0)) d0020_sal, sum (DECODE (dept_no,0030,sal,0)) D0030_sal from EMP WHERE ename like ' smith% ';
