|
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 |
---------- |