sql - 性能调整或替代 distinct 子句-6ren

sql - 性能调整或替代 distinct 子句

转载作者：行者123 更新时间：2023-12-02 04:47:45

我有一个带有不同子句的查询:

SELECT      --  /*+ first_rows  */
     distinct   a.sub_id, b.status,
            pkg_sp_subbrief.get_sub_typ (a.sub_id) sub_type,
            c.svc_provider_nm, fn_sp_get_svc_plan (a.sub_id) svc_plan,
            pkg_sp_subbrief.get_sub_contact_parm
                                    (a.sub_id,
                                    'contact.name.salutation'
                                    ) salutation,
            pkg_sp_subbrief.get_sub_contact_parm
                                        (a.sub_id,
                                        'first_name'
                                        ) first_name,
            pkg_sp_subbrief.get_sub_contact_parm
                                    (a.sub_id,
                                        'contact.name.middle'
                                    ) middle_name,
            pkg_sp_subbrief.get_sub_contact_parm
                                        (a.sub_id,
                                            'last_name'
                                        ) last_name,
            pkg_sp_subbrief.get_sub_parm (a.sub_id, 'company_name') company_name,
            pkg_sp_subbrief.get_sub_parm (a.sub_id, 'itc_account') itc_accout,
            pkg_sp_subbrief.get_sub_contact_parm
                                    (a.sub_id,
                                        'phones.home.number'
                                    ) phone_number,
            pkg_sp_subbrief.get_location_parm
                                        (a.sub_id,
                                            'address_1'
                                        ) addr_home_address,
            pkg_sp_subbrief.get_location_parm
                                                (a.sub_id,
                                                'city'
                                                ) addr_home_city,
            pkg_sp_subbrief.get_location_parm
                                                (a.sub_id,
                                                'prov'
                                                ) addr_home_prov,
            pkg_sp_subbrief.get_location_parm
                                    (a.sub_id,
                                        'postal_cd'
                                    ) addr_home_postal_code,
            pkg_sp_subbrief.get_location_parm
                                            (a.sub_id,
                                            'country'
                                            ) addr_home_country,
            e.val cm_mta_mac, ' ' telephone_number,
            pkg_sp_subbrief.get_subsvc_parm_first (a.sub_id,
                                                    3374
                                                    ) alpha_tag,
            NVL (j.voipdn1, ' ') voip_dn1,
            NVL (j.mtavoiceport, ' ') mta_voice_port
        FROM svc_provider c,
            ref_status b,
            sub a,
            sub_svc d,
            sub_svc_parm e,
            (SELECT f.sub_id, g.val voipdn1, i.val mtavoiceport
                FROM sub_svc f, sub_svc_parm g, sub_svc_parm i
                WHERE f.svc_id = SvcId('smp_dial_tone_access')
                AND f.sub_svc_status_id ! = 29
                AND f.sub_svc_id = g.sub_svc_id
                AND g.parm_id = get_cgo_parm_id('voip_dn1', GET_CLASS_ID('SubSvcSpec'), SvcID('smp_dial_tone_access'))
                AND f.sub_svc_id = i.sub_svc_id
                AND i.parm_id = 20410) j
        WHERE a.svc_provider_id = c.svc_provider_id
        AND a.sub_status_id = b.status_id
        AND a.sub_id = d.sub_id
        AND d.sub_svc_id = e.sub_svc_id
        AND (e.parm_id = 1254 OR e.parm_id = 20249)
        AND d.sub_svc_status_id != 29
        AND a.sub_status_id != 9
        AND a.sub_id = j.sub_id(+);

此查询返回 10,146 行，执行时间将近 135 秒

对此的解释计划是:

Execution Plan
----------------------------------------------------------
Plan hash value: 2622307916

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name             | Rows  |     Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                  | 10409 |      1118K|       |  2540   (4)| 00:00:31 |
|   1 |  HASH UNIQUE                     |                  | 10409 |      1118K|  1240K|  2540   (4)| 00:00:31 |
|*  2 |   HASH JOIN RIGHT OUTER          |                  | 10409 |      1118K|       |  2279   (4)| 00:00:28 |
|   3 |    VIEW                          |                  |   899 |     23374 |       |  1340   (6)| 00:00:17 |
|*  4 |     HASH JOIN                    |                  |   899 |     53940 |       |  1340   (6)| 00:00:17 |
|*  5 |      HASH JOIN                   |                  |   885 |     35400 |       |   918   (8)| 00:00:12 |
|*  6 |       TABLE ACCESS BY INDEX ROWID| SUB_SVC          |   877 |     17540 |       |   434   (0)| 00:00:06 |
|*  7 |        INDEX RANGE SCAN          | SUB_SVC_IX2      |   951 |           |       |     3   (0)| 00:00:01 |
|*  8 |       TABLE ACCESS FULL          | SUB_SVC_PARM     |  1770 |     35400 |       |   483  (14)| 00:00:06 |
|*  9 |      TABLE ACCESS FULL           | SUB_SVC_PARM     |  2632 |     52640 |       |   422   (2)| 00:00:06 |
|* 10 |    HASH JOIN                     |                  | 10409 |       853K|       |   939   (2)| 00:00:12 |
|* 11 |     TABLE ACCESS FULL            | REF_STATUS       |    95 |      2280 |       |     3   (0)| 00:00:01 |
|* 12 |     HASH JOIN                    |                  | 10409 |       609K|       |   935   (2)| 00:00:12 |
|  13 |      VIEW                        | index$_join$_001 |    49 |       588 |       |     3  (34)| 00:00:01 |
|* 14 |       HASH JOIN                  |                  |       |           |       |            |          |
|  15 |        INDEX FAST FULL SCAN      | SVC_PROVIDER_PK  |    49 |       588 |       |     1   (0)| 00:00:01 |
|  16 |        INDEX FAST FULL SCAN      | SVC_PROVIDER_UK1 |    49 |       588 |       |     1   (0)| 00:00:01 |
|* 17 |      HASH JOIN                   |                  | 10409 |       487K|       |   932   (2)| 00:00:12 |
|* 18 |       TABLE ACCESS FULL          | SUB              |  8777 |       111K|       |    53   (0)| 00:00:01 |
|* 19 |       HASH JOIN                  |                  | 10607 |       362K|       |   878   (2)| 00:00:11 |
|* 20 |        TABLE ACCESS FULL         | SUB_SVC_PARM     | 10607 |       207K|       |   423   (2)| 00:00:06 |
|* 21 |        TABLE ACCESS FULL         | SUB_SVC          | 90284 |      1322K|       |   454   (1)| 00:00:06 |
-----------------------------------------------------------------------------------------------------------------

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

2 - access("A"."SUB_ID"="J"."SUB_ID"(+))
4 - access("F"."SUB_SVC_ID"="I"."SUB_SVC_ID")
5 - access("F"."SUB_SVC_ID"="G"."SUB_SVC_ID")
6 - filter("F"."SUB_SVC_STATUS_ID"<>29)
7 - access("F"."SVC_ID"="SVCID"('smp_dial_tone_access'))
8 - filter("G"."PARM_ID"="GET_CGO_PARM_ID"('voip_dn1',"GET_CLASS_ID"  ('SubSvcSpec'),"SVCID"('smp_dial_tone_access')))
9 - filter("I"."PARM_ID"=20410)
10 - access("A"."SUB_STATUS_ID"="B"."STATUS_ID")
11 - filter("B"."STATUS_ID"<>9)
12 - access("A"."SVC_PROVIDER_ID"="C"."SVC_PROVIDER_ID")
14 - access(ROWID=ROWID)
17 - access("A"."SUB_ID"="D"."SUB_ID")
18 - filter("A"."SUB_STATUS_ID"<>9)
19 - access("D"."SUB_SVC_ID"="E"."SUB_SVC_ID")
20 - filter("E"."PARM_ID"=1254 OR "E"."PARM_ID"=20249)
21 - filter("D"."SUB_SVC_STATUS_ID"<>29)

Statistics
----------------------------------------------------------

   470461  recursive calls
        0  db block gets
 13591783  consistent gets
        0  physical reads
        0  redo size
  1272441  bytes sent via SQL*Net to client
     7960  bytes received via SQL*Net from client
      678  SQL*Net roundtrips to/from client
        0  sorts (memory)
        0  sorts (disk)
    10146  rows processed

当我从查询中删除 distinct 子句时，它会在 4 秒内执行但查询返回 10163 行，这意味着它也返回重复的行。

这是没有distinct子句的执行计划:

 Execution Plan
 ----------------------------------------------------------
 Plan hash value: 3514824003

---------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                  | 10409 |  3608K|  2279   (4)| 00:00:28 |
|*  1 |  HASH JOIN RIGHT OUTER          |                  | 10409 |  3608K|  2279   (4)| 00:00:28 |
|   2 |   VIEW                          |                  |   899 |   237K|  1340   (6)| 00:00:17 |
|*  3 |    HASH JOIN                    |                  |   899 | 53940 |  1340   (6)| 00:00:17 |
|*  4 |     HASH JOIN                   |                  |   885 | 35400 |   918   (8)| 00:00:12 |
|*  5 |      TABLE ACCESS BY INDEX ROWID| SUB_SVC          |   877 | 17540 |   434   (0)| 00:00:06 |
|*  6 |       INDEX RANGE SCAN          | SUB_SVC_IX2      |   951 |       |     3   (0)| 00:00:01 |
|*  7 |      TABLE ACCESS FULL          | SUB_SVC_PARM     |  1770 | 35400 |   483  (14)| 00:00:06 |
|*  8 |     TABLE ACCESS FULL           | SUB_SVC_PARM     |  2632 | 52640 |   422   (2)| 00:00:06 |
|*  9 |   HASH JOIN                     |                  | 10409 |   853K|   939   (2)| 00:00:12 |
|* 10 |    TABLE ACCESS FULL            | REF_STATUS       |    95 |  2280 |     3   (0)| 00:00:01 |
|* 11 |    HASH JOIN                    |                  | 10409 |   609K|   935   (2)| 00:00:12 |
|  12 |     VIEW                        | index$_join$_001 |    49 |   588 |     3  (34)| 00:00:01 |
|* 13 |      HASH JOIN                  |                  |       |       |            |          |
|  14 |       INDEX FAST FULL SCAN      | SVC_PROVIDER_PK  |    49 |   588 |     1   (0)| 00:00:01 |
|  15 |       INDEX FAST FULL SCAN      | SVC_PROVIDER_UK1 |    49 |   588 |     1   (0)| 00:00:01 |
|* 16 |     HASH JOIN                   |                  | 10409 |   487K|   932   (2)| 00:00:12 |
|* 17 |      TABLE ACCESS FULL          | SUB              |  8777 |   111K|    53   (0)| 00:00:01 |
|* 18 |      HASH JOIN                  |                  | 10607 |   362K|   878   (2)| 00:00:11 |
|* 19 |       TABLE ACCESS FULL         | SUB_SVC_PARM     | 10607 |   207K|   423   (2)| 00:00:06 |
|* 20 |       TABLE ACCESS FULL         | SUB_SVC          | 90284 |  1322K|   454   (1)| 00:00:06 |
----------------------------------------------------------------------------------------------------


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

 1 - access("A"."SUB_ID"="J"."SUB_ID"(+))
 3 - access("F"."SUB_SVC_ID"="I"."SUB_SVC_ID")
 4 - access("F"."SUB_SVC_ID"="G"."SUB_SVC_ID")
 5 - filter("F"."SUB_SVC_STATUS_ID"<>29)
 6 - access("F"."SVC_ID"="SVCID"('smp_dial_tone_access'))
 7 - filter("G"."PARM_ID"="GET_CGO_PARM_ID"('voip_dn1',"GET_CLASS_ID"  ('SubSvcSpec'),"SVCID"('smp_dial_tone_access')))
 8 - filter("I"."PARM_ID"=20410)
 9 - access("A"."SUB_STATUS_ID"="B"."STATUS_ID")
10 - filter("B"."STATUS_ID"<>9)
11 - access("A"."SVC_PROVIDER_ID"="C"."SVC_PROVIDER_ID")
13 - access(ROWID=ROWID) 
16 - access("A"."SUB_ID"="D"."SUB_ID")
17 - filter("A"."SUB_STATUS_ID"<>9)  
18 - access("D"."SUB_SVC_ID"="E"."SUB_SVC_ID")
19 - filter("E"."PARM_ID"=1254 OR "E"."PARM_ID"=20249)
20 - filter("D"."SUB_SVC_STATUS_ID"<>29)


Statistics
----------------------------------------------------------
   470461  recursive calls
        0  db block gets
 13592455  consistent gets
        0  physical reads
        0  redo size
  1168343  bytes sent via SQL*Net to client
     7971  bytes received via SQL*Net from client
      679  SQL*Net roundtrips to/from client
        0  sorts (memory)
        0  sorts (disk)
    10163  rows processed

所以我正在寻找 distinct 子句的任何替代方案来调整此查询。

我的数据库版本是:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

最佳答案

我会通过取出函数然后在外部查询中选择那些列来包装整个不同的查询。应该减少函数调用的次数，并且这样 distinct 也应该更快。

SELECT *, pkg_sp_subbrief.get_sub_contact_parm
                                    (a.sub_id,
                                    'contact.name.salutation'
                                    ) salutation,
            pkg_sp_subbrief.get_sub_contact_parm
                                        (a.sub_id,
                                        'first_name'
                                        ) first_name,
            pkg_sp_subbrief.get_sub_contact_parm
                                    (a.sub_id,
                                        'contact.name.middle'
                                    ) middle_name,
            pkg_sp_subbrief.get_sub_contact_parm
                                        (a.sub_id,
                                            'last_name'
                                        ) last_name,
            pkg_sp_subbrief.get_sub_parm (a.sub_id, 'company_name') company_name,
            pkg_sp_subbrief.get_sub_parm (a.sub_id, 'itc_account') itc_accout,
            pkg_sp_subbrief.get_sub_contact_parm
                                    (a.sub_id,
                                        'phones.home.number'
                                    ) phone_number,
            pkg_sp_subbrief.get_location_parm
                                        (a.sub_id,
                                            'address_1'
                                        ) addr_home_address,
            pkg_sp_subbrief.get_location_parm
                                                (a.sub_id,
                                                'city'
                                                ) addr_home_city,
            pkg_sp_subbrief.get_location_parm
                                                (a.sub_id,
                                                'prov'
                                                ) addr_home_prov,
            pkg_sp_subbrief.get_location_parm
                                    (a.sub_id,
                                        'postal_cd'
                                    ) addr_home_postal_code,
            pkg_sp_subbrief.get_location_parm
                                            (a.sub_id,
                                            'country'
                                            ) addr_home_country,\
            pkg_sp_subbrief.get_subsvc_parm_first (a.sub_id,
                                                    3374
                                                    ) alpha_tag,
            fn_sp_get_svc_plan (a.sub_id) svc_plan,
            pkg_sp_subbrief.get_sub_typ (a.sub_id) sub_type
FROM (                                          
    SELECT      --  /*+ first_rows  */
         distinct   a.sub_id, b.status,
                c.svc_provider_nm,
                e.val cm_mta_mac, ' ' telephone_number,
                NVL (j.voipdn1, ' ') voip_dn1,
                NVL (j.mtavoiceport, ' ') mta_voice_port
            FROM svc_provider c,
                ref_status b,
                sub a,
                sub_svc d,
                sub_svc_parm e,
                (SELECT f.sub_id, g.val voipdn1, i.val mtavoiceport
                    FROM sub_svc f, sub_svc_parm g, sub_svc_parm i
                    WHERE f.svc_id = SvcId('smp_dial_tone_access')
                    AND f.sub_svc_status_id ! = 29
                    AND f.sub_svc_id = g.sub_svc_id
                    AND g.parm_id = get_cgo_parm_id('voip_dn1', GET_CLASS_ID('SubSvcSpec'), SvcID('smp_dial_tone_access'))
                    AND f.sub_svc_id = i.sub_svc_id
                    AND i.parm_id = 20410) j
            WHERE a.svc_provider_id = c.svc_provider_id
            AND a.sub_status_id = b.status_id
            AND a.sub_id = d.sub_id
            AND d.sub_svc_id = e.sub_svc_id
            AND (e.parm_id = 1254 OR e.parm_id = 20249)
            AND d.sub_svc_status_id != 29
            AND a.sub_status_id != 9
            AND a.sub_id = j.sub_id(+)
    )

关于sql - 性能调整或替代 distinct 子句，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/31512225/

文章推荐： azure - 从 VS 2013 管理 Azure 存储

文章推荐： java - 从 Hibernate persistenceContext 缓存中排除 JPA 实体？

文章推荐： uml - 是否有必要包括每个用例的登录名？

Javascript 调整
我是 Javascript 新手，所以请原谅基本代码。有什么方法可以让我使用用户输入的指定调整来打印代码？ height: width: 最佳答案为按钮
javascript - 调整 A 框架中的屏幕截图大小
我有一个带有 A 框架的场景，我正在使用方法 getCanvas 来获取屏幕截图并将其发送到 PHP。有没有办法调整 getCanvas 图像大小？因为默认的是 4096x2048，我需要它更小。如果
Oracle 调整/分析表
安排自动“分析表”的方法是什么。当大量数据通过插入和删除发生更改时，是否可以请求自动“分析表”？参数化自动分析表过程的方法是什么，即设置何时应该触发的规则。最佳答案您使用的是哪个版本的 Oracl
C - 调整/放大图像
我只是想说，我是 C 语言的新手。好吧，除此之外，我在圣诞假期的任务是编写一个以各种方式操作 PNG 图像的程序。我已经完成了大部分事情，但是在尝试编写放大图像的程序时遇到了问题。我已经尝试过了，并且
postgresql - Postgres 调整
在 Postgres 中编写更快查询的有效方法是什么？请不要包括一般良好的数据库实践(例如使用索引或规范化)。我正在寻找像派生表比子查询工作得更快或使用 python 字符串函数似乎比 pgsql 字
用于对齐页面元素的 Css 调整？
我不知道自己做了什么，但我要么将页眉和导航向右移动，要么将页面的其余部分向左移动。使用 tw Bootstrap 。我想不通。我对它进行了调试并查看了我的 css 编辑，没有看到任何负边距(我怀疑是这
html - 调整/缩放内部容器中的字体
我希望能够增加默认字体大小，但只能在特定的 DIV 内。很明显，这似乎正是 ems 所针对的那种情况。我的问题是我只想增加字体大小，而不影响使用em设置大小的其他内容，例如填充和边距。这可能看起来
algorithm - 感知器算法 - 调整
我正在我的大学上数据挖掘类(class)。我真的不明白这个问题。谁能帮我理解一下？最佳答案重要性权重让您了解在采样时找到特定数据点的频率。您可以使用它来增加训练数据集。例如，如果您只有两个数据点:
C++ 调整/优化
按照目前的情况，这个问题不适合我们的问答形式。我们希望答案得到事实、引用或专业知识的支持，但这个问题可能会引发辩论、争论、投票或扩展讨论。如果您觉得这个问题可以改进并可能重新打开，visit the
c - 调整 argVector
我的部分程序如下所示: char *argVector[] = {"./doTasks","0", "1", "3", NULL}; int numChild = 3; int temp; char
c++ - 调整 Qt 窗口大小时出现闪烁的白色区域
我在调整 QWindow 大小时观察到一个奇怪的行为。当我调整窗口大小时使宽度和高度都增加或减少时，窗口不会以白色背景闪烁。但是当我增加宽度同时减小高度(或反之亦然)时，窗口会闪烁并暂时用白色填充新的
调整 ggplot geom_dotplot 的大小
我在使用 ggplot2 创建图形时遇到问题。我正在使用带有中心堆叠的 geom_dotplot 来显示我的数据，这些数据是 4 个类别的离散值。出于审美原因，我想自定义点的位置，这样沿 y 轴减
javascript - 调整 Canvas 大小以适应容器
在尝试让我的 Canvas/Stage 调整大小并使其正确适合父容器时遇到一些问题。我发现了其他类似的帖子，虽然答案确实帮助我获得了新的屏幕尺寸，但它仍然不想适应容器，而是直接进入屏幕的边缘(这在示例
svg - 调整 svg 组的路径
我想将路径大小调整为 20 像素左右。 SVG 的大小应为 500 * 500，现在路径宽度为 297，高度为 180.7。现在我需要这条路径，其宽度为 277，高度为 160.7，在之前的路径中。
javascript - 调整 SVG 的大小围绕其原点旋转
我有一个矩形 svg，可以围绕二维平面拖动，围绕它自己的原点旋转并调整大小。 class SVG extends React.Component { constructor(props) {
python - 调整 MLPRegressor 超参数
我一直在尝试调整 MLP 模型的超参数来解决回归问题，但我总是收到收敛警告。这是我的代码 def mlp_model(X, Y): estimator=MLPRegressor() param_gr
SwiftUI - 调整 ScrollView 滚动指示器插入
我正在创建一个聊天应用程序，我希望 ScrollView 的内容位于输入字段下方(向上滚动时)。我已经将 ScrollView 和输入字段放在 ZStack 中。 ScrollView 上的底部填充使
.NET 垃圾收集 (GC) 调整
我遇到 GC 来不及删除空闲对象的情况。该代码将一个大文档加载到内存中并循环处理它。如果我在此循环中停止(在 Debug模式下)或添加 GC.Collect()，内存使用量将下降到 70 MB 以下。
pdf - 调整 PDF 格式以更快地打印它
我正在使用 iTextSharp 和 PdfSharp 的组合来组装一个大型 PDF 文件，以便打印到 Canon Oce VarioPrint 6000 系列打印机。 PDF 正在替换后记文件。这
sql - 调整 mysql 查询以获得更好的性能
关闭。这个问题不符合Stack Overflow guidelines .它目前不接受答案。这个问题似乎不是关于 a specific programming problem, a softwar

行者123

个人简介

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

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

sql - 性能调整或替代 distinct 子句