me |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 1 | 19 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| BASOPT | 2 | 22 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_BASOPT | 2 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 1 | 8 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | BASOPTUSER | 1 | 8 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."OPTID"="B"."OPTID")
filter("A"."OPTID"="B"."OPTID")
5 - filter("B"."USERID"=1)
--用use_nl(b)指定使用nested loops连接使用basoptuser作为 表
SQL> select /*+ ordered use_nl(b)*/ optname,userid from basopt a,basoptuser b where a.optid = b.optid and b.userid = 1;
执行计划
----------------------------------------------------------
Plan hash value: 3306984809
--------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 1 | 19 | 7 (0)| 00:00:01
|
| 1 | NESTED LOOPS | | 1 | 19 | 7 (0)| 00:00:01
|
| 2 | TABLE ACCESS FULL| BASOPT | 2 | 22 | 3 (0)| 00:00:01
|
|* 3 | TABLE ACCESS FULL| BASOPTUSER | 1 | 8 | 2 (0)| 00:00:01
|
--------------------------------------------------------------------------------
-
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("B"."USERID"=1 AND "A"."OPTID"="B"."OPTID")
SQL>
--实例2：
--/*+ leading() */ hint实例,表BASOPT上有optid列上的索引PK_BASOPT,表SYSUSER上有userid列上的索引PK_SYSUSER
SQL> select 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: 1787196989
-------------------------------------------------------------------------------------------
| 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 | INDEX UNIQUE SCAN | PK_SYSUSER | 1 | 3 | 0 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | BASOPTUSER | 1 | 8 | 3 (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 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("C"."USERID"=1)
5 - filter("B"."USERID"=1)
6 - access("A"."OPTID"="B"."OPTID")
--设定驱动表b c
SQL> select /*+ leading(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 va