Oracle Database Optimization

Oracle Database Optimization

Information Management Department

Directory

One, the SELECT query statement to avoid the use of ' * ' ... ..... ..... ..... ..... ..... ..... ................ 2

Second, to reduce the number of database visits: ...... ..... ....... ......... ............................. 2

Third, the search for a single record ..... ..... ..... ...................... ..... ..... ..... ..... ..... .............. .......... 2

Four, choose the best table name order: ...... ..... ................. ....... ..... ................. ......... 2

V. The connection in the WHERE clause ..... ....... ................. ........ ................... ....... 3

Vi. use the Decode function to avoid duplicate scans of identical records or duplicate joins in the same table 3

Seven, the removal of the full table operation is recommended to use TRUNCATE do not recommend the use of delete ... 3

Try to use commit as much as possible: ........ ..... ............ ............................. 4

Nine, reduce the query of the table: ...... ..... ..... ................. ..... ..... ..... ................. .......... 5

x. Improve SQL efficiency through internal functions: ..... ... ..... ... ..... ..... ..... ..... ..... ..... ..... .................. 5

Xi. Use the alias of the table (alias): ....... ..... ............ ............................. 5

12, the common query conditions to set the index ..... ... ..... ..... ..... ..... ..... ..... ..... ..... ..... ..... ..... ...., ..... ....... 5

13. SQL statements are capitalized; Because Oracle always parses the SQL statements first, the lowercase letters are converted to uppercase and then executed. .................................................................................................................... 6

14. Use the connector "+" connection string as little as possible in Java code! ............... 6

XV, avoid using not on the index column ... ..... .... ... ..... ... ..... ..... ..... ..... ..... ..... ..... ..... ..... .... ..... ........ 6

16, avoid the use of the index on the calculation ........ ..... ......... ....................... ........ 6

17. Replace with >=, ..... ..... ....... ....................... ..... ..... ..... ..... ..... ............... ......... 6

18. Replace the or with Union (for the index column) ..... ..... ..... ..... ..... ..... ..... ..... ..... ....... 6

19, ordinary query with in to replace or ... ..... ... .... ... ..... ... ..... ..... ..... ..... ..... ..... ..... .............. 7

20. Avoid using is null on the index column, and is not null ...... .......... 7

21. Always use the first column of the index: ..... .... ... ..... ..... ..... .....? ........................ 7

22. Replace the union with Union-all (if possible): ...... 7

23. Avoid changing the type of the indexed column: ..... ... ..... ... ..... ..... ..... ..... ..... ..... ..... ..... .....? ............. 7

24. Some WHERE clauses do not use indexes ..... ..... ..... ..... ..... ..... ..... ..... ................ 7

25. Avoid the use of resource-intensive operations: ..... ... ..... ..... ..... ..... ..... ..... ..... ..... ................. 8

26. Optimize GROUP by ...... ..... .................... ....... ..... ....................... 8

27, ... ... ..... ..... .... ..... ..... ..... ....... .................... ..... ..... ..... ..... ..... ..... ..... ..... ..... ..... ..... ....... ....... 8

Avoid using ' * ' in a SELECT query statement

Second, reduce the number of database access:

Reduced database IO Operating pressure

Third, query a single record

Iv. Select the optimal table name order:

The Oracle parser parsing rule processes the FROM clause table name in right-to-left order, at which point the table with the fewest number of records or the crosstab (the table with the other reference) should be used as the underlying table (the last table written in the FROM clause)

V. Connections in the WHERE clause

The Oracle Parser parse WHERE clause is parsed from bottom to top, filtering for large amounts of data is recommended to be written at the end of the WHERE clause

Vi. use the Decode function to avoid duplicate scans of the same record or duplicate connections to the same table

Vii. Delete Full table operation recommended truncate not recommended for use with delete

Truncate will completely delete the data is unrecoverable and consumes less resources than delete.

① functionally, truncate is emptying the contents of a table, which is equivalent to the DELETE from table_name

②delete is a DML operation, truncate is a DDL operation, so deleting the entire table's data with delete results in a lot of roolback (rollback), consumes a lot of rollback segments (rollback segment), and truncate does not

③ in memory, delete data with delete, table space in its deleted data table occupies space, easy to use later, and it is "false" delete, equivalent to delete data in Windows with delete is to put the data in the Recycle Bin, can also be restored, Of course, if you restart the system (OS or RDBMS) at this time, it can't be restored!

With truncate to clear the data, the table space in the memory of its deleted data tables occupy the space will be immediately released, the equivalent of windows in Shift+delete delete data, can not be restored!

④truncate adjust high water mark and delete no; truncate, table HWM is returned to initial and next (the default) delete is not possible.

⑤truncate can only be table,view,synonym to Table,delete.

The ⑥truncatetable object must be in this mode, or have the permission to drop any table and delete either the object must be in this mode or be granted the delete Onschema. Permissions for table or delete any table

⑦ in the outer layer, after truncate or delete, the space occupied by it will be released

⑧truncate and delete delete data only, and drop deletes the entire table (structure and data)

Tip: When you delete a large amount of data (most of the data in a table),

First, the data that does not need to be deleted is copied to a temporary table;

trunc table tables;

Copy the data that does not need to be deleted back.

Viii. use commit as much as possible:

Reduce as much as possible with the resources freed by commit requirements:

Resources Freed by Commit:

A. Information for recovering data on a rollback segment.

B. Locks acquired by program statements

C. Space in the Redo Logbuffer

D. Oracle manages internal spending on 3 of these resources

Ix. reduce the query on the table:

In the SQL statement that contains the subquery, pay special attention to reducing the query on the table.

Improve SQL efficiency through internal functions.:

Complex SQL often sacrifices execution efficiency. More inclined to use the function to solve the problem

Xi. alias using the table:

When you concatenate multiple tables in an SQL statement, use the alias of the table and prefix the alias to each column. This reduces the time to parse and reduces the syntax errors caused by column ambiguity.

12. Set indexes on common query conditions

An index is a conceptual part of a table used to improve the efficiency of retrieving data, and Oracle uses a complex self-balancing b-tree structure. In general, querying data through an index is faster than a full table scan. The Oracle Optimizer uses the index when Oracle finds the best path to execute queries and UPDATE statements. Also, using indexes when joining multiple tables can improve efficiency. Another advantage of using an index is that it provides the uniqueness of the primary key (PrimaryKey) Validation: Those long or long raw data types, you can index almost all the columns. In general, using indexes in large tables is particularly effective. Of course, you will also find that using indexes can also improve efficiency when scanning small tables. Although the use of indexes can improve the efficiency of query, but we must also pay attention to its cost. Indexes require space to store, and they need to be maintained regularly, and the index itself is modified whenever a record is added to a table or the index column is modified. This means that each record's insert, DELETE, and update will pay more than 4, 5 disk I/O. Because indexes require additional storage space and processing, those unnecessary indexes can slow query response time. It is necessary to periodically refactor the index:

ALTER index<indexname> REBUILD <TABLESPACENAME>

13. SQL statements are capitalized; Because Oracle always parses the SQL statements first, the lowercase letters are converted to uppercase and then executed.

14. Use the connector "+" connection string as little as possible in Java code!

XV, avoid using not on indexed columns

Not produces the same effect as using functions on an indexed column. When Oracle "encounters" not, he stops using the index instead of performing a full-table scan.

16. Avoid using calculations on indexes

An indexed column is part of a function. The optimizer will use a full table scan without using an index.

17, replace > with >=

Data logging processing mechanism issues

18. Replace or with union (for indexed columns)

Replacing or in the WHERE clause with union will have a good effect. Using or with an indexed column will result in a full table scan. Note that the above rules are valid only for multiple indexed columns. If a column is not indexed, the query efficiency may be reduced because you did not select or

19. Replace or with in for normal query

20. Avoid using IsNull and is not NULL on index columns

Try to avoid null values in the field information and Null fields in the index will cause Oracle to not use the index

21. Always use the first column of an index:

If the index is built on more than one column, the optimizer chooses to use the index only if its first column (leading column) is referenced by a WHERE clause. This is also a simple and important rule, when referencing only the second column of an index, the optimizer uses a full table scan and ignores the index

22. Replace union with Union-all (if possible):

When the SQL statement requires a union of two query result sets, the two result sets are merged in a union-all manner and then sorted before the final result is output. If you use UNION ALL instead of union, this sort is not necessary.

UNION all repeats the same record in the two result set, so please use it according to your specific requirements.

23. Avoid changing the type of indexed columns:

Oracle automatically makes simple type conversions to columns when comparing data of different data types. .

24. Some WHERE clauses do not use indexes

(1) '! = ' will not use the index. Remember, the index can only tell you what exists in the table, not what does not exist in the table.

(2) ' | | ' is a character join function. As with other functions, the index is deactivated.

(3) ' + ' is a mathematical function. As with other mathematical functions, the index is deactivated.

(4) The same index columns cannot be compared to each other, which will enable full table scanning.

25. Avoid using resource-intensive operations:

SQL statements with Distinct,union,minus,intersect,orderby start the SQL engine

Performs a resource-intensive sorting (sort) function. Distinct requires a sort operation, while the others need to perform at least two sorting. Typically, SQL statements with union, minus, and intersect can be overridden in other ways.

26. Optimize GROUP BY

Increase the efficiency of the group BY statement by filtering out unwanted records before group by

Oracle Database Optimization