需要优化Oracle SQL查询

unftdfkk 于 2023-11-17 发布在 Oracle

关注(0)|答案(2)|浏览(125)

我需要优化这个Oracle SQL查询：

select /*+ INDEX(ex1 EXCH_RATE_BK_IDX) */
            ex1.currency_id as currency_id,
            ex1.underly_currency_id as underly_currency_id,
            ex1.type_id as type_id,
            ex1.third_id as third_id,
            ex1.market_third_id as market_third_id,
            ex1.exch_d as exch_d,
            ex1.daily_dflt_f as daily_dflt_f,
            ex1.exch_rate as exch_rate,
            ex1.external_seq_no as external_seq_no,
            ex1.creation_d as creation_d,
            ex1.creation_user_id as creation_user_id,
            ex1.last_modif_d as last_modif_d,
            ex1.last_user_id as last_user_id
            /* security_level_e = 0 */
            from exch_rate ex1
               inner join ( select max(exch_d) as max_exch_d, type_id, third_id, market_third_id
                from exch_rate
                where currency_id = '1004'
                and underly_currency_id = '2'
                and exch_d >= TO_DATE('24-12-2009 00:00:00', 'DD-MM-YYYY HH24:MI:SS') and exch_d <= TO_DATE('14-10-2012 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
                group by type_id, third_id, market_third_id
                ) ex3
           on  ex1.exch_d = ex3.max_exch_d 
            and (ex1.type_id = ex3.type_id or (ex1.type_id is NULL and ex3.type_id is NULL)) 
            and (ex1.third_id = ex3.third_id or (ex1.third_id is  NULL and ex3.third_id is NULL)) 
            and (ex1.market_third_id = ex3.market_third_id or (ex1.market_third_id is NULL and ex3.market_third_id is NULL))
           where (ex1.currency_id = '1004') and ( ex1.underly_currency_id = '2') 
           and ((ex1.exch_d >= TO_DATE('24-12-2009 00:00:00', 'DD-MM-YYYY HH24:MI:SS') and (ex1.exch_d <= TO_DATE('14-10-2012 00:00:00', 'DD-MM-YYYY HH24:MI:SS'))))
            order by exch_d desc nulls last
;

字符串
表创建脚本和索引如下：

CREATE TABLE "EXCH_RATE" 
   (    "CURRENCY_ID" NUMBER(14,0) NOT NULL ENABLE, 
    "UNDERLY_CURRENCY_ID" NUMBER(14,0) NOT NULL ENABLE, 
    "TYPE_ID" NUMBER(14,0), 
    "THIRD_ID" NUMBER(14,0), 
    "MARKET_THIRD_ID" NUMBER(14,0), 
    "EXCH_D" TIMESTAMP (6) NOT NULL ENABLE, 
    "DAILY_DFLT_F" NUMBER(3,0) DEFAULT 0 NOT NULL ENABLE, 
    "EXCH_RATE" NUMBER(23,14) NOT NULL ENABLE, 
    "EXTERNAL_SEQ_NO" NUMBER(20,0), 
    "CREATION_D" TIMESTAMP (6), 
    "CREATION_USER_ID" NUMBER(14,0), 
    "LAST_MODIF_D" TIMESTAMP (6), 
    "LAST_USER_ID" NUMBER(14,0), 
     CONSTRAINT "EXCH_RATE_DAILY_DFLT_CHK" CHECK (daily_dflt_f in (0, 1)) ENABLE, 
     CONSTRAINT "FK_701001" FOREIGN KEY ("CURRENCY_ID")
      REFERENCES "D_ORA_22_1_AAAMAINDB"."CURRENCY" ("ID") ON DELETE CASCADE ENABLE, 
     CONSTRAINT "FK_701002" FOREIGN KEY ("UNDERLY_CURRENCY_ID")
      REFERENCES "D_ORA_22_1_AAAMAINDB"."CURRENCY" ("ID") ON DELETE CASCADE ENABLE, 
     CONSTRAINT "FK_701003" FOREIGN KEY ("TYPE_ID")
      REFERENCES "D_ORA_22_1_AAAMAINDB"."TYPE" ("ID") ENABLE, 
     CONSTRAINT "FK_701004" FOREIGN KEY ("THIRD_ID")
      REFERENCES "D_ORA_22_1_AAAMAINDB"."THIRD_PARTY" ("ID") ENABLE, 
     CONSTRAINT "FK_701005" FOREIGN KEY ("MARKET_THIRD_ID")
      REFERENCES "D_ORA_22_1_AAAMAINDB"."THIRD_PARTY" ("ID") ENABLE
   ) 

  CREATE UNIQUE INDEX "EXCH_RATE_BK_IDX" ON "EXCH_RATE" ("CURRENCY_ID", "EXCH_D", "UNDERLY_CURRENCY_ID", "TYPE_ID", "THIRD_ID", "MARKET_THIRD_ID")

型
解释计划：

Explain plan
Plan hash value: 471245728
 
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                  |     1 |   259 |    29   (4)| 00:00:01 |
|*  1 |  FILTER                                |                  |       |       |            |          |
|   2 |   SORT GROUP BY                        |                  |     1 |   259 |    29   (4)| 00:00:01 |
|*  3 |    HASH JOIN                           |                  |     1 |   259 |    28   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                   | EXCH_RATE_BK_IDX |  2291 |   174K|    14   (0)| 00:00:01 |
|   5 |     TABLE ACCESS BY INDEX ROWID BATCHED| EXCH_RATE        |  2291 |   404K|    14   (0)| 00:00:01 |
|*  6 |      INDEX RANGE SCAN                  | EXCH_RATE_BK_IDX |     9 |       |    14   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("EX1"."EXCH_D"=MAX("EXCH_D"))
   3 - access(SYS_OP_MAP_NONNULL("EX1"."TYPE_ID")=SYS_OP_MAP_NONNULL("TYPE_ID") AND 
              SYS_OP_MAP_NONNULL("EX1"."THIRD_ID")=SYS_OP_MAP_NONNULL("THIRD_ID") AND 
              SYS_OP_MAP_NONNULL("EX1"."MARKET_THIRD_ID")=SYS_OP_MAP_NONNULL("MARKET_THIRD_ID"))
   4 - access("CURRENCY_ID"=1004 AND "EXCH_D">=TIMESTAMP' 2009-12-24 00:00:00' AND 
              "UNDERLY_CURRENCY_ID"=2 AND "EXCH_D"<=TIMESTAMP' 2012-10-14 00:00:00')
       filter("UNDERLY_CURRENCY_ID"=2)
   6 - access("EX1"."CURRENCY_ID"=1004 AND "EX1"."EXCH_D">=TIMESTAMP' 2009-12-24 00:00:00' AND 
              "EX1"."UNDERLY_CURRENCY_ID"=2 AND "EX1"."EXCH_D"<=TIMESTAMP' 2012-10-14 00:00:00')
       filter("EX1"."UNDERLY_CURRENCY_ID"=2)
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

型

oracle

来源：https://stackoverflow.com/questions/77444304/need-to-optimize-an-oracle-sql-query

2条答案

按热度按时间

yk9xbfzb1#

优化Oracle查询的第一步通常是删除曾经认为有用的提示。
然后，您可以使用窗口函数重写查询，以获得每个type_id、third_id、market_third_id的最新行。

with 
  newest as
  (
    select *
    from exch_rate
    where currency_id = 1004
    and underly_currency_id = 2
    and exch_d >= date '2009-12-24' 
    and exch_d <  date '2012-10-15'
    order by rank() over (partition by type_id, third_id, market_third_id
                          order by exch_d desc)
    fetch first row with ties
  )
select *
from newest
order by exch_d desc nulls last;

字符串
现在，条件的选择性有多大？通过应用WHERE子句，你得到的行的百分比是多少？如果它是一小部分，比如表行的1%或更少，那么复合索引可能会有很大的帮助。如果它是10%的行，那么全表扫描可能会快得多。
你当然可以试试。创建索引

create index idx on exch_rate (currency_id, underly_currency_id, exch_d)

型
然后看看它是否被DBMS使用。如果没有，那么这可能和它得到的一样快。好吧，你可以添加一个并行提示（例如/*+parallel(4)*/），然后使用更多的CPU来执行任务。

赞(0）回复(0）举报 2023-11-17

bvuwiixz2#

如果不知道您的数据是什么样的，我们就无法优化您的查询。即使是执行计划的行估计也不能信任，因为我们不知道您的统计信息是否准确。计划指示使用了动态采样，这表明统计信息缺失或不足。您可能只需要删除提示并收集统计信息。
如果没有，我可以做一些即兴的观察：
1.不要在连接子句中使用OR，特别是如果它只是处理NULL等式的话。哈希连接将更好地与NVL()一起使用，将NULL视为可以在两边匹配的特定值：and NVL(ex1.type_id,-1) = NVL(ex3.type_id,-1)等。看起来Oracle已经为您完成了这个转换：SYS_OP_MAP_NONNULL，但最好自己显式地完成。
1.如果内部查询块ex3中的聚合（group by）确实减少了它的行数，我发现通常需要强制Oracle在块内执行聚合，而不是推迟到连接到父块中的表之后。要做到这一点，使用NO_MERGE提示来防止视图合并：

from exch_rate ex1
            inner join ( select /*+ NO_MERGE */ max(exch_d) as max_exch_d, type_id, third_id, market_third_id
             from exch_rate
             where currency_id = '1004'
             and underly_currency_id = '2'
             and exch_d >= TO_DATE('24-12-2009 00:00:00', 'DD-MM-YYYY HH24:MI:SS') and exch_d <= TO_DATE('14-10-2012 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
             group by type_id, third_id, market_third_id
             ) ex3

字符串
1.你的日期范围相当广泛（2009年至2012年）。如果只有几种货币，（1004是常见的）和大多数表是过时的历史条目，要求这样一个广泛的时间段使索引的使用有问题。您可能想删除您的INDEX提示。事实上，在对数据一无所知的情况下，（因此只是做了一些可能是错误的假设），我可能会暗示这是为了 full 表扫描和一些并行性，因为它看起来更像是一个报告查询，将对很大一部分数据起作用：

select /*+ USE_HASH(ex3 ex1) FULL(ex1) PARALLEL(8) */
         ex1.currency_id as currency_id,
         ex1.underly_currency_id as underly_currency_id,
         ex1.type_id as type_id,
         ex1.third_id as third_id,
         ex1.market_third_id as market_third_id,
         ex1.exch_d as exch_d,
         ex1.daily_dflt_f as daily_dflt_f,
         ex1.exch_rate as exch_rate,
         ex1.external_seq_no as external_seq_no,
         ex1.creation_d as creation_d,
         ex1.creation_user_id as creation_user_id,
         ex1.last_modif_d as last_modif_d,
         ex1.last_user_id as last_user_id
         from exch_rate ex1
            inner join ( select /*+ NO_MERGE FULL(exch_rate) */ max(exch_d) as max_exch_d, type_id, third_id, market_third_id
             from exch_rate
             where currency_id = '1004'
             and underly_currency_id = '2'
             and exch_d >= TO_DATE('24-12-2009 00:00:00', 'DD-MM-YYYY HH24:MI:SS') and exch_d <= TO_DATE('14-10-2012 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
             group by type_id, third_id, market_third_id
             ) ex3
        on  ex1.exch_d = ex3.max_exch_d 
         and NVL(ex1.type_id,-1) = NVL(ex3.type_id,-1)
         and NVL(ex1.third_id,-1) = NVL(ex3.third_id,-1)
         and NVL(ex1.market_third_id,-1) = NVL(ex3.market_third_id,-1)
        where (ex1.currency_id = '1004') and ( ex1.underly_currency_id = '2') 
        and ((ex1.exch_d >= TO_DATE('24-12-2009 00:00:00', 'DD-MM-YYYY HH24:MI:SS') and (ex1.exch_d <= TO_DATE('14-10-2012 00:00:00', 'DD-MM-YYYY HH24:MI:SS'))))
         order by exch_d desc nulls last

型
;
如果你的表很小，你可能需要省略parallel提示，并且你试图得到这个亚秒。parallel有一些开销，所以只在查询时使用它，如果没有它，至少需要几秒钟。
当然，如果currency_id = 1004是 rare，那么索引仍然有价值，这些提示不应该使用。只有对数据的了解才能确定哪个更合适。

赞(0）回复(0）举报 2023-11-17

我来回答

需要优化Oracle SQL查询

2条答案

相关问题

热门标签

最新问答