AUTOTRACE
SQL> conn scott/tiger
SQL> set autotrace on
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql
SQL> grant plustrace to scott;
SQL> conn scott/tiger
显示结果集以及执行计划
SQL> set autotrace on
不显示结果集,显示执行计划
SQL> set autotrace traceonly
SQL> select * from dept
2 minus
3 select * from dept1;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
30 SALES CHICAGO
40 OPERATIONS BOSTON
Execution Plan
----------------------------------------------------------
Plan hash value: 1627988746
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 150 | 8 (63)| 00:00:01 |
| 1 | MINUS | | | | | |
| 2 | SORT UNIQUE | | 4 | 120 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 |
| 4 | SORT UNIQUE | | 1 | 30 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL| DEPT1 | 1 | 30 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
................
SQL> select * from dept where deptno not in(select deptno from dept1);
DEPTNO DNAME LOC
---------- -------------- -------------
30 SALES CHICAGO
40 OPERATIONS BOSTON
10 ACCOUNTING NEW YORK
Execution Plan
----------------------------------------------------------
Plan hash value: 1948948592
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 172 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN ANTI NA | | 4 | 172 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT1 | 1 | 13 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
............
SQL> select * from dept a,dept1 b where a.deptno=b.deptno(+) and b.deptno is null;
DEPTNO DNAME LOC DEPTNO DNAME LOC
---------- -------------- ------------- ---------- -------------- -------------
30 SALES CHICAGO
40 OPERATIONS BOSTON
10 ACCOUNTING NEW YORK
Execution Plan
----------------------------------------------------------
Plan hash value: 2254963940
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 60 | 7 (15)| 00:00:01 |
|* 1 | FILTER | | | | | |
|* 2 | HASH JOIN OUTER | | 1 | 60 | 7 (15)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| DEPT1 | 1 | 30 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
.........
SQL> conn scott/tiger
SQL> set autotrace on
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql
SQL> grant plustrace to scott;
SQL> conn scott/tiger
显示结果集以及执行计划
SQL> set autotrace on
不显示结果集,显示执行计划
SQL> set autotrace traceonly
SQL> select * from dept
2 minus
3 select * from dept1;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
30 SALES CHICAGO
40 OPERATIONS BOSTON
Execution Plan
----------------------------------------------------------
Plan hash value: 1627988746
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 150 | 8 (63)| 00:00:01 |
| 1 | MINUS | | | | | |
| 2 | SORT UNIQUE | | 4 | 120 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 |
| 4 | SORT UNIQUE | | 1 | 30 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL| DEPT1 | 1 | 30 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
................
SQL> select * from dept where deptno not in(select deptno from dept1);
DEPTNO DNAME LOC
---------- -------------- -------------
30 SALES CHICAGO
40 OPERATIONS BOSTON
10 ACCOUNTING NEW YORK
Execution Plan
----------------------------------------------------------
Plan hash value: 1948948592
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 172 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN ANTI NA | | 4 | 172 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT1 | 1 | 13 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
............
SQL> select * from dept a,dept1 b where a.deptno=b.deptno(+) and b.deptno is null;
DEPTNO DNAME LOC DEPTNO DNAME LOC
---------- -------------- ------------- ---------- -------------- -------------
30 SALES CHICAGO
40 OPERATIONS BOSTON
10 ACCOUNTING NEW YORK
Execution Plan
----------------------------------------------------------
Plan hash value: 2254963940
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 60 | 7 (15)| 00:00:01 |
|* 1 | FILTER | | | | | |
|* 2 | HASH JOIN OUTER | | 1 | 60 | 7 (15)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| DEPT1 | 1 | 30 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
.........
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29785807/viewspace-1467540/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29785807/viewspace-1467540/