How Oracle databases Improve access performance

A, avoid using the IS null and is not NULL operation on an indexed column

To avoid using columns that can then be empty in the index, Oracle will not be able to use that index. For a single-column index, if a null value is included, the index does not exist for this record. For composite indexes if each column is empty, the same record does not exist in the index. If at least one column is not empty, the record exists in the index

B, replace OR with UNION (for indexed columns)

In general, replacing the or in the WHERE clause with union will do a good work. Using or with an indexed column causes a full table scan. (The above rules are only valid for multiple indexed columns, and if column is not indexed, the query efficiency may be reduced because it is not used or)

C, separating tables and indexes

Always set up your tables and indexes in a different tablespace (tablespaces). Never store objects that are not part of an Oracle internal system in the systems table space. Also, make sure that the data table space and the index table space are on different hard disks (this requirement can be achieved by specifying a table space)

D, shared SQL statements

In order not to parse the same SQL statement repeatedly, after the first resolution, Oracle stores the SQL statement in memory, which is a shared pool in the system global area of the SGA Memory can be shared by all database users, so when you execute an SQL statement (sometimes called a cursor), if it is exactly the same as the previous executed statement, Oracle can quickly get the parsed statement and the best execution path. This feature of Oracle greatly improves the performance of SQL execution and saves memory usage. When you submit an SQL statement to Oracle, Oracle will first look for the same statement in this memory. It is important to note that Oracle takes a "strict match" between the two, to achieve sharing, the SQL statement must be identical (including spaces, newline, etc.)

E,where clause Join order

The order of connections in the WHERE clause,
Oracle uses a bottom-up sequence to parse where clauses, and according to this principle, the connections between tables must be written before other where conditions, and those that can filter out the maximum Allow records to be written at the end of the WHERE clause

F,select clauses Avoid using *

When you want to list all columns in the SELECT clause, it is a convenient way to use dynamic SQL column references *. Unfortunately, this is a very inefficient approach. In fact, in the process of parsing, Oracle will convert the * to all column names, which is done by querying the data dictionary, which means that it will consume more time

G, use the DECODE function to reduce processing time

Use the Decode function to avoid repeatedly scanning the same record or repeatedly connecting 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 UKE ' 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 EMP WHERE ename like ' smith% ';
Similarly, the Decode function can also be used in the group BY and ORDER BY clauses.

H, replace Delete with truncate

When you delete a record in a table, in general, the rollback segment (rollback segments) is used to hold information that can be recovered. If you do not have a COMMIT transaction, Oracle restores the data to the state before it was deleted (to be exact, before the delete command was executed) and when the truncate is applied, the rollback segment no longer holds any recoverable information, and when the command is run, The data cannot be restored so that very few resources are invoked and the execution time is very short.

(Note: Truncate only applies if the full table is removed, truncate is DDL is not DML)

I, use commit as much as possible

Whenever possible, use commit as much as you can in a program, so that the performance of the program is improved and the requirements are reduced by the resources freed by the commit: the resources freed by commit:

A. Information for recovering data on a rollback segment,

B. Locks acquired by program statements

C. Redo space in the log buffer

D. Oracle manages internal spending on 3 of these resources
(Note: The integrity of the transaction must be noted in the use of commit, and the real-world efficiency and transactional integrity are often not available for both fish and paws.)

J,where clause replace HAVING clause

Avoid having a HAVING clause that filters the result set only after all records have been retrieved. This process requires sorting, totals, and so on. If you can limit the number of records through a WHERE clause, you can reduce this overhead.

K, replace in with exists

In many base-table-based queries, it is often necessary to join another table in order to satisfy one condition in which case, using EXISTS (or not EXISTS) will usually improve the efficiency of the query

L, replace not with not exists

In a subquery, the NOT IN clause performs one by one internal sorting and merging in either case, not in is the least effective (because it performs a full table traversal of the table in the subquery). To avoid using not, we can change it to an outer join (Outer Joins) or not EXISTS

How Oracle databases Improve access performance