koko的oracle杂货铺

一个简单比较的sql

SQL> set linesize 200
SQL> set pages 0
SQL> SELECT *
  2    FROM (  SELECT DISTINCT
  3                   hotel_id,
  4                   gisid,
  5                   chn_name,
  6                   longitude,
  7                   latitude,
  8                   chn_address,
  9                   POWER ((ABS (120 - longitude) * ABS (120 - longitude)
 10                       + ABS (35 - latitude) * ABS (35 - latitude)),0.5)AS distance
 11              FROM htl_ii.htl_hotel
 12             WHERE     longitude > 0
 13                   AND latitude > 0
 14                   AND longitude IS NOT NULL
 15                   AND latitude IS NOT NULL
 16                   AND gisid > 0
 17                   AND active = 1
 18                   AND HOTEL_SYSTEM_SIGN = '01'
 19          ORDER BY distance ASC)
 20   WHERE ROWNUM <= 5;

Elapsed: 00:00:01.47

Execution Plan
----------------------------------------------------------
Plan hash value: 997346329

-----------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |     1 |   325 |   451   (2)| 00:00:06 |
|*  1 |  COUNT STOPKEY        |           |       |       |            |          |
|   2 |   VIEW                |           |     1 |   325 |   451   (2)| 00:00:06 |
|*  3 |    SORT UNIQUE STOPKEY|           |     1 |   117 |   450   (1)| 00:00:06 |
|*  4 |     TABLE ACCESS FULL | HTL_HOTEL |     1 |   117 |   449   (1)| 00:00:06 |
-----------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=5)
   3 - filter(ROWNUM<=5)
   4 - filter("LONGITUDE" IS NOT NULL AND "LATITUDE" IS NOT NULL AND
              "LONGITUDE">0 AND "LATITUDE">0 AND "GISID">0 AND TO_NUMBER("ACTIVE"=1 AND
              "HOTEL_SYSTEM_SIGN"='01')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       2014  consistent gets
          0  physical reads
          0  redo size
       1468  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          5  rows processed

修改以后

SQL> SELECT gisid,
  2         chn_name,
  3         longitude,
  4         latitude,
  5         chn_address,
  6         hotel_id,
  7         POWER (distance, 0.5)
  8    FROM (  SELECT DISTINCT
  9                   hotel_id,
  10                  gisid,
 11                   chn_name,
 12                   longitude,
 13                   latitude,
 14                   chn_address,
 15                   --POWER (
 16                   --(
 17                   ABS ( (120 - longitude) * (120 - longitude))
 18                   + ABS ( (35 - latitude) * (35 - latitude))
 19                      --),
 20                      --0.5)
 21                      AS distance
 22              FROM htl_ii.htl_hotel
 23             WHERE     longitude > 0
 24                   AND latitude > 0
 25                   AND longitude IS NOT NULL
 26                   AND latitude IS NOT NULL
 27                   AND gisid > 0
 28                   AND active = 1
 29                   AND HOTEL_SYSTEM_SIGN = '01'
 30          ORDER BY distance ASC)
 31   WHERE ROWNUM <= 5;

Elapsed: 00:00:00.06

Execution Plan
----------------------------------------------------------
Plan hash value: 997346329

-----------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |     1 |   325 |   451   (2)| 00:00:06 |
|*  1 |  COUNT STOPKEY        |           |       |       |            |          |
|   2 |   VIEW                |           |     1 |   325 |   451   (2)| 00:00:06 |
|*  3 |    SORT UNIQUE STOPKEY|           |     1 |   117 |   450   (1)| 00:00:06 |
|*  4 |     TABLE ACCESS FULL | HTL_HOTEL |     1 |   117 |   449   (1)| 00:00:06 |
-----------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=5)
   3 - filter(ROWNUM<=5)
   4 - filter("LONGITUDE" IS NOT NULL AND "LATITUDE" IS NOT NULL AND
              "LONGITUDE">0 AND "LATITUDE">0 AND "GISID">0 AND TO_NUMBER("ACTIVE"=1 AND
              "HOTEL_SYSTEM_SIGN"='01')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       2014  consistent gets
          0  physical reads
          0  redo size
       1479  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          5  rows processed

 单从执行计划和统计信息来看这2个sql几乎没有任何分别，返回结果数，逻辑读，排序记录数等等都一样，唯一不同的就是执行时间。

说说这个sql,当我拿到这个sql的时候，1s的执行时间非常让我费解，不就才1w行记录的全表读么？70多个字段,1k的平均行长，总表大小16m,至于用1s才返回结果吗？当我发现()里的power以后，我顿时明白了，这样的问题再简单不过了，是先做运算再排序然后返回前N条更快，还是先排序然后返回前N条再做运算更快？一个power执行了1w次，一个power执行了5次，谁会更快？

 从这个问题延伸开来,当sql涉及到排序然后返回固定条数记录的时候，比如分页，我们是不是该认真考虑下这个问题：是不是有些计算，是可以在最后返回的记录数出来以后才进行而且并不影响最终结果的？比如这个例子中的power，再比如简单点的+,-,*,/,复杂点的还有trunc,to_date,to_char,ceil,substr,regexp...等等