详解两条T-SQL语句(如下) --1.select * from A,B where A.AID=B.BID and A.AID=1--2.select * from (select AID from A where AID=1) as A1,B where A1.AID=B.BID1.在任何情况下,上面两条T-SQL语句的执行结果是否相同?2.详解上面两条T-SQL语句在什么情况下,执行效率问题? 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 select d.* from emp e,dept d where e.deptno=d.deptno and e.deptno=10;执行计划如下:执行计划----------------------------------------------------------Plan hash value: 568005898----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 5 | 110 | 4 (0)| 00:00:01 || 1 | NESTED LOOPS | | 5 | 110 | 4 (0)| 00:00:01 || 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 ||* 3 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 ||* 4 | TABLE ACCESS FULL | EMP | 5 | 10 | 3 (0)| 00:00:01 |----------------------------------------------------------------------------------------select d.* from (select a.deptno from emp a where a.deptno=10) e,dept d where e.deptno=d.deptno;执行计划如下:执行计划----------------------------------------------------------Plan hash value: 568005898----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 5 | 110 | 4 (0)| 00:00:01 || 1 | NESTED LOOPS | | 5 | 110 | 4 (0)| 00:00:01 || 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 ||* 3 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 ||* 4 | TABLE ACCESS FULL | EMP | 5 | 10 | 3 (0)| 00:00:01 |---------------------------------------------------------------------------------------- 两者执行计划相同,返回结果相同 首先谢谢你的回答。顺便请问你,如何通过执行计划比较T-SQL语句的性能?比较那几个? 急,求解答 left join 连接问题 Oracle数据库迁移问题,请高手帮帮忙,不胜感激! 求一条批量更新的sql( update ... select ) oracle job进程 怎么找不到我的问题 请问哪里有ORACLE这软件下载啊! 如何对一个取出的字段,进行编辑 请教oracle数据类型的问题 请问oracle8i中对表中的碎片整理的命令是什么? oracle 分组统计 小计的 求解 . oracle 零时表扫描,完成数据验证后,插入到其他表
执行计划如下:
执行计划
----------------------------------------------------------
Plan hash value: 568005898----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 110 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 5 | 110 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | EMP | 5 | 10 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------select d.* from (select a.deptno from emp a where a.deptno=10) e,dept d where e.deptno=d.deptno;
执行计划如下:
执行计划
----------------------------------------------------------
Plan hash value: 568005898----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 110 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 5 | 110 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | EMP | 5 | 10 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------- 两者执行计划相同,返回结果相同
顺便请问你,如何通过执行计划比较T-SQL语句的性能?比较那几个?