Selecting the most efficient table name sequence for Oracle performance optimization learning notes

Selecting the most efficient table name sequence for Oracle performance optimization learning notes
Select the most efficient table name sequence (only valid in the rule-based Optimizer)

The ORACLE parser processes the table names in the FROM clause in the order FROM right to left. Therefore, the table written in the FROM clause (basic table driving table) will be processed first. when the FROM clause contains multiple tables, You must select the table with the least number of records as the base table. when ORACLE processes multiple tables, it uses sorting and merging to connect them. first, scan the first table (the last table in the FROM clause) and sort the records, and then scan the second table (the last second table in the FROM clause ), finally, all records retrieved from the second table are merged with the appropriate records in the first table.

For example:

Table TAB1: 16,384 records, table TAB2: 1 record

Select TAB2 as the basic table (the best method): the execution time is 0.96 seconds.

select count(*) from tab1,tab2 

Select TAB2 as the basic table (poor method): the execution time is 26.09 seconds.

select count(*) from tab2,tab1

If more than three tables are connected for query, You need to select an intersection table as the base table, which is the table referenced by other tables.
For example:

The EMP table describes the intersection between the LOCATION table and the CATEGORY table.

SELECT * FROM LOCATION L ,       CATEGORY C,      EMP E WHERE E.EMP_NO BETWEEN 1000 AND 2000AND E.CAT_NO = C.CAT_NOAND E.LOCN = L.LOCN

It is more efficient than the following SQL statements:

SELECT * FROM EMP E ,LOCATION L ,       CATEGORY CWHERE  E.CAT_NO = C.CAT_NOAND E.LOCN = L.LOCNAND E.EMP_NO BETWEEN 1000 AND 2000

Note:
This article is taken from Baidu Library. The specific link is found. Sorry.