SQL Tutorial: Some suggestions for improving SQL execution efficiency

Source: Internet
Author: User
Tags filter execution sql tutorial sql query table name

Several suggestions for improving SQL execution efficiency:

Try not to include subqueries in the Where;

About the time of the query, try not to write: where To_char (dif_date, ' Yyyy-mm-dd ') =to_char (' 2007-07-01 ', ' yyyy-mm-dd ');

In the filter condition, the condition that can filter out the maximum number of records must be placed at the end of the WHERE clause;

The last table (the underlying table, driving table), which is written in the FROM clause, will be processed first, and in the case where multiple tables are included in the FROM clause, you must select the table with the least number of records as the underlying table. If there are more than three connection queries, it is necessary to select the Crosstab table (intersection table) as the underlying table, which is the table referenced by the other tables;

Using binding variables

Try not to use or in a where

Substituting exists instead of in, using not exists instead of in;

Avoid using calculations on indexed columns: where sal*12>25000;

Use in to replace Or:where loc_id=10 or loc_id=15 or loc_id=20

Avoid using is null and is not NULL on indexed columns;

Always use the first column of the index;

substituting Union-all for union;

Avoid changing the type of indexed columns: SELECT ... From EMP where empno= ' 123 ', because of the implicit data type conversion, TO_CHAR (EMPNO) = ' 123 ', therefore, will not use the index, generally in the use of string patchwork dynamic SQL statements appear;

'!= ' will not use the index;

Optimize GROUP by;

Avoid wildcard characters with like parameters, like ' 4ye% ' uses indexes, but like '%ye ' does not use indexes

Avoid the use of difficult formal expressions, such as SELECT * from customer where zipcode like "98___", even if an index is established on ZipCode, in which case sequential scans are also used. If the statement is changed to select * from customer where zipcode> "98000", the query will be executed using the index to query, obviously greatly improve the speed;

Make the SQL statements as clear as possible, and minimize the database work. For example, when you write a SELECT statement, you need to explicitly indicate the table name for the query's fields. Try not to use the SELECT * statement. When organizing SQL statements, try to organize them according to the habits of the database.



Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.