(1) Select the most effective table name order (only valid in rule-based optimizer):

ORACLE's parser processes table names in the FROM clause FROM right to left, and the from clause writes.

The last table (basic table, driving table) will be processed first, and the FROM clause contains multiple tables.

In this case, the table with the least number of records must be selected as the basic table. If there are more than three table join queries

, then you need to choose the intersection table as the basic table, and the intersection table refers to

The table referenced by his table.

(the connection order in the where clause. ;

ORACLE parses the WHERE clause in bottom-up order. According to this principle, the connection between tables must be

It must be written before other WHERE conditions, and those conditions that can filter out the maximum number of records must be written in WHERE.

The end of the clause.

(3) Avoid using' *' in the select clause:

During parsing, ORACLE will convert' *' into all column names in turn. This work is done through

Looking up the data dictionary means spending more time.

(4) Reduce the number of visits to the database:

ORACLE has done a lot of work internally: parsing SQL statements, estimating the utilization rate of indexes, and binding changes.

Number, read data blocks, etc. ;

(5) reset the ARRAYSIZE parameter in SQL*Plus, SQL*Forms and pro * C.

The recommended value is 200 for the amount of data retrieved for each database access.

(6) Use the decoding function to reduce the processing time:

Use the DECODE function to avoid scanning the same records repeatedly or connecting the same tables.

(7) Simple integration and irrelevant database access:

If you have several simple database query statements, you can integrate them into one query (even if

There is no relationship between them)

(8) Delete duplicate records:

Example of the most effective way to delete duplicate records (because ROWID is used):

Delete WHERE E. ROWID & gt from EMP E (select the minimum value (X.ROWID).

FROM EMP X where x.emp _ no = e.emp _ no);

(9) Replace deletion with truncation:

When deleting records in a table, in general, a rollback segment is used to save them.

Put information that can be recovered. If the transaction is not committed, ORACLE will recover the data before deletion.

The state of (exactly, it is restored to the state before the delete command is executed), and when TRUNCATE is used, it returns.

The scrolling section no longer stores any recoverable information. When the command runs, the data cannot be recovered, so it is rare.

When the resource is called, the execution time will be short. (Translator's press: TRUNCATE only deletes the whole table.

TRUNCATE is DDL, not DML.

(10) Use submit whenever possible:

Whenever possible, use COMMIT as much as possible in the program, so that the performance of the program is improved and the demand is also increased.

Will be reduced by submitting released resources:

Commit released resources:

A. information used to recover data on the rollback segment.

B. Lock acquired by program statement

C. Redo log buffer space

D.ORACLE manages the internal expenses in the above three resources.

Replace the HAVING clause with the Where clause:

Avoid using the HAVING clause. HAVING will not be performed on the result set until all records have been retrieved.

Filter. This process requires operations such as sorting and total. If the WHERE clause can limit the number of records,

That will reduce the cost in this respect. (Not in Oracle Bone Inscriptions) on, where and having can all be added.

In a fragment clause, on is the first one to be executed, followed by where, and having is the last one, because on is the first one to put nonconformities.

Only after screening out a record can statistics be made, which can reduce the data to be processed in the intermediate operation, which should have been

The speed is the fastest, and it should be faster than having, because it filters the data before summation.

When connecting two tables, on is used, so when connecting a table, only where is compared with having.

In this case of single-table query statistics, if the conditions to be filtered do not involve the fields to be calculated, then they

The result is the same, except that rushmore technology can be used for speed, but not for speed.

The latter is slower. If a calculated field is involved, it means that the value of the field is not before calculation.

Of course, according to the workflow written in the last article, the action time of where is completed before calculation, and

Having only works after calculation, so in this case, the result will be different. Zaiduo

When tables join queries, on works earlier than where. First, according to the connection between tables,

After combining multiple tables into a temporary table, filter by where, then calculate and press.

Must be filtered. Therefore, if you want the filter conditions to play a correct role, you must first understand this article.

When should it work, and then decide where to put it.

(12) Reduce the query of tables:

In SQL statements with subqueries, special attention should be paid to reducing queries on tables. Example:

Select TAB_NAME from the table, where (TAB_NAME, DB_VER) = (SELECT

TAB_NAME, DB_VER FROM TAB_COLUMNS where VERSION = 604)

(13) Improve SQL efficiency through internal functions.

Complex SQL often sacrifices execution efficiency. Be able to master the above methods of using functions to solve problems.

It is very meaningful in practical work.

(14) Use the alias of the table:

When connecting multiple tables in an SQL statement, use the alias of the table and use the alias as the prefix of each column.

This can reduce parsing time and syntax errors caused by column ambiguity.

(15) Replace IN with EXISTS and Replace NOT IN with NOT EXISTS.

In many queries based on basic tables, it is usually necessary to join another table in order to satisfy one condition.

Then, in this case, using EXISTS (or NOT EXISTS) will usually improve the efficiency of the query.

In the query, the NOT IN clause will perform internal sorting and merging. In both cases, it is no longer.

It is the least efficient (because it performs a full table traversal on the tables in the subquery). Avoid using NOT IN.

We can rewrite it as external connection or nonexistent.

Example:

(efficient) SELECT * FROM EMP (basic table) where EMPNO >；; 0 and exists (select

"X" comes from department, where department number = employee. DEPTNO and LOC = 'MELB')

(inefficient) SELECT * FROM EMP (basic table) where EMPNO >；; 0 and DEPTNO IN (select

Department number comes from department location = 'MELB')

(16) Identifying "inefficient execution" SQL statements:

Although various graphical tools about SQL optimization emerge one after another at present, we write our own SQL tools.

Solving problems is always the best way;

SELECT execution, DISK_READS, BUFFER_GETS, ROUND((BUFFER_GETS-

DISK_READS)/BUFFER_GETS，2) Hit_radio，ROUND(DISK_READS/EXECUTIONS，2)

Number of readings per run,

SQL _ TEXT FROM V $ SQLAREA WHERE EXECUTIONS & gt； 0 and BUFFER _ GETS & gt0 and

(BUFFER _ GETS-DISK _ READS)/BUFFER _ GETS & lt； 0.8 order by 4 desc；;

(17) Using indexes to improve efficiency;

Index is a conceptual part of table, which is used to improve the efficiency of retrieving data. ORACLE uses complex.

Miscellaneous self-balanced B-tree structure. In general, querying data through an index is faster than scanning an entire table. When ORACLE looks for it,

The ORACLE optimizer uses indexes when finding the best path to execute queries and update statements.

Using one index for multiple tables can also improve efficiency. Another advantage of using an index is that it provides a primary key.

Unique verification of (primary key). You can almost index those LONG or LONG RAW data types.

All columns. In general, using indexes in large tables is particularly effective. Of course, you will also find that when scanning is small.

Tables, using indexes can also improve efficiency. Although using indexes can improve query efficiency, we

We must also pay attention to its cost. Indexes need space to store and need to be maintained regularly. Whenever there is a record in.

When adding or deleting tables or modifying index columns, the index itself will also be modified. This means inserting each record.

, DELETE, UPDATE will pay 4 to 5 times more disk I/O for this, because the index needs extra storage.

Storage space and processing, those unnecessary indexes will reduce query response time. Rebuild the index regularly.

This is necessary. ;

Change index & ltINDEXNAME & gt rebuild & lt tablespace name & gt

(18) Replace DISTINCT with EXISTS:

When submitting a query containing one-to-many information tables (such as department tables and employee tables), you should avoid using.

Use DISTINCT in the SELECT clause. Generally, you can consider using EXIST instead, which makes the query faster.

Speed, because the RDBMS core module will return the results immediately when the conditions of the subquery are met. Example:

(Inefficient): Choose different department numbers and names from Department D and Employee E.

Among them, department number = department number (effective): select department number and department name from departments.

D. Location of existence (select "X" from EMP E, where e.dept _ no = d.dept _ no);

(19) sql statement capitalization; Because oracle always parses sql statements first and converts lowercase letters into uppercase letters before execution.

(20) Use the connector "+"to connect strings as little as possible in Java code!

2 1) Avoid using NOT on index columns. Usually,

We should avoid using NOT, NOT index columns, which has the same effect as using functions on index columns. When "ORACLE" encounters "NOT", it will stop using indexes and perform a full table scan instead.

(22) Avoid using calculations on indexed columns.

In the WHERE clause, if the indexed column is part of a function, the optimizer will use a full table scan instead of an index. Example: Inefficiency:

SELECT…FROM DEPT WHERE SAL * 12 & gt； 25000; Efficient:

SELECT … FROM department SAL & gt25000/ 12;

(23) use > = replace >

Efficient:

SELECT * FROM EMP WHERE DEPTNO & gt=4 is inefficient:

The difference between SELECT * FROM EMP WHERE DEPTNO & gt is that the former DBMS will jump directly to the first record with DEPT equal to 4, while the latter will locate the record with DEPTNO=3 equal to 3, and then scan forward to the first record with DEPT greater than 3.

(24) replace OR with UNION (applicable to index columns)

Generally speaking, it is very effective to replace the OR in the WHERE clause with UNION. Using or will cause a full table scan on indexed columns. Note that the above rules are only valid for multiple index columns. If the column has no index, the query efficiency may be reduced because you have not selected or. In the following example, both LOC_ID and REGION have indexes. Efficient: select loc _ id, loc _ desc, region from location where loc _ id =10 union select loc _ id, loc _ desc, Region from location where region = "Melbourne" Inefficient: select LOC _ ID, LOC _ Desc, Region from location where LOC _ ID =10 OR region = "Melbourne" If you insist on using OR, you need to write the index column with the least records first.

(25) Replace or with.

This is a simple and easy-to-remember rule, but the actual implementation effect needs to be tested. Under ORACLE8i, the execution paths of the two seem to be the same.

Inefficient: select ... from the location effectively selected by loc _ ID =10 orloc _ ID = 20 orloc _ ID = 30 ... from the location of loc _ in (10/0,20,30);

(26) Avoid using IS NULL and IS NOT NULL on index columns.

Avoid using any columns that can be empty in the index, and ORACLE will not be able to use the index. For a single-column index, if the column contains null values, the record will not exist in the index. For a composite index, if each column is empty, the record will not exist in the index. If at least one column is not empty, the record will exist in the index. For example, if a unique index is established on columns A and B of a table, and there is a record A in the table, if the value of B is (123, null), then ORACLE will not accept the next record (insert) with the same value of A and B (123, null). But if all the index columns are empty, ORACLE will think that the whole key value is empty, and empty does not mean empty. Therefore, you can insert 1000 records with the same key value. Of course, because there are no null values in the index column, comparing the null values of the index column in the WHERE clause will cause ORACLE to disable the index.

Inefficient: (invalid index) department where dept _ code is not empty; Efficient: (the indicator is valid) select…from dept _ code >；; =0;

(27) Always use the first column of the index:

If the index is built on more than one column, the optimizer will choose to use the index only when the where clause refers to its first leading column. This is also a simple and important rule. When only the second column of the index is referenced, the optimizer uses a full table scan and ignores the index.

(28) Replace UNION with UNION-ALL (if possible):

When an SQL statement needs two UNION query result sets, the two result sets will be merged in the way of UNION-ALL, then sorted, and finally the results will be output. If UNION ALL is used instead of UNION, sorting is unnecessary and efficiency will be improved accordingly. It should be noted that UNION ALL will repeatedly output the same record in both result sets. Therefore, you should analyze the feasibility of using UNION ALL from the perspective of business requirements. UNION will sort the result set, and this operation will use the memory SORT_AREA_SIZE. It is also very important to optimize this memory. The following SQL can be used to query the consumption of sorting.

Inefficient:

?

Select ACCT quantity and balance amount from debit transaction.

In which TRAN _ date =' 365438+951Feb. 0' trade union chooses ACCT _ quantity and balance _ amount.

From debt _ transactions, where trans _ date =' 31-dec-95' efficiency: selectacct _ number, balance _ amount.

TRANsaction from the borrower, in which Tran date =' 365438+951Feb. 0' UNIONALLSELECT ACCT quantity, balance amount.

Transaction from the borrower, where TRAN date ='3 1-DEC-95'?

(29) change ORDER to WHERE:

The ORDER BY clause uses indexes only under two strict conditions.

All columns in ORDER BY must be contained in the same index and keep the order in the index.

All columns in ORDER BY must be defined as non-empty.

The index used in the WHERE clause and the index used in the ORDER BY clause cannot be collocated.

For example, the table DEPT contains the following columns:

DEPT_CODE primary key is not empty.

DESC department is not empty.

The department type is empty.

Inefficient: select dept _ code from department order by dept _ type. Efficient: select dept _ code from departments with dept _ type > 0.

(30) Avoid changing the types of index columns;

When comparing data of different data types, ORACLE automatically performs simple type conversion on columns.

Suppose EMPNO is an index column of numeric type. Select … from EMP where EMPNO ='123' In fact, after ORACLE type conversion, the statement is converted into: select … from EMP where EMPNO = to _ NUMBER ('123'). Fortunately, there is no type conversion on the index column, and the purpose of the index has not changed. Now, suppose EMP_TYPE is an index column of character type. The statement of select … from EMP where EMP _ type =123 was converted by ORACLE into: select … from EMP where to _ number (EMP _ type) =123. This index will not change due to internal type conversion. In order to avoid implicit type conversion of your SQL by ORACLE, it is best to express the type conversion explicitly. Note that when comparing characters with numeric values, ORACLE will give priority to converting numeric values into character types.

3 1) WHERE clause to pay attention to:

The WHERE clause in some SELECT statements does not use indexes. Here are some examples. In the following example, (1)'! =' Index will not be used. Remember, an index can only tell you what exists in the table, not what doesn't exist in the table. (2)' ||' is a character concatenation function. As with other functions, indexing is disabled. (3)'+'is a mathematical function. Just like other mathematical functions, indexes are disabled. (4) The same index columns cannot be compared with each other, which will

(32) A. If the retrieved data exceeds 30% of the records in the table, there will be no obvious efficiency improvement by using the index.

B. In some cases, using indexes may be slower than full table scanning, but they are of the same order of magnitude. Usually using an index is several times or even thousands of times faster than scanning a full table!

(33) Avoid using operations that consume resources:

SQL statements with DISTINCT, UNION, MINUS, intersect and order by will start the SQL engine to perform sorting functions that consume resources. Distinct needs to perform a sort operation once, while other distinct needs to perform a sort operation at least twice. Usually, SQL statements using union, minus and INTERSECT can be rewritten in other ways. If the SORT_AREA_SIZE of your database is well allocated, UNION, MINUS and INTERSECT can also be considered. After all, they are very readable.

(34) Optimize the group by:

In order to improve the efficiency of GROUP BY statement, we can filter out unnecessary records before GROUP BY. The following two queries return the same results, but the second query is obviously much faster.

Inefficient:

Select a job, AVG (sal)

Employee group jobs valid from job = "President" or job = "Manager":

Select a job, AVG (sal)

From electromagnetic pulse

Where JOB = "president"

Or JOB = 'MANAGER' group job.

?

Let's take a look at the implementation process in Oracle Bone Inscriptions.

Analyze how SQL statements work in Orcle.

A, a user sends an SQL request and opens a cursor;

B, storing SQL sentence syntax analysis, execution plan, data dictionary and other information into a memory * * * sharing pool;

C, reading relevant data blocks in the data file into the data buffer;

D, corresponding operations are executed, if the modification is made, a row-level lock is added first, and after confirmation, the contents recorded before and after the modification are stored in the redo log buffer;

E. return the result to the user and close the cursor.

Note: SQL statements are case-sensitive. If the same statement is case-sensitive,

No, oracle needs to perform analysis twice, and each sentence should be followed by ";" End.