select a.optrouteid as id,
       ....
       get_cablename(a.optcableid) as optcablename,
       get_cablemode(a.optcableid) as optcablemode,
       get_projectstatus(a.optrouteid) as projectstatus,
       get_routetype(a.routetype) as routetype
       ...
  from a,b,c,d
 where ...
 group by ...
          get_cablename(a.optcableid),
          get_cablemode(a.optcableid),
          get_projectstatus(a.optrouteid),
          get_routetype(a.routetype)      --SQL经过简化处理

开发反馈上面的SQL非常慢。看到这个SQL ：group  by 里面使用了函数，还是第一次看到这种写法。

由于上面的SQL太复杂，写了一个简单的例子来跟踪：

SQL> set autot trace
SQL> select regionid id, f_getRegionName(t.regionid), count(1)
  2    from bas_station t
  3   group by regionid ;

20 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 705412536

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |    19 |   171 |    19   (6)| 00:00:01 |
|   1 |  HASH GROUP BY     |             |    19 |   171 |    19   (6)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| BAS_STATION |  4393 | 39537 |    18   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
         30  recursive calls
          0  db block gets
         85  consistent gets
          0  physical reads
          0  redo size
        748  bytes sent via SQL*Net to client
        246  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         20  rows processed

SQL> select regionid id, f_getRegionName(t.regionid), count(1)
  2    from bas_station t
  3   group by regionid, f_getRegionName(t.regionid);  --注意这儿多了 f_getRegionName(t.regionid)

20 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 705412536

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |    19 |   171 |    19   (6)| 00:00:01 |
|   1 |  HASH GROUP BY     |             |    19 |   171 |    19   (6)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| BAS_STATION |  4393 | 39537 |    18   (0)| 00:00:01 |
----------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
       4476  recursive calls
          0  db block gets
       8683  consistent gets
          0  physical reads
          0  redo size
        786  bytes sent via SQL*Net to client
        246  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         20  rows processed

重上面显示结果：

可以看到第二个SQL的  "8683  consistent gets"比第一个SQL的"85  consistent gets"有大很多的逻辑读。

第二个的"4476  recursive calls"比第一个的"30  recursive calls"递归调用大很多。

这说明： group by regionid, f_getRegionName(t.regionid) 是在分组前就做了 f_getRegionName(t.regionid)的运算。而第一个SQL是分组完后再进行 f_getRegionName(t.regionid)，所以逻辑和递归调用都读少。

我例子中的第一个SQL的group by 是没有 f_getRegionName(t.regionid)可以正常运行的，原因是select 中有了regionid,group by 中的 f_getRegionName(t.regionid)就不需要了。

所以：上的文章开始提及的SQL可以修改为：

select a.optrouteid as id,
         a.optcableid,
          a.optcableid,
          a.optrouteid,
          a.routetype,
       get_cablename(a.optcableid) as optcablename,
       get_cablemode(a.optcableid) as optcablemode,
       get_projectstatus(a.optrouteid) as projectstatus,
       get_routetype(a.routetype) as routetype
       ...
  from a,b,c,d
 where ...
 group by ...
          a.optcableid,
          a.optcableid,
          a.optrouteid,
          a.routetype