1. Select 3 of the optimizer of Oracle Oracle Oracle
A, Rule (Based on Rules) B, COST (Based on Cost) C, Choose (Selective)
Set the default optimizer, you can pass the various declarations of the Optimizer_Mode parameter in the init.ora file, such as Rule, Cost, Choose, All_Rows, First_ROWS. Of course, it is also overwritten on a SQL sentence class or a session.
To use a cost-based optimizer (CBO, COST-BASED OPTIMIZER), you must regularly run the Analyze command to increase the accuracy of object statistics in the database.
If the database's optimizer mode is set to selectability, the actual optimizer mode will be related to whether the Analyze command is running. If Table has been passed by Analyze, the optimizer mode will automatically become CBO. Conversely, the database will use an optimizer in the form of Rule.
By default, Oracle uses a Choose optimizer, in order to avoid Full Table Scan, you must try to avoid using the Choose optimizer without using a rule or cost-based optimizer.
2. Visit the way Oracle uses the ways recorded in both access tables:
A, full table scan
The full table scan is the sequential access to each record in the table. Oracle optimizes full mete scans in a way in which multiple data blocks (Database Block) is read.
B, access to the table via RowID
You can use ROWID-based access methods, improve access table efficiency, and RowID contains physical location information recorded in the table. Oracle uses indexing (index) to implement the link between data and storage data. Usually the index provides a way to quickly access the ROWID, so those queries based on the index column can be improved.
3. Shared SQL statement In order not to repeat the same SQL statement, after the first resolution, Oracle stores the SQL statement in memory. The memory in the shared buffer pool of the system global area SGA (SYSTEM GLOBAL AREA) can be shared by all database users. So when you perform a SQL statement (sometimes referred to as a cursor), if it and the previously performed statements are exactly the same, Oracle can quickly obtain the statements that have been parsed and the best execution path. Oracle's feature greatly increases SQL execution performance and saves the use of memory.
Unfortunately, Oracle only provides cache buffering for simple tables, which does not apply to multi-table connection queries.
The database administrator must set the appropriate parameters for this area in Init.ora, the larger the memory area, the more statements can be retained, and the possibility of being shared is, the larger.
When you submit an SQL statement to Oracle, Oracle will first find the same statement in this memory. What needs to be indicated here is that Oracle takes a strict match to reach a sharing, and the SQL statement must be identical (including space, wrap, etc.).
The database administrator must set the appropriate parameters for this area in Init.ora, the larger the memory area, the more statements can be retained, and the possibility of being shared is, the larger.
The shared statement must meet three conditions:
A, Charity comparison: The currently executed statement and the statement in the shared pool must be exactly the same.
B, the objects referred to in both statements must be identical:
C, the bound variables of the same name must be used in two SQL statements.
4. Select the most efficient table order (which is only in the rule-based optimizer) Oracle's parser handles the table name in the FROM clause in order from right to left, so the FROM clause is written in the last Table (Basic Table DRIVING TABLE) will be processed first. In the case where multiple tables are included in the FROM clause, you must select the number of tables that the number of records is the basic table. When the Oracle handles multiple tables, they will be used to connect them. First, scan the first table (the last table in the FROM clause) and sequence the record, then scan the second table (the last second table in the FROM clause), and finally all from the second table The records in the middle search are combined with the appropriate records in the first table. If there are more than 3 table connection queries, you need to select a crosstum (INTERSECTION TABLE) as a base table, and the crosstab refers to the table referenced by the other tables.
5. Connection sequence inwhere clause Oracle parses the WHERE clause from the bottom-up order, according to this principle, the connection between the table must be written before the other WHERE condition, the conditions that can be filtered out must be written. At the end of the WHERE clause.
6. SELECT clause Avoid using '*' When you want to list all column in the Select clause, use the dynamic SQL column reference '*' is a convenient method. Unfortunately, this is a very low-efficiency method. In fact, Oracle is converted into all column names during parsing, which is done by querying the data dictionary, which means more time.
7. Reduce the number of access to the database When performing each SQL statement, Oracle has performed a lot of work inside: parsing the SQL statement, estimating the utilization of the index, binding variables, reading data blocks, etc. It can be seen that the number of access to the database can actually reduce the workload of Oracle.
8. Use the Decode function to reduce processing time using the DECODE function to avoid repeated scans the same record or repeat the same table.
9. Integrate simple, unrelated database access If you have a few simple database query statements, you can integrate them into a query (even if they have no relationship)
10. Delete Record
11. Use Truncate to replace DELETE When the record is deleted, in general, the Rollback Segments is used to store information that can be recovered. If you don't have a Commit transaction, Oracle restores the status before the deletion (accurately said to be restored to the status before performing the delete command).
When using Truncate, the rollback segment no longer stores any information that can be recovered. Data cannot be recovered when the command is running. Therefore, few resources are called, and the execution time will be very short.
12. Try to use Commit as much as possible, as much as possible in the program, so that the performance of the program is improved, the demand will also reduce the resources released by Commit.
Resources released by Commit:
A. Information for recovering data on a rollback segment.
B, the lock obtained by the program statement.
C, the space in the Redo log buffer.
D, oracle is cost-effective to manage the internal three resources.
13. The number of recordings and the general view, count (*) is slightly faster than count (1), of course, if you can retrieve it, the count of the index column is still the fastest. For example count (Empno)
14. Replace the Having clause with WHERE clause to avoid using a Having clause, and Having will only filter the result set after retrieving all records. This process requires sorting, totaling, etc. If the number of records can be restricted through the WHERE clause, it can reduce the overhead of this. 15. Reduce the query of the table In the SQL statement containing the child, pay special attention to reduce the query of the table.
16. Improve SQL efficiency by internal functions.
17. Use the alias (ALIAS) When you connect multiple tables in the SQL statement, use the alias of the table and embark on the alias on each column. In this way, the parsing time can be reduced and reduced the grammatical errors that are caused by the ambiguity of Column.
18. Instead of Exists in in many base tables, in order to meet a condition, another table is often needed to join. In this case, using Exists (or NOT EXISTS) will usually improve the efficiency of the query.
19. Instead of NOT EXISTS NOT in subquery, the Not IN clause will execute an internal sort and merge. No matter which case, Not in is minimal (because it performs a full table traversal in the table in the subquery). To avoid using NOT IN, we can rewrite the Outer Join or Not Exists.
20. Connecting Exists using a table Usually, using a table connection than EXISTS more efficient
21. Replace DistINCT with Exists When submitting a query containing a pair of multi-table information (such as department tables and employee tables), avoid using DISTINCT in the SELECT clause. Generally consider replacement with exist
