SQL aliases and SQL execution order and SQL optimizations

Alias for SQL

1. You cannot use the alias of the column name in the WHERE clause, that is, the select name T from EMP where t>2999; is not allowed

2. Benefits of using aliases:

Improve the readability of SQL

Improve the efficiency of SQL parsing execution

Grammar check

Semantic check

Shared Pool Checks

Build the execution tree

Perform

3. Hard parsing and soft parsing of SQL?

Execution Order of SQL

1. From statement--where statement--group by Statement--having statement--select statement--order by statement

Use of RowNum

SELECT * FROM emp rownum<=2/* Find the first two records of the EMP table */

SELECT * FROM (select RowNum t,ename from EMP) m

where m.t>=5 and m.t<=10/* Query a range of records, first query out rownum and save as temp table m, then query */

Where and having the difference?

Where to filter the details in the group before grouping

Having to filter the information after grouping

Inner connection, outer connection, self-connection?

SQL optimization

1. Avoid using SELECT * instead of specifying which columns to select

2, SQL case, the database in the parsing of SQL, the SQL statement first converted to uppercase, and then parse operations, it is recommended to provide direct capitalization of SQL statements.

3, filter,> and >=, use >= priority, such as a>2000 and a>=2001, so as to avoid filtering the decimal between 2000 and 2001, can be adjusted high efficiency

4, where clause is not equal to the judgment will invalidate the index.

5. In the WHERE clause, operations on the column, for example, subtraction, will invalidate the Suoyin column. So try not to operate on the Suoyin column.

6, fuzzy query, like "%abc%", the former Blur will cause the index to fail, after the blur will not cause the index invalidation.

7, in and exists difference?

1, in the case of the small appearance of the inner table, select * from the EMP where Emp.deptno in (select Deptno from dept)

2, the exists is suitable for the appearance small inside the table big situation. Use this to improve efficiency

Note: Because the two tables are connected differently, resulting in the above distinction, in is the appearance and the inner table as a hash connection, and exists is the external loop loop, each loop loop and then query the internal table.

8, not in and not exists usage ibid.

9. In some cases, exists can be used to replace the distinct.

SQL aliases and SQL execution order and SQL optimizations