Common Oracle SQL skills and oraclesql skills

Common Oracle SQL skills (conversion) and oraclesql skills

1. Avoid "*" in the SELECT clause.
When you want to list all columns in the SELECT clause, using dynamic SQL COLUMN reference '*' is a convenient method. unfortunately, this is a very inefficient method. in fact, ORACLE converts "*" into all column names in sequence during parsing. This task is done by querying the data dictionary, which means it takes more time.

2. Use the DECODE function to reduce processing time
You can use the DECODE function to avoid repeated scan of the same record or join the same table. For example:

SQL code

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 efficiently get the same result:

SQL code

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 EMP WHERE ENAME LIKE ‘SMITH%’;

Similarly, the DECODE function can also be used in the group by and order by clauses.

3. Delete duplicate records
The most efficient method for deleting duplicate records (because ROWID is used)

SQL code

DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);

4. Replace DELETE with TRUNCATE
When deleting records in a table, a rollback segment is usually used to store information that can be recovered. If you do not have a COMMIT transaction, ORACLE restores the data to the State before the deletion (which is precisely the State before the deletion command is executed). When TRUNCATE is used, rollback segments no longer store any recoverable information. after the command is run, the data cannot be restored. therefore, few resources are called and the execution time is short.

5. Calculate the number of records
In contrast, count (*) is slightly faster than count (1). Of course, if you can search by index, the index column COUNT is still the fastest. For example, count (EMPNO)

6. Replace HAVING clause with Where clause
Avoid using the HAVING clause. HAVING filters the result set only after all records are retrieved. This processing requires sorting and total operations. If you can use the WHERE clause to limit the number of records, this can reduce the overhead, for example:

SQL code

-- Inefficient select region, AVG (LOG_SIZE) from location group by region having region! = 'Sydney 'and region! = 'Perth' -- efficient select region, AVG (LOG_SIZE) from location where region! = 'Sydney 'and region! = 'Perth' GROUP BY REGION

7. Replace IN with EXISTS
In many basic table-based queries, to meet one condition, you often need to join another table. in this case, using EXISTS (or not exists) usually improves the query efficiency.

SQL code

-- Inefficient SELECT * from emp where empno> 0 and deptno in (select deptno from dept where loc = 'melb') -- efficient: SELECT * from emp where empno> 0 and exists (SELECT 'x' from dept where dept. DEPTNO = EMP. deptno and loc = 'melb ')

8. replace not in with NOT EXISTS
IN a subquery, the not in Clause executes an internal sorting and merging. IN either case, not in is the most inefficient (because it executes a full table traversal for the table IN the subquery ). to avoid the use of not in, we can rewrite it into an Outer join (Outer Joins) or not exists. for example:
SELECT... From emp where DEPT_NO not in (SELECT DEPT_NO from dept where DEPT_CAT = 'A ');

SQL code

-- Rewrite to improve efficiency: (Method 1: efficient) SELECT .... From emp a, dept B WHERE. DEPT_NO = B. DEPT (+) and B. DEPT_NO is null and B. DEPT_CAT (+) = 'A' -- (Method 2: most efficient) SELECT .... From emp e where not exists (SELECT 'x' from dept d where d. DEPT_NO = E. DEPT_NO AND DEPT_CAT = 'A ');

9. Replace DISTINCT with EXISTS
When you submit a query that contains one-to-many table information (such as the Department table and employee table), avoid using DISTINCT in the SELECT clause. Generally, you can consider replacing it with EXIST.
For example:

SQL code

-- Inefficiency: select distinct DEPT_NO, DEPT_NAME from dept d, emp e where d. DEPT_NO = E. DEPT_NO -- efficiency: SELECT DEPT_NO, DEPT_NAME from dept d where exists (SELECT 'x' from emp e where e. DEPT_NO = D. DEPT_NO); -- EXISTS makes the query more rapid, because the RDBMS core module will return results immediately after the subquery conditions are met.

10. Use indexes to improve efficiency
An index is a conceptual part of a table to improve the efficiency of data retrieval. In fact, ORACLE uses a complex self-balancing B-tree structure, generally, data query by index is faster than full table scan. When ORACLE finds the optimal path for executing the query and Update statements, the ORACLE optimizer uses the index, using indexes to join multiple tables can also improve efficiency. Another advantage of using indexes is that it provides uniqueness verification of the primary key, except for those LONG or long raw data types, You Can index almost all columns. generally, using indexes in large tables is particularly effective. of course, you will also find that using indexes to scan small tables can also improve the efficiency. Although using indexes can improve the query efficiency, we must pay attention to the cost. the index requires space for storage and regular maintenance. The index itself is modified whenever a record is added or removed from the table or the index column is modified, which means the INSERT of each record, DELETE and UPDATE will pay four or five more disk I/O operations. Because the index requires additional storage space and processing, unnecessary indexes will slow the query response time.
Note: Regular index reconstruction is necessary.

11. Avoid using computation on index Columns
In the WHERE clause, if the index column is part of the function, the optimizer uses full table scan without using the index. For example:

SQL code

-- Inefficiency: SELECT... From dept where sal * 12> 25000; -- efficiency: SELECT... From dept where sal> 25000/12;

12. Replace with> =>

SQL code

-- If DEPTNO has an index -- efficiency: SELECT * from emp where deptno> = 4 -- inefficiency: SELECT * from emp where deptno> 3

The difference between the two lies in that the former DBMS will jump directly to the first record whose DEPT is equal to 4, while the latter will first locate the record whose DEPTNO is = 3 and scan forward to the record whose first DEPT is greater than 3.