如果视图名称不断变化，但查询结构保持不变，那么如何“全局”调优SQL查询，使其由视图中的记录驱动？

Question

我有一个水晶报表运行在一个应用程序中，由于一个低效的查询需要15分钟，因此需要很长时间才能运行。我们运行的是Oracle 19.4。CURSOR_SHARING = FORCE用于数据库，这对于每个供应商都是必需的。请参阅下面的查询。

问题是，查询中的视图名称(如下例中的TW_RPT_11263_7833_199916 )会根据应用程序中运行的查询进行更改，以提供经过筛选的记录ID列表。每次基于不同的应用程序查询运行报告时，都会根据特定视图的选择标准使用不同的SQL ID。

因此，可以生成SQL配置文件，但它只适用于一个查询/一个视图。即使使用FORCE选项生成SQL Profile，当它具有不同的视图名称TW_RPT_####_#时也不会使查询更快，并且它没有使用v$sql中看到的sql_profile。

向查询添加提示效果很好；查询在1秒内运行(参见下面的SQL )。然而，每个用户都有不同的视图名称，这意味着应用提示只适用于一个视图和特定的查询ID。我也不知道如何可能注入这个提示；它是Crystal报表。另外，我不知道是否可以在模式匹配中使用提示，比如/*+ USE_HASH(TW_RPT_%) */，或者是否可以使用其他一些技术来根据视图名称更改提示。

PR表有两百万行，而视图只有几行，因此视图需要驱动查询。

带提示USE_HASH的查询用时<1秒，不带提示的查询用时15分钟：

SELECT /*+ USE_HASH(TW_RPT_11263_7833_199916)*/ "PR"."ID", "PR"."NAME", "TW_V_IMPACT_LEVEL"."S_VALUE", "PROJECT"."NAME", "PR_1"."ID", "PROJECT_1"."NAME", "PR_1"."NAME", "PR_STATUS_TYPE_1"."NAME", "PR_STATUS_TYPE"."NAME", "PR_1"."PARENT_ID", "PROJECT_2"."NAME", "TW_RPT_11263_7833_199916"."ID", "TW_V_DESCRIPTION"."TEXT", "TW_V_MATERIAL_CONTINUATION_DEC"."TEXT", "TW_V_DESCRIPTION_1"."TEXT", "TW_V_JUSTIFICATION"."TEXT", "TW_V_CLOSURE_SUMMARY"."TEXT", "TW_V_QI_CLOSURE_SUMMARY"."TEXT" FROM   (((((((((((((("TRACKWISE_OWNER"."PR" "PR" LEFT OUTER JOIN "TRACKWISE_OWNER"."PR" "PR_1" ON "PR"."ID"="PR_1"."ROOT_PARENT_ID") LEFT OUTER JOIN "TRACKWISE_OWNER"."TW_V_DESCRIPTION" "TW_V_DESCRIPTION" ON "PR"."ID"="TW_V_DESCRIPTION"."PR_ID") LEFT OUTER JOIN "TRACKWISE_OWNER"."TW_V_MATERIAL_CONTINUATION_DEC" "TW_V_MATERIAL_CONTINUATION_DEC" ON "PR"."ID"="TW_V_MATERIAL_CONTINUATION_DEC"."PR_ID") LEFT OUTER JOIN "TRACKWISE_OWNER"."TW_V_IMPACT_LEVEL" "TW_V_IMPACT_LEVEL" ON "PR"."ID"="TW_V_IMPACT_LEVEL"."PR_ID") LEFT OUTER JOIN "TRACKWISE_OWNER"."PROJECT" "PROJECT" ON "PR"."PROJECT_ID"="PROJECT"."ID") LEFT OUTER JOIN "TRACKWISE_OWNER"."PR_STATUS_TYPE" "PR_STATUS_TYPE" ON "PR"."STATUS_TYPE"="PR_STATUS_TYPE"."ID") LEFT OUTER JOIN "TRACKWISE_OWNER"."TW_V_CLOSURE_SUMMARY" "TW_V_CLOSURE_SUMMARY" ON "PR"."ID"="TW_V_CLOSURE_SUMMARY"."PR_ID") LEFT OUTER JOIN "TRACKWISE_OWNER"."TW_V_QI_CLOSURE_SUMMARY" "TW_V_QI_CLOSURE_SUMMARY" ON "PR"."ID"="TW_V_QI_CLOSURE_SUMMARY"."PR_ID") LEFT OUTER JOIN "TRACKWISE_OWNER"."PROJECT" "PROJECT_1" ON "PR_1"."PROJECT_ID"="PROJECT_1"."ID") LEFT OUTER JOIN "TRACKWISE_OWNER"."TW_V_DESCRIPTION" "TW_V_DESCRIPTION_1" ON "PR_1"."ID"="TW_V_DESCRIPTION_1"."PR_ID") LEFT OUTER JOIN "TRACKWISE_OWNER"."PR_STATUS_TYPE" "PR_STATUS_TYPE_1" ON "PR_1"."STATUS_TYPE"="PR_STATUS_TYPE_1"."ID") LEFT OUTER JOIN "TRACKWISE_OWNER"."PR" "PR_2" ON "PR_1"."PARENT_ID"="PR_2"."ID") LEFT OUTER JOIN "TRACKWISE_OWNER"."TW_V_JUSTIFICATION" "TW_V_JUSTIFICATION" ON "PR_1"."ID"="TW_V_JUSTIFICATION"."PR_ID") LEFT OUTER JOIN "TRACKWISE_OWNER"."PROJECT" "PROJECT_2" ON "PR_2"."PROJECT_ID"="PROJECT_2"."ID") INNER JOIN "TRACKWISE_OWNER"."TW_RPT_11263_7833_199916" "TW_RPT_11263_7833_199916" ON "PR"."ID"="TW_RPT_11263_7833_199916"."ID" WHERE  ("PROJECT"."NAME"='Quality Investigation - SC' OR "PROJECT"."NAME"='Quality Issue')

无论视图的名称是什么(TW_RPT_#-#)，我正在寻找任何想法来帮助Oracle找出具有这种结构的查询的最佳联接顺序。可以肯定地假设，视图的行数总是比PR表少得多。

以下是应用程序在运行报告之前根据最终用户在应用程序查询中指定的内容创建的示例视图：

**TW_RPT_11263_7833_199916:**
  CREATE OR REPLACE FORCE EDITIONABLE VIEW "TRACKWISE_OWNER"."TW_RPT_11263_7833_199916" ("ID") DEFAULT COLLATION "USING_NLS_COMP"  AS 
  SELECT DISTINCT PR.id 
FROM 
    pr, project , Project_member, Group_member 
WHERE 
    project.id = pr.project_id AND 
    pr.id IN (
        SELECT 
            pr_addtl_data.pr_id 
        FROM 
            pr_addtl_data 
        WHERE 
            pr_addtl_data.pr_id = pr.id AND 
            pr_addtl_data.data_field_id = 573 AND 
            pr_addtl_data.n_value IN (6164231)
    ) AND PR.project_parent_id IN(366,279,395,396) AND Project_member.project_id = PR.project_parent_id AND Group_member.project_member_id = Project_member.id AND Project_member.person_rel_id = 13836 AND ((Project_member.view_all = 1) OR (Project_member.view_self_created = 1 and PR.created_by_rel_id = 13836) OR (Project_member.view_assigned_to = 1 and PR.responsible_rel_id = 13836) OR (Project_member.view_group_created = 1 and PR.user_group_id = Group_member.user_group_id) OR (Project_member.view_by_entity = 1 and PR.entity_id = 1251));

视图的结果是两个记录in，如下所示，返回的单位为毫秒: 2012202和2012397

Answer 1

一种选择是使用Command作为报告的数据源。参数可以控制命令语法中使用的表/视图。

Answer 2

只需对别名使用更好的命名:使用比TW_RPT_11263_7833_199916更常见的名称。例如，使用INNER JOIN "TRACKWISE_OWNER"."TW_RPT_11263_7833_199916" "TW_RPT_JOINED"代替INNER JOIN "TRACKWISE_OWNER"."TW_RPT_11263_7833_199916" "TW_RPT_11263_7833_199916"，并将其用于提示

SELECT /*+ USE_HASH(TW_RPT_JOINED)*/ 
    "PR"."ID", 
    "PR"."NAME", 
    "TW_V_IMPACT_LEVEL"."S_VALUE", 
    "PROJECT"."NAME", 
    "PR_1"."ID", 
    "PROJECT_1"."NAME", 
    "PR_1"."NAME", 
    "PR_STATUS_TYPE_1"."NAME", 
    "PR_STATUS_TYPE"."NAME", 
    "PR_1"."PARENT_ID", 
    "PROJECT_2"."NAME", 
    "TW_RPT_JOINED"."ID", 
    "TW_V_DESCRIPTION"."TEXT", 
    "TW_V_MATERIAL_CONTINUATION_DEC"."TEXT", 
    "TW_V_DESCRIPTION_1"."TEXT", 
    "TW_V_JUSTIFICATION"."TEXT", 
    "TW_V_CLOSURE_SUMMARY"."TEXT", 
    "TW_V_QI_CLOSURE_SUMMARY"."TEXT" 
FROM   
    (
        (
            (
                (
                    (
                        (
                            (
                                (
                                    (
                                        (
                                            (
                                                (
                                                    (
                                                        ("TRACKWISE_OWNER"."PR" "PR" 
                                                        LEFT OUTER JOIN "TRACKWISE_OWNER"."PR" "PR_1" 
                                                            ON "PR"."ID"="PR_1"."ROOT_PARENT_ID"
                                                        ) 
                                                    LEFT OUTER JOIN "TRACKWISE_OWNER"."TW_V_DESCRIPTION" "TW_V_DESCRIPTION" 
                                                        ON "PR"."ID"="TW_V_DESCRIPTION"."PR_ID"
                                                    ) 
                                                    LEFT OUTER JOIN "TRACKWISE_OWNER"."TW_V_MATERIAL_CONTINUATION_DEC" "TW_V_MATERIAL_CONTINUATION_DEC" 
                                                        ON "PR"."ID"="TW_V_MATERIAL_CONTINUATION_DEC"."PR_ID"
                                                ) 
                                                LEFT OUTER JOIN "TRACKWISE_OWNER"."TW_V_IMPACT_LEVEL" "TW_V_IMPACT_LEVEL" 
                                                    ON "PR"."ID"="TW_V_IMPACT_LEVEL"."PR_ID"
                                            ) 
                                            LEFT OUTER JOIN "TRACKWISE_OWNER"."PROJECT" "PROJECT" 
                                                ON "PR"."PROJECT_ID"="PROJECT"."ID"
                                        ) 
                                        LEFT OUTER JOIN "TRACKWISE_OWNER"."PR_STATUS_TYPE" "PR_STATUS_TYPE" 
                                            ON "PR"."STATUS_TYPE"="PR_STATUS_TYPE"."ID"
                                    ) 
                                    LEFT OUTER JOIN "TRACKWISE_OWNER"."TW_V_CLOSURE_SUMMARY" "TW_V_CLOSURE_SUMMARY" 
                                        ON "PR"."ID"="TW_V_CLOSURE_SUMMARY"."PR_ID"
                                ) 
                                LEFT OUTER JOIN "TRACKWISE_OWNER"."TW_V_QI_CLOSURE_SUMMARY" "TW_V_QI_CLOSURE_SUMMARY" 
                                    ON "PR"."ID"="TW_V_QI_CLOSURE_SUMMARY"."PR_ID"
                            ) 
                            LEFT OUTER JOIN "TRACKWISE_OWNER"."PROJECT" "PROJECT_1" 
                                ON "PR_1"."PROJECT_ID"="PROJECT_1"."ID"
                        ) 
                        LEFT OUTER JOIN "TRACKWISE_OWNER"."TW_V_DESCRIPTION" "TW_V_DESCRIPTION_1" 
                            ON "PR_1"."ID"="TW_V_DESCRIPTION_1"."PR_ID"
                    ) 
                    LEFT OUTER JOIN "TRACKWISE_OWNER"."PR_STATUS_TYPE" "PR_STATUS_TYPE_1" 
                        ON "PR_1"."STATUS_TYPE"="PR_STATUS_TYPE_1"."ID"
                ) 
                LEFT OUTER JOIN "TRACKWISE_OWNER"."PR" "PR_2" 
                    ON "PR_1"."PARENT_ID"="PR_2"."ID"
            ) 
            LEFT OUTER JOIN "TRACKWISE_OWNER"."TW_V_JUSTIFICATION" "TW_V_JUSTIFICATION" 
                ON "PR_1"."ID"="TW_V_JUSTIFICATION"."PR_ID"
        ) 
        LEFT OUTER JOIN "TRACKWISE_OWNER"."PROJECT" "PROJECT_2" 
            ON "PR_2"."PROJECT_ID"="PROJECT_2"."ID"
    ) 
    INNER JOIN "TRACKWISE_OWNER"."TW_RPT_11263_7833_199916" "TW_RPT_JOINED" 
        ON "PR"."ID"="TW_RPT_JOINED"."ID" 
WHERE  ("PROJECT"."NAME"='Quality Investigation - SC' OR "PROJECT"."NAME"='Quality Issue')

PS。“很棒的”SQL生成器--为什么有这么多(((()))))...