二十七、关联查询中表的连接顺序对SQL

╰+攻爆jí腚メ 2022-04-06 07:51 135阅读 0赞

### 1、基于代价时代（CBO）的表连接 ###

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 linesize 1000
    set timing on 
    select count(*) from tab_big,tab_small   ;  
    select count(*) from tab_small,tab_big   ;

SQL> select count(*) from tab_big,tab_small   ;
    
    已用时间:  00: 00: 00.04
    
    执行计划
    ----------------------------------------------------------
    Plan hash value: 177389953
    
    ---------------------------------------------------------------------------
    | Id  | Operation             | Name      | Rows  | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |           |     1 |  1186   (1)| 00:00:15 |
    |   1 |  SORT AGGREGATE       |           |     1 |            |          |
    |   2 |   MERGE JOIN CARTESIAN|           |   330K|  1186   (1)| 00:00:15 |
    |   3 |    TABLE ACCESS FULL  | TAB_SMALL |    10 |     3   (0)| 00:00:01 |
    |   4 |    BUFFER SORT        |           | 33012 |  1183   (1)| 00:00:15 |
    |   5 |     TABLE ACCESS FULL | TAB_BIG   | 33012 |   118   (0)| 00:00:02 |
    ---------------------------------------------------------------------------
    
    Note
    -----
       - dynamic sampling used for this statement (level=2)
    
    
    统计信息
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
            425  consistent gets
              0  physical reads
              0  redo size
            527  bytes sent via SQL*Net to client
            520  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   ;
    
    已用时间:  00: 00: 00.01
    
    执行计划
    ----------------------------------------------------------
    Plan hash value: 177389953
    
    ---------------------------------------------------------------------------
    | Id  | Operation             | Name      | Rows  | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |           |     1 |  1186   (1)| 00:00:15 |
    |   1 |  SORT AGGREGATE       |           |     1 |            |          |
    |   2 |   MERGE JOIN CARTESIAN|           |   330K|  1186   (1)| 00:00:15 |
    |   3 |    TABLE ACCESS FULL  | TAB_SMALL |    10 |     3   (0)| 00:00:01 |
    |   4 |    BUFFER SORT        |           | 33012 |  1183   (1)| 00:00:15 |
    |   5 |     TABLE ACCESS FULL | TAB_BIG   | 33012 |   118   (0)| 00:00:02 |
    ---------------------------------------------------------------------------
    
    Note
    -----
       - dynamic sampling used for this statement (level=2)
    
    
    统计信息
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
            425  consistent gets
              0  physical reads
              0  redo size
            527  bytes sent via SQL*Net to client
            520  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              1  sorts (memory)
              0  sorts (disk)
              1  rows processed

在代价时代，ORACLE优化器会比较两种写法的代价大小，进行比较，选择代价小的写法。

### 2、基于规则时代的表连接顺序 ###

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

SQL> select /*+rule*/ count(*) from tab_big,tab_small ;
    
    已用时间:  00: 00: 00.00
    
    执行计划
    ----------------------------------------------------------
    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
           4223  consistent gets
              0  physical reads
              0  redo size
            527  bytes sent via SQL*Net to client
            520  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 ;
    
    已用时间:  00: 00: 00.07
    
    执行计划
    ----------------------------------------------------------
    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
          90422  consistent gets
              0  physical reads
              0  redo size
            527  bytes sent via SQL*Net to client
            520  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25ld2JpZV85MDc0ODY4NTI_size_16_color_FFFFFF_t_70_pic_center][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25ld2JpZV85MDc0ODY4NTI_size_16_color_FFFFFF_t_70_pic_center]: /images/20220406/cdcf3f83b7554256b50a67e1c5d02e3e.png