需要通过避免并集来优化查询.请帮助其他选项查询如下

Question

SELECT  image_name
    ,   document_id
    ,   document_name
    ,   document_category
    ,   workflow_state_name
    ,   container_name
    ,   modelyear
    ,   build_phase
    ,   revision_level
    ,   owner
    ,   container_id
    ,   document_description description
FROM container_document_view
WHERE document_id IS NOT NULL
UNION
SELECT DISTINCT 'icon_folder.gif' image_name
            ,   container_id document_id
            ,   container_name document_name
            ,   '' document_category
            ,   workflow_state_name
            ,   container_name
            ,   modelyear
            ,   build_phase
            ,   revision_level
            ,   owner
            ,   container_id
            ,   container_description description
FROM container_details_view
WHERE container_id NOT IN (
        SELECT container_id
        FROM document
    )
UNION
SELECT  image_name
    ,   document_id
    ,   document_name
    ,   document_category
    ,   workflow_state_name
    ,   container_name
    ,   modelyear
    ,   build_phase
    ,   revision_level
    ,   owner
    ,   container_id
    ,   document_description description
FROM container_link_view
WHERE container_id IS NOT NULL
ORDER BY container_name, container_id, document_name, modelyear, build_phase, revision_level

Answer 1

为什么要避免它？

如果速度太慢，您可以检查是否可以改为UNION ALL，这样可以避免昂贵的DISTINCT。

此外，当您将NOT IN重写为NOT EXISTS时，它可能更高效。