Database optimization Scheme

1. Efficient SQL statement design:

Typically, you can use the following methods to optimize the performance of SQL for data operations:
(1) Reduce the number of queries to the database, that is, reduce requests for system resources, using distributed database objects such as snapshots and graphs to reduce the number of queries to the database.
(2) Try to use the same or very similar SQL statements to query, so that not only take full advantage of the parsed syntax tree in the SQL shared pool, the likelihood of the data being queried to hit in the SGA will be greatly increased.
(3) Avoid the execution of SQL statements without any conditions. SQL statements that do not have any conditions are usually executed with FTS, the database locates a block of data first, and then sequentially finds other data in order, which is a lengthy process for large tables.
(4) If you have constraints on the data in some tables, it is best to implement the SQL statement in the table with the description integrity rather than the SQL program.

2. Operator optimization

(1) In operator: often encountered in the use of the statement, it is necessary to replace it with exists, because Oracle processing in is done by or, even if the use of the index is very slow

(2) Not in operator: The index of the table cannot be applied. Replace with not EXISTS or (outer join + empty) scheme

(3) is NULL or NOT NULL operation: Inefficient: (index invalidation) SELECT ...                         　　　　From DEPARTMENT WHERE dept_code isnotnull; efficient: (index valid) SELECT ... From DEPARTMENT WHERE dept_code >=0;

(4) > and < operator (greater than or less than operator): replace > Efficient with >=: SELECT ... From DEPARTMENT WHERE dept_code >=0; inefficient: Select*from empwhere DEPTNO >3 The difference between the two is that the former DBMS will Jump directly to the first dept equals 4 record and the latter will first locate the Dept no=3 record and scan forward to the first dept greater than 3.

(5) Like operator: the LIKE operator can apply a wildcard query, the wildcard combination may reach almost arbitrary query, but if the use of a bad can produce performance problems, such as the "%5400%" such as the query does not reference the index, and "x5400%" The range index is referenced

(6) Replace distinct:exists with exists to make the query more rapid, because the RDBMS core module will return results immediately after the conditions of the subquery are met (inefficient): SelectDistinct dept_no,dept_namefrom DE PT D, EMP ewhere d.dept_no = e.dept_no (efficient): Select Dept_no,dept_namefrom DEPT D whereexists (select ' X ' from EMP ewhe RE e.dept_no = d.dept_no);

(7) 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 (efficient): SELECT Loc_id,loc_desc,regionfrom     Location where loc_id =ten unionselect loc_id, Loc_desc, egionfrom location where region = ' MELBOURNE ' (inefficient): SELECT loc_id,loc_desc,regionfrom location WHERE loc_id= Tenor region = ' MELBOURNE ' If you insist on or, you need to return the least-logged index column Written at the front.

(8) Replace or with in: inefficient: select....from location WHERE loc_id =tenor loc_id= or loc_id=efficiency: SELECT ... From location WHERE loc_in in (ten,+);

3. SQL Statement Structure optimization

(1), avoid using ' * ' in the SELECT clause:

(2), replace Delete with truncate:

(3), replace the HAVING clause with a WHERE clause:

(4), SQL statements in uppercase because Oracle always parses SQL statements first, lowercase letters are converted to uppercase and then executed.

(5), in the Java code to minimize the use of the connector "+" connection string!

(6), avoid changing the type of indexed columns:

7. Optimize GROUP BY: Improve the efficiency of the group BY statement by filtering out unwanted records before group by. The following two inefficiencies: 1SELECT job,avg (SAL) from EMP GROUPby jobhaving J Ob= ' president ' OR JOB = ' MANAGER '

Efficient: 1SELECT job,avg (SAL) from EMP WHERE job = ' President ' OR job= ' MANAGER ' GROUPby job

Database optimization Scheme