Hint&ordered&leading&use_nl - 数据库编程

lue: 3853709033 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 22 | 4 (0)| 00:00:01 | | 1 | NESTED LOOPS | | | | | | | 2 | NESTED LOOPS | | 1 | 22 | 4 (0)| 00:00:01 | | 3 | MERGE JOIN CARTESIAN | | 1 | 11 | 3 (0)| 00:00:01 | |* 4 | TABLE ACCESS FULL | BASOPTUSER | 1 | 8 | 3 (0)| 00:00:01 | | 5 | BUFFER SORT | | 1 | 3 | 0 (0)| 00:00:01 | |* 6 | INDEX UNIQUE SCAN | PK_SYSUSER | 1 | 3 | 0 (0)| 00:00:01 | |* 7 | INDEX UNIQUE SCAN | PK_BASOPT | 1 | | 0 (0)| 00:00:01 | | 8 | TABLE ACCESS BY INDEX ROWID| BASOPT | 1 | 11 | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter("B"."USERID"=1) 6 - access("C"."USERID"=1) 7 - access("A"."OPTID"="B"."OPTID") --设定驱动表b a SQL> select /*+ leading(b a) */ optname,c.userid from basopt a,basoptuser b,sysuser c where a.optid = b.optid and b.userid = c.userid and b.userid = 1; 执行计划 ---------------------------------------------------------- Plan hash value: 1915872201 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 22 | 4 (0)| 00:00:01 | | 1 | MERGE JOIN CARTESIAN | | 1 | 22 | 4 (0)| 00:00:01 | | 2 | NESTED LOOPS | | | | | | | 3 | NESTED LOOPS | | 1 | 19 | 4 (0)| 00:00:01 | |* 4 | TABLE ACCESS FULL | BASOPTUSER | 1 | 8 | 3 (0)| 00:00:01 | |* 5 | INDEX UNIQUE SCAN | PK_BASOPT | 1 | | 0 (0)| 00:00:01 | | 6 | TABLE ACCESS BY INDEX ROWID| BASOPT | 1 | 11 | 1 (0)| 00:00:01 | | 7 | BUFFER SORT | | 1 | 3 | 3 (0)| 00:00:01 | |* 8 | INDEX UNIQUE SCAN | PK_SYSUSER | 1 | 3 | 0 (0)| 00:00:01 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter("B"."USERID"=1) 5 - access("A"."OPTID"="B"."OPTID") 8 - access("C"."USERID"=1) --设定驱动表b c,并且b和c表之间的连接使用nested loops连接 SQL>

select /*+ leading(b c) use_nl(b c) */ optname,c.userid from basopt a,basoptuser b,sysuser c where a.optid = b.optid and b.userid = c.userid and b.userid = 1; 执行计划 ---------------------------------------------------------- Plan hash value: 683070851 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 22 | 4 (0)| 00:00:01 | | 1 | NESTED LOOPS | | | | | | | 2 | NESTED LOOPS | | 1 | 22 | 4 (0)| 00:00:01 | | 3 | NESTED LOOPS | | 1 | 11 | 3 (0)| 00:00:01 | |* 4 | TABLE ACCESS FULL | BASOPTUSER | 1 | 8 | 3 (0)| 00:00:01 | |* 5 | INDEX UNIQUE SCAN | PK_SYSUSER | 1 | 3 | 0 (0)| 00:00:01 | |* 6 | INDEX UNIQUE SCAN | PK_BASOPT | 1 | | 0 (0)| 00:00:01 | | 7 | TABLE ACCESS BY INDEX ROWID| BASOPT | 1 | 11 | 1 (0)| 00:00:01 | ----------

Hint&ordered&leading&use_nl(四)