数据库哪种查询效率高? 数据库多表查询和嵌套查询哪个效率高?今天老大看我写的SQL,让我不要用嵌套查询。我认为嵌套查能更高效,老大说这样没效率,而且不好懂。 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 什么事情都是具体问题具体分析的物竞天择 如果能确定哪一种效率高 另一种方法就该被淘汰了既然能够并存 就说明 存在的就是合理的我的理解是 如果两个表是通过某一个主键或索引相关联,那么就可以用多表关联,这样可能比嵌套查询效率高,而且嵌套的话,sql看起来也不简洁明了 哦,不好意思,是我的概念错误了,不应该叫嵌套查询我说的是这种情况,要不要把所有表都放在一个FROM里?下面两种写法哪种好?--1select t1.*, t2.*, t3.*from t1,t2,t3where t1.c1 = t2.c1 and t2.c2 = t3.c2;--2select t_a.*, t_3.* from (select t1.* t2.* from t1, t2 where t1.c1 = t2.c1) t_awhere t3.c2 = t_a.c2; --这两种的执行计划是一样的select t.*,sg.*from (select emp.empno,emp.sal,dept.deptno,dept.dnamefrom emp,deptwhere emp.deptno=dept.deptno) t, salgrade sgwhere t.sal=sg.sal EMPNO SAL DEPTNO DNAME SAL SALLEVEL---------- ---------- ---------- -------------- ---------- ---------- 7369 800 20 RESEARCH 800 least 7788 3000 20 RESEARCH 3000 middle 7839 5000 10 ACCOUNTING 5000 high 7902 3000 20 RESEARCH 3000 middleExecution Plan----------------------------------------------------------Plan hash value: 802173295----------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 4 | 176 | 8 (13)| 00:00:01 || 1 | NESTED LOOPS | | 4 | 176 | 8 (13)| 00:00:01 ||* 2 | HASH JOIN | | 4 | 124 | 7 (15)| 00:00:01 || 3 | TABLE ACCESS FULL | SALGRADE | 3 | 60 | 3 (0)| 00:00:01 ||* 4 | TABLE ACCESS FULL | EMP | 14 | 154 | 3 (0)| 00:00:01 || 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 ||* 6 | INDEX UNIQUE SCAN | BIN$8mPMMewIQa6BxCHkeZP6Wg==$0 | 1 | | 0 (0)| 00:0----------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("EMP"."SAL"="SALGRADE"."SAL") 4 - filter("EMP"."SAL" IS NOT NULL) 6 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")Note----- - dynamic sampling used for this statement select emp.empno,emp.sal,dept.deptno,dept.dname,salgrade.sal,salgrade.sallevel from emp,dept,salgrade where emp.deptno=dept.deptno and emp.sal=salgrade.sal EMPNO SAL DEPTNO DNAME SAL SALLEVEL---------- ---------- ---------- -------------- ---------- ---------- 7369 800 20 RESEARCH 800 least 7788 3000 20 RESEARCH 3000 middle 7839 5000 10 ACCOUNTING 5000 high 7902 3000 20 RESEARCH 3000 middleExecution Plan----------------------------------------------------------Plan hash value: 802173295----------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 4 | 176 | 8 (13)| 00:00:01 || 1 | NESTED LOOPS | | 4 | 176 | 8 (13)| 00:00:01 ||* 2 | HASH JOIN | | 4 | 124 | 7 (15)| 00:00:01 || 3 | TABLE ACCESS FULL | SALGRADE | 3 | 60 | 3 (0)| 00:00:01 ||* 4 | TABLE ACCESS FULL | EMP | 14 | 154 | 3 (0)| 00:00:01 || 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 ||* 6 | INDEX UNIQUE SCAN | BIN$8mPMMewIQa6BxCHkeZP6Wg==$0 | 1 | | 0 (0)| 00:0----------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("EMP"."SAL"="SALGRADE"."SAL") 4 - filter("EMP"."SAL" IS NOT NULL) 6 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")Note----- - dynamic sampling used for this statement 求教一 oracle sql 正则表达式 oracle连接问题 对于游标表达式的问题 累加计算某字段的值,求高人指点。 求救RHEL 4 下安装oracle 9i oracle 11g64位问题 请叫orcale安装的问题,怪问题! 请问关于C#调用oracle存储包返回游标的用法? 急!:谁知道在solaris英文版上装的ORACLE的字符集NLS_LANGUAGE是什么 安装oracle8i时出现乱码问题,请高手帮我解决,谢谢! 关于一个SQL语句group by的实现 oracle current 关键字用法
物竞天择 如果能确定哪一种效率高 另一种方法就该被淘汰了
既然能够并存 就说明 存在的就是合理的我的理解是 如果两个表是通过某一个主键或索引相关联,那么就可以用多表关联,这样可能比嵌套查询效率高,而且嵌套的话,sql看起来也不简洁明了
下面两种写法哪种好?
--1
select t1.*, t2.*, t3.*
from t1,t2,t3
where t1.c1 = t2.c1
and t2.c2 = t3.c2;
--2
select t_a.*, t_3.*
from
(select t1.* t2.*
from t1, t2
where t1.c1 = t2.c1) t_a
where t3.c2 = t_a.c2;
select t.*,sg.*
from (select emp.empno,emp.sal,dept.deptno,dept.dname
from emp,dept
where emp.deptno=dept.deptno) t, salgrade sg
where t.sal=sg.sal EMPNO SAL DEPTNO DNAME SAL SALLEVEL
---------- ---------- ---------- -------------- ---------- ----------
7369 800 20 RESEARCH 800 least
7788 3000 20 RESEARCH 3000 middle
7839 5000 10 ACCOUNTING 5000 high
7902 3000 20 RESEARCH 3000 middle
Execution Plan
----------------------------------------------------------
Plan hash value: 802173295----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 176 | 8 (13)| 00:00:01 |
| 1 | NESTED LOOPS | | 4 | 176 | 8 (13)| 00:00:01 |
|* 2 | HASH JOIN | | 4 | 124 | 7 (15)| 00:00:01 |
| 3 | TABLE ACCESS FULL | SALGRADE | 3 | 60 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | EMP | 14 | 154 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | BIN$8mPMMewIQa6BxCHkeZP6Wg==$0 | 1 | | 0 (0)| 00:0
----------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 2 - access("EMP"."SAL"="SALGRADE"."SAL")
4 - filter("EMP"."SAL" IS NOT NULL)
6 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")Note
-----
- dynamic sampling used for this statement select emp.empno,emp.sal,dept.deptno,dept.dname,salgrade.sal,salgrade.sallevel
from emp,dept,salgrade
where emp.deptno=dept.deptno and emp.sal=salgrade.sal EMPNO SAL DEPTNO DNAME SAL SALLEVEL
---------- ---------- ---------- -------------- ---------- ----------
7369 800 20 RESEARCH 800 least
7788 3000 20 RESEARCH 3000 middle
7839 5000 10 ACCOUNTING 5000 high
7902 3000 20 RESEARCH 3000 middle
Execution Plan
----------------------------------------------------------
Plan hash value: 802173295----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 176 | 8 (13)| 00:00:01 |
| 1 | NESTED LOOPS | | 4 | 176 | 8 (13)| 00:00:01 |
|* 2 | HASH JOIN | | 4 | 124 | 7 (15)| 00:00:01 |
| 3 | TABLE ACCESS FULL | SALGRADE | 3 | 60 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | EMP | 14 | 154 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | BIN$8mPMMewIQa6BxCHkeZP6Wg==$0 | 1 | | 0 (0)| 00:0
----------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 2 - access("EMP"."SAL"="SALGRADE"."SAL")
4 - filter("EMP"."SAL" IS NOT NULL)
6 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")Note
-----
- dynamic sampling used for this statement