MAX函数导致select子句oracle中出现性能问题

6uxekuva  于 2023-01-25  发布在  Oracle
关注(0)|答案(1)|浏览(122)

我们有一个查询由于"in select cause in case“语句而导致性能问题。如果没有该部分，完整查询将在20秒内完成，而使用此查询将花费3分钟时间。有什么方法可以提高下面选择查询的性能

(SELECT MAX (ordered_item)
FROM apps.oe_order_lines_all ool, APPS.MTL_SYSTEM_ITEMS_B b
WHERE ool.ato_line_id = oola.line_id
AND ool.inventory_item_id = b.inventory_item_id
AND ool.ship_from_org_id = b.organization_id
AND b.item_type = 'CONFIGURED_ITEM'
AND ool.FLOW_STATUS_CODE <> 'CANCELLED')

因为它是自我加入与ool，如果我删除该加入是需要时间的

> SELECT  
>        DISTINCT
>         hzp.PARTY_NAME,
>               CASE
>            WHEN b.item_type = 'ATO'
>            THEN
>                (SELECT MAX (ordered_item)
>                   FROM apps.oe_order_lines_all ool, APPS.MTL_SYSTEM_ITEMS_B b
>                  WHERE     ool.ato_line_id = oola.line_id
>                        AND ool.inventory_item_id = b.inventory_item_id
>                        AND ool.ship_from_org_id = b.organization_id
>                        AND b.item_type = 'CONFIGURED_ITEM'
>                        AND ool.FLOW_STATUS_CODE <> 'CANCELLED')
>            ELSE
>                NULL
>        END
>            ato_configured_item   FROM APPS.OE_ORDER_HEADERS_ALL    ooha,
>        APPS.OE_ORDER_LINES_ALL      oola,
>        APPS.MTL_SYSTEM_ITEMS_B      b,
>        APPS.MTL_PARAMETERS          mp,
>        APPS.OE_HOLD_DEFINITIONS     ohd,
>        APPS.OE_HOLD_SOURCES_ALL     ohsa,
>        APPS.OE_ORDER_HOLDS_ALL      ooha1,
>        APPS.FND_USER                fu,
>        APPS.HZ_CUST_SITE_USES_ALL   hzcsu,
>        APPS.HZ_CUST_ACCT_SITES_ALL  hzcas,
>        APPS.HZ_CUST_ACCOUNTS        hzca,
>        APPS.HZ_PARTIES              hzp,-----------
>        APPS.HZ_PARTY_SITES          hzps  WHERE     ooha.HEADER_ID = oola.HEADER_ID 
>        AND ooha.ORG_ID = oola.ORG_ID 
>        AND oola.INVENTORY_ITEM_ID = b.INVENTORY_ITEM_ID 
>        AND b.ORGANIZATION_ID = mp.ORGANIZATION_ID 
>        AND oola.SHIP_FROM_ORG_ID = mp.ORGANIZATION_ID 
>        AND ooha.HEADER_ID = ooha1.HEADER_ID
>        AND oola.LINE_ID = ooha1.LINE_ID
>        AND ooha1.HOLD_SOURCE_ID = ohsa.HOLD_SOURCE_ID
>        AND ohsa.HOLD_ID = ohd.HOLD_ID
>        AND b.CREATED_BY = fu.USER_ID
>        AND hzcsu.SITE_USE_ID = ooha.INVOICE_TO_ORG_ID
>        AND hzcas.CUST_ACCT_SITE_ID = hzcsu.CUST_ACCT_SITE_ID
>        AND hzca.CUST_ACCOUNT_ID = hzcas.CUST_ACCOUNT_ID
>        AND hzp.PARTY_ID = hzca.PARTY_ID
>        AND hzps.PARTY_SITE_ID = hzcas.PARTY_SITE_ID
>        AND oola.ITEM_TYPE_CODE IN ('MODEL', 'STANDARD', NULL) --Configured Items and Parts
>        AND ooha1.RELEASED_FLAG IN ('N', NULL)              --Active holds only
>        AND ooha.org_id IN ('1703')                            --1703 = Valves
>       AND oola.FLOW_STATUS_CODE NOT IN ('CLOSED', 'CANCELLED')
>        AND ooha.FLOW_STATUS_CODE NOT IN ('CLOSED', 'CANCELLED') 
>        AND oola.open_flag IN ('Y', NULL)          
>        AND mp.ORGANIZATION_CODE IN ('FIM'
>                                    )

执行计划
我们有一个查询由于select cause in case语句中的MAX（ordered_item）而导致性能问题。如果没有该部分，查询将在20秒内完成，而使用此部分，则一个查询将花费3分钟的时间。是否有任何方法可以处理此MAX以提高性能。

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                     | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                          |        |       |   197K(100)|          |
|   1 |  SORT AGGREGATE                            |                          |      1 |    62 |            |          |
|   2 |   NESTED LOOPS                             |                          |        |       |            |          |
|   3 |    NESTED LOOPS                            |                          |     42 |  2604 |   145   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS BY INDEX ROWID            | OE_ORDER_LINES_ALL       |     42 |  1722 |    19   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN                      | OE_ORDER_LINES_N17       |     45 |       |     4   (0)| 00:00:01 |
|*  6 |     INDEX UNIQUE SCAN                      | MTL_SYSTEM_ITEMS_B_U1    |      1 |       |     2   (0)| 00:00:01 |
|*  7 |    TABLE ACCESS BY INDEX ROWID             | MTL_SYSTEM_ITEMS_B       |      1 |    21 |     3   (0)| 00:00:01 |
|   8 |  SORT AGGREGATE                            |                          |      1 |   210 |            |          |
|   9 |   NESTED LOOPS                             |                          |      1 |   210 |    11   (0)| 00:00:01 |
|  10 |    NESTED LOOPS                            |                          |      1 |   162 |     8   (0)| 00:00:01 |
|  11 |     NESTED LOOPS                           |                          |      1 |    82 |     6   (0)| 00:00:01 |
|  12 |      NESTED LOOPS                          |                          |      1 |    41 |     5   (0)| 00:00:01 |
|* 13 |       INDEX RANGE SCAN                     | MTL_ITEM_CATEGORIES_U1   |      1 |    22 |     4   (0)| 00:00:01 |
|  14 |       TABLE ACCESS BY INDEX ROWID          | MTL_CATEGORY_SETS_B      |      1 |    19 |     1   (0)| 00:00:01 |
|* 15 |        INDEX UNIQUE SCAN                   | MTL_CATEGORY_SETS_B_U1   |      1 |       |     0   (0)|          |
|* 16 |      TABLE ACCESS BY INDEX ROWID           | MTL_CATEGORY_SETS_TL     |      1 |    41 |     1   (0)| 00:00:01 |
|* 17 |       INDEX UNIQUE SCAN                    | MTL_CATEGORY_SETS_TL_U1  |      1 |       |     0   (0)|          |
|* 18 |     TABLE ACCESS BY INDEX ROWID            | MTL_CATEGORIES_B         |      1 |    80 |     2   (0)| 00:00:01 |
|* 19 |      INDEX UNIQUE SCAN                     | MTL_CATEGORIES_B_U1      |      1 |       |     1   (0)| 00:00:01 |
|* 20 |    INDEX RANGE SCAN                        | FND_LOOKUP_VALUES_U1     |      1 |    48 |     3   (0)| 00:00:01 |
|  21 |  SORT AGGREGATE                            |                          |      1 |   210 |            |          |
|  22 |   NESTED LOOPS                             |                          |      1 |   210 |    11   (0)| 00:00:01 |
|  23 |    NESTED LOOPS                            |                          |      1 |   162 |     8   (0)| 00:00:01 |
|  24 |     NESTED LOOPS                           |                          |      1 |    82 |     6   (0)| 00:00:01 |
|  25 |      NESTED LOOPS                          |                          |      1 |    41 |     5   (0)| 00:00:01 |
|* 26 |       INDEX RANGE SCAN                     | MTL_ITEM_CATEGORIES_U1   |      1 |    22 |     4   (0)| 00:00:01 |
|  27 |       TABLE ACCESS BY INDEX ROWID          | MTL_CATEGORY_SETS_B      |      1 |    19 |     1   (0)| 00:00:01 |
|* 28 |        INDEX UNIQUE SCAN                   | MTL_CATEGORY_SETS_B_U1   |      1 |       |     0   (0)|          |
|* 29 |      TABLE ACCESS BY INDEX ROWID           | MTL_CATEGORY_SETS_TL     |      1 |    41 |     1   (0)| 00:00:01 |
|* 30 |       INDEX UNIQUE SCAN                    | MTL_CATEGORY_SETS_TL_U1  |      1 |       |     0   (0)|          |
|* 31 |     TABLE ACCESS BY INDEX ROWID            | MTL_CATEGORIES_B         |      1 |    80 |     2   (0)| 00:00:01 |
|* 32 |      INDEX UNIQUE SCAN                     | MTL_CATEGORIES_B_U1      |      1 |       |     1   (0)| 00:00:01 |
|* 33 |    INDEX RANGE SCAN                        | FND_LOOKUP_VALUES_U1     |      1 |    48 |     3   (0)| 00:00:01 |
|  34 |  SORT AGGREGATE                            |                          |      1 |   210 |            |          |
|  35 |   NESTED LOOPS                             |                          |      1 |   210 |    11   (0)| 00:00:01 |
|  36 |    NESTED LOOPS                            |                          |      1 |   162 |     8   (0)| 00:00:01 |
|  37 |     NESTED LOOPS                           |                          |      1 |    82 |     6   (0)| 00:00:01 |
|  38 |      NESTED LOOPS                          |                          |      1 |    41 |     5   (0)| 00:00:01 |
|* 39 |       INDEX RANGE SCAN                     | MTL_ITEM_CATEGORIES_U1   |      1 |    22 |     4   (0)| 00:00:01 |
|  40 |       TABLE ACCESS BY INDEX ROWID          | MTL_CATEGORY_SETS_B      |      1 |    19 |     1   (0)| 00:00:01 |
|* 41 |        INDEX UNIQUE SCAN                   | MTL_CATEGORY_SETS_B_U1   |      1 |       |     0   (0)|          |
|* 42 |      TABLE ACCESS BY INDEX ROWID           | MTL_CATEGORY_SETS_TL     |      1 |    41 |     1   (0)| 00:00:01 |
|* 43 |       INDEX UNIQUE SCAN                    | MTL_CATEGORY_SETS_TL_U1  |      1 |       |     0   (0)|          |
|* 44 |     TABLE ACCESS BY INDEX ROWID            | MTL_CATEGORIES_B         |      1 |    80 |     2   (0)| 00:00:01 |
|* 45 |      INDEX UNIQUE SCAN                     | MTL_CATEGORIES_B_U1      |      1 |       |     1   (0)| 00:00:01 |
|* 46 |    INDEX RANGE SCAN                        | FND_LOOKUP_VALUES_U1     |      1 |    48 |     3   (0)| 00:00:01 |
|  47 |  HASH UNIQUE                               |                          |      1 |   412 |   197K  (1)| 00:00:08 |
|  48 |   NESTED LOOPS                             |                          |        |       |            |          |
|  49 |    NESTED LOOPS                            |                          |      1 |   412 |   197K  (1)| 00:00:08 |
|  50 |     NESTED LOOPS                           |                          |      1 |   374 |   197K  (1)| 00:00:08 |
|  51 |      NESTED LOOPS                          |                          |      1 |   362 |   197K  (1)| 00:00:08 |
|  52 |       NESTED LOOPS                         |                          |      1 |   356 |   197K  (1)| 00:00:08 |
|  53 |        NESTED LOOPS                        |                          |      1 |   337 |   197K  (1)| 00:00:08 |
|  54 |         NESTED LOOPS                       |                          |      1 |   332 |   197K  (1)| 00:00:08 |
|  55 |          NESTED LOOPS                      |                          |      1 |   287 |   197K  (1)| 00:00:08 |
|  56 |           NESTED LOOPS                     |                          |      1 |   266 |   197K  (1)| 00:00:08 |
|  57 |            NESTED LOOPS                    |                          |      1 |   252 |   197K  (1)| 00:00:08 |
|  58 |             NESTED LOOPS                   |                          |    710 |   159K|   195K  (1)| 00:00:08 |
|  59 |              NESTED LOOPS                  |                          |    710 |   124K|   194K  (1)| 00:00:08 |
|  60 |               NESTED LOOPS                 |                          |  40073 |  4461K| 75450   (1)| 00:00:03 |
|* 61 |                TABLE ACCESS FULL           | MTL_PARAMETERS           |     32 |   288 |    20   (0)| 00:00:01 |
|  62 |                INLIST ITERATOR             |                          |        |       |            |          |
|* 63 |                 TABLE ACCESS BY INDEX ROWID| OE_ORDER_LINES_ALL       |   1252 |   128K|  3362   (1)| 00:00:01 |
|* 64 |                  INDEX RANGE SCAN          | OE_ORDER_LINES_X101      |  10395 |       |   161   (1)| 00:00:01 |
|  65 |               TABLE ACCESS BY INDEX ROWID  | MTL_SYSTEM_ITEMS_B       |      1 |    66 |     3   (0)| 00:00:01 |
|* 66 |                INDEX UNIQUE SCAN           | MTL_SYSTEM_ITEMS_B_U1    |      1 |       |     2   (0)| 00:00:01 |
|* 67 |              TABLE ACCESS BY INDEX ROWID   | OE_ORDER_HEADERS_ALL     |      1 |    50 |     2   (0)| 00:00:01 |
|* 68 |               INDEX UNIQUE SCAN            | OE_ORDER_HEADERS_U1      |      1 |       |     1   (0)| 00:00:01 |
|* 69 |             TABLE ACCESS BY INDEX ROWID    | OE_ORDER_HOLDS_ALL       |      1 |    22 |     6   (0)| 00:00:01 |
|* 70 |              INDEX RANGE SCAN              | OE_ORDER_HOLDS_ALL_N2    |      3 |       |     3   (0)| 00:00:01 |
|  71 |            TABLE ACCESS BY INDEX ROWID     | HZ_CUST_SITE_USES_ALL    |      1 |    14 |     3   (0)| 00:00:01 |
|* 72 |             INDEX UNIQUE SCAN              | HZ_CUST_SITE_USES_U1     |      1 |       |     2   (0)| 00:00:01 |
|  73 |           TABLE ACCESS BY INDEX ROWID      | OE_HOLD_SOURCES_ALL      |      1 |    21 |     3   (0)| 00:00:01 |
|* 74 |            INDEX UNIQUE SCAN               | OE_HOLD_SOURCES_U1       |      1 |       |     2   (0)| 00:00:01 |
|* 75 |          TABLE ACCESS BY INDEX ROWID       | OE_HOLD_DEFINITIONS      |      1 |    45 |     1   (0)| 00:00:01 |
|* 76 |           INDEX UNIQUE SCAN                | OE_HOLDS_U1              |      1 |       |     0   (0)|          |
|* 77 |         INDEX UNIQUE SCAN                  | FND_USER_U1              |      1 |     5 |     0   (0)|          |
|  78 |        TABLE ACCESS BY INDEX ROWID         | HZ_CUST_ACCT_SITES_ALL   |      1 |    19 |     2   (0)| 00:00:01 |
|* 79 |         INDEX UNIQUE SCAN                  | HZ_CUST_ACCT_SITES_U1    |      1 |       |     1   (0)| 00:00:01 |
|* 80 |       INDEX UNIQUE SCAN                    | HZ_PARTY_SITES_U1        |      1 |     6 |     1   (0)| 00:00:01 |
|* 81 |      INDEX RANGE SCAN                      | XXAR_HZ_CUST_ACCOUNTS_N2 |      1 |    12 |     2   (0)| 00:00:01 |
|* 82 |     INDEX UNIQUE SCAN                      | HZ_PARTIES_U1            |      1 |       |     1   (0)| 00:00:01 |
|  83 |    TABLE ACCESS BY INDEX ROWID             | HZ_PARTIES               |      1 |    38 |     2   (0)| 00:00:01 |
oracle

1条答案

按热度按时间
xwbd5t1u

xwbd5t1u1#

为什么不试试这样的东西：

with ord_items as (
    SELECT MAX (ordered_item) moi, ool.ato_line_id
    FROM apps.oe_order_lines_all ool, APPS.MTL_SYSTEM_ITEMS_B b
    WHERE ool.inventory_item_id = b.inventory_item_id
    AND ool.ship_from_org_id = b.organization_id
    AND b.item_type = 'CONFIGURED_ITEM'
    AND ool.FLOW_STATUS_CODE <> 'CANCELLED'
    group by ool.ato_line_id)
SELECT  
    DISTINCT
     hzp.PARTY_NAME,
           CASE
        WHEN b.item_type = 'ATO'
        THEN
            (select ois.moi from ord_items ois where ois.ato_line_id = oola.line_id)
        ELSE
            NULL
    END
        ato_configured_item   FROM  ....
赞(0）分享  回复(0）举报  2023-01-25
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com