How to select a driver table in Oracle

The execution speed of SQL statements may vary depending on many factors. However, the main influencing factors are: driving the table, executing the operation sequence, and applying the index. These factors can be indirectly changed by many different methods to achieve optimal execution speed. Here we mainly discuss the optimization principles that should be followed when connecting to multiple tables:

(1) columns used for join clauses should be indexed, and indexes should be used as far as possible in the Where clause, rather than avoiding indexes.

(2) The Connection operation should be driven by a small number of rows returned.

(3) If tables A and B are connected, the length of Table A is much larger than that of Table B. We recommend that you drive the data from A large table.

(4) If the Where clause contains a selective condition Where No = 20, place the most selective part at the end of the expression.

(5) If only one table has an index and the other table has no index, a non-index table is usually used as the driving table. If the No column of Table A is indexed, And the No column of Table B is not indexed, table B should be used as the driving table and Table A as the driving table.

(6) If the columns used for connection and other selection condition columns in the Where clause have indexes, the query validity and selectivity of each index are determined based on the specific data composition in the table, to select an Optimization Path, consider which columns in the clause can use indexes, which indexes are unique, and the number of rows in the table to be queried.

-------------------------------------------------------------------

If we see the driver table according to the execution plan?

Look at connections and indentation. The indentation at the same level is listed below the driver table.

-------------------------------------------------------------------

How Does Oracle select a driver table?

If RBO or CBO does not analyze the table, the operations on the two tables, in The FROM clause, RBO selects the rightmost table as the driving table (generally the last table in from as the driving table ). Therefore, for the nested loops, hash join, and sort merge join modes, a smaller table (placed on the rightmost end of from) is used as the driving table, which is faster. Tables with primary and foreign key relationships exist, since the primary key is automatically indexed by oracle, it is best to create an index on the foreign key to avoid full table scanning. For three or more table join queries, for the FROM clause, RBO processes the table join from right to left, that is, the rightmost table of the from clause is used as the driving table.

The execution speed of SQL statements may vary depending on many factors. However, the main influencing factors are: driving the table, executing the operation sequence, and applying the index. These factors can be indirectly changed by many different methods to achieve optimal execution speed. Here we mainly discuss the optimization principles that should be followed when connecting to multiple tables:

(1) columns used for join clauses should be indexed, and indexes should be used as far as possible in the Where clause, rather than avoiding indexes.

(2) The Connection operation should be driven by a small number of rows returned.

(3) If tables A and B are connected, the length of Table A is much larger than that of Table B. We recommend that you drive the data from A large table.

(4) If the Where clause contains a selective condition Where No = 20, place the most selective part at the end of the expression.

(5) If only one table has an index and the other table has no index, a non-index table is usually used as the driving table. If the No column of Table A is indexed, And the No column of Table B is not indexed, table B should be used as the driving table and Table A as the driving table.

(6) If the columns used for connection and other selection condition columns in the Where clause have indexes, the query validity and selectivity of each index are determined based on the specific data composition in the table, to select an Optimization Path, consider which columns in the clause can use indexes, which indexes are unique, and the number of rows in the table to be queried.

-------------------------------------------------------------------

If we see the driver table according to the execution plan?

Look at connections and indentation. The indentation at the same level is listed below the driver table.

-------------------------------------------------------------------

How Does Oracle select a driver table?

If RBO or CBO does not analyze the table, the operations on the two tables, in The FROM clause, RBO selects the rightmost table as the driving table (generally the last table in from as the driving table ). Therefore, for the nested loops, hash join, and sort merge join modes, a smaller table (placed on the rightmost end of from) is used as the driving table, which is faster. Tables with primary and foreign key relationships exist, since the primary key is automatically indexed by oracle, it is best to create an index on the foreign key to avoid full table scanning. For three or more table join queries, for the FROM clause, RBO processes the table join from right to left, that is, the rightmost table of the from clause is used as the driving table.