oracle 如何改进此SELECT？

sr4lhrrt  于 2023-11-17  发布在  Oracle
关注(0)|答案(1)|浏览(118)

这个选择是否可以重写以更快地执行？你会有什么建议？从哪里开始寻找...？任何帮助都将非常感谢。
我不明白为什么在执行计划的第19行中有“TABLE ACCESS FULL”，因为COL7在T1（“ba”）中是PK，而在T6（“bba”）中有一个唯一的索引（多一列的组合索引）。
另外，T2在COL4上没有索引，所以我添加了它，但是执行计划是一样的。什么也没改变。仍然在第21和22行进行全表扫描。为什么？

SELECT pcl.COL1,
       pcl.COL2,
       pcl.COL3,
       pcl.COL4,
       (SELECT cc.COL5
          FROM T3 cc
          JOIN T4 cvp
            ON cvp.COL10 = cc.COL10
           AND cvp.COL2 = pcl.COL2) AS COL10,
       pp.COL11
FROM T1 ba
JOIN T2 pcl
  ON pcl.COL1 = ba.COL1
 AND (pcl.COL2 = ba.COL2 OR ba.COL2 = 0)
JOIN T5 pscl
  ON pscl.COL2 = pcl.COL2
 AND pscl.COL4 <> 3
 AND (pscl.COL6 = ba.COL6 OR ba.COL6 = 'ALL')
LEFT JOIN T6 bba
  ON bba.COL7 = ba.COL7
LEFT JOIN T7 bsa
  ON bsa.COL7 = ba.COL7
LEFT JOIN T8 pp
  ON pp.COL1 = pcl.COL1
WHERE (ba.COL8 = 617617 OR bsa.COL8 = 617617 OR bba.COL9 = 617617)
 AND ba.COL4 <> 3
 AND pcl.COL4 <> 3
 AND pscl.COL4 <> 3
 AND NOT EXISTS
(SELECT *
       FROM T1 ba2
      WHERE ba2.COL1 = ba.COL1
        AND ((ba2.COL2 = pcl.COL2 AND
            (ba2.COL6 = pscl.COL6 OR ba2.COL6 = 'ALL')) OR
            (ba.COL6 = 'ALL' AND ba.COL2 = 0 AND ba2.COL6 = pscl.COL6))
        AND (ba2.COL2 <> ba.COL2 OR ba2.COL2 = 0)
        AND ba2.COL13 = ba.COL13
        AND ba2.COL12 = ba.COL12
        AND ba2.COL4 <> 3)
UNION (SELECT pcl.COL1,
           pcl.COL2,
           pcl.COL3,
           pcl.COL4,
           (SELECT cc.COL5
              FROM T3 cc
              JOIN T4 cvp
                ON cvp.COL10 = cc.COL10
               AND cvp.COL2 = pcl.COL2) AS COL10,
           pp.COL11
      FROM T9 sa
      LEFT JOIN T2 pcl
        on sa.COL1 = pcl.COL1
       and (sa.COL2 = pcl.COL2 or sa.COL2 = 0)
       and (sa.COL14 = pcl.COL14)
       and pcl.COL4 <> 6
       and pcl.COL4 is not null
       AND pcl.COL3 is not null
      LEFT JOIN T8 pp
        on pp.COL1 = pcl.COL1
     WHERE sa.COL8 = 617617
       AND pcl.COL14 = 100805)
ORDER BY COL11, COL1, COL4, COL3;

字符串
数据库版本为Oracle 19c Enterprise Edition。

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                                   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                                        |      1 |        |      1 |00:00:00.89 |   64899 |       |       |          |
|   1 |  SORT UNIQUE                            |                                        |      1 |   4136K|      1 |00:00:00.89 |   64899 |  2048 |  2048 |     1/0/0|
|   2 |   UNION-ALL                             |                                        |      1 |        |     27 |00:00:00.71 |   64899 |       |       |          |
|   3 |    NESTED LOOPS                         |                                        |      1 |      1 |      1 |00:00:00.01 |       7 |       |       |          |
|   4 |     TABLE ACCESS BY INDEX ROWID         | T4                                     |      1 |      1 |      1 |00:00:00.01 |       5 |       |       |          |
|*  5 |      INDEX UNIQUE SCAN                  | I_PK_VPCCONT                           |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|   6 |     TABLE ACCESS BY INDEX ROWID         | T3                                     |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  7 |      INDEX UNIQUE SCAN                  | I_PK_SYSCONCA                          |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
|*  8 |    FILTER                               |                                        |      1 |        |     27 |00:00:00.71 |   64888 |       |       |          |
|   9 |     NESTED LOOPS                        |                                        |      1 |   5940K|     27 |00:00:00.71 |   64885 |       |       |          |
|  10 |      NESTED LOOPS                       |                                        |      1 |   5940K|     81 |00:00:00.71 |   64861 |       |       |          |
|  11 |       NESTED LOOPS OUTER                |                                        |      1 |   7702 |      1 |00:00:00.89 |   64857 |       |       |          |
|* 12 |        HASH JOIN                        |                                        |      1 |   7702 |      1 |00:00:00.89 |   64853 |   962K|   962K|     1/0/0|
|* 13 |         FILTER                          |                                        |      1 |        |      1 |00:00:00.25 |   38012 |       |       |          |
|* 14 |          HASH JOIN RIGHT OUTER          |                                        |      1 |  16426 |   1645K|00:00:00.84 |   38012 |  2920K|  2920K|     1/0/0|
|  15 |           VIEW                          | index$_join$_011                       |      1 |      1 |      0 |00:00:00.01 |       4 |       |       |          |
|* 16 |            HASH JOIN                    |                                        |      1 |        |      0 |00:00:00.01 |       4 |  2078K|  2078K|     1/0/0|
|  17 |             INDEX FAST FULL SCAN        | I_FK_BUSTLACC_SETTACC_COL8             |      1 |      1 |      0 |00:00:00.01 |       4 |       |       |          |
|  18 |             INDEX FAST FULL SCAN        | I_UK_BUSTLACC_COL7_AMOUNTTYPE          |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 19 |           HASH JOIN RIGHT OUTER         |                                        |      1 |   1641K|   1645K|00:00:00.54 |   38008 |    24M|  4789K|     1/0/0|
|  20 |            TABLE ACCESS FULL            | T6                                     |      1 |    414K|    413K|00:00:00.01 |    1634 |       |       |          |
|* 21 |            TABLE ACCESS FULL            | T1                                     |      1 |   1632K|   1636K|00:00:00.28 |   36374 |       |       |          |
|* 22 |         TABLE ACCESS FULL               | T2                                     |      1 |   1100K|   1102K|00:00:00.16 |   26841 |       |       |          |
|  23 |        TABLE ACCESS BY INDEX ROWID      | T8                                     |      1 |      1 |      1 |00:00:00.01 |       4 |       |       |          |
|* 24 |         INDEX UNIQUE SCAN               | I_PK_PARTNER                           |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|* 25 |       INDEX RANGE SCAN                  | I_FK_PCSUBLIN_CONTRACT_COL2            |      1 |      8 |     81 |00:00:00.01 |       4 |       |       |          |
|* 26 |      TABLE ACCESS BY INDEX ROWID        | T5                                     |     81 |    771 |     27 |00:00:00.01 |      24 |       |       |          |
|* 27 |     TABLE ACCESS BY INDEX ROWID BATCHED | T1                                     |      1 |      1 |      0 |00:00:00.01 |       3 |       |       |          |
|* 28 |      INDEX RANGE SCAN                   | I_BUSIACC_COL1_3C                      |      1 |      1 |      0 |00:00:00.01 |       3 |       |       |          |
|  29 |    NESTED LOOPS                         |                                        |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|  30 |     TABLE ACCESS BY INDEX ROWID         | T4                                     |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 31 |      INDEX UNIQUE SCAN                  | I_PK_VPCCONT                           |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|  32 |     TABLE ACCESS BY INDEX ROWID         | T3                                     |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 33 |      INDEX UNIQUE SCAN                  | I_PK_SYSCONCA                          |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|  34 |    NESTED LOOPS OUTER                   |                                        |      1 |      1 |      0 |00:00:00.01 |       4 |       |       |          |
|  35 |     NESTED LOOPS                        |                                        |      1 |      1 |      0 |00:00:00.01 |       4 |       |       |          |
|* 36 |      TABLE ACCESS BY INDEX ROWID        | T9                                     |      1 |      1 |      0 |00:00:00.01 |       4 |       |       |          |
|* 37 |       INDEX UNIQUE SCAN                 | I_PK_SETTACC                           |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|* 38 |      TABLE ACCESS BY INDEX ROWID BATCHED| T2                                     |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 39 |       INDEX RANGE SCAN                  | I_PARCONLI_PID_PARTNERMCC              |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|  40 |     TABLE ACCESS BY INDEX ROWID         | T8                                     |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 41 |      INDEX UNIQUE SCAN                  | I_PK_PARTNER                           |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   5 - access("CVP"."COL2"=:B1)
   7 - access("CVP"."COL10"="CC"."COL10")
   8 - filter( IS NULL)
  12 - access("PCL"."COL1"="BA"."COL1")
       filter(("PCL"."COL2"="BA"."COL2" OR "BA"."COL2"=0))
  13 - filter(("BA"."COL8"=617617 OR "BSA"."COL8"=617617 OR "BBA"."COL9"=617617))
  14 - access("BSA"."COL7"="BA"."COL7")
  16 - access(ROWID=ROWID)
  19 - access("BBA"."COL7"="BA"."COL7")
  21 - filter("BA"."COL4"<>3)
  22 - filter("PCL"."COL4"<>3)
  24 - access("PP"."COL1"="PCL"."COL1")
  25 - access("PSCL"."COL2"="PCL"."COL2")
  26 - filter(("PSCL"."COL4"<>3 AND ("PSCL"."COL6"="BA"."COL6" OR "BA"."COL6"='ALL')))
  27 - filter(("BA2"."COL12"=:B1 AND "BA2"."COL13"=:B2 AND "BA2"."COL4"<>3))
  28 - access("BA2"."COL1"=:B1)
       filter((((:B1='ALL' AND :B2=0 AND "BA2"."COL6"=:B3) OR ("BA2"."COL2"=:B4 AND INTERNAL_FUNCTION("BA2"."COL6"))) AND 
              ("BA2"."COL2"<>:B5 OR "BA2"."COL2"=0)))
  31 - access("CVP"."COL2"=:B1)
  33 - access("CVP"."COL10"="CC"."COL10")
  36 - filter("SA"."COL14"=100805)
  37 - access("SA"."COL8"=617617)
  38 - filter(("PCL"."COL14"=100805 AND ("SA"."COL2"="PCL"."COL2" OR "SA"."COL2"=0) AND "PCL"."COL4"<>6))
  39 - access("SA"."COL1"="PCL"."COL1")
  41 - access("PP"."COL1"="PCL"."COL1")

型
表和列的名字都是匿名的，希望你不要介意。

oracle

1条答案

按热度按时间
7fyelxc5

7fyelxc51#

正如你所看到的，所有有索引的都工作得非常快。最可怕的问题是表T1，T2和T6 -它们都有TABLE ACCESS FULL的访问权限-这是非常糟糕的。
当然，他们应该有索引，但条件，这真的很奇怪. (ba.COL8 = 617617 OR bsa.COL8 = 617617 OR bba.COL9 = 617617), ba.COL4 <> 3,
正如我们在评论中所讨论的，它可能是问题的根源，所以，为了避免FULL ACCESS，如果你需要这些值中的一个，你可以使用UNION，类似这样。
如果你有一些OR这样的情况1：

with q as (
    select   id, name
    from     table
    where    id in (5, 6, 7)
)
select   *
from     q

字符串
你可以把它转换成这样的情况2：

with q as (
    select   id, name
    from     table
    where    id = 5
    union all
    select   id, name
    from     table
    where    id = 6
    union all
    select   id, name
    from     table
    where    id = 7
)
select   *
from     q

型
因此，在案例2中，数据库将能够为表使用索引。

赞(0）分享  回复(0）举报  2023-11-17
我来回答

相关问题

    查看更多

    热门标签

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

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com