Improved --eg method the efficiency of database: mass circulation insert data si

In a recent study SQL query optimization, try to get a large amount of data of database is used to test the SQL statement, write the following stored procedure, using a circular insertion method, efficiency difference, the parameter is set to 30000, running 20 minutes not to run. How to optimize not like a stored procedure? And if you know which blog SQL query optimization aspects of the article, all trouble recommend what, thank you!
Copy code
create PROCEDURE [dbo].[P_InsertAdminInfo] @dataNumber INT -- To insert data AS ------------------------------- set nocount on ------------------------------ DECLARE @count INT; -- Counter DECLARE @adminLoginName NVARCHAR(50); -- Administrator login DECLARE @adminPassWord NVARCHAR(50); -- The administrator password DECLARE @adminLevel INT; -- Administrator level SET @count=1; SET @adminPassWord='111111'; WHILE(@count<=@dataNumber) BEGIN SET @adminLoginName='manager'+CAST(@count AS VARCHAR); SET @adminLevel=CAST(CEILING(RAND()*2) AS INT); IF EXISTS(SELECT 1 FROM adminInfo WHERE adminLoginName=@adminLoginName) BEGIN SET @count=@count+1 CONTINUE END ELSE BEGIN INSERT INTO adminInfo ( -- adminId -- this column value is auto-generated, adminLoginName, adminPassword, adminLevel ) VALUES ( @adminLoginName, @adminPassword, @adminLevel ) SET @count=@count+1 END END END GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO
Copy code

Optimization——
1 on checksum
Copy code
CREATE TABLE adminInfo ( -- adminId -- this column value is auto-generated, adminLoginName nvarchar(50), adminPassword nvarchar(50), adminLevel int ) GO CREATE PROCEDURE [dbo].[P_InsertAdminInfo] @DataNumber INT - the need to insert data AS INSERT INTO adminInfo SELECT TOP(@dataNumber) 'manager' + RTRIM(ROW_NUMBER() OVER(ORDER BY o.object_id)), '1111111', ABS(CHECKSUM(NEWID())) % 2 + 1 FROM sys.all_objects AS o,sys.all_columns AS c GO EXEC [dbo].[P_InsertAdminInfo] 500 SELECT * FROM adminInfo GO DROP TABLE adminInfo DROP PROCEDURE [dbo].[P_InsertAdminInfo]
Copy code

2 to consider whether the check field adminLoginName already exists, re optimization

Copy code
CREATE TABLE adminInfo ( -- adminId -- this column value is auto-generated, adminLoginName nvarchar(50), adminPassword nvarchar(50), adminLevel int ) GO CREATE PROCEDURE [dbo].[P_InsertAdminInfo] @DataNumber INT - the need to insert data AS INSERT INTO adminInfo SELECT * FROM ( SELECT TOP(@dataNumber) 'manager' + RTRIM(ROW_NUMBER() OVER(ORDER BY o.object_id)) AS adminLoginName, '1111111' AS adminPassword, ABS(CHECKSUM(NEWID())) % 2 + 1 AS adminLevel FROM sys.all_objects AS o,sys.all_columns AS c ) AS T WHERE NOT EXISTS( SELECT 1 FROM adminInfo WHERE adminLoginName = T.adminLoginName) GO EXEC [dbo].[P_InsertAdminInfo] 500 SELECT * FROM adminInfo GO DROP TABLE adminInfo DROP PROCEDURE [dbo].[P_InsertAdminInfo]
Copy code

3 further improvement

Copy code
CREATE TABLE adminInfo ( -- adminId -- this column value is auto-generated, adminLoginName nvarchar(50), adminPassword nvarchar(50), adminLevel int ) GO CREATE PROCEDURE [dbo].[P_InsertAdminInfo] @DataNumber INT - the need to insert data AS INSERT INTO adminInfo SELECT * FROM ( SELECT TOP(@dataNumber) 'manager' + RTRIM(ISNULL((SELECT MAX(REPLACE(adminLoginName,'manager','')*1) FROM adminInfo)+ROW_NUMBER() OVER(ORDER BY o.object_id),ROW_NUMBER() OVER(ORDER BY o.object_id))) AS adminLoginName, '1111111' AS adminPassword, ABS(CHECKSUM(NEWID())) % 2 + 1 AS adminLevel FROM sys.all_objects AS o,sys.all_columns AS c ) AS T WHERE NOT EXISTS( SELECT 1 FROM adminInfo WHERE adminLoginName = T.adminLoginName) GO EXEC [dbo].[P_InsertAdminInfo] 500 SELECT * FROM adminInfo GO DROP TABLE adminInfo DROP PROCEDURE [dbo].[P_InsertAdminInfo]
Copy code

4 simplified

Copy code
CREATE TABLE adminInfo ( -- adminId -- this column value is auto-generated, adminLoginName nvarchar(50), adminPassword nvarchar(50), adminLevel int ) GO CREATE PROCEDURE [dbo].[P_InsertAdminInfo] @DataNumber INT - the need to insert data AS INSERT INTO adminInfo SELECT TOP(@dataNumber) 'manager' + RTRIM(ISNULL((SELECT MAX(REPLACE(adminLoginName,'manager','')*1) FROM adminInfo),0)+ROW_NUMBER() OVER(ORDER BY o.object_id)), '1111111', ABS(CHECKSUM(NEWID())) % 2 + 1 FROM sys.all_objects AS o,sys.all_columns AS c GO EXEC [dbo].[P_InsertAdminInfo] 500 SELECT * FROM adminInfo GO DROP TABLE adminInfo DROP PROCEDURE [dbo].[P_InsertAdminInfo]
Copy code

SQL Server database query there are many reasons for the slow, there are several common:
    1, No index or did not use index (this is the slow query of the most common problems, defects in program design)
    2, I/O throughput is small, forming a bottleneck effect.
    3, The computed column query without optimization was not created.
    4, Insufficient memory
    5, Slow network
    6, Check out the large amounts of data (which can be used for multiple query, other methods to reduce the amount of data)
    7, The lock or deadlock (this is also slow query the most common problem, defective programming)
    8, sp_lock,sp_who,The activities of the user view, the reason is reading and writing competition for resources.
    9, Return the unwanted rows and columns
    10, The query is not good, no optimization
  ●To optimize the query through the following methods :
  1, The data, log, index in different I/O devices, increase reading speed, before Tempdb can be put on RAID0, SQL2000 does not support. The amount of data (size) is larger, more important to improve the I/O.
  2, Longitudinal, transverse partition table, reduce the table size(sp_spaceuse)
  3, Upgrade hardware
  4, Establishment of index according to the query condition, optimization, index, optimizing access mode, limit the amount of data collection. Pay attention to the appropriate fill factor (the best is to use the default value of 0). The index should be as small as possible, using small columns indexed bytes (create good reference index), field not to some limited values of single index such as gender field construction.
  5, Improve the speed.
  6, Expand the server memory, Windows 2000 and SQL Server 2000 supports 4-8G memory.
  Configuration of virtual memory: virtual memory size should be configured to run concurrent services on a computer based on. Run the Microsoft SQL Server? 2000, which is considered to be a virtual memory size set to 1.5 times the physical memory in a computer installation. If in addition the full-text search function to install, and intend to run the Microsoft search service to perform a full text indexing and query, can be considered: the configuration of virtual memory size is at least 3 times the physical memory in the computer installation. The SQL Server Max server memory server configuration options configured for 1.5 times the physical memory (half virtual memory size set).
  7, Increasing the number of server CPU; but must understand the parallel processing of serial processing needs more resources such as memory. The use of parallel or series of travel is to select the automatic assessment of MSSQL. A single task into multiple tasks, you can run on the processor. For example, for query sort, connection, scanning and GROUP BY words at the same time, parallel level determines SQL SERVER optimal according to the load condition of the system, complex need to consume a large amount of CPU query is most suitable for parallel processing. But the update operation UPDATE, INSERT, DELETE is not parallel processing.
  8, If you are using like query words, the simple use of index is not good, but the full-text index, consumption space. Like''a%''like''%a'' does not use index index like''%a%''query, the query time and the total length is proportional to the value of the field, so you can't use the CHAR type, but VARCHAR. For the value of the field for a long the full-text index.
  9, DB Server and APPLication Server OLTP and OLAP separation separation;
  10, Distributed partitioned views can be used to realize the combination of database server.
  A federation is a set of separate management server, but the processing load them cooperate sharing system. Mechanism of the formation of the database server through the Commonwealth partition data can enlarge a group of servers, to support processing multilayer Web site large needs. For more information, see the design of federated database servers. (refer to the SQL help file 'partitioned view'')
  a, Before implementing a partitioned view, you must first level partition table
  b, In the creation of the member tables, defining a distributed partitioned view on each member server, and each view has the same name. So, citing a distributed partitioned view name query can run on any of a member server. Operation of the system as each member servers have a copy of the original table, but each server has only a member table and a distributed partitioned view. The location of the data is transparent to the application.
  11, Rebuild the index DBCC REINDEX, DBCC INDEXDEFRAG, shrinkage data and log DBCC SHRINKDB, DBCC SHRINKFILE. set to automatically shrink log. For large databases do not set the database grow automatically, it will reduce the performance of the server.
  A great deal of attention in the T-sql method, the common points are listed below: firstly, processing the query plan DBMS is this:
    1, Lexical, syntax checking query
    2, The statement submitted to the DBMS query optimizer
    3, Optimization the optimizer do algebra optimization and access path
    4, Module generates a query plan compiled by pre
    5, Then at the right time to submit to the system execution
    6, Finally, the results will be returned to the user.
  Secondly, look at the structure of SQL SERVER data storage: a page size of 8K bytes (8060), 8 pages for a disk storage area, according to the B tree.
Copy code
We should not only write SQL, but also to do write performance of SQL, the author, excerpts, and exchange information and share headquarters! (1) choose the most efficient sequence (table name only based on the effective rule optimizer): ORACLE parser in right to left order processing tables in the FROM clause, the FROM clause is written in the final table (table driving table based) will be processed first, in the FROM clause contains multiple tables, you must select a minimum number of record table as the basis for the table. If there is3Above the table join query, It will need to choose a crosstab(intersec tion table)As the underlying table, Cross table refers to the cited other table table. (2) WHERE The join order clause.: ORACLE The sequence analysis of bottom-upWHERE Clause,According to this principle,Connection between the tables must be written on the otherWHE RE Conditions before, Conditions that can filter out the maximum number of records must be written inWHERE At the end of the clause. (3) SELECT Avoid the use of clause' * ': ORACLE In the parsing process, Will'*' Turn into all of the column name, This is done by querying the data dictionary, This means there will be more time consuming (4) 减少访问数据库The次数: ORACLE In the internal implementation of a lot of work: AnalyticalSQL Sentence, The estimating index rate, Bind variable, Read data block., (5) 在SQL*Plus , SQL*Forms AndPro*C ResetARRAYSIZE Parameters, Each database access can increase the amount of data retrieval,Recommended values for the200 (6) 使UseDECODE Function to reduce the processing time: UseDECODE Function can avoid repeated scans of the same record or repeat the same table.. (7) 整合简单,No association of database access: 如果你有几个简单The数据库查询Sentence,You can integrate them into a single query(It doesn't matter if they are) (8) 删除重复记录: 最EfficientThe删除重复记录方法( Because of the use ofROWID)Example: DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO); (9) UseTRUNCATE ReplaceDELETE: 当删除TableIn theRecordWhen the,In the general case, Rollback segment(rollback segments ) Used to store the information can be recovered. If you don'tCOMMIT Transaction,ORACLE Data will be restored to the state before deleting(Accurately speaking, a return to the previous delete command status) When using theTRUNCATE 时, Rollback segment does not store any information can be recovered. When the command operation,Data could not be restored.So few resources are called,The execution time is very short. (The translator at: TRU NCATE Only in the full table is deleted,TRUNCATE IsDDL No, not at allDML) (10) 尽量多UseCOMMIT: 只要有可能,Try to use in a programCOMMIT, This procedure can improve the performance of,Demand will also becauseCOMMIT The release of resources and reduce the: COMMIT The release of resources: a. To restore data rollback segment. b. By the program statement locks obtained c. redo log buffer In space d. ORACLE For the management of the3Internal resources cost (11) 用Where Replacement clauseHAVING Clause: 避免使用HAVING Clause, HAVING Only after retrieving all records of the result set filter. This process requires sorting,The total amount of operation. If you canWHERE The number of limit record,It can reduce the overhead of this. (Nonoracle 中)on, where, having Clause three can add conditions., On is the first to perform, Whe re times, Having finally, Because on is to first do not meet the conditions of the log filter after statistics, It can reduce the intermediate calculation of data to be processed, There should is the fastest, Where also should be more than having fast., Because it is to filter the data after sum, In the two table connection when using on, So in a form of time, On the remaining where in comparison with having. In this single table query statistics, if you want to filter conditions not involves calculating the fields, that they are the same, only the where can use the Rushmore technology, and having would not, in the latter to slow speed if need to calculate the field, said no before, the the value of the field is uncertain, according to the written work flow, the action time of where is completed before the calculation, and having is in the calculation of after work, so in this case, the result will be different. In a multi table join query, on earlier than where effect. The system first according to the join conditions between each table, the table into a temporary table, then the where filter, then the calculation, calculation after filtered by having. Thus, If you want to filter the correct role, We must first understand the conditions should play a role in what time, Then decided to put in there (12) 减少对表的查询: 在含有子查询的SQL Statement,Special attention should be paid to reduce the query table.Example: SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT TAB_NAME,DB_VER FROM TAB_COLUMNS WHERE VERSION = 604) (13) 通过内部函数To improve theSQL Efficiency.: 复杂的SQL Often at the expense of execution efficiency. Method can grasp the function above to solve the problem is very meaningful in practical work (14) 使用表的别名(Alias): 当在SQL Statements in connection multiple tables, Please use the table aliases and aliases prefix to eachColumn On the.Thus, You can reduce the analysis time and reduce theColumn Ambiguity caused by mistakes in grammar. (15) 用EXISTS ReplaceIN, 用NOT EXISTS ReplaceNOT IN: 在许多基于The underlying table的查询中,In order to satisfy a condition,Often the need for another table connection.In this case, 使用EXISTS(OrNOT EXISTS)Will usually improve query efficiency. In the subquery,NOT IN Clause will execute an internal ranking and merging. In either case,NOT IN It is the most inefficient(Because it is on the tables in the query executed a full table scan). In order to avoid the use ofNOT IN ,We can rewrite it into external connection(Outer Join s)OrNOT EXISTS. Example: (Efficient)SELECT * FROM EMP (The underlying table) WHERE EMPNO > 0 AND EXISTS (SELECT 'X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = 'MELB') (Inefficient)SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEP TNO FROM DEPT WHERE LOC = 'MELB') (16) 识别'Inefficient execution'的SQL Sentence: 虽然目前各种关于SQL Graphical tools optimized emerge in an endless stream,But to write their ownSQL Tool to solve the problem is always one of the best methods: 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 4 DESC; 17) 用索引提Efficient率: 索引是表的一个概念部分,To improve the retrieval efficiency of data, ORACLE uses a complex self balance B-tr EE structure. Usually, the query data faster than a full table scan. When the ORACLE to find the best path to execute the query and Update statements, the ORACLE optimizer will use the index. Also use the index in the connecting multiple tables can also improve efficiency. Another use index is good, it provides the primary key (primary key) uniqueness verification. ThoseLONG OrLONG RAW Data type, You can index almost all columns. Usually, Particularly effective use index in the large table. Of course, You will also find, On the small table scan,Using the index can also improve the efficiency of. Although the use of the index can improve the efficiency of query,But we must also pay attention to it.. The index space required to store,Also need regular maintenance, Whenever a record increase in the table or indexed column to be modified, The index itself will also be modified. This means that each recordINSERT , DEL ETE , UPDATE This will more than pay4 , 5 The diskI/O . Because the index storage space and processing extra, Those unnecessary index will make the query response time is slow but. Remodeling index regularly is necessary.: ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME> 18) 用EXISTS ReplaceDISTINCT: 当提交一个包含一对多表信息(For example, the Department table and table)When the query,To avoid theSELECT ClauseD ISTINCT. General can be consideredEXIST Replace, EXISTS The query more quickly,BecauseRDBMS The core module in the sub query conditions once met,Returns the results immediately. Example: (Inefficient): SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP E WHERE D.DEPT_NO = E.DEPT_NO (Efficient): SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT 'X' FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO); (19) sql Statement in capital, 因为oracle Always first analysissql 语句, The re execution lowercase letters converted to uppercase (20) in the Java code to minimize the use of &ldquo ” connectors; connection string! (21) to avoid the indexed column using the NOT usually, We want to avoid in the indexed column using the NOT, NOT will be produced in and the influence of the indexed column using the same function. When ORAC LE&rdquo met ” NOT;, he will stop using the index to perform a full table scan. (22) to avoid computing in the indexed columns in the WHERE clause is used., If the indexed column is part of a function.优化器将不Using the index而使用全表扫描. 举例: Inefficient: SELECT … FROM DEPT WHERE SAL * 12 > 25000; Efficient: SELECT … FROM DEPT WHERE SAL > 25000/12; (23) 用>=Replace> Efficient: SELECT * FROM EMP WHERE DEPTNO >=4 Inefficient: SELECT * FROM EMP WHERE DEPTNO >3 The difference between the two, The formerDBMS Will jump directly to the firstDEPT Be equal to4Record and the latter will first locateDEPT NO=3Records and scans forward to the firstDEPT More than3的记录. (24) 用UNION ReplaceOR (For the indexed column) Normally, 用UNION ReplaceWHERE ClauseOR Will play a better effect. The use of indexed columnOR The full table scan. Be careful, The above rules only for more effective index column. If there iscolumn Is not indexed., The query efficiency may be because you have no choiceOR And reduce the. In the following example, LOC_ID AndREGION All built index. 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 usingOR, It needs to return to record at least indexed column is written on the front. (25) 用IN To replaceOR This is a simple rule to remember, But the implementation effect to test the actual, Under ORACLE8i, The two execution path seems to be the same. Inefficient: SELECT…. FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30 Efficient SELECT… FROM LOCATION WHERE LOC_IN IN (10,20,30); (26) 避免在索引列上使用IS NULL AndIS NOT NULL Avoid any nullable columns used in the index, ORACLE cannot use the index for the 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 also does not exist to the record. If at least one column is not null, Recorded in the index. For example: if the unique 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 accept with the same A a, B (123, null) records (insert). However, if the indexed column all is empty, ORACLE Will think that the key is empty space is not empty. So you can insert1000 The records with the same key value,Of course they are empty! Because null value does not exist in the indexed columns.,SoWHERE Clause of the indexed column will allow null values comparedORACLE Disable the index. Inefficient: (The index of failure) SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL; Efficient: (Efficient index) SELECT … FROM DEPARTMENT WHERE DEPT_CODE >=0; (27) 总是使用索引的第一个列: 如果索引是建立在多个列上, Only in the first column of it(leading column)Bewhere Clause references, The optimizer would choose to use the index. This is a simple but very important rule, When only the reference index of second columns, the optimizer uses a full table scan and ignore the index 28) replacing UNION with UNION-ALL (if possible): when the SQL statement requires UNION two query result set, the two result sets in UNION-ALL mode is combined, and then sort at the results of the final output. If you use UNION ALL instead of UNION, in this order is not necessary. The efficiency will be improved. Attention is needed, UNION ALL Will repeat the output two result sets in the same record. So you still have to use from the business needs analysisUNION ALL The feasibility of. UNION Set sort the results, This operation will be used toSORT_AREA_SIZE The memory of this. It is important for the optimization of the memory.. BelowSQL Can be used to query consumption low ranking: 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' 高效: 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) 用WHERE ReplaceORDER BY: ORDER BY Clause using the index in two kinds of strict conditions only. ORDER BY All of the columns must be included in the index in the sequence and remain in the same index. ORDER BY All of the columns must be defined as a non empty. WHERE Clause is used to index andORDER BY Index cannot be used in parallel clause. For example: 表DEPT Contains the following columns: DEPT_CODE PK NOT NULL DEPT_DESC NOT NULL DEPT_TYPE NULL Inefficient: (The index is not in use) SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_TYPE 高效: (使用索引) SELECT DEPT_CODE FROM DEPT WHERE DEPT_TYPE > 0 (30) 避免改变索引列的类型.: When comparing the different data types of data, ORACLE Automatic column simple type conversion. HypothesisEMPNO Is a numeric index column. SELECT … FROM EMP WHERE EMPNO = '123' In fact,AfterORACLE Type conversion, Statement into: SELECT … FROM EMP WHERE EMPNO = TO_NUMBER('123') Fortunately,Type conversion did not occur in the indexed columns.,The index uses has not been changed. Now,HypothesisEMP_TYPE Is a character type indexed column. SELECT … FROM EMP WHERE EMP_TYPE = 123 This statement isORACLE Conversion to: SELECT … FROM EMP WHERETO_NUMBER(EMP_TYPE)=123 Because the type of internal conversion occurs, This index will not be used! In order to avoidORACLE For youSQL For implicit type conversion, It is best to use explicit type conversion performance. Note that when the character and numerical comparison, ORACLE Priority will be converted numeric types to character type (31) 需要当心的WHERE Clause: SomeSELECT StatementWHERE Clause does not use index. Here are some examples. In the following example, (1)'!=' Will not use index. Remember, The index can only tell you what exists in the table, But can't tell you what does not exist in the table. (2) '||'Is a character connection function. Like other function., Disabled index. (3) '+'Is a mathematical function. Like other mathematical functions., Disabled index. (4)The same index columns cannot be compared to each other,This will enable a full table scan. (32) a. If the amount of data retrieval over30%The number of records in the table.Using the index will not improve significantly the efficiency. b. In the particular case, Using the index may slow than a full table scan, But this is the difference between the same order of magnitude.. Normally,Using the index blocks several times and even thousands of times more than a full table scan! (33) 避免使用耗费资源的操作: HaveDISTINCT,UNION,MINUS,INTERSECT,ORDER BY 的SQL The statement will startSQL Engine performs resource consuming sorting(SORT)Function. DISTINCT Need a sort operation, While the other requires at least two ranking. Usually, HaveUNION, MINUS , INTERSECT 的SQL Statement can use other ways to rewrite. If your databaseSORT_AREA_SIZE Mix well, 使用UNION , MINUS, INTERSECT Can be considered, After all, their strong readability (34) 优化GROUP BY: 提高GROUP BY Statement of efficiency, Can need not be recorded in theGROUP BY Before filtering.The next two queries return the same results but second apparently soon many. 低效: SELECT JOB , AVG(SAL) FROM EMP GROUP by JOB HAVING JOB = 'PRESIDENT' OR JOB = 'MANAGER' 高效: SELECT JOB , AVG(SAL) FROM EMP WHERE JOB = 'PRESIDENT' OR JOB = 'MANAGER' GROUP by JOB

Posted by Ignace at November 22, 2013 - 1:20 AM