Optimization notes for multi-table queries in oracle

ORACLE has a high-speed buffer concept. This high-speed buffer is used to store the executed SQL statements. corresponding to the executed SQL statements, the second execution speed is faster than the first one, it uses high-speed buffering. The high-speed buffer of ORACLE matches all characters. If the SQL statements are a little different (for example, there is a space), the high-speed cache does not recognize it.

1. When querying multiple tables, you must write small tables on the rightmost side of the query, when ORACLE parses an SQL statement, the table names behind the From clause are parsed From right to left. The table on the far right is scanned first, and then the table on the left is scanned, then, use the table on the left to match the data. After successful matching, the data is merged.

2. For a crosstab chart, you should place the crosstab chart at the end of the table and then the smallest table based on the oracle's scanning method From the right to the left of the From clause.

3. filter the condition following the Where clause. As we all know, the condition for filtering large batches of data is placed at the end.

4. Use less *

5, in and exists,

Select * from T1 where exists (select 1 from T2 where T1.a = T2.a );

Select * from T1 where T1.a in (select T2.a from T2); T1 has a large data volume and T2 data volume is small. T1> T2, in queries are highly efficient.

IN general, EXISTS is faster than IN queries.

Exists (xxx) indicates whether the statement in parentheses can identify the record and whether the record to be queried exists. Therefore, the "1" in "select 1" is irrelevant. It is okay to replace it with "*". It only cares whether the data in the brackets can be searched out, whether such a record exists. If so, the where condition of the sentence is true.

The content of the fields searched by the statement following the "in" must correspond to each other. Generally, the expression of field a in Table T1 and table T2 must be the same.