SQL statement optimization for database performance optimization

First, the issue of the proposed

At the early stage of the development of application system, due to the development of the database data is relatively small, for SQL query, complex view of writing experience not the performance of the pros and cons of the SQL statement variety of writing, but if the application system will be submitted to the actual application, with the increase of data in the database, the response speed of the system becomes one of the most important problems need to solve in the current system. A very important aspect of system optimization is the optimization of the SQL statement. For huge amounts of data, the velocity difference between SQL statements inferior quality SQL statements and can reach hundreds of times, visible for a system is not simple to achieve its function can be, but to write the SQL statement for the high quality, improve the system availability.

In most cases, the Oracle index is used to quickly traverse table, the optimizer mainly according to the definition of the index to improve performance. However, if the SQL code written in the where clause of the SQL statement is not reasonable, it willCause the optimizer uses a full table scan and delete index, in general, the SQL statement is the so-called inferior SQL statement. In writing the SQL statement we should clear the optimizer based on what kind of principle to delete the index, which is helpful to write high performance SQL statements.

Two, SQL statement prepared to pay attention to the problem

The following are some of the where statements in the preparation of the SQL clause need to pay attention to the details of the problem. In the where clause, even if some of the columns are indexed, but because the preparation of the inferior SQL system in running the SQL statement also cannot use the index, but also used to a full table scan, which resulted in the greatly decreased the response speed.

1 operator optimization

(a) IN operator

Written by SQL IN advantage is relatively easy to write and clear and easy to understand, which is more suitable for the development of modern software style. But with the SQL IN performance is always relatively low, from the Oracle implementation of the steps to analyze with the SQL IN and do not have the following SQL IN:

ORACLE attempts to convert it into a number of table connections, if the conversion is not successful in the implementation of the IN inside the sub query, and then query the outer table records, if the conversion is directly connected to the use of multiple table connection query. This shows that with SQL IN at least one conversion process. General SQL can be converted to success, but for the SQL can not be converted into a group of statistics, etc..

Recommended program:In the business intensive SQL which as far as possible not to use the IN operator, with the EXISTS program instead.

(NOT) IN B operator

This operation is not recommended because it cannot apply the index of the table.

Recommended program:Replace with EXISTS NOT solution

(c) NULL IS or NOT NULL IS operation(whether the field is empty)

The index is not used to determine whether the field is empty, since the index is not an empty value. Cannot use null as an index, any column that contains the null value will not be included in the index. Even if the index columns of this case, as long as these columns in a column containing null, the column will be excluded from the index. That is if a column has a null value, even if the column index does not improve performance. Any use of is null or is not null in the where clause of the statement is not allowed to use the optimizer index.

Recommended program: replace the same functions with other operations, such as: is not null a>0 to a or a> ', etc.. Field is not allowed, and a default value is used instead of the null value, such as the status field in the application is not allowed to be empty, the default is the application.

(d) > and < operator (greater than or less than the operator)

Is greater than or less than operator under normal circumstances is without adjusting the, because it has the index will be used to index lookup, but in some cases can be to optimize it, such as a table with 100 million records, a numeric field a, 30 million recorded in addition, 30 million records a, 39 million recorded a = 2, 1 million records of a = 3. Then the effect of the implementation of A>2 and A>=3 is very different, because when ORACLE A>2 will find out for 2 of the record index to compare, and ORACLE when A>=3 directly to find the =3 record index.

(E) LIKE operator

Like operator can use wildcard queries, inside the combinations of wildcards may reach almost arbitrary queries, but if used properly will produce performance problems, such as like '%5400%' this kind of query not citation index, and like 'X5400%', will reference index.

A practical example: YW YW_YHJBQK operating behind the number the user ID can be to query 'business number YY BH like'%5400% produced a full table scan, if you change the YY BH like 'X5400% or YY BH like' B5400% 'will use YY BH index of two range query and performance certainly is greatly improved.

With wildcards (%) of the like statement:

Also take the example above to see the situation. At present, the demand is this, the requirements in the employee table in the name of the query contains cliton. We can use the following query SQL statement:

One	Select *From EmployeeWhere Last_nameLike '%cliton%';

Here the wildcard (%) appear in the search for the first word, so the Oracle system does not use the last_name index. In many cases may not be able to avoid this situation, but must be in the heart bottom, wildcards such use will reduce the query speed. However, when wildcard appeared in a string of other position, the optimizer can use index. Index is used in the following query:

One	Select *From EmployeeWhere Last_nameLike 'c%';

(f) UNION operator

UNION in the table after the link will be filtered out repeated records, so after the table will be linked to the results set for sorting operations, delete duplicate records and then return the results. In most applications, it does not produce duplicate records, the most common is the process table and the history table UNION. Such as:

One Two Three

Select *From Gc_dfys Union  Select *From Ls_jg_dfys

This SQL at run time to take out the results of the two table, and then sort the space to sort delete duplicate records, and finally return to the result set, if the amount of table data can lead to a large amount of disk.

Recommended program:Use the ALL UNION operator to replace the UNION, because the ALL UNION operation is simply the result of the merger of the two returned.

One Two Three

Select *From Gc_dfys Union All  Select *From Ls_jg_dfys

(g) join columns

The join column, even if the connection value is a static value, the optimizer is not use index. Together we look at an example, if an employee table (employee), for a worker's first and last name is divided into two columns stored (first name and last name), and now want to query a named bill. Bill Cliton Clinton's staff.

The following is a SQL statement using the join query:

One	Select *From EmployssWhere First_name||'||last_name =Cliton''Beill;

The statement above can query whether the bill Cliton the employee, but note here, system optimizer index based on the last name created without the use of. When using the following SQL statements, last_name system can be used to create an index based on Oracle.

One	Where First_name ='Beill' And Last_name ='Cliton';

(H) by Order statement

BY ORDER statement determines how the Oracle will return the results of the query sort. The by Order statement does not have any special restrictions on the columns to sort, or you can add a function to the column (such as join or attach, etc.). Any non - index entry in the by Order statement, or a calculated expression, will reduce the query speed.

Carefully check the by order statement to find out the non index terms or expressions, which will reduce the performance. The solution to this problem is to rewrite the by order statement to use the index, also can be used for the column to establish another index, and should absolutely avoid the use of by order clause in the expression.

(I) NOT

We often use some logical expressions in the where clause in the query, such as greater than, less than, equal to, and not equal to, etc., can also use and (with), or (or) and not (non). NOT can be used to counter any logic operation symbol. The following is an example of a NOT clause:

One	Where Not (status ='VALID')

If you want to use NOT, you should add the bracket to the back of the phrase, plus the NOT operator before the phrase. The NOT operator is contained in another logical operators, this is not equal to (< >) operator. In other words, even if the NOT clause is not explicitly added to the query where clause, the NOT is still in the operator, see the next example:

One	Where < > status'INVALID';

For this query, you can overwrite it without using NOT:

One	Select *From EmployeeWhere Salary<3000Or Salary>3000;

Although the results of these two kinds of queries, but the second kinds of query plan will be faster than the first query program. The second query allows the Oracle to use an index to the salary column, and the first query cannot use the index.

2 the influence of SQL writing

(a) affect the same function of the same performance of writing SQL.

Such as a SQL in the A programmer to write for Select * zl_yhjbqk from

B programmers write for Select * dlyx.zl_yhjbqk from (with table owner prefix)

C programmers write for Select * DLYX.ZLYHJBQK from (capital table name)

D programmer to write the Select * DLYX.ZLYHJBQK from (middle of the space)

Above four SQL in Oracle analysis after the collation of results and execution time is the same, but from the Oracle shared principle of SGA memory can be that Oracle SQL for each will be an analysis of its, and occupy the shared memory, if the SQL string pass type written exactly the same, Oracle will only an analysis, shared memory is left will be a result of the analysis, which can not only reduce the time of SQL, and can reduce the repeated memory information sharing, Oracle can also accurate statistics SQL execution frequencies.

(b) the order of the conditions behind the WHERE

The conditional order in the back of the WHERE clause will have a direct impact on the query of the large scale data sheet. Such as:

One Two

Select *From Zl_yhjbqkWhere Dy_dj ='1KV below ' And Xh_bz=1 Select *From Zl_yhjbqkWhere Xh_bz=1And Dy_dj ='1KV below '

More than two SQL dy DJ (voltage levels) and XH BZ (cancellation mark), first SQL 99% records are dy DJ and XH BZ, and in the second SQL 0.5% records of Dy DJ and XH BZ, in order to be able to draw the second SQL CPU occupancy rate significantly lower than the first two fields are not indexed, so when the execution is a full table scan, the first SQL dy DJ = 'below 1kV' conditions in record set ratio was 99%, and ratio of XH Xh_bz=1 only for 0.5%.

(c) the effect of the order of the query table

In from the back of the table list the order will affect the performance of SQL execution, in no index, Oracle, no table statistical analysis, Oracle will link according to the order table, the table it can be seen in the wrong sequence will produce very labor-intensive service device resources number according to the cross. (Note: if the table for statistical analysis, ORACLE automatically advanced small table, then the big table)

3 use of SQL statement index

(a) some optimization of the conditional fields

The field that the function process cannot use the index,Such as:

One Two

Substr (hbs_bh, 1,4) = '5400', optimized processing: hbs_bhLike '5400%' TRUNC (sk_rq) =trunc (sysdate), optimized processing: sk_rq>=trunc (sysdate)And Sk_rq<trunc (sysdate+1)

Fields that perform explicit or implicit operations cannot be indexed, such as: ss_df+20>50, optimized processing: ss_df>30

One Two

The 'X' hbs_bh> 'X5400021452' ||, optimization: hbs_bh> 5400021542 Sk_rq+5=sysdate, optimized processing: sk_rq=sysdate-5

Hbs_bh=5401002554, optimized processing: hbs_bh= '5401002554', note: this condition carries on the implicit to_number conversion to hbs_bh, because the hbs_bh field is the character type.

The condition includes a number of tables in the field operations can not be indexedSuch as:

One Two

Ys_df>cx_df, can not be optimized Qc_bh || kh_bh= '5400250000', '5400' Optimization: qc_bh=And Kh_bh= '250000'

4 more aspects SQL optimization information sharing

(1) select the most efficient sequence (table name only based on effective rule optimizer):

Oracle parser follow from right to left order processing in the from clause is the name of the table. In the from clause written in the final table (base table driving table) will be processed first, to include multiple tables in the from clause, you must choose to record a minimum number of the table as the base table. If there are more than 3 table join queries, then you need to select a cross table (table intersection) as the base table, the cross table refers to the table referenced by the other table.

(2) the order of the connections in the WHERE clause:

Oracle uses the bottom-up order analytical the where clause, according to this principle, between the sheets of the connection must write before the other a where clause that can filter out the maximum number of records that must be written at the end of the where clause.

(3) to avoid the use of "*" in the SELECT clause:

Oracle in the analytical process, will be '*' successively converted all column names, this work is by querying the data dictionary, which means that it will spend more time.

(4) reducing the number of accesses to the database:

ORACLE in the internal implementation of a lot of work: parsing SQL statements, the use of the index to estimate the index, the binding variable, read data blocks, etc..

(5) in the SQL*Plus, SQL*Forms and Pro*C to reset the ARRAYSIZE parameters, you can increase the amount of data for each database access, the recommended value of 200.

(6) using the DECODE function to reduce the processing time:

Use the DECODE function to avoid duplicate scans of the same table with the same record or duplicate connection.

(7) the integration of simple, non relational database access:

If you have a few simple database queries, you can integrate them into one query (even if they don't have a relationship).

(8) delete duplicate records:

The most efficient way to remove duplicate records (because of the use of ROWID) example:

One	DELETE  FROM  EMP EWHERE  E.ROWID > (SELECT MIN(X.ROWID)FROM  EMP XWHERE  E.EMP_NO = X.EMP_NO.

(9) using TRUNCATE instead of DELETE:

When you delete a record in the table, under normal circumstances, the rollback segments to store information can be recovered. If you do not commit transaction. Oracle will data recovery to remove before the state (or more accurately is restored to before executing the erase command) and when applied to truncate, rollback segment no longer store any information can be recovered. When the command is run, data cannot be is restored. Therefore little resource is invoked, execution time will also very short. The translator to truncate only delete the appropriate table, truncate is not DML DDL).

(10) as much as possible to use COMMIT:

As long as possible, as much as possible in the process of using COMMIT, so the performance of the program has been improved, the demand will be reduced because of the release of COMMIT resources, COMMIT released by the resources:

The A. rollback segment is used to recover the data information.

B. the lock is obtained by the program statement.

Space in redo log buffer C.

ORACLE D. to manage the internal cost of these 3 kinds of resources

(11) replace the HAVING clause with the Where clause:

Avoid the use of the HAVING clause, HAVING only after retrieving all records of the result set is filtered. This process requires sorting, total operation. If the WHERE clause limits the number of records, it can reduce this overhead. (non Oracle) on, where, having this clause three can be with conditions, on is the first implementation of where, having finally, because on is the first to do not meet the conditions of the record filtering only after the statistics, it can reduce the intermediate operations of data to be processed, supposedly is the fastest, where should be faster than the having, because of its filtering the data after sum, in the two table connection when using on, so in a table when compared with having where on the left. In the single table query and statistics of, if filtering condition not related to calculation of the field, that their results is the same, but where can use Rushmore technology, and having cannot, in the speed the latter to slow if involves calculation of word segment, said before did not calculate the field value is uncertain. According to the workflow of the preceding write, where the role of time is before the calculation is completed, and having is in the calculation is used, so in this case, the two results will be different. In the multi table join query, on more than where to get up early effect. System first according to the connection between the various table conditions, the synthesis of a number of tables a temporary table, and then filtered by the where, and then calculated, after the calculation and then filtered by having. It can be seen that, in order to filter the conditions play a correct role, first of all to understand that this condition should be at what time to play, and then decided to put it there.

(12) reduce the query table:

In the presence of the SQL statement in the subquery, special attention should be paid to reduce the table query example:

One	SELECT  TAB_NAMEFROM TABLESWHERE (TAB_NAME, DB_VER) = ()SELECT TAB_NAME, DB_VERFROM  TAB_COLUMNSWHERE  VERSION = 604)

(13) to improve the efficiency of SQL through the internal function:

Complex SQL often sacrifice the efficiency of the implementation. It is very meaningful to master the method of using function to solve the problem in the practical work.

(14) using the table alias (Alias):

When connecting multiple tables in a SQL statement, use the name of the table and prefix the alias on each Column. This way, you can reduce parsing time and reduce the syntax errors caused by Column ambiguity.

(15) using EXISTS instead of IN, using EXISTS NOT instead of IN NOT:

In many based on the underlying table query, in order to satisfy a condition, often need to join the another table. In this case, using exists (or not exists) will usually improve query efficiency. In a subquery, not in clause will perform an internal sorting and merging. Whether in what kind of situation, not in are the most inefficient (because it subquery table performs a full table scan). In order to avoid the use of not in, we can rewrite it into connection (the outer) or not exists.

example

One Two

(efficient)SELECT *FROM  EMP (basic table)WHERE  EMPNO > 0AND  EXISTS (SELECT "X'DEPT WHERE DEPT.DEPTNO EMP.DEPTNO = AND LOC FROM =' MELB'') (inefficient)SELECT  *FROM  EMP (basic table)WHERE  EMPNO > 0AND  DEPTNOIN(SELECT DEPTNOFROM  DEPTWHERE  LOC = 'MELB' '

(16) identify 'inefficient implementation' of the SQL statement:

Although there are a variety of graphical tools on the SQL optimization, but to write their own SQL tool to solve the problem is always a best way:

One Two Three Four Five Six Seven Eight Nine

SELECT  EXECUTIONS, DISK_READS, BUFFER_GETS, ROUND ((BUFFER_GETS-DISK_READS) /BUFFER_GETS, 2) Hit_radio, ROUND (DISK_READS/EXECUTIONS, 2) Reads_per_run, SQL_TEXT FROM  V$SQLAREA WHERE  EXECUTIONS>0 AND  BUFFER_GETS > 0 AND  (BUFFER_GETS-DISK_READS) /BUFFER_GETS < 0.8 ORDER BY  FourDESC;

(17) using the index to improve efficiency:

The index is a list of some of the concepts, in order to improve the efficiency of data retrieval, Oracle uses the a complex self balanced B-tree structure. Usually, the index query data faster than a full table scan. When Oracle identify implementation is the best way to query and update statements, Oracle optimizer will use the index. In the same connection tables using the index can also improve the efficiency. On the other a use indexing benefits is. It provides a primary key uniqueness verification.. Those of LONG or LONG RAW data type, you can index almost all columns. Usually, the effective use of special index in large table. Of course, you will also find that, in the small scanning table, using the index can also improve efficiency. Although the use of the index can be query efficiency, but we must also pay attention to it the price index need space to store, also need regular maintenance, whenever a record in a table or index or column to be modified, the index itself will also be modified. This means that each record INSERT, DELETE, UPDATE will do more than pay 4, 5 times the disk I/O. Because the index need storage space and additional, unnecessary index will make the query response time is slow instead. It is necessary to reconstruct the index regularly:

One	ALTER  INDEX REBUILD <TABLESPACENAME> <INDEXNAME>

(18) replace DISTINCT with EXISTS:

When submitting a contains a pair of multi table information (such as the Department table and the employees table) query, to avoid in the select clause in use DISTINCT. can generally be considered to be replaced by a exist, exists, the query more quickly, because RDBMS core module in subquery once met and returns its result immediately. Examples:

One Two Three Four

(inefficient): SELECT  DISTINCT  DEPT_NO, DEPT_NAMEFROM  D EMP, E DEPTWHERE  D.DEPT_NO = E.DEPT_NO (efficient): SELECT  DEPT_NO, DEPT_NAMEFROM  DEPT DWHERE  EXISTS (SELECT "X'FROM  EMP EWHERE D.DEPT_NO = E.DEPT_NO;

(19) SQL statements in upper case; because Oracle always first parse the SQL statement, the lowercase letters are converted to upper case.

(20) in the Java code as little as possible with the connection "+" connection string!

(21) to avoid in the column index on the use of not, usually we have to avoid in the column index on the use of not, not will produce in and around the column index on the use function of the same effect. When the oracle "encounter" not, he will stop using the index instead perform a full table scan.

(22) avoid the use of computing on index columns

The WHERE clause, if the indexed column is a part of the function. The optimizer will not use the index using a full table scan. For example:

One Two Three Four

Low efficiency: SELECT ...FROM  DEPTWHERE SAL * 12 > 25000; High efficiency: SELECT ...FROM DEPTWHERE SAL > 25000/12;

(23) with alternative > >

One Two Three Four

High efficiency: SELECT *FROM  EMPWHERE  >=4 DEPTNO Low efficiency: SELECT *FROM EMPWHERE >3 DEPTNO

The difference between them is that the former DBMS will jump directly to the first DEPT is equal to 4 of the record and the latter will first locate the DEPTNO=3 record and forward scan to the first DEPT greater than 3 of the record.

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

Usually case, with the union replaced or will be in the where clause to good effect. On the indexed column or use will cause a full table scan. Note that these rules only for multiple index columns are valid. If the column is not indexed, query efficiency may because you have no choice or decreased. In the following example, LOC ID and region are built index.

One Two Three Four Five Six Seven Eight Nine Ten Eleven Twelve

High efficiency: SELECT LOC_ID, LOC_DESC, REGION FROM LOCATION WHERE LOC_ID = 10 UNION  SELECT LOC_ID, LOC_DESC, REGION FROM LOCATION WHERE REGION = "MELBOURNE" Low efficiency: SELECT LOC_ID, LOC_DESC, REGION FROM LOCATION WHERE LOC_ID = 10OR REGION = "MELBOURNE"

If you insist on using OR, you need to return to record the least indexed column in the front.

(25) using IN to replace OR

This is a simple, easy to remember the rules, but the actual effect of the implementation must test, in Oracle8i, both execution path seems to be the same.

One Two Three Four

Low efficiency: SELECT... .FROM LOCATIONWHERE LOC_ID = 10OR LOC_ID = 20OR LOC_ID = 30 Efficient SELECT...FROM LOCATIONWHERE LOC_ININ (10,20,30);

(26) avoid using NULL IS and NOT NULL IS on indexed columns

Avoid the use of any index can be empty column, ORACLE will not be able to use the index. For single index, if the column contains a null value, the index will not exist for this record. For the composite index, if each column is empty, the index does not exist in the same record. If at least one column is not null that is recorded in the index. For example: if the only index based on the A and B columns of the table, and there is a record in the A table, B value (123, null), ORACLE will not be accepted with the same A under a B value (123, null) records (insert). However, if the indexed column all is empty, ORACLE will think the key is empty and empty is not empty. So you can insert 1000 records with the same key value, of course they are empty! Because the null value does not exist in the columns in the index, so the WHERE clause of the index column Comparison of null values will enable ORACLE to disable the index.

One Two Three Four

Low efficiency: (index invalid) SELECT ...FROM  DEPARTMENTWHERE  DEPT_CODEIS NOT NULL; High efficiency: (index valid) SELECT ...FROM  DEPARTMENTWHERE  >=0 DEPT_CODE;

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

If the index is built in multiple columns, only in the first column (leading) column is where clause references, the optimizer will select using the index. This is a simple but very important rule, when the only reference index of the second column, the optimizer to use the full table scan and ignore the index.

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

When SQL statements need union two query result set, the two results set will UNION-ALL merged, and in front of the results of the final output to sort. If the union are replaced with the union all, this sort is not necessary. Efficiency will be therefore improved. Need to pay attention to is, union all will repeat the output two result sets in the same record. So you still have to from the business requirements analysis, feasibility of using union all. Union of the results set order. This operation will use to sort area size piece of memory. For this memory optimization is also very important. The following SQL can be used to query the sort of consumption

One Two Three Four Five Six Seven Eight Nine Ten Eleven Twelve Thirteen Fourteen Fifteen Sixteen

Low efficiency: SELECT  ACCT_NUM, BALANCE_AMT FROM  DEBIT_TRANSACTIONS WHERE TRAN_DATE ='31-DEC-95'  UNION  SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE ='31-DEC-95'  High efficiency: SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE ='31-DEC-95'  UNION ALL  SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE ='31-DEC-95'

(29) using WHERE instead of BY ORDER:

The BY ORDER clause only uses indexes in two kinds of strict conditions.

All columns in the BY ORDER must be included in the same index and are kept in the order of the index.

All columns in BY ORDER must be defined as non null.

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

For example:

Table DEPT contains the following columns:

One Two Three

PK DEPT_CODE不 空  dept_desc不 空  dept_type空

一 二 三 四

低效：（索引不被使用） 选择 dept_code从  系秩序 通过  dept_type 高效：（使用索引） 选择 dept_code从  系在哪儿  dept_type > 0

（30）避免改变索引列的类型：

当比较不同数据类型的数据时，Oracle自动对列进行简单的类型转换。

假设EMPNO是一个数值类型的索引列。

一	选择 …从 EMP在哪儿  empno =“123”

实际上，经过Oracle类型转换，语句转化为：

一	选择 …从 EMP在哪儿  to_number empno =（'123”）

幸运的是，类型转换没有发生在索引列上，索引的用途没有被改变。

现在，假设emp_type是一个字符类型的索引列。

一	选择 …从 EMP在哪儿 emp_type = 123

这个语句被Oracle转换为：

一	选择 …从 EMP在哪儿 to_number（emp_type）= 123

因为内部发生的类型转换，这个索引将不会被用到！为了避免Oracle SQL进行隐式的类型转换对你的，最好把类型转换用显式表现出来。注意当字符和数值比较时，Oracle会优先转换数值类型到字符类型。

分析

一	选择   emp_name型员工在哪儿   工资＞3000

在此语句中若工资是浮类型的，则优化器对其进行优化为转换（浮，3000），因为3000是个整数，我们应在编程时使用3000而不要等运行时让进行转化同样字符和整型数据的转换DBMS。

（31）在子句需要当心的：

某些选择语句中的哪里子句不使用索引。这里有一些例子。

在下面的例子里，（1）”！=“将不使用索引。记住，索引只能告诉你什么存在于表中，而不能告诉你什么不存在于表中。（2）“¦¦”是字符连接函数。就象其他函数那样，停用了索引。（3）“+”是数学函数。就象其他数学函数那样，停用了索引。（4）相同的索引列不能互相比较，这将会启用全表扫描。

（32）A.如果检索数据量超过的表中记录数使用索引将没有显著的效率提高30%。B.在特定情况下，使用索引也许会比全表扫描慢，但这是同一个数量级上的区别。而通常情况下，使用索引比全表扫描要块几倍乃至几千倍！

（33）避免使用耗费资源的操作：

带有鲜明、联盟、减、相交，通过的SQL语句会启动SQL引擎执行耗费资源的排序顺序（排序）功能。不同的需要一次排序操作，而其他的至少需要执行两次排序。通常，带有联盟、减、相交的SQL语句都可以用其他方式重写。如果你的数据库的sort_area_size调配得好，使用联盟、减、相交也是可以考虑的，毕竟它们的可读性很强。

（34）优化组：

提高组语句的效率，可以通过将不需要的记录在组下面两个查询返回相同结果但第二个明显就快了许多之前过滤掉。

一 二 三 四 五 六 七 八 九 十 十一 十二

低效： 选择 工作，AVG（萨尔） 从 EMP 组 通过 工作 有 工作=总裁' 或工作=“经理”  高效： 选择 工作，AVG（萨尔） 从 EMP 在哪儿 工作=总裁' 或工作=“经理”  组 通过 工作