oracle - 如何通过数据库 View 重用带有子查询因子的sql-6ren

oracle - 如何通过数据库 View 重用带有子查询因子的sql

转载作者：行者123 更新时间：2023-12-04 02:17:27

将以下使用子查询(with data as 子句)的 sql 语句转换为在数据库 View 中使用它的最佳实践是什么。据我所知，数据库 View 中不支持 with data as 子句(已编辑:Oracle 支持公用表表达式)，但在我的例子中，子查询分解提供了性能优势。如果我使用 Common Table Expression 创建数据库 View ，那么这个优势就失去了。

请看我的例子:

查询说明

a_table数百万个条目，通过 select 语句选择了几千个。

anchor_table对于 a_table 中的每个条目，在 anchor_table 中存在一个对应的条目。通过此表在运行时确定恰好一行作为 anchor 。请参见下面的示例。

horizon_table对于每个选择，在运行时只确定一个条目(a_table 选择的所有条目都具有相同的 horizon_id)

请注意:这是一个高度简化的 sql，目前运行良好。

实际上，将 20 多个表连接在一起以获得 data 的结果。where 子句要复杂得多。需要 horizon_table 和 anchor_table 的更多列来准备子查询中的 where 条件和结果列表，即将这些表移动到主查询不是解决方案。

with data as (
  select
  a_table.id,
  a_table.descr,
  horizon_table.offset,
  case
    when anchor_table.a_date = trunc(sysdate) then
    1
    else
    0
  end as anchor,
  row_number() over( 
  order by a_table.a_position_field) as position
  from a_table
  join anchor_table on (anchor_table.id = a_table.anchor_id)
  join horizon_table on (horizon_table.id = a_table.horizon_id)
  where a_table.a_value between 1 and 10000
)
select * 
from data d
where d.position between ( 
    select d1.position - d.offset 
    from data d1 
    where d1.anchor = 1) 
  and ( 
    select d2.position + d.offset 
    from data d2 
    where d2.anchor = 1)

with data as 选择示例:

id   descr   offset  anchor   position
1    bla     3       0        1
2    blab    3       0        2
5    dfkdj   3       0        3
4    dld     3       0        4
6    oeroe   3       1        5
3    blab    3       0        6
9    dfkdj   3       0        7
14   dld     3       0        8
54   oeroe   3       0        9
...

select * from data的结果

id   descr   offset  anchor   position
2    blab    3       0        2
5    dfkdj   3       0        3
4    dld     3       0        4
6    oeroe   3       1        5
3    blab    3       0        6
9    dfkdj   3       0        7
14   dld     3       0        8

即结果是 anchor 行和上方和下方的树行。

如何在数据库 View 中实现相同的目的？

由于性能问题，我的尝试失败了:

创建数据的 View with data as select above如上所述使用此 View

select * 
    from data d
    where d.position between ( 
        select d1.position - d.offset 
        from data d1 
        where d1.anchor = 1) 
      and ( 
        select d2.position + d.offset 
        from data d2 
        where d2.anchor = 1)

感谢您的任何建议:-)

修正案

如果我按照第一条评论中的建议创建 View ，则会遇到相同的性能问题。 Oracle 不使用子查询来限制结果。

这是我的生产查询的执行计划(请点击图片)

a) SQL

b) 查看

这是我的测试用例的执行计划

-- Create Testdata table with ~ 1,000,000 entries
insert into a_table
  (id, descr, a_position_field, anchor_id, horizon_id, a_value)
  select level, 'data' || level, mod(level, 10), level, 1, level
    from dual
  connect by level <= 999999;

insert into anchor_table
  (id, a_date)
  select level, trunc(sysdate) - 500000 + level
    from dual
  connect by level <= 999999;

insert into horizon_table (id, offset) values (1, 50);

commit;

-- Create view
create or replace view testdata_vw as
  with data as
   (select a_table.id,
           a_table.descr,
           a_table.a_value,
           horizon_table.offset,
           case
             when anchor_table.a_date = trunc(sysdate) then
              1
             else
              0
           end as anchor,
           row_number() over(order by a_table.a_position_field) as position
      from a_table
      join anchor_table
        on (anchor_table.id = a_table.anchor_id)
      join horizon_table
        on (horizon_table.id = a_table.horizon_id))
  select *
    from data d
   where d.position between
         (select d1.position - d.offset from data d1 where d1.anchor = 1) and
         (select d2.position + d.offset from data d2 where d2.anchor = 1);

-- Explain plan of subquery factoring select statement
explain plan for
  with data as
   (select a_table.id,
           a_table.descr,
           a_value,
           horizon_table.offset,
           case
             when anchor_table.a_date = trunc(sysdate) then
              1
             else
              0
           end as anchor,
           row_number() over(order by a_table.a_position_field) as position
      from a_table
      join anchor_table
        on (anchor_table.id = a_table.anchor_id)
      join horizon_table
        on (horizon_table.id = a_table.horizon_id)

     where a_table.a_value between 500000 - 500 and 500000 + 500)
  select *
    from data d
   where d.position between
         (select d1.position - d.offset from data d1 where d1.anchor = 1) and
         (select d2.position + d.offset from data d2 where d2.anchor = 1);

select plan_table_output
  from table(dbms_xplan.display('plan_table', null, null));

/*

Note: Size of SYS_TEMP_0FD9D6628_284C5768 ~ 1000 rows

Plan hash value: 1145408420

----------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                             |     1 |    62 |  1791   (2)| 00:00:31 |
|   1 |  TEMP TABLE TRANSFORMATION |                             |       |       |            |          |
|   2 |   LOAD AS SELECT           | SYS_TEMP_0FD9D6628_284C5768 |       |       |            |          |
|   3 |    WINDOW SORT             |                             |    57 |  6840 |  1785   (2)| 00:00:31 |
|*  4 |     HASH JOIN              |                             |    57 |  6840 |  1784   (2)| 00:00:31 |
|*  5 |      TABLE ACCESS FULL     | A_TABLE                     |    57 |  4104 |  1193   (2)| 00:00:21 |
|   6 |      MERGE JOIN CARTESIAN  |                             |  1189K|    54M|   586   (2)| 00:00:10 |
|   7 |       TABLE ACCESS FULL    | HORIZON_TABLE               |     1 |    26 |     3   (0)| 00:00:01 |
|   8 |       BUFFER SORT          |                             |  1189K|    24M|   583   (2)| 00:00:10 |
|   9 |        TABLE ACCESS FULL   | ANCHOR_TABLE                |  1189K|    24M|   583   (2)| 00:00:10 |
|* 10 |   FILTER                   |                             |       |       |            |          |
|  11 |    VIEW                    |                             |    57 |  3534 |     2   (0)| 00:00:01 |
|  12 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6628_284C5768 |    57 |  4104 |     2   (0)| 00:00:01 |
|* 13 |    VIEW                    |                             |    57 |   912 |     2   (0)| 00:00:01 |
|  14 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6628_284C5768 |    57 |  4104 |     2   (0)| 00:00:01 |
|* 15 |    VIEW                    |                             |    57 |   912 |     2   (0)| 00:00:01 |
|  16 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6628_284C5768 |    57 |  4104 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("HORIZON_TABLE"."ID"="A_TABLE"."HORIZON_ID" AND 
              "ANCHOR_TABLE"."ID"="A_TABLE"."ANCHOR_ID")
   5 - filter("A_TABLE"."A_VALUE">=499500 AND "A_TABLE"."A_VALUE"<=500500)
  10 - filter("D"."POSITION">= (SELECT "D1"."POSITION"-:B1 FROM  (SELECT + CACHE_TEMP_TABLE 
              ("T1")  "C0" "ID","C1" "DESCR","C2" "A_VALUE","C3" "OFFSET","C4" "ANCHOR","C5" "POSITION" FROM 
              "SYS"."SYS_TEMP_0FD9D6628_284C5768" "T1") "D1" WHERE "D1"."ANCHOR"=1) AND "D"."POSITION"<= 
              (SELECT "D2"."POSITION"+:B2 FROM  (SELECT + CACHE_TEMP_TABLE ("T1")  "C0" "ID","C1" 
              "DESCR","C2" "A_VALUE","C3" "OFFSET","C4" "ANCHOR","C5" "POSITION" FROM 
              "SYS"."SYS_TEMP_0FD9D6628_284C5768" "T1") "D2" WHERE "D2"."ANCHOR"=1))
  13 - filter("D1"."ANCHOR"=1)
  15 - filter("D2"."ANCHOR"=1)

Note
-----
   - dynamic sampling used for this statement (level=4)

*/

-- Explain plan of database view
explain plan for
  select *
    from testdata_vw
   where a_value between 500000 - 500 and 500000 + 500;

select plan_table_output
  from table(dbms_xplan.display('plan_table', null, null));

/*

Note: Size of SYS_TEMP_0FD9D662A_284C5768 ~ 1000000 rows

Plan hash value: 1422141561

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                             |  2973 |   180K|       | 50324   (1)| 00:14:16 |
|   1 |  VIEW                       | TESTDATA_VW                 |  2973 |   180K|       | 50324   (1)| 00:14:16 |
|   2 |   TEMP TABLE TRANSFORMATION |                             |       |       |       |            |          |
|   3 |    LOAD AS SELECT           | SYS_TEMP_0FD9D662A_284C5768 |       |       |       |            |          |
|   4 |     WINDOW SORT             |                             |  1189K|   136M|   147M| 37032   (1)| 00:10:30 |
|*  5 |      HASH JOIN              |                             |  1189K|   136M|       |  6868   (1)| 00:01:57 |
|   6 |       TABLE ACCESS FULL     | HORIZON_TABLE               |     1 |    26 |       |     3   (0)| 00:00:01 |
|*  7 |       HASH JOIN             |                             |  1189K|   106M|    38M|  6860   (1)| 00:01:57 |
|   8 |        TABLE ACCESS FULL    | ANCHOR_TABLE                |  1189K|    24M|       |   583   (2)| 00:00:10 |
|   9 |        TABLE ACCESS FULL    | A_TABLE                     |  1209K|    83M|       |  1191   (2)| 00:00:21 |
|* 10 |    FILTER                   |                             |       |       |       |            |          |
|* 11 |     VIEW                    |                             |  1189K|    70M|       |  4431   (1)| 00:01:16 |
|  12 |      TABLE ACCESS FULL      | SYS_TEMP_0FD9D662A_284C5768 |  1189K|    81M|       |  4431   (1)| 00:01:16 |
|* 13 |     VIEW                    |                             |  1189K|    18M|       |  4431   (1)| 00:01:16 |
|  14 |      TABLE ACCESS FULL      | SYS_TEMP_0FD9D662A_284C5768 |  1189K|    81M|       |  4431   (1)| 00:01:16 |
|* 15 |     VIEW                    |                             |  1189K|    18M|       |  4431   (1)| 00:01:16 |
|  16 |      TABLE ACCESS FULL      | SYS_TEMP_0FD9D662A_284C5768 |  1189K|    81M|       |  4431   (1)| 00:01:16 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("HORIZON_TABLE"."ID"="A_TABLE"."HORIZON_ID")
   7 - access("ANCHOR_TABLE"."ID"="A_TABLE"."ANCHOR_ID")
  10 - filter("D"."POSITION">= (SELECT "D1"."POSITION"-:B1 FROM  (SELECT + CACHE_TEMP_TABLE ("T1")  
              "C0" "ID","C1" "DESCR","C2" "A_VALUE","C3" "OFFSET","C4" "ANCHOR","C5" "POSITION" FROM 
              "SYS"."SYS_TEMP_0FD9D662A_284C5768" "T1") "D1" WHERE "D1"."ANCHOR"=1) AND "D"."POSITION"<= (SELECT 
              "D2"."POSITION"+:B2 FROM  (SELECT + CACHE_TEMP_TABLE ("T1")  "C0" "ID","C1" "DESCR","C2" 
              "A_VALUE","C3" "OFFSET","C4" "ANCHOR","C5" "POSITION" FROM "SYS"."SYS_TEMP_0FD9D662A_284C5768" "T1") "D2" 
              WHERE "D2"."ANCHOR"=1))
  11 - filter("A_VALUE">=499500 AND "A_VALUE"<=500500)
  13 - filter("D1"."ANCHOR"=1)
  15 - filter("D2"."ANCHOR"=1)

Note
-----
   - dynamic sampling used for this statement (level=4)
*/

sqlfiddle

解释sql计划http://www.sqlfiddle.com/#!4/6a7022/3

讲解平面图http://www.sqlfiddle.com/#!4/6a7022/2

最佳答案

您需要编写一个 View 定义，它将所有可能的 a_value 可选范围作为两列返回，start_a_value 和 end_a_value，以及落入每个开始/结束范围的所有记录。换句话说，正确的 View 定义应该在逻辑上描述一个 |n^3|。在 a_table 中给定 n 行的结果集。

然后将该 View 查询为:

SELECT * FROM testdata_vw WHERE START_A_VALUE = 4950 AND END_A_VALUE = 5050;

此外，您对“数据”的多次引用是不必要的；相同的逻辑可以通过附加的分析功能来传递。

最终 View 定义:

CREATE OR REPLACE VIEW testdata_vw AS
SELECT  * 
FROM    
    (
    SELECT  T.*, 
            MAX(CASE WHEN ANCHOR=1 THEN POSITION END) 
                OVER (PARTITION BY START_A_VALUE, END_A_VALUE) ANCHOR_POS 
    FROM 
        (
        SELECT  S.A_VALUE                                       START_A_VALUE, 
                E.A_VALUE                                       END_A_VALUE, 
                B.ID                                            ID,
                B.DESCR                                         DESCR,
                HORIZON_TABLE.OFFSET                            OFFSET,
                CASE
                  WHEN ANCHOR_TABLE.A_DATE = TRUNC(SYSDATE) 
                    THEN 1
                    ELSE 0
                  END                                           ANCHOR,
                ROW_NUMBER() 
                    OVER(PARTITION BY S.A_VALUE, E.A_VALUE 
                         ORDER BY B.A_POSITION_FIELD)           POSITION
        FROM 
                A_TABLE S 
        JOIN    A_TABLE E 
                    ON S.A_VALUE<E.A_VALUE 
        JOIN    A_TABLE B 
                    ON B.A_VALUE BETWEEN S.A_VALUE AND E.A_VALUE
        JOIN    ANCHOR_TABLE
                    ON  ANCHOR_TABLE.ID = B.ANCHOR_ID
        JOIN    HORIZON_TABLE
                    ON HORIZON_TABLE.ID = B.HORIZON_ID
        ) T
    ) T
WHERE POSITION BETWEEN ANCHOR_POS - OFFSET AND ANCHOR_POS+OFFSET;

编辑:SQL Fiddle with expected execution plan

我在这里看到的(明智的)计划与我在数据库中看到的计划相同；如果您得到不同的东西，请发送 fiddle 链接。

使用索引查找在“S”A_TABLE (A_VALUE = 4950) 中找到 1 行
使用索引查找在“E”A_TABLE (A_VALUE = 5050) 中找到 1 行
嵌套循环连接 #1 和 #2(1 x 1 连接，仍然是 1 行)
FTS HORIZON 表中的 1 行
笛卡尔连接 #1 和 #2(1 x 1，可以使用笛卡尔)。
使用索引查找在“B”A_TABLE 中查找值介于 4950 和 5050 之间的约 100 行。
笛卡尔连接 #5 和 #6(1 x 102，可以使用笛卡尔)。
FTS ANCHOR_TABLE 与 #7 的散列连接。
分析函数的窗口排序

关于oracle - 如何通过数据库 View 重用带有子查询因子的sql，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/33080890/

文章推荐： google-drive-api - Google 云端硬盘上传返回 - (403) 权限不足

文章推荐： string - 如何管理大字符串

文章推荐： angularjs - 将 Adal.js 与 $stateProvider 结合使用

文章推荐： wordpress - 如何从 Wordpress RSS 提要中排除某些帖子？

r - (因子)数据帧列的小写
我有这种格式的data.frame: 'data.frame': 244 obs. of 1 variable: $ names: Factor w/ 244 levels "ERA","BA
Java查询求解拉伸(stretch)因子
这就是问题: write a Java Program that accepts a String and an integer stretch factor P as parameters and
r - 基于另一列的 block 因子
该示例显示了不同工厂的产量测量值，第一列表示工厂最后一列是生产量。 factory % mutate(factory=fct_lump(factory,2)) factory produc
r - 分类变量(因子)与虚拟变量的区别
我正在使用分类变量运行回归并遇到 this question .在这里，用户想要为每个虚拟对象添加一列。这让我很困惑，因为我虽然列有很长的数据，包括使用 as.factor() 存储的所有虚拟数据。相
r - 修改 R 因子？
假设在 R 中有一个 Data.Frame 对象，其中所有字符列都已转换为因子。然后我需要“修改”与数据帧中某一行相关联的值——但将其编码为一个因子。我首先需要提取一行，所以这就是我正在做的。这是一个
r - 计算一个数字(因子)在每组中出现的次数
利用下面的可重现数据， dat head(dat) Bin Number 1 1 3 2 1 5 3 1 4 4 1 5 5 1
R:删除具有预定出现频率和自动更新因子水平的变量(因子)的行
我有一组包含多个变量的数据。其中一个变量 - 阶乘包含组的名称 - A、B、C 等。其余变量是数字。 > data1 Group Value 1 A 23 2 A
r - 虚拟变量到 R 中的单个分类变量(因子)
我有一组编码为二项式的变量。 Pre VALUE_1 VALUE_2 VALUE_3 VALUE_4 VALUE_5 VALUE_6 VALUE_7 VALUE_8 1 1 0
r - 将各种虚拟/逻辑变量从 R 中的名称转换为单个分类变量/因子
我的问题与 this one 非常相似和 this other one ，但我的数据集有点不同，我似乎无法使这些解决方案起作用。如果我误解了什么并且这个问题是多余的，请原谅。我有一个这样的数据集:
r - ggplot2 因子 x 变量打破 geom_area
我一直在尝试生成一个带有离散 x 变量的堆积面积图(因为我想显示财政年度，即“2013/14”，而不是日历年)。但是，将 x 轴变量转换为一个因子会阻止在最终图表中呈现 geom。有解决办法吗？ l
java - 具有 1.0 maxLoad 因子、时间复杂度的哈希表
只是一个简单的问题来确认我的想法，使用负载因子 1.0 的哈希表的复杂性将是二次时间，用以下符号 O(n^2) 表示。这是因为必须不断调整大小并一遍又一遍地插入。如果我错了，请纠正我。谢谢最佳
r - 因子 MSZoning 具有新级别 NA(数据中有新因子，但线性回归模型中没有)
我正在尝试使用 kaggle 的一些数据集进行房价预测。这是我的代码 library(ggplot2) dataset=read.csv('train(1).csv') dataset_test=r
javascript - 如何构建操纵 DOM 的 Angular 因子/服务
我正在用 Angular 构建一个类似咆哮的 UI。我想将其公开为工厂(或服务)，以使其在我的 Controller 中可用。调用 Growl.add 将导致 DOM 发生变化，所以看起来我应该有一个
python - 将 Pandas Dataframe 列转换为 R 因子
我正在尝试将 pandas 数据框的一列转换为因数，因为我试图在 R 中调用的函数需要因数。 pandas2ri.activate() #second column of labels has
R plotly x 轴字符/因子(组合数字和 -)。绘图仅显示仅包含数字的轴。漏洞？
我正在尝试使用 plotly 绘制一个以字符串(组合数)作为 x 轴的条形图。 (“1”、“2”、“3”、“4 - 5”、“6 - 8”、“9 - 13”、“14 - 21”、“22 - 34”、“3
因子()和 NAs 的 R caret/rfe 变量选择
我有一个包含 NA 的数据集。此外，它还有一些列需要factors()。我正在使用 caret 包中的 rfe() 函数来选择变量。似乎 rfe() 中的 functions= 参数使用 lmF
r - 如何防止 write.csv 将 POSIXct、日期和时间类更改回字符/因子？
我有一个 .csv 文件，其中每个字段用于日期时间、日期和时间。最初它们都是字符字段，我已经相应地转换了它们。在我的代码结束时，如果我这样做: str(data) 我会得到 datetime: P
r - R 中的 NaiveBayes 无法预测 - 因子 (0) 级别 :
我有一个如下所示的数据集: data.flu data.flu chills runnyNose headache fever flu 1 1 0 M
c++ - QMainWindow::splitDockWidget 的 QDockWidget 拉伸(stretch)因子？
我正在使用 QMainWindow 在 C++ 中手动布置 Qt 应用程序。我希望在屏幕底部有两个并排停靠的小部件，但我希望它们具有不成比例的宽度。目前，我只能让它们具有相同的宽度。有没有办法设置拉伸
java - 使用 Java 计算 MOS、抖动和 R 因子
我需要通过在两个主机(2 个 Java 进程)之间发送合成调用来计算 VOIP 质量。我应该找出 MOS、抖动和 R 因子(VOIP 质量指标)。根据目前的研究，我发现我应该在两台主机之间发送 RTP

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

oracle - 如何通过数据库 View 重用带有子查询因子的sql