Oracle Performance Tuning Order table name to select the most effective learning notes

Select the most efficient sequential table name (only valid rule-based optimizations)

The Oracle parser handles the table name from right-to-left from the FROM clause to the order, so the From is written in the final table (the underlying table, driving tables) will be processed first. In the case of including more than one table in the FROM clause, 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.

Like what:

Table tab1:16,384 Records, table Tab2:1 Records

Select TAB2 as the base table (best method): Run time 0.96 seconds

Select TAB2 as the base table (poor method): Run 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 is the table referenced by the other table.
Like what:

The EMP table description 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 is from Baidu Library, detailed link wood has found. I'm sorry.

Copyright: Author: jiankunking Resources: http://blog.csdn.net/jiankunking This article is copyrighted by the author and Csdn jointly owned. Welcome reprint. However, if the submit permission is not declared from this section, it must be persisted and connected to the original article page given the view location.

Oracle Performance Tuning Order table name to select the most effective learning notes