Connection sequence of tables in oracle Query

During the oracle Query Process, Oracle takes a lot of time to check the proper order of connected tables for queries that connect many tables. The order of table connections in oracle queries has become a problem that needs to be considered.

Evaluation Table connection Sequence

During SQL statement preparation, the most costly step is to generate an execution plan, especially for queries with multiple table connections. When Oracle evaluates the table connection sequence, it must consider all possible connections between tables. For example, six tables are connected by a 7206 factorial, or 6*5*4*3*2*1 = 720.

When an oracle query contains connections of more than 10 tables, the problem of sorting becomes more significant. For connections between 15 tables, the possible query order to be evaluated will exceed 1 trillion
The exact number is 1,307,674,368,000.

Use the optimizer_search_limit parameter to set the limit.

By using the optimizer_search_limit parameter, you can specify the maximum number of connection combinations that the optimizer uses to evaluate. With this parameter, we will be able to prevent the optimizer from
It takes an indefinite amount of time to evaluate all possible connection combinations. If the number of tables in the oracle query is less than the value of optimizer_search_limit, the optimizer checks all possible
Connection combination.

For example, if there are five tables connected, there will be 1205 queries! = 5*4*3*2*1 = 120) possible connection combinations, So If optimizer_search_limit is equal to 5
Default Value), the optimizer evaluates all 120 possibilities. The optimizer_search_limit parameter also controls the threshold value for calling the connection prompt with an asterisk. When
If the number is more than optimizer_search_limit, the asterisk (*) Prompt will be given priority.

Another tool: optimizer_max_permutations

The initialization parameter optimizer_max_permutations defines the maximum number of combinations that the optimizer_search_limit considers.
The default value of optimizer_max_permutations is 80,000.

The optimizer_search_limit and optimizer_max_permutations parameters are used together to determine the maximum number of combinations considered by the optimizer: unless the number of tables or combinations is used)
Exceeds the value set by optimizer_search_limit or optimizer_max_permutations. Otherwise, the optimizer generates all possible connection combinations. Once
The analyzer stops the join combination of the evaluation table and selects the lowest cost combination.

Use the ordered prompt to specify the connection Sequence

You can set the maximum number of evaluations performed by the optimizer. However, even with a highly valuable ranking evaluation, we still have the ability to enable the optimizer to discard complex queries as soon as possible.
Important opportunities. Recall that there are more than 1 trillion connection combinations in 15 connection queries. If the optimizer stops after evaluating the 80,000 combinations
Only 0.000006% of the possible combinations are evaluated, and perhaps no optimal connection sequence is found for this huge query.

The best way to solve this problem in Oracle SQL is to manually specify the table connection sequence. To create the smallest solution set as soon as possible, the rule here is to combine tables
Normally, the most restrictive WHERE clause is used to connect tables.

Implementation of Oracle query of user tablespace

16 oracle query date statements

Summary of Oracle query statement Application Experience

Solution for Oracle to query the first 10 records

Oracle statements used to query all tables of a user