Oracle 关于: 重新审视参数化视图

嗨，康纳，

谢谢你对我的问题的回答。我有一个关于综合观点的后续问题。我做了以下建议:

create or replace
2查看V as
3选择 “T1” tname，t1。* 从T1
4联合全部
5选择 “T2” tname，t2。* 从T2
6联合全部
7选择 “T3” 名称，t3。* 从T3
8联盟全部
9选择 “T4” tname，t4。* 从T4

t3 and t4 tables each have about 500M rows. However I observe that 
  select * from V where tname='T4" and V.col1 = 'foo'
is slower than
  select * from t4 where t4.col1 = 'foo' 
-- note table t4 has an index 
复制

这是意料之中的还是我错过了什么？

这里有更多详细信息，其中对合并视图的查询比直接在基表上执行等效项要慢，因此

>EXPLAIN PLAN SET STATEMENT_ID = 'testview' FOR select count(*) from p4_1777_rev where dfile is not null
>SELECT * FROM TABLE(dbms_xplan.display)

Plan hash value: 2349164066
 
-------------------------------------
| Id  | Operation                | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------
|   0 | SELECT STATEMENT         |                   |     1 |   517 |  2285K  (1)| 07:37:12 |
|   1 |  SORT AGGREGATE          |                   |     1 |   517 |            |          |
|   2 |   VIEW                   | P4_REV_VIEW       |   628M|   302G|  2285K  (1)| 07:37:12 |
|   3 |    UNION-ALL             |                   |       |       |            |          |
|*  4 |     FILTER               |                   |       |       |            |          |
|   5 |      INDEX FAST FULL SCAN| P4_1666_REV_DFILE |   519M|    45G|  1674K  (1)| 05:34:51 |
|*  6 |     FILTER               |                   |       |       |            |          |
|   7 |      INDEX FAST FULL SCAN| P4_1700_REV_DFILE |    22M|  2010M| 73400   (1)| 00:14:41 |
|   8 |     INDEX FAST FULL SCAN | P4_1777_REV_DFILE |   628M|    62G|  2285K  (1)| 07:37:12 |
|*  9 |     FILTER               |                   |       |       |            |          |
|  10 |      INDEX FAST FULL SCAN| P4_1888_REV_DFILE |   120M|  9770M|   354K  (1)| 01:10:59 |
|* 11 |     FILTER               |                   |       |       |            |          |
|  12 |      INDEX FAST FULL SCAN| P4_1999_REV_DFILE |    76M|  9140M|   321K  (1)| 01:04:16 |
|* 13 |     FILTER               |                   |       |       |            |          |
|  14 |      INDEX FAST FULL SCAN| P4_7004_REV_DFILE |    27M|  1889M| 70084   (1)| 00:14:02 |
-------------------------------------
 
谓词信息 (由操作id标识):
---------------------------------------------------
 
   4 - filter(NULL IS NOT NULL)
6-过滤器 (NULL不为NULL)
   9 - filter(NULL IS NOT NULL)
  11 - filter(NULL IS NOT NULL)
  13 - filter(NULL IS NOT NULL)

vs.

>EXPLAIN PLAN SET STATEMENT_ID = 'testview' FOR select count(*) from p4_1777_rev where dfile is not null
>SELECT * FROM TABLE(dbms_xplan.display)

Plan hash value: 4011998830

----------------------
| Id  | Operation             | Name          | Rows  | Cost (%CPU)| Time     |
----------------------
|   0 | SELECT STATEMENT      |               |     1 |   297K  (2)| 00:59:26 |
|   1 |  SORT AGGREGATE       |               |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| SYS_C00288006 |   628M|   297K  (2)| 00:59:26 |
----------------------
复制

基表p4_1777_rev中的行数是632M。
合并视图p4_rev_view中的行数为2B。
合并视图p4_rev_view创建为:

"SELECT 1666 P4PORT, p1666."REVID",p1666."DFILE",p1666."REV",p1666."TYPE",p1666."ACTION",p1666."CHANGE",p1666."DATE",p1666."MODTIME" from P4_1666_REV p1666
union all
SELECT 1700 P4PORT, p1700."REVID",p1700."DFILE",p1700."REV",p1700."TYPE",p1700."ACTION",p1700."CHANGE",p1700."DATE",p1700."MODTIME" from P4_1700_REV p1700
union all
SELECT 1777 P4PORT, p1777."REVID",p1777."DFILE",p1777."REV",p1777."TYPE",p1777."ACTION",p1777."CHANGE",p1777."DATE",p1777."MODTIME" from P4_1777_REV p1777
union all
SELECT 1888 P4PORT, p1888."REVID",p1888."DFILE",p1888."REV",p1888."TYPE",p1888."ACTION",p1888."CHANGE",p1888."DATE",p1888."MODTIME" from P4_1888_REV p1888
union all
SELECT 1999 P4PORT, p1999."REVID",p1999."DFILE",p1999."REV",p1999."TYPE",p1999."ACTION",p1999."CHANGE",p1999."DATE",p1999."MODTIME" from P4_1999_REV p1999
union all
SELECT 7004 P4PORT, p7004."REVID",p7004."DFILE",p7004."REV",p7004."TYPE",p7004."ACTION",p7004."CHANGE",p7004."DATE",p7004."MODTIME" from P4_7004_REV p7004"
复制

你问

嗨，康纳和克里斯 (欢迎玛丽亚)，

我已经阅读了一些有关存储过程和ref cursor的条目，以支持参数化视图。然而我的情况略有不同。

在我的情况下，我给了n个表 (TBL_1，TBL_2，...)，每个表都使用相同的模式定义，分别保存产品1，产品2，... 的数百万条记录。出于抽象目的，是否有可能编写一个流水线函数/存储过程，该过程将根据参数选择基础表。

在报告 (示例) 中:
从表中选择 * (whatsup(1)) w左连接w.col1 = s.col2上的其他表s-whatsup将动态返回TBL_1。

即使这是可能的，它会表现出来吗？作为替代方案，我已经尝试创建一个视图合并所有表以简化事情，但查询太慢。

非常感谢!

我们说...

我会重新审视你的整合方法。如果定义视图以便优化器可以消除表，那么事情应该以最佳方式运行，例如


SQL> drop表t1级联约束清除;

表掉了。

SQL> drop表t2级联约束清除;

表掉了。

SQL> drop表t3级联约束清除;

表掉了。

SQL> drop表t4级联约束清除;

表掉了。

SQL> 从dba_objects创建表t1作为select *;

创建的表。

SQL> 从dba_objects创建表t2作为select *;

创建的表。

SQL> 从dba_objects创建表t3作为select *;

创建的表。

SQL> 从dba_objects创建表t4为select *;

创建的表。

SQL>
SQL> 创建或替换
2查看V as
3选择 “T1” tname，t1。* 从T1
4联合全部
5选择 “T2” tname，t2。* 从T2
6联合全部
7选择 “T3” 名称，t3。* 从T3
8联盟全部
9选择 “T4” tname，t4。* 从T4
10/

视图创建。

SQL>
SQL> 设置自动跟踪仅解释
SQL> 从v中选择 *
2其中tname = 't2';

执行计划
-
计划哈希值: 289899379

--------------------
| Id | 操作 | 名称 | 行 | 字节 | 成本 (% CPU)| 时间 |
--------------------
| 0 | SELECT语句 | | 104K | 36M | 312 (1)| 00:00:01 |
| 1 | 视图 | V | 104K | 36M | 312 (1)| 00:00:01 |
| 2 | 联盟-所有 |
| * 3 | 过滤器 |
| 4 | 表访问完全 | T1 | 104K | 11M | 312 (1)| 00:00:01 |
| 5 | 表访问完全 | T2 | 104K | 11M | 312 (1)| 00:00:01 |
| * 6 | 过滤器 |
| 7 | 表访问完全 | T3 | 104K | 11M | 312 (1)| 00:00:01 |
| * 8 | 过滤器 |
| 9 | 表访问完全 | T4 | 104K | 11M | 312 (1)| 00:00:01 |
--------------------

谓词信息 (由操作id标识):
---------------------------------------------------

3-过滤器 (NULL不为NULL)
6-过滤器 (NULL不为NULL)
8-过滤器 (NULL不为NULL)

SQL> 设置自动跟踪关闭

“我错过了什么吗？”

是的 :-)

您的脚本向我们展示了每个测试的性能和响应时间!

向我们展示:

设置时间开启
将自动跟踪设置为on
从V中选择 *，其中tname = 't4 "和V.col1 = 'foo'
从t4中选择 *，其中t4.col1 = 'foo'


=

你没有显示执行，你有显示执行计划。执行将 * 看起来 * 更复杂，因为我们正在做过滤器等，但请参阅 “null不是null” 行-这意味着大多数东西永远不会运行。

* 运行 * 他们，对结果进行时间