Select the most efficient table name order (valid only in the rule-based optimizer)
the parser for Oracle processes the table names in the FROM clause in a right-to-left order, so the table that is written in the FROM clause (the underlying table, driving tables) is processed first. In the case that the FROM clause contains more than one table, You must select a table with the fewest number of records to use as the underlying table. When Oracle processes multiple tables, it connects them in a sort and merge manner. First, scan the first table (the last table in the FROM clause) and order 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 from the first table.
For example:
Table tab1:16,384 Records, table Tab2:1 Records
Select TAB2 as the base table (best method): Execution time 0.96 seconds
Select TAB2 as the base table (poor method): Execution time 26.09 seconds
Select COUNT (*) from TAB2,TAB1
If you have more than 3 tables connected to the query, you need to select the crosstab (intersection table) as the underlying table, which refers to the table that is referenced by the other table.
For example:
The EMP table describes the intersection of the location table and the category table.
SELECT * from location L, CATEGORY C, EMP E WHERE e.emp_no between and 2000AND e.cat_no = C.cat_noand E.LOCN = L.LOCN
will be more efficient than the following SQL:
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
Small bet
This digest from Baidu Library, specific links to the wood found, sorry.
Oracle Performance Optimization Learning notes Select the most efficient table name order