原创

Oracle子查询因子化(公共表表达式)及递归

原SQL:

select *
  from (select product, country, year, week, inventory, sale, receipts
          from sales_fact model return updated rows partition by(product, country) dimension by(year, week) measures(0 inventory, sale, receipts) rules automatic
         order(inventory [ year, week ] = nvl(inventory [ cv(year), cv(week) - 1 ], 0) - sale [ cv(year), cv(week) ] + receipts [ cv(year), cv(week) ]))
 where country in ('Australia')
   and product = 'Xtend Memory'
 order by product, country, year, week;

使用with子句建立因子化子查询(便于阅读、理解):

with model_subquery as
(select product, country, year, week, inventory, sale, receipts
          from sales_fact model return updated rows partition by(product, country) dimension by(year, week) measures(0 inventory, sale, receipts) rules automatic
         order(inventory [ year, week ] = nvl(inventory [ cv(year), cv(week) - 1 ], 0) - sale [ cv(year), cv(week) ] + receipts [ cv(year), cv(week) ]))
         select * from model_subquery where country in ('Australia')
   and product = 'Xtend Memory'
 order by product, country, year, week;

Oracle 12c可以使用with子句声明并定义PL/SQL函数和存储过程。在定义之后,可以在声明这个子句的查询(包括它的任何子查询)中引用该PL/SQL函数。

with function calc_a(pn number,pm number) return number
is
begin
  return pn*pm;
  end;
  select calc_a(10,10) a10 from dual;
/

如果不是在最顶层查询之前先声明with子句,需要使用with_plsql提示。with_plsql提示并不是优化器提示。

select /*+ with_plsql */* from  
(with function calc_a(pn number,pm number) return number
is
begin
  return pn*pm;
  end;
  select calc_a(10,10) a10 from dual);
/

在测试子查询因子化的性能时,可以在因子化子查询中分别使用inline提示和materialize提示。

with model_subquery as
(select /*+ materialize */ product, country, year, week, inventory, sale, receipts
          from sales_fact model return updated rows partition by(product, country) dimension by(year, week) measures(0 inventory, sale, receipts) rules automatic
         order(inventory [ year, week ] = nvl(inventory [ cv(year), cv(week) - 1 ], 0) - sale [ cv(year), cv(week) ] + receipts [ cv(year), cv(week) ]))

materialize提示会将子查询当作临时表物化。inline提示不使用临时表转换。

递归子查询因子化(递归公共表表达式):
connect by:
select level,last_name,employee_id,manager_id from hr.employees start with manager_id is null connect by prior employee_id = manager_id order siblings by last_name;
start with a.a1 is null connect by prior a.a1 = a.a2 start with指定定位点成员(最上层),connect by指定递归成员,prior运算符将当前的a1与另一行的a2列值匹配。反复这么做就建立了一个递归查询。
level伪列保存了递归的深度值。
order siblings by针对树状SQL,按照层次结构排序。
connect by函数、运算符和伪列:

  • sys_connect_by_path 返回当前数据行的所有祖先(函数)
  • connect_by_root 返回根数据行的值(运算符)
  • prior 用来表明层级型查询,在递归子查询中不需要(运算符)
  • connect_by_iscycle 在层级中检查循环(伪列)
  • nocycle connect by的参数,与connect_by_iscycle一起使用(参数)
  • connect_by_isleaf 标识叶子数据行(伪列)
  • level 表明层级中的深度(伪列)
    level伪列经常被用来实现输出缩进。
    sys_connect_by_path返回组成层级的直到当前行的值。sys_connect_by_path(last_name,':')建立一个冒号分隔的从根到节点的层级。
    connect_by_root返回当前行的根节点。
    select level,last_name,employee_id,manager_id,connect_by_root last_name root from hr.employees start with manager_id is null connect by prior employee_id = manager_id order siblings by last_name;
    nocycle参数可以阻止发生ora-01436错误(connect by loop in user data),connect_by_iscycle显示导致错误发生的行(错误行的值为1)。
    select level,last_name,employee_id,manager_id,connect_by_iscycle from hr.employees start with manager_id is null connect by nocycle prior employee_id = manager_id order siblings by last_name;
    connect_by_isleaf用来在层级数据中识别叶子节点(没有子节点的分层树形结构中的节点)。
    RSF:
    with rsf_emp (lv,last_name,employee_id,manager_id) as
    (select 1 lv,last_name,employee_id,manager_id
    from hr.employees
    where manager_id is null
    union all
    select rsf_emp.lv+1 lv,hr.employees.last_name,hr.employees.employee_id,hr.employees.manager_id
    from hr.employees
    join rsf_emp on rsf_emp.employee_id = hr.employees.manager_id)
    search depth first by last_name set order1
    select * from rsf_emp;
    
    递归的with子句需要两个查询块:定位点成员和递归成员。这两个子查询块必须通过集合运算符union all结合在一起。定位点成员是union all之前的查询,递归成员是其后面的查询。递归子查询必须引用定义子查询--这样就进行了递归。
    不能在RSF的递归成员中使用:
  • distinct关键字或group by子句;
  • model子句;
  • 聚合函数,但在select列表中可以使用分析函数;
  • 引用query_name的子查询;
  • 引用query_name作为右表的外联结。
    RSF查询返回的列必须在with rsf_emp (lv,last_name,employee_id,manager_id)定义中声明。
    RSF查询默认情况下是search breadth first:在返回任何子数据行之前返回每一层级上的兄弟数据行。而search depth first会按照层级的顺序返回数据行。
    with rsf_emp (lv,last_name,employee_id,manager_id) as
    (select 1 lv,last_name,employee_id,manager_id
    from hr.employees
    where manager_id is null
    union all
    select rsf_emp.lv+1 lv,hr.employees.last_name,hr.employees.employee_id,hr.employees.manager_id
    from hr.employees
    join rsf_emp on rsf_emp.employee_id = hr.employees.manager_id)
    search breadth first by last_name set order1
    select * from rsf_emp;
    
    search子句中的set order1部分将order1伪列的值设置为数据行返回的顺序值。
    search子句中指定了按照last_name进行搜索,也可以是其他列、列表。这样做控制了每个层级中各行的顺序。
    RSF中可通过cycle子句实现connect by中的nocycle与connect_by_iscycle组合:
    with rsf_emp (lv,last_name,employee_id,manager_id) as
    (select 1 lv,last_name,employee_id,manager_id
    from hr.employees 
    where manager_id is null
    union all
    select rsf_emp.lv+1 lv,hr.employees.last_name,hr.employees.employee_id,hr.employees.manager_id
    from hr.employees
    join rsf_emp on rsf_emp.employee_id = hr.employees.manager_id)
    search depth first by last_name set order1
    cycle employee_id set is_cycle to 1 default 0
    select * from rsf_emp;
    
    cycle employee_id set is_cycle to 1 default 0:is_cycle是自定义列名,1是允许设置的单值字符。
正文到此结束
该篇文章的评论功能已被站长关闭