sql - 查询语句在 oracle 上执行时间很长-6ren

sql - 查询语句在 oracle 上执行时间很长

转载作者：行者123 更新时间：2023-12-04 20:09:36

我的任务是改进我公司的现有代码/查询，

数据库版本

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
"CORE   10.2.0.4.0  Production"
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production

这就是问题所在——当执行下面的代码时，完成这项工作所需的时间超过 4 个小时，大约 7 到 8 个小时。

enter image description here

3小时37分钟内395行数据

  SELECT DISTINCT GROUP_DIST_NUMBER, BEGIN_DATE, PRICE_DROP_DATE
    FROM (SELECT DISTINCT
                 G.GROUP_DIST_NUMBER,
                 TO_DATE (:B2, 'DD-MON-YYYY') BEGIN_DATE,
                 TO_DATE (:B2, 'DD-MON-YYYY') PRICE_DROP_DATE
            FROM POS_DISTI_GROUP G,
                 POS_CUST_XREF M,
                 S_CPT_SEQ_NO C,
                 PP_STD_PRICE P,
                 S_CPT_AUDIT A,
                 RPT_PRODUCT_VALUE_LEVEL L
           WHERE     G.END_DATE > TO_DATE (:B2, 'DD-MON-YYYY')
                 AND G.GROUP_DIST_NUMBER = M.DIST_NUMBER
                 AND M.SG_BILL_TO_CUST_NO = A.BILL_TO_CUST_NO
                 AND A.START_DATE <= TO_DATE (:B2, 'DD-MON-YYYY')
                 AND A.END_DATE >= TO_DATE (:B2, 'DD-MON-YYYY')
                 AND L.PROD_VALUE = P.PROD_VALUE
                 AND L.PROD_LEVEL = P.PROD_LEVEL
                 AND C.CPT_PRICE_CODE IN
                        (SELECT /*+ PRECOMPUTE_SUBQUERY */
                                DISTINCT C1.CPT_PRICE_CODE
                           FROM PP_STD_PRICE P1,
                                S_CPT_PRICE_CODE C1,
                                S_CPT_SEQ_NO S1
                          WHERE     P1.STDP_ID = :B1
                                AND C1.CPT_PRICE_CAT LIKE 'NB%'
                                AND C1.CPT_PRICE_CODE = S1.CPT_PRICE_CODE
                                AND S1.PRICE_PROTECTABLE = 'Y')
                 AND C.CPT_PRICE_CODE = P.CUST_PRICE_TYPE
                 AND P.STDP_ID = :B1
                 AND A.CUST_PRICE_TYPE = C.CPT_BILL_CODE
                 AND M.ACTIVE_IND != 'N'
                 AND (M.CATEGORY_TYPE LIKE 'DIRECT%' OR M.INDIRECT_DISTI = 'Y')
                 AND TRUNC (M.ARCHIVE_DATE) > TRUNC (SYSDATE)
          UNION
            SELECT G.GROUP_DIST_NUMBER,
                   P.BEGIN_DATE,
                   MIN (INVT.PRICE_DROP_DATE) PRICE_DROP_DATE
              FROM POS_DISTI_GROUP G,
                   POS_CUST_XREF M,
                   PP_DEBIT_AUTHORIZATION P,
                   RPT_PRODUCT_VALUE_LEVEL L,
                   POS_PP_INVENTORY INVT
             WHERE     G.END_DATE > TO_DATE (:B2, 'DD-MON-YYYY')
                   AND G.GROUP_DIST_NUMBER = M.DIST_NUMBER
                   AND M.ACTIVE_IND != 'N'
                   AND (M.CATEGORY_TYPE LIKE 'DIRECT%' OR M.INDIRECT_DISTI = 'Y')
                   AND G.DIST_NUMBER = P.DIST_NUMBER
                   AND L.PROD_VALUE = P.PROD_VALUE
                   AND L.PROD_LEVEL = P.PROD_LEVEL
                   AND P.BEGIN_DATE >= TO_DATE (:B2, 'DD-MON-YYYY') - 6
                   AND P.BEGIN_DATE <= TO_DATE (:B2, 'DD-MON-YYYY')
                   AND INVT.DIST_NUMBER = G.GROUP_DIST_NUMBER
                   AND INVT.STMODEL = L.MOD_DESC
                   AND INVT.PPCF_SHOW_DATE = P.BEGIN_DATE
                   AND P.PRICE_TYPE = 'I'
                   AND (   P.POS_PROCESSED_FLAG IS NULL
                        OR P.POS_PROCESSED_FLAG != 'C')
                   AND P.POS_PP_FLAG = 'Y'
                   AND TRUNC (M.ARCHIVE_DATE) > TRUNC (SYSDATE)
          GROUP BY G.GROUP_DIST_NUMBER, P.BEGIN_DATE)
ORDER BY GROUP_DIST_NUMBER;

我不知道如何调整此查询语句以提高性能并使其执行得更快

这里是解释计划

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                             |   101 |  2525 |       | 24156  (10)|       |       |
|   1 |  SORT ORDER BY                            |                             |   101 |  2525 |       | 24156  (10)|       |       |
|   2 |   VIEW                                    |                             |   101 |  2525 |       | 24155  (10)|       |       |
|   3 |    SORT UNIQUE                            |                             |   101 | 17691 |       | 24155  (75)|       |       |
|   4 |     UNION-ALL                             |                             |       |       |       |            |       |       |
|*  5 |      HASH JOIN                            |                             |    10M|  1680M|       |  6446   (5)|       |       |
|*  6 |       TABLE ACCESS FULL                   | S_CPT_SEQ_NO                |   651 |  5208 |       |     5   (0)|       |       |
|*  7 |       HASH JOIN                           |                             |  2383K|   379M|       |  6318   (3)|       |       |
|*  8 |        TABLE ACCESS FULL                  | POS_DISTI_GROUP             |   100 |  1800 |       |     5   (0)|       |       |
|*  9 |        HASH JOIN                          |                             |  2396K|   340M|  4320K|  6283   (3)|       |       |
|  10 |         VIEW                              | RPT_PRODUCT_VALUE_LEVEL     |   138K|  2697K|       |  1905   (3)|       |       |
|  11 |          UNION-ALL                        |                             |       |       |       |            |       |       |
|* 12 |           HASH JOIN RIGHT OUTER           |                             | 13965 |   627K|       |    91   (5)|       |       |
|  13 |            INDEX FULL SCAN                | PK_SEAEGO_PRODUCT_HIERARCHY |   298 |  4172 |       |     1   (0)|       |       |
|* 14 |            HASH JOIN RIGHT OUTER          |                             | 13965 |   436K|       |    89   (4)|       |       |
|  15 |             INDEX FULL SCAN               | PK_S_CAP_GROUP              |     2 |     8 |       |     1   (0)|       |       |
|  16 |             TABLE ACCESS FULL             | SMA_STMODEL                 | 13965 |   381K|       |    87   (3)|       |       |
|* 17 |           HASH JOIN RIGHT OUTER           |                             | 14175 |  1065K|       |   158   (5)|       |       |
|  18 |            INDEX FAST FULL SCAN           | PK_S_FAMILY                 |  1366 |  5464 |       |     2   (0)|       |       |
|* 19 |            HASH JOIN RIGHT OUTER          |                             | 14175 |  1010K|       |   156   (5)|       |       |
|  20 |             INDEX FULL SCAN               | PK_F_MODPRODMGR             |    22 |    88 |       |     1   (0)|       |       |
|* 21 |             HASH JOIN                     |                             | 14175 |   955K|       |   154   (4)|       |       |
|  22 |              TABLE ACCESS FULL            | SMA_PRODUCTMODEL            | 14132 |   317K|       |    62   (2)|       |       |
|* 23 |              HASH JOIN RIGHT OUTER        |                             | 13965 |   627K|       |    91   (5)|       |       |
|  24 |               INDEX FULL SCAN             | PK_SEAEGO_PRODUCT_HIERARCHY |   298 |  4172 |       |     1   (0)|       |       |
|* 25 |               HASH JOIN RIGHT OUTER       |                             | 13965 |   436K|       |    89   (4)|       |       |
|  26 |                INDEX FULL SCAN            | PK_S_CAP_GROUP              |     2 |     8 |       |     1   (0)|       |       |
|  27 |                TABLE ACCESS FULL          | SMA_STMODEL                 | 13965 |   381K|       |    87   (3)|       |       |
|  28 |           MAT_VIEW ACCESS FULL            | RPT_PROD_MV                 |   109K|  1288K|       |  1656   (3)|       |       |
|* 29 |         HASH JOIN                         |                             |   141K|    17M|       |  3191   (3)|       |       |
|* 30 |          INDEX RANGE SCAN                 | UK_PP_STD_PRICE_STDP_ID     |  4128 |   108K|       |    23   (0)|       |       |
|* 31 |          HASH JOIN                        |                             |  5341 |   532K|       |  3165   (3)|       |       |
|* 32 |           TABLE ACCESS FULL               | POS_CUST_XREF               |    54 |  2268 |       |    25   (4)|       |       |
|* 33 |           HASH JOIN                       |                             |   193K|    11M|       |  3137   (3)|       |       |
|* 34 |            TABLE ACCESS FULL              | S_CPT_AUDIT                 |    68 |  2108 |       |    76   (4)|       |       |
|* 35 |            HASH JOIN                      |                             |   745K|    20M|       |  3052   (2)|       |       |
|  36 |             TABLE ACCESS FULL             | S_CPT_SEQ_NO                |  1301 | 16913 |       |     5   (0)|       |       |
|  37 |             MERGE JOIN CARTESIAN          |                             | 88205 |  1378K|       |  3037   (2)|       |       |
|* 38 |              INDEX RANGE SCAN             | UK_PP_STD_PRICE_STDP_ID     |  4128 | 20640 |       |    23   (0)|       |       |
|  39 |              BUFFER SORT                  |                             |    21 |   231 |       |  3014   (2)|       |       |
|* 40 |               TABLE ACCESS FULL           | S_CPT_PRICE_CODE            |    21 |   231 |       |     1   (0)|       |       |
|  41 |      HASH GROUP BY                        |                             |     1 |   191 |       | 16421   (5)|       |       |
|* 42 |       FILTER                              |                             |       |       |       |            |       |       |
|  43 |        NESTED LOOPS                       |                             |     1 |   191 |       | 16419   (5)|       |       |
|* 44 |         HASH JOIN                         |                             |     7 |  1176 |       | 16370   (5)|       |       |
|* 45 |          HASH JOIN                        |                             |    74 |  8584 |       |  4790   (3)|       |       |
|* 46 |           HASH JOIN                       |                             |    60 |  3780 |       |    31   (7)|       |       |
|* 47 |            TABLE ACCESS FULL              | POS_CUST_XREF               |    60 |  2100 |       |    25   (4)|       |       |
|* 48 |            TABLE ACCESS FULL              | POS_DISTI_GROUP             |   100 |  2800 |       |     5   (0)|       |       |
|* 49 |           TABLE ACCESS FULL               | PP_DEBIT_AUTHORIZATION      |   345 | 18285 |       |  4759   (3)|       |       |
|  50 |          PARTITION RANGE ALL              |                             | 18192 |   923K|       | 11579   (6)|     1 |    33 |
|* 51 |           INDEX FAST FULL SCAN            | POS_PP_INVENTORY_PK         | 18192 |   923K|       | 11579   (6)|     1 |    33 |
|* 52 |         VIEW                              | RPT_PRODUCT_VALUE_LEVEL     |     1 |    23 |       |     7   (0)|       |       |
|  53 |          UNION ALL PUSHED PREDICATE       |                             |       |       |       |            |       |       |
|* 54 |           FILTER                          |                             |       |       |       |            |       |       |
|  55 |            NESTED LOOPS OUTER             |                             |     1 |    46 |       |     2   (0)|       |       |
|  56 |             NESTED LOOPS OUTER            |                             |     1 |    42 |       |     2   (0)|       |       |
|  57 |              TABLE ACCESS BY INDEX ROWID  | SMA_STMODEL                 |     1 |    28 |       |     2   (0)|       |       |
|* 58 |               INDEX UNIQUE SCAN           | PK_SMA_STMODEL              |     1 |       |       |     1   (0)|       |       |
|* 59 |              INDEX UNIQUE SCAN            | PK_SEAEGO_PRODUCT_HIERARCHY |   298 |  4172 |       |     0   (0)|       |       |
|* 60 |             INDEX UNIQUE SCAN             | PK_S_CAP_GROUP              |     2 |     8 |       |     0   (0)|       |       |
|  61 |           NESTED LOOPS OUTER              |                             |     1 |    77 |       |     3   (0)|       |       |
|  62 |            NESTED LOOPS OUTER             |                             |     1 |    73 |       |     3   (0)|       |       |
|  63 |             NESTED LOOPS OUTER            |                             |     1 |    69 |       |     3   (0)|       |       |
|  64 |              NESTED LOOPS OUTER           |                             |     1 |    65 |       |     3   (0)|       |       |
|  65 |               NESTED LOOPS                |                             |     1 |    51 |       |     3   (0)|       |       |
|* 66 |                TABLE ACCESS BY INDEX ROWID| SMA_PRODUCTMODEL            |     1 |    23 |       |     2   (0)|       |       |
|* 67 |                 INDEX UNIQUE SCAN         | PK_SMA_PRODUCTMODEL         |     1 |       |       |     1   (0)|       |       |
|  68 |                TABLE ACCESS BY INDEX ROWID| SMA_STMODEL                 |     1 |    28 |       |     1   (0)|       |       |
|* 69 |                 INDEX UNIQUE SCAN         | PK_SMA_STMODEL              |     1 |       |       |     0   (0)|       |       |
|* 70 |               INDEX UNIQUE SCAN           | PK_SEAEGO_PRODUCT_HIERARCHY |   298 |  4172 |       |     0   (0)|       |       |
|* 71 |              INDEX UNIQUE SCAN            | PK_S_FAMILY                 |  1366 |  5464 |       |     0   (0)|       |       |
|* 72 |             INDEX UNIQUE SCAN             | PK_S_CAP_GROUP              |     2 |     8 |       |     0   (0)|       |       |
|* 73 |            INDEX UNIQUE SCAN              | PK_F_MODPRODMGR             |    22 |    88 |       |     0   (0)|       |       |
|* 74 |           MAT_VIEW ACCESS BY INDEX ROWID  | RPT_PROD_MV                 |     1 |    24 |       |     2   (0)|       |       |
|* 75 |            INDEX UNIQUE SCAN              | IDX_RPT_PROD_MV_PROD_NO     |     1 |       |       |     1   (0)|       |       |
--------------------------------------------------------------------------------------------------------------------------------------

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

   5 - access("C1"."CPT_PRICE_CODE"="S1"."CPT_PRICE_CODE")
   6 - filter("S1"."PRICE_PROTECTABLE"='Y')
   7 - access("G"."GROUP_DIST_NUMBER"="M"."DIST_NUMBER")
   8 - filter("G"."END_DATE">TO_DATE(:B2,'DD-MON-YYYY'))
   9 - access("L"."PROD_VALUE"="P"."PROD_VALUE" AND "L"."PROD_LEVEL"="P"."PROD_LEVEL")
  12 - access("ST"."MARKETING_NAME"="PH"."MARKETING_NAME"(+))
  14 - access("ST"."MOD_CAPACITY_FORMATTED"="SCG"."MOD_CAPACITY_FORMATTED"(+))
  17 - access("SF"."FAMILY"(+)=SUBSTRB("PM"."MODEL",1,3))
  19 - access("PM"."DESIGN_APPLICATION"="DA"."DESIGN_APPLICATION"(+))
  21 - access("PM"."MOD_DESC"="ST"."MOD_DESC")
  23 - access("ST"."MARKETING_NAME"="PH"."MARKETING_NAME"(+))
  25 - access("ST"."MOD_CAPACITY_FORMATTED"="SCG"."MOD_CAPACITY_FORMATTED"(+))
  29 - access("C"."CPT_PRICE_CODE"="P"."CUST_PRICE_TYPE")
  30 - access("P"."STDP_ID"=TO_NUMBER(:B1))
  31 - access("M"."SG_BILL_TO_CUST_NO"="A"."BILL_TO_CUST_NO")
  32 - filter("M"."SG_BILL_TO_CUST_NO" IS NOT NULL AND ("M"."INDIRECT_DISTI"='Y' OR "M"."CATEGORY_TYPE" LIKE 'DIRECT%') AND 
              "M"."ACTIVE_IND"<>'N' AND TRUNC(INTERNAL_FUNCTION("M"."ARCHIVE_DATE"))>TRUNC(SYSDATE@!))
  33 - access("A"."CUST_PRICE_TYPE"="C"."CPT_BILL_CODE")
  34 - filter("A"."START_DATE"<=TO_DATE(:B2,'DD-MON-YYYY') AND "A"."END_DATE">=TO_DATE(:B2,'DD-MON-YYYY'))
  35 - access("C"."CPT_PRICE_CODE"="C1"."CPT_PRICE_CODE")
  38 - access("P1"."STDP_ID"=TO_NUMBER(:B1))
  40 - filter("C1"."CPT_PRICE_CAT" LIKE 'NB%')
  42 - filter(TO_DATE(:B2,'DD-MON-YYYY')-6<=TO_DATE(:B2,'DD-MON-YYYY'))
  44 - access("INVT"."DIST_NUMBER"="G"."GROUP_DIST_NUMBER" AND "INVT"."PPCF_SHOW_DATE"="P"."BEGIN_DATE")
  45 - access("G"."DIST_NUMBER"="P"."DIST_NUMBER")
  46 - access("G"."GROUP_DIST_NUMBER"="M"."DIST_NUMBER")
  47 - filter(("M"."INDIRECT_DISTI"='Y' OR "M"."CATEGORY_TYPE" LIKE 'DIRECT%') AND "M"."ACTIVE_IND"<>'N' AND 
              TRUNC(INTERNAL_FUNCTION("M"."ARCHIVE_DATE"))>TRUNC(SYSDATE@!))
  48 - filter("G"."END_DATE">TO_DATE(:B2,'DD-MON-YYYY'))
  49 - filter("P"."PRICE_TYPE"='I' AND "P"."POS_PP_FLAG"='Y' AND ("P"."POS_PROCESSED_FLAG"<>'C' OR "P"."POS_PROCESSED_FLAG" 
              IS NULL) AND "P"."BEGIN_DATE"<=TO_DATE(:B2,'DD-MON-YYYY') AND "P"."BEGIN_DATE">=TO_DATE(:B2,'DD-MON-YYYY')-6)
  51 - filter("INVT"."PPCF_SHOW_DATE"<=TO_DATE(:B2,'DD-MON-YYYY') AND "INVT"."PPCF_SHOW_DATE">=TO_DATE(:B2,'DD-MON-YYYY')-6)
  52 - filter("L"."PROD_LEVEL"="P"."PROD_LEVEL")
  54 - filter("P"."PROD_VALUE"="INVT"."STMODEL")
  58 - access("ST"."MOD_DESC"="P"."PROD_VALUE")
  59 - access("ST"."MARKETING_NAME"="PH"."MARKETING_NAME"(+))
  60 - access("ST"."MOD_CAPACITY_FORMATTED"="SCG"."MOD_CAPACITY_FORMATTED"(+))
  66 - filter("PM"."MOD_DESC"="INVT"."STMODEL")
  67 - access("PM"."MODEL"="P"."PROD_VALUE")
  69 - access("ST"."MOD_DESC"="INVT"."STMODEL")
  70 - access("ST"."MARKETING_NAME"="PH"."MARKETING_NAME"(+))
  71 - access("SF"."FAMILY"(+)=SUBSTRB("PM"."MODEL",1,3))
  72 - access("ST"."MOD_CAPACITY_FORMATTED"="SCG"."MOD_CAPACITY_FORMATTED"(+))
  73 - access("PM"."DESIGN_APPLICATION"="DA"."DESIGN_APPLICATION"(+))
  74 - filter("MOD_DESC"="INVT"."STMODEL")
  75 - access("PROD_NO"="P"."PROD_VALUE")

Note
-----
   - 'PLAN_TABLE' is old version

和表的行数统计

    TABLE_Name              NUM_ROWS
    -----------             ---------
    POS_DISTI_GROUP          2009 
    POS_CUST_XREF            2801
    S_CPT_SEQ_NO             1301
    PP_STD_PRICE             2658450
    S_CPT_AUDIT            27200
    PP_DEBIT_AUTHORIZATION   1199420
    POS_PP_INVENTORY     7276850
    PP_STD_PRICE             2658450
    S_CPT_PRICE_CODE     192
    S_CPT_SEQ_NO             1301
    SMA_STMODEL          13965
    RPT_PROD_MV          109980

创建表语句。 CLICK HERE

表说明。 CLICK HERE

按照@jonearles 的建议重新运行gather_plan_statistics 来检索EXPLAIN PLAN。 CLICK HERE

*来自谷歌文档的链接

最佳答案

问题

执行计划中的聚合发生得太晚了。计划 ID 4 和 5 生成 130 亿行，占执行时间的 95%。 Oracle 错误地认为行数会更小，并且应该将较早的聚合合并在一起。

计划 ID 6 到 40 代表内联 View 的前半部分，在 UNION 之前.查询的那部分有两个 DISTINCT s，但执行计划的那部分没有任何类型的聚合操作。 Oracle 错误地认为最好先加入所有内容并执行一个 SORT UNIQUE , 而不是执行多个 SORT UNIQUE或 HASH GROUP BY并结合结果。

问题复现

在没有完全导出的情况下完全重现这个问题几乎是不可能的。尽管它只是一个中等复杂的 SQL 语句，但它涉及到数千个变量。下面的代码仅演示了 Oracle 如何错误地合并聚合操作。

首先，创建两个简单的表。每个都有 100K 行。 TEST1 有从 1 到 100000 的数字。TEST2 包含 100000 行，但只有一个不同的数字。为了人为地制定一个糟糕的计划，在 TEST2 上收集统计数据过早。优化器认为 TEST2 只有一行，但实际上有 100000。

drop table test1 purge;
drop table test2 purge;

create table test1(a number);
create table test2(a number);

insert into test1 select level from dual connect by level <= 100000;
insert into test2 values (1);
commit;

begin
    dbms_stats.gather_table_stats(user, 'test1');
    dbms_stats.gather_table_stats(user, 'test2');
end;
/

insert into test2 select 1 from dual connect by level <= 100000;
commit;

下面的示例查询检索所有不同的 TEST1.A，其中 A 也在不同的 TEST2.A 中。

默认情况下，使用人为错误的统计信息，Oracle 先连接表，然后执行 HASH GROUP BY和 HASH UNIQUE .这是一个糟糕的计划，它加入了所有人
来自 TEST2 的 100K 值。最好执行 HASH GROUP BY首先，然后只加入该表中的 1 行。

explain plan for
select distinct a from test1 where a in (select a from test2 group by a);

select * from table(dbms_xplan.display(format => 'outline'));

------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |     1 |     8 |    79   (2)| 00:00:01 |
|   1 |  HASH UNIQUE         |       |     1 |     8 |    79   (2)| 00:00:01 |
|   2 |   HASH GROUP BY      |       |     1 |     8 |    79   (2)| 00:00:01 |
|*  3 |    HASH JOIN         |       |     1 |     8 |    79   (2)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| TEST2 |     1 |     3 |     3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| TEST1 |   100K|   488K|    76   (2)| 00:00:01 |
------------------------------------------------------------------------------

潜在解决方案 #1:提示

不幸的是，没有官方提示可以控制何时何地进行排序和分组。通过玩弄 outline格式选项我找到了一些可能有用的提示: USE_HASH_AGGREGATION , OUTLINE_LEAF , 和 PLACE_DISTINCT . (这些提示真的很棘手 - 我在示例中使用 group by 而不是另一个 distinct 的原因是因为我在 PLACE_DISTINCT 提示上遇到了很多麻烦!)

使用这些未记录的提示可以制定更好的计划。 TEST2 的结果通过 HASH GROUP BY马上，正如他们应该的那样。这类似于如果统计准确则将生成的计划。

explain plan for
select /*+ USE_HASH_AGGREGATION(@"SEL$5DA710D3") OUTLINE_LEAF(@"SEL$683B0107") */
distinct a from test1 where a in (select a from test2 group by a);

select * from table(dbms_xplan.display(format => 'outline alias'));

----------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     1 |     8 |    79   (2)| 00:00:01 |
|   1 |  HASH UNIQUE          |          |     1 |     8 |    79   (2)| 00:00:01 |
|*  2 |   HASH JOIN SEMI      |          |     1 |     8 |    79   (2)| 00:00:01 |
|   3 |    VIEW               | VW_NSO_1 |     1 |     3 |     3   (0)| 00:00:01 |
|   4 |     HASH GROUP BY     |          |     1 |     3 |     3   (0)| 00:00:01 |
|   5 |      TABLE ACCESS FULL| TEST2    |     1 |     3 |     3   (0)| 00:00:01 |
|   6 |    TABLE ACCESS FULL  | TEST1    |   100K|   488K|    76   (2)| 00:00:01 |
----------------------------------------------------------------------------------

可能的解决方案 #2:强制执行 ROWNUM 计划。

一个更简单、更安全的版本是使用 ROWNUM . ROWNUM是一个伪列，表示返回的行的顺序。当有 ROWNUM Oracle 无法移动 distinct和 group by因为它会影响那个顺序。

不幸的是，这个技巧需要额外的代码并在计划中生成额外的步骤。这些额外的步骤大多只是传递数据，不应该减慢完成的速度。

explain plan for
select distinct a from test1 where a in
(
    --Extra level only because we only want to project one column.
    --It's syntactically required, but the optimizer throws out this inline view.
    select a
    from
    (
        --The ROWNUM forces everything in this inline view to happen separately.
        select a, rownum
        from
        (
            select a from test2 group by a
        )
    )
);

select * from table(dbms_xplan.display(format => 'outline alias'));

---------------------------------------------------------------------------------
| Id  | Operation               | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |       |     1 |     8 |    79   (2)| 00:00:01 |
|   1 |  HASH UNIQUE            |       |     1 |     8 |    79   (2)| 00:00:01 |
|*  2 |   HASH JOIN SEMI        |       |     1 |     8 |    79   (2)| 00:00:01 |
|   3 |    VIEW                 |       |     1 |     3 |     3   (0)| 00:00:01 |
|   4 |     COUNT               |       |       |       |            |          |
|   5 |      VIEW               |       |     1 |     3 |     3   (0)| 00:00:01 |
|   6 |       HASH GROUP BY     |       |     1 |     3 |     3   (0)| 00:00:01 |
|   7 |        TABLE ACCESS FULL| TEST2 |     1 |     3 |     3   (0)| 00:00:01 |
|   8 |    TABLE ACCESS FULL    | TEST1 |   100K|   488K|    76   (2)| 00:00:01 |
---------------------------------------------------------------------------------

潜在解决方案#3:修正基数估计并希望最好。

如果估计的行数准确，则计划几乎总是好的。当行估计值相差很远时，找到基数错误的执行计划的第一部分。对于此计划，它似乎是计划 ID 36。 E-Rows 和 A-Rows 相差一个数量级:

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem |  O/1/M   |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
...
|* 36 |             TABLE ACCESS FULL             | POS_CUST_XREF               |      1 |     54 |    579 |00:00:00.01 |     131 |      0 |       |       |          |

步骤 36 有一个涉及 SYSDATE 的复杂谓词。

  36 - filter(("M"."SG_BILL_TO_CUST_NO" IS NOT NULL AND ("M"."INDIRECT_DISTI"='Y' OR "M"."CATEGORY_TYPE" LIKE 'DIRECT%') AND "M"."ACTIVE_IND"<>'N' AND
              TRUNC(INTERNAL_FUNCTION("M"."ARCHIVE_DATE"))>TRUNC(SYSDATE@!)))

即使有最新的统计数据，这种情况也很难预测。动态采样可能会有所帮助。尝试使用这样的顶级提示重新运行查询:

SELECT /*+ dynamic_sampling(6) */ ...

解决这些早期的差异通常会解决计划后期的其他问题。此示例只是基数不匹配的一种可能来源。可能需要其他技巧来改进其他基数估计。这可能是一种非常困难的方法，但它可以通过多种方式获得返回。

红鲱鱼

任何中等复杂的 SQL 语句都有许多潜在的改进。评论和答案中有几个好主意。但是在调整时，始终必须关注最慢的地方，而不是最容易修复的地方。这听起来很明显，但这是一个非常容易落入的陷阱。这就是为什么我要你使用 /*+ gather_plan_statistics*/ ，这就是为什么我的回答只关注计划中实际时间较长的部分。

例如，在我之前的评论中，我建议查看 NESTED LOOPS其中 ROWS = 1。现在我们有了实际时间，我们知道建议没有帮助。 (尽管一般来说，您仍然应该对具有大表但 ROWS=1 的计划持怀疑态度。)

关于sql - 查询语句在 oracle 上执行时间很长，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/19922915/

文章推荐： knockout.js - ko.validation.configure 与 ko.validation.init 的区别

文章推荐： png - 在Windows程序中使用.png文件而不是使用.ico文件

文章推荐： api - 为分页 API 制作 Scala 迭代器

Mysql 查询 JOIN 查询
我有三张 table 。表 A 有选项名称(即颜色、尺寸)。表 B 有选项值名称(即蓝色、红色、黑色等)。表C通过将选项名称id和选项名称值id放在一起来建立关系。我的查询需要显示值和选项的名称，而
查询
在mysql中，如何计算一行中的非空单元格？我只想计算某些列之间的单元格，比如第 3-10 列之间的单元格。不是所有的列...同样，仅在该行中。最佳答案如果你想这样做，只能在 sql 中使用名称而
sql - 查询、 native 查询、命名查询和类型化查询之间的区别
关闭。这个问题需要多问focused 。目前不接受答案。想要改进此问题吗？更新问题，使其仅关注一个问题 editing this post . 已关闭 7 年前。 Improve this ques
elasticsearch - 在Elasticsearch查询中没有为[查询]注册的[查询]
我正在为版本7.6进行Elasticsearch查询我的查询是这样的: { "query": { "bool": { "should": [ {
sql - 查询、 native 查询、命名查询和类型化查询之间的区别
关闭。这个问题需要多问focused 。目前不接受答案。想要改进此问题吗？更新问题，使其仅关注一个问题 editing this post . 已关闭 7 年前。 Improve this ques
php - Mysql WHERE NOT EXISTS(查询)OR(查询)
是否可以编写一个查询来检查任一子查询(而不是一个子查询)是否正确？ SELECT * FROM employees e WHERE NOT EXISTS (
javascript - 查询。为表中的每一行发送 ajax 查询
我找到了很多关于我的问题的答案，但问题没有解决我有表格，有数据，例如: Data 1 Data 2 Data 3
salesforce - SOQL 查询 - 如何通过将字段设为小写并进行比较来编写 SOQL 查询？
以下查询返回错误: 查询: SELECT Id, FirstName, LastName, OwnerId, PersonEmail FROM Account WHERE lower(PersonEm
salesforce - SOQL 查询 - 如何通过将字段设为小写并进行比较来编写 SOQL 查询？
以下查询返回错误: 查询: SELECT Id, FirstName, LastName, OwnerId, PersonEmail FROM Account WHERE lower(PersonEm
Android SQLite 查询(我想解析一般的 SQL 查询)
我从 EditText 中获取了 String 值。以及提交查询的按钮。 String sql=editQuery.getText().toString();// SELECT * FROM empl
mysql 查询 - 为一个巨大的表优化现有的 MAX-MIN 查询
我有一个或多或少有效的查询(关于结果)，但处理大约需要 45 秒。这对于在 GUI 中呈现数据来说肯定太长了。所以我的需求是找到一个更快/更高效的查询(几毫秒左右会很好)我的数据表大约有 3000
SQL 查询 - 将 NULL 结果添加到 SELECT 查询
这是我第一次使用 Stack Overflow，所以我希望我以正确的方式提出这个问题。我有 2 个 SQL 查询，我正在尝试比较和识别缺失值，尽管我无法将 NULL 字段添加到第二个查询中以识别缺失
sql - 什么是动态 SQL 查询？何时需要使用动态 SQL 查询？
什么是动态 SQL 查询？何时需要使用动态 SQL 查询？我使用的是 SQL Server 2005。最佳答案这里有几篇文章: Introduction to Dynamic SQL Dynami
php - 在另一个 mysql 查询 while 循环中调用 mysql 查询
include "mysql.php"; $query= "SELECT ID,name,displayname,established,summary,searchlink,im
java - MySQL 查询 "select top 5"查询
我有一个查询要“转换”为 mysql。这是查询: select top 5 * from (select id, firstName, lastName, sum(fileSize) as To
c# - Entity Framework 查询 ToString 不会产生 SQL 查询
通过我的研究，我发现至少从 EF 4.1 开始，EF 查询上的 .ToString() 方法将返回要运行的 SQL。事实上，这对我来说非常有用，使用 Entity Framework 5 和 6。但
MySQL 查询(或 Doctrine 1.2 查询)- 从连接表和过滤器中获取最新项目
我在构造查询来执行以下操作时遇到问题: 按activity_type_id过滤联系人，仅显示最近事件具有所需activity_type_id或为NULL(无事件)的联系人表格结构如下: 一个联系人可
php - 如何在执行另一个 SQL 查询 x 分钟后执行一个 SQL 查询？
如何让我输入数据库的信息在输入数据 5 分钟后自行更新？假设我有一张 table : +--+--+-----+ |id|ip|count| +--+--+-----+ |
database - 如何在 N1QL 查询(Couchbase 查询)中使用 LENGTH() 字符串函数
我正在尝试搜索正好是 4 位数字的 ID，我知道我需要使用 LENGTH() 字符串函数，但找不到如何使用它的示例。我正在尝试以下(和其他变体)但它们不起作用。 SELECT max(car_id)
php - 将 SQL 查询 (+JOIN) 转换为 Symfony Propel 查询
我有一个在 mysql 上运行良好的 sql 查询(查询 + 连接): select sum(pa.price) from user u , purchase pu , pack pa where (

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

sql - 查询语句在 oracle 上执行时间很长