oracle 如何改善视野？

o4hqfura 于 2023-10-16 发布在 Oracle

关注(0)|答案(1)|浏览(100)

这就是

CREATE OR REPLACE FORCE EDITIONABLE VIEW "MDB7_RES"."V_NODE_FOR_MAPPING" 
(
    "ID", 
    "NODE_ID_FK", 
    "NODEID", 
    "NODENAME", 
    "SAMPLE_REPORT", 
    "EARLY_MDB", 
    "MODULEID", 
    "VERSION_NO", 
    "ITEM_NO", 
    "AI", 
    "SUPPLIER_NO", 
    "COUNT_ADDRESS", 
    "CHANGE_DATE", 
    "ROW_VERSION", 
    "ASSIGNMENT_DATE", 
    "ASSIGNMENT_ID_FK", 
    "ASSIGNMENT_MODE", 
    "MAPPING_DATE"
) 
AS 
SELECT 
    n.ID || a.ID AS ID, 
    n.ID AS node_id_fk, 
    n.nodeid, 
    n.nodename, 
    NVL(m.IS_PRELIMINARY, 0), 
    NVL(m.IS_EARLY , 0), 
    m.moduleid, 
    m.version_no, 
    t.ITEM_NO, 
    t.AI, 
    s.SUPPLIER_NO, 
    s.COUNT_ADDRESS, 
    n.change_date, 
    n.row_version, 
    a.ASSIGNMENT_DATE, 
    a.ID AS ASSIGNMENT_ID_FK, 
    a.ASSIGNMENT_MODE,
    (
        SELECT max(bm.CHANGE_DATE) 
        FROM T_BOM_MAPPING bm 
        JOIN T_BOM_POSITION bp ON bm.BOM_POSITION_ID_FK = bp.ID 
        WHERE bp.SACHNR = t.ITEM_NO
    ) AS MAPPING_DATE
FROM 
    T_NODE n 
    LEFT OUTER JOIN T_MODULE m ON  n.module_id_fk = m.ID 
    JOIN T_BMW_MDB_ASSIGNMENT a ON  a.node_id_fk = n.ID 
    JOIN T_TAIS_PART t ON a.part_id_fk = t.ID 
    JOIN T_GPS_SUPPLIER s ON a.supplier_id_fk = s.ID
WHERE 
    a.cancel_date IS NULL 
    AND (n.state_id_fk = 11 OR (m.IS_EARLY=1 AND n.STATE_ID_FK IN (10,11)))
    AND n.nodetype IN ('C', 'H', 'M');

我尝试了这个版本与不同的顺序加入，但没有得到任何差异，这是几乎相同的时间没有任何变化

CREATE OR REPLACE FORCE EDITIONABLE VIEW "MDB7_RES"."V_NODE_FOR_MAPPING" AS
SELECT 
    n.ID || a.ID AS ID, 
    n.ID AS node_id_fk, 
    n.nodeid, 
    n.nodename, 
    COALESCE(m.IS_PRELIMINARY, 0) AS SAMPLE_REPORT, 
    COALESCE(m.IS_EARLY, 0) AS EARLY_MDB, 
    m.moduleid, 
    m.version_no, 
    t.ITEM_NO, 
    t.AI, 
    s.SUPPLIER_NO, 
    s.COUNT_ADDRESS, 
    n.change_date, 
    n.row_version, 
    a.ASSIGNMENT_DATE, 
    a.ID AS ASSIGNMENT_ID_FK, 
    a.ASSIGNMENT_MODE,
    (
        SELECT MAX(bm.CHANGE_DATE) 
        FROM T_BOM_MAPPING bm 
        JOIN T_BOM_POSITION bp ON bm.BOM_POSITION_ID_FK = bp.ID 
        WHERE bp.SACHNR = t.ITEM_NO
    ) AS MAPPING_DATE
FROM 
    T_BMW_MDB_ASSIGNMENT a 
    JOIN T_NODE n ON a.node_id_fk = n.ID 
    JOIN T_TAIS_PART t ON a.part_id_fk = t.ID 
    JOIN T_GPS_SUPPLIER s ON a.supplier_id_fk = s.ID
    LEFT OUTER JOIN T_MODULE m ON n.module_id_fk = m.ID 
WHERE 
    a.cancel_date IS NULL 
    AND (n.state_id_fk = 11 OR (m.IS_EARLY = 1 AND n.STATE_ID_FK IN (10, 11)))
    AND n.nodetype IN ('C', 'H', 'M');

它工作得很慢，因为连接和额外的选择作为返回值。怎么才能让它更快？此外，它返回大约1.000.000行，所以我猜有很多数据和连接查询需要很多时间。
解释计划
enter image description here

oracle

来源：https://stackoverflow.com/questions/77006046/how-can-i-improve-the-view

1条答案

按热度按时间

r7knjye21#

SQL是一种声明性语言。您告诉DBMS要查找哪些数据，而不是使用哪种算法来查找数据。这意味着更改联接的顺序不能对执行计划产生任何影响，因为您仍然告诉DBMS获取相同的数据。
理想情况下，这将适用于查询的任何更改-只要您查询相同的数据，DBMS就应该提出相同的计划。在真实的世界中，优化器并不聪明到可以检测到所有的查询重写，但是Oracle的优化器在这方面非常出色。看看执行计划。似乎优化器已经看到，

SELECT
    ...,
    (
        SELECT max(bm.change_date) 
        FROM t_bom_mapping bm 
        JOIN t_bom_position bp ON bm.bom_position_id_fk = bp.id 
        WHERE bp.sachnr = t.item_no
    ) AS mapping_date
FROM ...

是一样的

SELECT
    ...,
    xyz.mapping_date
FROM ...
LEFT OUTER JOIN
(
    SELECT bp.sachnr, max(bm.change_date) AS mapping_date
    FROM t_bom_mapping bm 
    JOIN t_bom_position bp ON bm.bom_position_id_fk = bp.id 
    GROUP BY bp.sachnr
) xyz ON xyz.sachnr = t.item_no

这意味着为了更快地获得查询而重写工作查询通常是没有意义的。
这个规则也有例外，因为我们知道优化器并不完美。例如，优化器可以很好地猜测它可以通过n.state_id_fk = 11访问多少行，但在猜测(m.IS_EARLY=1 AND n.STATE_ID_FK IN (10,11))甚至期望的组合(n.state_id_fk = 11 OR (m.IS_EARLY=1 AND n.STATE_ID_FK IN (10,11)))的行数时存在问题。在这种情况下，将查询分为两部分可能会有所帮助，从而提示优化器：

SELECT ...
WHERE a.cancel_date IS NULL AND n.nodetype IN ('C', 'H', 'M') AND n.state_id_fk = 11
UNION ALL
SELECT ...
WHERE a.cancel_date IS NULL AND n.nodetype IN ('C', 'H', 'M') AND n.state_id_fk = 10 AND m.is_early = 1

这可能会也可能不会帮助优化器获得更好的计划。它现在可能会有所帮助，但在未来版本的DBMS中可能会变得多余。
为了加快查询速度，我们更愿意做的是提供适当的索引。在您的查询中，您有三个主要的限制条件（因为m.is_early = 1仅适用于特定的上下文）：

a.cancel_date IS NULL
n.节点类型IN（'C'，'H'，'M'）
n.state_id_fk IN（10，11）

最具限制性的标准是什么？当访问的数据非常小（可能只有表行的4%或更少）时，索引是有意义的。这是否符合标准之一？几乎所有的数据都被取消了，除了一小部分？节点类型是一些只出现在表的1%中的罕见类型吗？10号和11号是超级稀有的吗？它们都不是真正的限制，那么索引就不会有多大帮助，因为几乎所有的行都必须被连接。如果您有这样的限制因素，那么创建索引。
假设状态ID 10和11非常罕见。我们将首先重写WHERE子句，使其清楚地适用于所有行（如果优化器本身没有看到这一点）：

WHERE n.state_id_fk IN (10, 11)
  AND n.nodetype IN ('C', 'H', 'M')
  AND (n.state_id_fk = 11 OR m.is_early = 1)
  AND a.cancel_date IS NULL

我们看到限制条件n.state_id_fk IN (10, 11)，甚至在同一个表上有条件（n.nodetype IN ('C', 'H', 'M')），我们使用这些条件来查找t_node行，特别是它们的ID和它们的module_id_fk，以便连接到其他表。因此，指数为

CREATE INDEX idx1 ON t_node (state_id_fk, nodetype, id, module_id_fk);

要从t_node到t_bmw_mdb_assignment，并从那里使用其part_id_fk和supplier_id_fk：

CREATE INDEX idx2 ON t_bmw_mdb_assignment (node_id_fk, part_id_fk, supplier_id_fk);

要访问t_tais_part和t_gps_supplier，您只需要它们的ID上的主键索引，您应该已经有了。

赞(0）回复(0）举报 2023-10-16

我来回答

oracle 如何改善视野？

1条答案

相关问题

热门标签

最新问答