以前，我发现执行计划中的"成本"可以很好地指示相对执行时间。为什么这种情况不同？我认为执行计划具有相关性吗？我具体可以尝试提高v_test性能的什么？

谢谢你。

使用Oracle 10g，我在下面定义了一个简单的查询视图

create or replace view v_test as
  select distinct u.bo_id as bo_id, upper(trim(d.dept_id)) as dept_id
  from
      cust_bo_users u
  join cust_bo_roles r on u.role_name=r.role_name
  join cust_dept_roll_up_tbl d on 
                            (r.region is null or trim(r.region)=trim(d.chrgback_reg)) and 
                            (r.prod_id is null or trim(r.prod_id)=trim(d.prod_id)) and
                            (r.div_id is null or trim(r.div_id)=trim(d.div_id )) and
                            (r.clus_id is null or trim(r.clus_id )=trim( d.clus_id)) and
                            (r.prod_ln_id is null or trim(r.prod_ln_id)=trim(d.prod_ln_id)) and
                            (r.dept_id is null or trim(r.dept_id)=trim(d.dept_id))

定义为替换以下视图

create or replace view v_bo_secured_detail
  select distinct Q.BO_ID, Q.DEPT_ID
  from (select U.BO_ID BO_ID, UPPER(trim(D.DEPT_ID)) DEPT_ID
        from CUST_BO_USERS U, CUST_BO_ROLES R, CUST_DEPT_ROLL_UP_TBL D
        where U.ROLE_NAME = R.ROLE_NAME and
                R.ROLE_LEVEL = 'REGION' and
                trim(R.REGION) = UPPER(trim(D.CHRGBACK_REG))
        union all
        select U.BO_ID BO_ID, UPPER(trim(D.DEPT_ID)) DEPT_ID
        from CUST_BO_USERS U, CUST_BO_ROLES R, CUST_DEPT_ROLL_UP_TBL D
        where U.ROLE_NAME = R.ROLE_NAME and
                R.ROLE_LEVEL = 'RG_PROD' and
                trim(R.REGION) = UPPER(trim(D.CHRGBACK_REG)) and
                trim(R.PROD_ID) = UPPER(trim(D.PROD_ID))
        union all
        select U.BO_ID BO_ID, UPPER(trim(D.DEPT_ID)) DEPT_ID
        from CUST_BO_USERS U, CUST_BO_ROLES R, CUST_DEPT_ROLL_UP_TBL D
        where U.ROLE_NAME = R.ROLE_NAME and
                R.ROLE_LEVEL = 'PROD' and
                trim(R.PROD_ID) = UPPER(trim(D.PROD_ID))
        union all
        select U.BO_ID BO_ID, UPPER(trim(D.DEPT_ID)) DEPT_ID
        from CUST_BO_USERS U, CUST_BO_ROLES R, CUST_DEPT_ROLL_UP_TBL D
        where U.ROLE_NAME = R.ROLE_NAME and
                R.ROLE_LEVEL = 'DIV' and
                trim(R.DIV_ID) = UPPER(trim(D.DIV_ID))
        union all
        select U.BO_ID BO_ID, UPPER(trim(D.DEPT_ID)) DEPT_ID
        from CUST_BO_USERS U, CUST_BO_ROLES R, CUST_DEPT_ROLL_UP_TBL D
        where U.ROLE_NAME = R.ROLE_NAME and
                R.ROLE_LEVEL = 'RG_DIV' and
                trim(R.REGION) = UPPER(trim(D.CHRGBACK_REG)) and
                trim(R.DIV_ID) = UPPER(trim(D.DIV_ID))
        union all
        select U.BO_ID BO_ID, UPPER(trim(D.DEPT_ID)) DEPT_ID
        from CUST_BO_USERS U, CUST_BO_ROLES R, CUST_DEPT_ROLL_UP_TBL D
        where U.ROLE_NAME = R.ROLE_NAME and
                R.ROLE_LEVEL = 'CLUS' and
                trim(R.CLUS_ID) = UPPER(trim(D.CLUS_ID))
        union all
        select U.BO_ID BO_ID, UPPER(trim(D.DEPT_ID)) DEPT_ID
        from CUST_BO_USERS U, CUST_BO_ROLES R, CUST_DEPT_ROLL_UP_TBL D
        where U.ROLE_NAME = R.ROLE_NAME and
                R.ROLE_LEVEL = 'RG_CLUS' and
                trim(R.REGION) = UPPER(trim(D.CHRGBACK_REG)) and
                trim(R.CLUS_ID) = UPPER(trim(D.CLUS_ID))
        union all
        select U.BO_ID BO_ID, UPPER(trim(D.DEPT_ID)) DEPT_ID
        from CUST_BO_USERS U, CUST_BO_ROLES R, CUST_DEPT_ROLL_UP_TBL D
        where U.ROLE_NAME = R.ROLE_NAME and
                R.ROLE_LEVEL = 'PROD_LN' and
                trim(R.PROD_LN_ID) = UPPER(trim(D.PROD_LN_ID))
        union all
        select U.BO_ID BO_ID, UPPER(trim(R.DEPT_ID)) DEPT_ID
        from CUST_BO_USERS U, CUST_BO_ROLES R
        where U.ROLE_NAME = R.ROLE_NAME and
                R.ROLE_LEVEL = 'DEPT') Q

目的是消除对ROLE_LEVEL列的依赖。

为了简单起见，v_test的执行计划明显低于v_bo_secured_detail

select * from <view> where bo_id='value'

查询。并且在实际查询中使用时明显更低

select CT_REPORT.RPT_KEY,
         CT_REPORT_ENTRY.RPE_KEY,
         CT_REPORT_ENTRY.CUSTOM16,
         Exp_Sub_Type.value,
         min(CT_REPORT_PAYMENT_CONF.PAY_DATE),
         CT_REPORT.PAID_DATE
  from CT_REPORT,
      <VIEW> SD,
      CT_REPORT_ENTRY,
      CT_LIST_ITEM_LANG Exp_Sub_Type,
      CT_REPORT_PAYMENT_CONF,
      CT_STATUS_LANG Payment_Status
  where (CT_REPORT_ENTRY.RPT_KEY = CT_REPORT.RPT_KEY) and
        (Payment_Status.STAT_KEY = CT_REPORT.PAY_KEY) and
        (Exp_Sub_Type.LI_KEY = CT_REPORT_ENTRY.CUSTOM9 and Exp_Sub_Type.LANG_CODE = 'en') and
        (CT_REPORT.RPT_KEY = CT_REPORT_PAYMENT_CONF.RPT_KEY) and
        (SD.BO_ID = 'JZHU9') and
        (SD.DEPT_ID = UPPER(CT_REPORT_ENTRY.CUSTOM5)) and
        (Payment_Status.name = 'Payment Confirmed' and (Payment_Status.LANG_CODE = 'en') and
        CT_REPORT.PAID_DATE > to_date('01/01/2008', 'mm/dd/yyyy') and Exp_Sub_Type.value != 'Korea')
  group by CT_REPORT.RPT_KEY,
            CT_REPORT_ENTRY.RPE_KEY,
            CT_REPORT_ENTRY.CUSTOM16,
            Exp_Sub_Type.value,
            CT_REPORT.PAID_DATE

执行时间完全不同。 v_test视图花费15个小时，而v_bo_secured_detail花费几秒钟。

谢谢所有回应的人

这是我要记住的一个。表达式的理论和数学与基于硬件的执行的现实相遇的地方。哎哟。

解决方案

低成本-高执行时间的一个方面是，当我们查看大型数据集时，整体上做事通常在整体上效率更高，而如果想要快速获得结果，则做事效率更高尽可能少的工作来获得第一张唱片。在大型设备上工作时，重复进行小操作以使外观快速响应的效果不太可能得到良好的结果。

很多时候，当我们想要快速获得结果时，USE_NL优化器提示会有所帮助。

另外，在测试视图中，它依赖于IS NULL ... IS NULL不能使用索引，也不能在'table-side'参数上使用例如trim的功能。

执行计划是理论，执行时间是现实。

该计划向我们展示了引擎如何执行查询，但是某些步骤可能会导致解决查询的工作量过大。使用" x为null或者x = y"的气味很难闻。如果r和d是大表，我们可能会受到某种组合爆炸的打击，并且请求在大量磁盘块列表中无休止地循环。我想我们在执行过程中会看到很多I / O。

另一方面，联合的选择又短又甜，因此可能会重用较早选择仍然存在的许多磁盘块，并且/或者我们可以从对相同磁盘块的读取中获得一定程度的并行性。

还在各处使用trim()和upper()看起来有点可疑。如果数据非常不干净，则不时需要进行一些定期的房屋清洁，这样我们就可以说" x = y"并知道它可以工作了。

更新：我们询问了一些改进v_test的技巧。清理数据，以便没有必要trim()和upper()。它们可能会阻止使用索引(尽管这也会影响联合的选择版本)。

如果无法摆脱" x为null或者x = y"，则y = nvl(x，'does-not-exist')可能具有更好的特性(假设'does-not-exist'是'can' t发生" id值)。

我们是否已在所有基础表上收集了优化器统计信息？没有它们，优化者的估计可能与现实完全背道而驰。

如Oracle文档所述，成本是相对于特定执行计划的估计成本。当我们调整查询时，可以相对于计算成本的特定执行计划进行更改。有时是戏剧性的。

v_test性能的问题在于，除了执行嵌套循环外，Oracle无法想到执行它的方法，对于每个cust_bo_roles，扫描所有cust_dept_roll_up_tbl以查找匹配项。如果表的大小为n和m，则需要花费n * m的时间，这对于大型表来说很慢。相比之下，设置v_bo_secured_detail使其成为一系列查询，每个查询都可以通过其他某种机制来完成。 (Oracle有一个可能使用的数字，包括使用索引，动态建立哈希表或者对数据集进行排序并将它们合并。这些操作都是O(n * log(n))或者更好。)快速查询是快速的。

尽管很痛苦，但是如果我们希望此查询速度很快，则需要像上一个查询一样将其分解。

当我们说"查询计划较低"时，我们是说它较短，还是实际成本估算较低？替换视图的一个明显问题是，与cust_dept_roll_up_tbl的联接几乎只使用不可索引的条件(索引可以满足" is null"测试，但是涉及对每个参数调用trim的条件不能满足)，因此计划者必须对表进行至少一次(可能是几次)连续扫描才能满足查询要求。

我不确定Oracle是否有此限制，但是许多数据库只能对包含的表进行一次索引扫描，因此，即使我们将联接条件清理为可索引的，它也只能满足一个索引条件。扫描，其余部分则必须使用顺序扫描。