In-depth understanding of Oracle tables (1): ORDERED and USE_NL

An in-depth understanding of Oracle tables (1): ORDERED and USE_NL ORDERED are good at understanding, that is, they represent sequential join Based on the table following from, from left to right, the table on the left is the driving table use_nl (t1, t2): Nested cyclic connections are used when table t1 and t2 are associated. The optimizer cannot determine who is the driving table or who is the driving table www.2cto.com USE_NL (), let's take a look at how oracle doc says: In this statement, the USE_NL hint explicitly chooses a nested loops join with the customers table as the inner table: SELECT/* + ORDERED USE_NL (customers) to get first row faster */accounts. balance, customers. last_name, cu Stomers. first_name FROM accounts, customers WHERE accounts. customer_id = customers. customer_id; customers as inner table, that is, as the driver table. The driving table is called the outer table. If the specified table is an outer table (the driving table), the optimizer will ignore this hint. If you have to force it as an inner table, the ordered parameter oradered can be added to indicate that the drive table is created from left to right join and left table based on the order of the tables following from, 3 or more of the three most useful www.2cto.com that is to say use_nl if only one table name is included as the parameter, then the table is a driven table if more than two parameters are included, oracle does not specify which driver table is in use_nl (a, B). Therefore, we often use ordered, full (), or index () to strengthen our goals. The following are the testing objectives: [SQL] hr @ ORCL> select first_name, parameters. department_id from employees, deployments where employees. department_id = parameters. department_id; Execution Plan -------------------------------------------------------- Plan hash value: 169719308 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | latency | 0 | select statement | 106 | 1484 | 3 (0) | 00:00:01 | 1 | nested loops | 106 | 1484 | 3 (0) | 00:00:01 | 2 | table access full | EMPLOYEES | 107 | 1070 | 3 (0) | 00:00:01 | * 3 | index unique scan | DEPT_ID_PK | 1 | 4 | 0 (0) | 00:00:01 | region www.2cto.com the optimizer selects employees as the driver table, because there is an index on the deployments column, and the index is exactly created in the connection column [SQL] hr @ ORCL> select/* + use_nl (employees) */first_name, deployments. department_id from employees, deployments where employees. department_id = parameters. department_id; Execution Plan -------------------------------------------------------- Plan hash value: 169719308 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | latency | 0 | select statement | 106 | 1484 | 3 (0) | 00:00:01 | 1 | nested loops | 106 | 1484 | 3 (0) | 00:00:01 | 2 | table access full | EMPLOYEES | 107 | 1070 | 3 (0) | 00:00:01 | * 3 | index unique scan | DEPT_ID_PK | 1 | 4 | 0 (0) | 00:00:01 | because employees is used as the driver table, the optimizer ignores the hint prompt [SQL] hr @ ORCL> select/* + ordered use_nl (employees) */first_name, parameters. department_id from orders, employees where employees. department_id = parameters. department_id; Execution Plan -------------------------------------------------------- Plan hash value: 2677871237 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | period | 0 | select statement | 106 | 1484 | 8 (0) | 00:00:01 | 1 | table access by index rowid | EMPLOYEES | 4 | 40 | 1 (0) | 00:00:01 | 2 | nested loops | 106 | 1484 | 8 (0) | 00:00:01 | 3 | index full scan | DEPT_ID_PK | 27 | 108 | 1 (0) | 00:00:01 | * 4 | index range scan | EMP_DEPARTMENT_IX | 10 | 0 (0) | 00:00:01 | the sequence is now the sequence as the driver table.