统计信息不准导致执行计划出错跑不出结果，优化后只要1分钟 - 数据库编程

N_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" 63 rows selected. Elapsed: 00:00:47.64 Execution Plan ---------------------------------------------------------- Plan hash value: 3074972763 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 1065 | 4 (50)| 00:00:01 | | 1 | SORT GROUP BY | | 1 | 1065 | 4 (50)| 00:00:01 | |* 2 | HASH JOIN RIGHT OUTER| | 1 | 1065 | 3 (34)| 00:00:01 | | 3 | INDEX FULL SCAN | IX_MO_CON_VALUE | 1 | 526 | 0 (0)| 00:00:01 | | 4 | TABLE ACCESS FULL | AAA | 1 | 539 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("A1"."RES_ID"(+)="A2"."RES_CODE") Statistics ---------------------------------------------------------- 1065 recursive calls 3 db block gets 13375 consistent gets 16369 physical reads 0 redo size 4862 bytes sent via SQL*Net to client 791 bytes received via SQL*Net from client 6 SQL*Net roundtrips to/from client 12 sorts (memory) 1 sorts (disk) 63 rows processed 第二次优化后：

SELECT/*+use_hash(A1,A2) full(A1) full(A2) parallel(A1,5) parallel(A2,5) swap_join_inputs(A1)*/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";

63 rows selected.

Elapsed: 00:00:10.21

Execution Plan
----------------------------------------------------------
Plan hash value: 4044842257

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------

统计信息不准导致执行计划出错跑不出结果，优化后只要1分钟(二)