SQL查询代码

àì夳堔傛蜴生んèń 2023-10-09 16:19 40阅读 0赞

-- 看到很多人，写SQL代码非常随意，想法固然没错：每种方法都可以，只是结果一样就行!
    -- 但是，我想：养成良好的SQL风格，会使你终生受益!
    -- 因为：有时不同的查询，得到同样的结果，其效率却相差甚远......
    
    -- 请看：
    -- 查询一表中昨天生成的数据
    -- 原表mobilefrends中的cdate字段上有索引，创建索引语句是：create index mobilefrends_cdate_idx on mobilefrends(cdate);
    
    ---------------------------------------------------------------------------------------------------------------------
    -- 方法一：用to_char()函数
    
    hll@SZTYORA> select count(*) from mobilefrends where to_char(cdate,'yyyy-mm-dd')=to_char(sysdate-1,'yyyy-mm-dd');
    
      COUNT(*)
    ----------
         82119
    
    已用时间:  00: 00: 17.18
    
    执行计划
    ----------------------------------------------------------
    Plan hash value: 3731074549
    
    ------------------------------------------------------------------------------------------------
    | Id  | Operation             | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |                        |     1 |     8 | 11773  (16)| 00:02:22 |
    |   1 |  SORT AGGREGATE       |                        |     1 |     8 |            |          |
    |*  2 |   INDEX FAST FULL SCAN| MOBILEFRENDS_CDATE_IDX |   129K|  1012K| 11773  (16)| 00:02:22 |
    ------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter(TO_CHAR(INTERNAL_FUNCTION("CDATE"),'yyyy-mm-dd')=TO_CHAR(SYSDATE@!-1,'yyyy
                  -mm-dd'))
    
    
    统计信息
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
          36896  consistent gets
              0  physical reads
              0  redo size
            345  bytes sent via SQL*Net to client
            338  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    ---------------------------------------------------------------------------------------------------------------------
    -- 方法二：用trunc()函数
    
    hll@SZTYORA> select count(*) from mobilefrends where trunc(cdate)=trunc(sysdate-1);
    
      COUNT(*)
    ----------
         82119
    
    已用时间:  00: 00: 16.32
    
    执行计划
    ----------------------------------------------------------
    Plan hash value: 3731074549
    
    ------------------------------------------------------------------------------------------------
    | Id  | Operation             | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |                        |     1 |     8 | 12580  (22)| 00:02:31 |
    |   1 |  SORT AGGREGATE       |                        |     1 |     8 |            |          |
    |*  2 |   INDEX FAST FULL SCAN| MOBILEFRENDS_CDATE_IDX |   129K|  1012K| 12580  (22)| 00:02:31 |
    ------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter(TRUNC(INTERNAL_FUNCTION("CDATE"))=TRUNC(SYSDATE@!-1))
    
    
    统计信息
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
          36896  consistent gets
              0  physical reads
              0  redo size
            345  bytes sent via SQL*Net to client
            338  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    ---------------------------------------------------------------------------------------------------------------------
    -- 方法三：不用函数
    hll@SZTYORA> select count(*) from mobilefrends where cdate>=trunc(sysdate-1) and cdate<trunc(sysdate);
    
      COUNT(*)
    ----------
         82119
    
    已用时间:  00: 00: 00.43
    
    执行计划
    ----------------------------------------------------------
    Plan hash value: 2668176725
    
    ---------------------------------------------------------------------------------------------
    | Id  | Operation          | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |                        |     1 |     8 |     3   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE    |                        |     1 |     8 |            |          |
    |*  2 |   FILTER           |                        |       |       |            |          |
    |*  3 |    INDEX RANGE SCAN| MOBILEFRENDS_CDATE_IDX |    48 |   384 |     3   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter(TRUNC(SYSDATE@!-1)<TRUNC(SYSDATE@!))
       3 - access("CDATE">=TRUNC(SYSDATE@!-1) AND "CDATE"<TRUNC(SYSDATE@!))
    
    
    统计信息
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
            221  consistent gets
              0  physical reads
              0  redo size
            345  bytes sent via SQL*Net to client
            338  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    ---------------------------------------------------------------------------------------------------------------------
    
    
    -- 由上三种方法可见：
    -- 方法一：用to_char()函数，耗时17.18秒，逻辑读为36896次，执行的是全索引扫描；
    -- 方法二：用trunc()函数，耗时16.32秒，逻辑读也是36896次，执行的也是全索引扫描；
    -- 方法三：不用函数，耗时0.43秒，逻辑读为221次，执行的是索引范围扫描；
    
    
    -- 请问：哪种方法好呢？
    
    
    
    -- 在我的SQL语句中，我的原则是：
    -- *(01) 尽量避免隐式类型转换
    --      (例如：如果字段是日期类型的，我会在where语句等式左边用字段原型，而尽量在等式右边用函数，
                   绝不会在左边用函数转换成字符再去与右边比较)；
    -- *(02) 尽量少用函数，同样一件事情，能够用两个函数就能解决的，我绝不会嵌套三个函数；
    --      (例如：求上个月的最后一天，我会用trunc(sysdate,'mm')-1，而不用last_day(add_months(sysdate,-1)) )
    -- *(03) 在所有的存储过程中，我会尽量用绑定变量，以避免硬解析带来的资源消耗!
    -- *(04) 在所有的存储过程中，能够用SQL语句的，我绝不会用循环去实现!
    --      (例如：列出上个月的每一天，我会用connect by去递归查询一下，绝不会去用循环从上个月第一天到最后一天)
    -- *(05) 当有一批处理的插入或更新时，我会用批量插入或批量更新，绝不会一条条记录的去更新!