一天查看数据库长会话，发现1个sql跑得很慢，1个多小时不出结果，花了点时间把它给优化了。

优化前：

SELECT 20131023,
       "A2"."ORG_ID",
       COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE"
                       WHEN 'DP' THEN
                        "A2"."RES_CODE"
                     END,
                     'nls_sort=''BINARY''')),
       COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE"
                       WHEN 'BOX' THEN
                        "A2"."RES_CODE"
                     END,
                     'nls_sort=''BINARY''')),
       COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE"
                       WHEN 'ONU' THEN
                        "A2"."RES_CODE"
                     END,
                     'nls_sort=''BINARY''')),
       COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE"
                       WHEN 'OBD' THEN
                        "A2"."RES_CODE"
                     END,
                     'nls_sort=''BINARY''')),
       COUNT(DISTINCT NLSSORT(CASE
                       WHEN ("A1"."CON_TYPE" = '001' AND "A2"."RES_TYPE" = 'DP') THEN
                        "A1"."RES_ID"
                     END,
                     'nls_sort=''BINARY''')),
       COUNT(DISTINCT NLSSORT(CASE
                       WHEN ("A1"."CON_TYPE" = '002' AND "A2"."RES_TYPE" = 'BOX') THEN
                        "A1"."RES_ID"
                     END,
                     'nls_sort=''BINARY''')),
       COUNT(DISTINCT NLSSORT(CASE
                       WHEN ("A1"."CON_TYPE" = '0011' AND "A2"."RES_TYPE" = 'ONU') THEN
                        "A1"."RES_ID"
                     END,
                     'nls_sort=''BINARY''')),
       COUNT(DISTINCT NLSSORT(CASE
                       WHEN ("A1"."CON_TYPE" = '0022' AND "A2"."RES_TYPE" = 'OBD') THEN
                        "A1"."RES_ID"
                     END,
                     'nls_sort=''BINARY'''))
  FROM "CRM_SZ"."AAA" "A2",
       "CRM_SZ"."BBB" "A1"
 WHERE "A1"."RES_ID"(+) = "A2"."RES_CODE"
 GROUP BY "A2"."ORG_ID"

执行计划：
Plan hash value: 2627707252
 
---------------------------------------------------------------------------------------
| Id  | Operation           | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                 |     1 |  1065 |     3  (34)| 00:00:01 |
|   1 |  SORT GROUP BY      |                 |     1 |  1065 |     3  (34)| 00:00:01 |
|   2 |   NESTED LOOPS OUTER|                 |     1 |  1065 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| AAA             |     1 |   539 |     2   (0)| 00:00:01 |
|*  4 |    INDEX FULL SCAN  | IX_MO_CON_VALUE |     1 |   526 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("A1"."RES_ID"(+)="A2"."RES_CODE")
       filter("A1"."RES_ID"(+)="A2"."RES_CODE")

第一次优化后：