大表在前，下表在后的说法真的对吗？

最新推荐文章于 2021-04-27 15:22:22 发布
最新推荐文章于 2021-04-27 15:22:22 发布
阅读量2.3k 收藏
点赞数
分类专栏： Basic SQL & PL/SQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u013412535/article/details/44922595
版权

我自己以前在网上，看到说oracle是从下到上，从右到左访问，因此我们写sql时应该把大表放在前面，小表放在后面。


今天我看了一个教程，然后自己做了实验，发现这种说法是有问题的。

下面，让我用事实来说话。

首先，准备数据

drop table tab_big;
drop table tab_small;
create table tab_big  as select * from dba_objects where rownum<=30000;
create table tab_small  as select * from dba_objects where rownum<=10;
set autotrace traceonly
set timing on


 下面进行测试：



SQL> select count(*) from tab_big,tab_small;


  COUNT(*)
----------
    300000


已用时间:  00: 00: 00.07


执行计划
----------------------------------------------------------
Plan hash value: 177389953


---------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |     1 |  1137   (1)| 00:00:14 |
|   1 |  SORT AGGREGATE       |           |     1 |            |          |
|   2 |   MERGE JOIN CARTESIAN|           |   331K|  1137   (1)| 00:00:14 |
|   3 |    TABLE ACCESS FULL  | TAB_SMALL |    10 |     2   (0)| 00:00:01 |
|   4 |    BUFFER SORT        |           | 33181 |  1135   (1)| 00:00:14 |
|   5 |     TABLE ACCESS FULL | TAB_BIG   | 33181 |   113   (0)| 00:00:02 |
---------------------------------------------------------------------------


Note
-----
   - dynamic sampling used for this statement (level=2)




统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        424  consistent gets
          0  physical reads
          0  redo size
        423  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed


SQL> select count(*) from tab_small,tab_big   ;


  COUNT(*)
----------
    300000


已用时间:  00: 00: 00.05


执行计划
----------------------------------------------------------
Plan hash value: 177389953


---------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |     1 |  1137   (1)| 00:00:14 |
|   1 |  SORT AGGREGATE       |           |     1 |            |          |
|   2 |   MERGE JOIN CARTESIAN|           |   331K|  1137   (1)| 00:00:14 |
|   3 |    TABLE ACCESS FULL  | TAB_SMALL |    10 |     2   (0)| 00:00:01 |
|   4 |    BUFFER SORT        |           | 33181 |  1135   (1)| 00:00:14 |
|   5 |     TABLE ACCESS FULL | TAB_BIG   | 33181 |   113   (0)| 00:00:02 |
---------------------------------------------------------------------------


Note
-----
   - dynamic sampling used for this statement (level=2)




统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        424  consistent gets
          0  physical reads
          0  redo size
        423  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

可以看到，上面两种方式执行的时间与代价基本相同，微微的差别可能是运行的机器造成的。

下面对上面两句加上/*+rule*/注释：


SQL> select /*+rule*/ count(*) from tab_big,tab_small ;


  COUNT(*)
----------
    300000


已用时间:  00: 00: 00.03


执行计划
----------------------------------------------------------
Plan hash value: 41936691


-----------------------------------------
| Id  | Operation           | Name      |
-----------------------------------------
|   0 | SELECT STATEMENT    |           |
|   1 |  SORT AGGREGATE     |           |
|   2 |   NESTED LOOPS      |           |
|   3 |    TABLE ACCESS FULL| TAB_SMALL |
|   4 |    TABLE ACCESS FULL| TAB_BIG   |
-----------------------------------------


Note
-----
   - rule based optimizer used (consider using cbo)




统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       4213  consistent gets
          0  physical reads
          0  redo size
        423  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


SQL> select /*+rule*/ count(*) from tab_small,tab_big ;


  COUNT(*)
----------
    300000


已用时间:  00: 00: 00.33


执行计划
----------------------------------------------------------
Plan hash value: 2127005654


-----------------------------------------
| Id  | Operation           | Name      |
-----------------------------------------
|   0 | SELECT STATEMENT    |           |
|   1 |  SORT AGGREGATE     |           |
|   2 |   NESTED LOOPS      |           |
|   3 |    TABLE ACCESS FULL| TAB_BIG   |
|   4 |    TABLE ACCESS FULL| TAB_SMALL |
-----------------------------------------


Note
-----
   - rule based optimizer used (consider using cbo)




统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      90421  consistent gets
          0  physical reads
          0  redo size
        423  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


SQL>

可以看到，加注释后表的放置顺序对性能就有影响了。

结论：写sql时，在RBO中是需要考虑表的访问顺序的，但是在CBO中不需要考虑，因为CBO是基于代价的优化器，哪种方式的代价小，优化器就会使用哪种方式。


网上有一句原话是这样的：

WHERE子句中的连接顺序．：
ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.

下面我们来做实验：

准备数据：

SQL> create table t1 as select * from dba_objects;


表已创建。


SQL> create table t2 as select rownum id ,dbms_random.string('b', 50) n ,data_ob
ject_id data_id from dba_objects where rownum<=10000;


表已创建。


SQL> set autotrace traceonly
SQL> set linesize 1000
SQL> set timing on

SQL>

下面开始实验：



SQL> select * from t1,t2 where t1.object_id=29 and t2.data_id>8;


已选择2085行。


已用时间:  00: 00: 00.18


执行计划
----------------------------------------------------------
Plan hash value: 787647388


-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      | 18650 |    39M|   540   (1)| 00:00:07 |
|   1 |  MERGE JOIN CARTESIAN|      | 18650 |    39M|   540   (1)| 00:00:07 |
|*  2 |   TABLE ACCESS FULL  | T1   |    11 |  2277 |   283   (1)| 00:00:04 |
|   3 |   BUFFER SORT        |      |  1638 |  3244K|   257   (1)| 00:00:04 |
|*  4 |    TABLE ACCESS FULL | T2   |  1638 |  3244K|    23   (0)| 00:00:01 |
-----------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   2 - filter("T1"."OBJECT_ID"=29)
   4 - filter("T2"."DATA_ID">8)


Note
-----
   - dynamic sampling used for this statement (level=2)




统计信息
----------------------------------------------------------
        412  recursive calls
          0  db block gets
       1291  consistent gets
          0  physical reads
          0  redo size
     156639  bytes sent via SQL*Net to client
       1933  bytes received via SQL*Net from client
        140  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       2085  rows processed


SQL> select * from t1,t2 where t2.data_id>8 and t1.object_id=29 ;


已选择2085行。


已用时间:  00: 00: 00.19


执行计划
----------------------------------------------------------
Plan hash value: 787647388


-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      | 18650 |    39M|   540   (1)| 00:00:07 |
|   1 |  MERGE JOIN CARTESIAN|      | 18650 |    39M|   540   (1)| 00:00:07 |
|*  2 |   TABLE ACCESS FULL  | T1   |    11 |  2277 |   283   (1)| 00:00:04 |
|   3 |   BUFFER SORT        |      |  1638 |  3244K|   257   (1)| 00:00:04 |
|*  4 |    TABLE ACCESS FULL | T2   |  1638 |  3244K|    23   (0)| 00:00:01 |
-----------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   2 - filter("T1"."OBJECT_ID"=29)
   4 - filter("T2"."DATA_ID">8)


Note
-----
   - dynamic sampling used for this statement (level=2)




统计信息
----------------------------------------------------------
          7  recursive calls
          0  db block gets
       1251  consistent gets
          0  physical reads
          0  redo size
     156639  bytes sent via SQL*Net to client
       1933  bytes received via SQL*Net from client
        140  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       2085  rows processed

看，使用CBO，并没有连接顺序的带来的性能问题，性能基本一致。

SQL> select /*+rule*/ * from t1,t2 where t2.data_id>8 and t1.object_id=29 ;


已选择2085行。


已用时间:  00: 00: 19.87


执行计划
----------------------------------------------------------
Plan hash value: 4016936828


-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 |  NESTED LOOPS      |      |
|*  2 |   TABLE ACCESS FULL| T2   |
|*  3 |   TABLE ACCESS FULL| T1   |
-----------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   2 - filter("T2"."DATA_ID">8)
   3 - filter("T1"."OBJECT_ID"=29)


Note
-----
   - rule based optimizer used (consider using cbo)




统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
    2168766  consistent gets
          0  physical reads
          0  redo size
     156639  bytes sent via SQL*Net to client
       1933  bytes received via SQL*Net from client
        140  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       2085  rows processed


SQL> select /*+rule*/ * from t1,t2 where t1.object_id=29 and t2.data_id>8;


已选择2085行。


已用时间:  00: 00: 18.51


执行计划
----------------------------------------------------------
Plan hash value: 4016936828


-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 |  NESTED LOOPS      |      |
|*  2 |   TABLE ACCESS FULL| T2   |
|*  3 |   TABLE ACCESS FULL| T1   |
-----------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   2 - filter("T2"."DATA_ID">8)
   3 - filter("T1"."OBJECT_ID"=29)


Note
-----
   - rule based optimizer used (consider using cbo)




统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
    2168764  consistent gets
       2155  physical reads
          0  redo size
     156639  bytes sent via SQL*Net to client
       1933  bytes received via SQL*Net from client
        140  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       2085  rows processed

看结果，RBO都做不出来网上所说的那样的结果啊！


再加上连接条件：


SQL> select /*+rule*/ * from t1,t2 where t1.object_id=t2.id and t1.object_id=29
and t2.data_id>8;


已用时间:  00: 00: 00.06


执行计划
----------------------------------------------------------
Plan hash value: 1792967693


------------------------------------
| Id  | Operation           | Name |
------------------------------------
|   0 | SELECT STATEMENT    |      |
|   1 |  MERGE JOIN         |      |
|   2 |   SORT JOIN         |      |
|*  3 |    TABLE ACCESS FULL| T2   |
|*  4 |   SORT JOIN         |      |
|*  5 |    TABLE ACCESS FULL| T1   |
------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   3 - filter("T2"."DATA_ID">8)
   4 - access("T1"."OBJECT_ID"="T2"."ID")
       filter("T1"."OBJECT_ID"="T2"."ID")
   5 - filter("T1"."OBJECT_ID"=29)


Note
-----
   - rule based optimizer used (consider using cbo)




统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1129  consistent gets
          0  physical reads
          0  redo size
       1623  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed


SQL> select /*+rule*/ * from t1,t2 where t1.object_id=t2.id and t2.data_id>8 and
 t1.object_id=29 ;


已用时间:  00: 00: 00.03


执行计划
----------------------------------------------------------
Plan hash value: 1792967693


------------------------------------
| Id  | Operation           | Name |
------------------------------------
|   0 | SELECT STATEMENT    |      |
|   1 |  MERGE JOIN         |      |
|   2 |   SORT JOIN         |      |
|*  3 |    TABLE ACCESS FULL| T2   |
|*  4 |   SORT JOIN         |      |
|*  5 |    TABLE ACCESS FULL| T1   |
------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   3 - filter("T2"."DATA_ID">8)
   4 - access("T1"."OBJECT_ID"="T2"."ID")
       filter("T1"."OBJECT_ID"="T2"."ID")
   5 - filter("T1"."OBJECT_ID"=29)


Note
-----
   - rule based optimizer used (consider using cbo)




统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1129  consistent gets
          0  physical reads
          0  redo size
       1623  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

同样，基本没什么影响啊！！


所以说，网上的一些的sql优化准则，有时候真的是有问题的，优化还是需要具体问题具体分析才行。

做人也是如此，那些人们认为天经地义的准则，有时候是有问题的，还是需要我们自己去检验，最后得出真理啊！！

大数据最好
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
LED灯管到底要多亮呢？CNS国家标各种场所照度标准表
01-20
LED灯管到底要多亮呢？ 本厂从事LED灯管OEM/ODM的过程中，在前期的需求访谈阶段，常常会有通路客户提到产品的光源与实际应用面的数据。曾经有传统灯具客户采购LED灯时，要求一定要超过20W的 LED灯管，不然会"不够亮，诸如此类的说法与观念在传统照明与LED照明的世代交替之际，时常会有所误解。 　　20W, 40W 所指的是灯工作时消耗功率的数据，并不代表"亮度",只是传统白炽灯的发光效率为 12Lm/W~15Lm/W,所以20W的白炽灯 会 40W白炽灯来得亮。然而，LED照明产品要节能，就是透过光、机、电、热四个层面的开发设计能力，用少的耗能来产生的照明，到底要多亮才是亮呢？我们也
oracle 表连接 大表小表_优化必备基础：Oracle中常见的三种表连接方式
weixin_30486089的博客
01-17 2880
在Oracle SQL语句中，如果from后面有多个表时，表的连接方式是一个很重要的考量。从Oracle 6开始，优化器就支持下面4种表连接方式：— 嵌套循环连接（Nested Loop Join）— 群集连接（Cluster Join）— 排序合并连接（Sort-Merge Join）— 笛卡尔连接（Cartesian Join）在Oracle 7.3中，新增加了哈希连接（Hash Join）。...
oracle左关联 号,Oracle左连接(+在等号右边),右连接(+在等号左边)
weixin_30114439的博客
04-02 593
数据表的连接有:1、内连接(自然连接): 只有两个表相匹配的行才能在结果集中出现2、外连接: 包括(1)左外连接(左边的表不加限制)(2)右外连接(右边的表不加限制)(3)全外连接(左右两表都不加限制)3、自连接(连接发生在一张基表内)select a.studentno, a.studentname, b.classnamefrom students a, classes bwhere a.cl...
oracle先关联后条件,两个表如何分优先级条件关联，请大神指点
weixin_28934777的博客
04-03 399
本帖最后由 仰望Pizza 于 2016-5-23 15:07 编辑bell6248 发表于 2016-5-23 14:43 把建表和插入的数据脚本贴一下， 把最终结果也贴一下如下，我不是楼主drop table temp_a;create table temp_a(id number,tax_code varchar2(50),acc_num varchar2(30));insert into ...
Oracle中常用的sql优化
lzl1101206656的博客
06-07 907
1、WHERE子句中的连接顺序 ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。 2、SELECT子句中避免使用 ” * ” ORACLE在解析的过程中, 会将”*” 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间。 3、用Wh...
记ORACLE表的查询条件或关联条件执行顺序问题
JMan.DYS的专栏
04-09 1944
查询条件或关联条件执行顺序是按照数据库结构字段顺序执行的 （下面例子中，t_1表，C1字段在前，C2字段在后） 如： select * from table t_1 t where t.c2= 123, t.c1='123'; 假设，C2字段存放的值都是字符串，从表面来看，此语句执行是会报错的，因为123是数字，数据会将C2字段转换成数字和123进行比较，这样就会报错“无效数字”。然而...
面试宝典-多个表进行连接操作大表在前还是小表在前
qq_34861505的博客
04-27 4145
答案：小表在前。 假设A表200条数据，B表20万条数据。两个做连接操作： 如果小的循环在外层，对于表连接来说就只连接200次 ; 如果大的循环在外层，则需要进行20万次表连接，从而浪费资源，增加消耗 ; 小表驱动大表的主要目的是通过减少表连接创建的次数，加快查询速度 。 ...
多个表左连接 先连大表还是小表_优化必备基础：Oracle中常见的三种表连接方式...
weixin_39593498的博客
12-06 2982
在Oracle SQL语句中，如果from后面有多个表时，表的连接方式是一个很重要的考量。从Oracle 6开始，优化器就支持下面4种表连接方式：— 嵌套循环连接(Nested Loop Join)— 群集连接(Cluster Join)— 排序合并连接(Sort-Merge Join)— 笛卡尔连接(Cartesian Join)在Oracle 7.3中，新增加了哈希连接(Hash Join)。...
Oracle 多表 连接 顺序 与 性能关系 测试
David Dai -- Focus on Oracle
06-16 1万+
一. 创建表并insert 数据create table ta (id number,name varchar2(10));create table tb(id number,job varchar2(10)); beginfor i in 1..1000000 loopbegininsert into ta values(i,dave);commit;
SQL Join连接大小表在前在后的重要性（小表在前提高执行效率）
热门推荐
山山来驰的博客
03-25 1万+
经常看到一些Hive优化的建议中说当小表与大表做关联时，把小表写在前面，这样可以使Hive的关联速度更快，提到的原因都是说因为小表可以先放到内存中，然后大表的每条记录再去内存中检测，最终完成关联查询。这样的原因看似合理，但是仔细推敲，又站不住脚跟。 多小的表算小表？如果所谓的小表在内存中放不下怎么办？我用2个只有几条记录的表做关联查询，这应该算是小表了，在查看reduce的执行日志时依然是有写磁...
036在驱动中使用链表sys部分.7z
09-19
链表sys部分？C语言中有这说法吗？好像没有，不过可以看下，在驱动中是怎样呢
为什么说MySQL单表数据不要超过500万行
12-16
曾经在中国互联网技术圈广为流传着这么一个说法：MySQL 单表数据量大于 2000 万行，性能会明显下降。事实上，这个传闻据说最早起源于百度。具体情况大概是这样的，当年的 DBA 测试 MySQL性能时发现，当单表的量在 ...
基础电子中的LED灯管到底要多亮呢？CNS国家标各种场所照度标准表
10-21
曾经有传统灯具客户采购LED灯时，要求一定要超过20W的 LED灯管，不然会"不够亮，诸如此类的说法与观念在传统照明与LED照明的世代交替之际，时常会有所误解。 　20W, 40W 所指的是灯工作时消耗功率的数据，并不代表...
Python 数据结构之旋转链表
12-24
题目描述：给定一个链表，旋转链表，使得每个...不过，需要注意的是，题中没有给出k的大小，当k比链表的长度还大的时候，我们就需要先用k对链表的长度求余，比如，如果k = 7，那么上面的例子还是将4->5移动到整个链表前
执行计划查看方法（Explain plan）
Murphyt的专栏
02-07 7294
什么是执行计划 所谓执行计划，顾名思义，就是对一个查询任务，做出一份怎样去完成任务的详细方案。举个生活中的例子，我从珠海要去英国，我可以 选择先去香港然后转机，也可以先去北京转机，或者去广州也可以。但是到底怎样去英国划算，也就是我的费用最少，这是一件值得考究 的事情。同样对于查询而言，我们提交的SQL仅仅是描述出了我们的目的地是英国，但至于怎么去，通常我们的SQL中是没有给出提示信息 的，
case when 与 decode 用法
Murphyt的专栏
02-09 1506
case when 在不同条件需要有不同返回值的情况下使用非常方便，可以在给变量赋值时使用，也可以在select查询语句中使用。 case搜索语句格式： case  when 条件1 then 返回值1  when 条件2 then 返回值2  ...  else 返回值N  end; case when使用示例代码： select empno,ename,job,
多表连接的三种方式hash join，merge join，nested loop
Murphyt的专栏
02-09 1486
多表之间的连接有三种方式：Nested Loops，Hash Join 和 Sort Merge Join. 下面来介绍三种不同连接的不同：     一． NESTED LOOP: 对于被连接的数据子集较小的情况，嵌套循环连接是个较好的选择。在嵌套循环中，内表被外表驱动，外表返回的每一行都要在内表中检索找到与它匹配的行，因此整个查询返回的结果集不能太大（大于1 万不适合），要把返回子
with as 的使用
Murphyt的专栏
02-04 1485
with as语法 –针对一个别名 with tmp as (select * from tb_name) –针对多个别名 with    tmp as (select * from tb_name),    tmp2 as (select * from tb_name2),    tmp3 as (select * from tb_name3),    … --相当于建了个e临
关于CAP多明细表显示方式说法正确的是？
最新发布
03-11
水平显示是指每个明细表的字段都在同一行显示，而垂直显示则是每个明细表的字段都在同一列显示。正确的说法是，CAP多明细表可以采用水平或垂直显示方式，具体选择哪种方式取决于实际情况和用户需求。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值