oracle 嵌套循环优化问题

allenzhang0006 2010-09-20 04:59:34
for acct in (select ucracct_cust_code,
ucracct_prem_code,
ucracct_cycl_code,
ucracct_status_ind
from ucracct -------数据50万
where ucracct_cycl_code>=vcycle_code_begin
and ucracct_cycl_code<=vcycle_code_end
and (vacct_type='%' or ucracct_status_ind=vacct_type)
)
loop
if mod(vrows,vinterval)=0 then
for shis in ( select urrshis_serv_num,
urrshis_styp_code,
urrshis_scat_code,
urrshis_action_date,
urrshis_actn_code,
urrshis_rtyp_code,
urrshis_reading,
urrshis_multiplier,
urrshis_consumption,
urrshis_dos,
urrshis_charge_date,
urrshis_chrg_calc_num
from urrshis ---数据上千万
where urrshis_prem_code=acct.ucracct_prem_code
and urrshis_cust_code=acct.ucracct_cust_code
and urrshis_action_date < sysdate - 45
and nvl(urrshis_actn_code,'*')<>'SKIP'
and nvl(urrshis_charge_date,to_date('12/31/2099','mm-dd-yyyy')) = to_date('12/31/2099','mm-dd-yyyy')
)
loop
...

这样的嵌套循环，如何优化呢？？不能写成sql，不能动数据库结构。望达人相助。
...全文
308 16 打赏 收藏 转发到动态 举报
写回复
用AI写文章
16 条回复
切换为时间正序
请发表友善的回复…
发表回复
心中的彩虹 2010-09-21
  • 打赏
  • 举报
 回复
[Quote=引用楼主 allenzhang0006 的回复:]
for acct in (select ucracct_cust_code,
ucracct_prem_code,
ucracct_cycl_code,
ucracct_status_ind
from ucracct -------数据50万
where ucracct_cycl_code>=vcycle_code_begin
and ucracct_cycl_……
[/Quote]

看可以采用数组 以及 使用bulk collect into 代替第一层循环
allenzhang0006 2010-09-21
  • 打赏
  • 举报
 回复
谢谢 提醒
下午结贴了。
minitoy 2010-09-21
  • 打赏
  • 举报
 回复
很难,看你sql不复杂.需要注意的是尽量不要在有索引的字段上用函数,这样会导致无法利用索引.
allenzhang0006 2010-09-21
  • 打赏
  • 举报
 回复
不能通过优化sql语句 达到目的吗？
allenzhang0006 2010-09-21
  • 打赏
  • 举报
 回复
谢谢 我去找找相关资料。
zhone 2010-09-21
  • 打赏
  • 举报
 回复
[Quote=引用 13 楼 allenzhang0006 的回复:]
对，看第二个游标的条件

for shis in ( select urrshis_serv_num,
urrshis_styp_code,
urrshis_scat_code,
urrshis_action_date,
urrshis_actn_code,
urrshis_rtyp_code,
urrshis_reading,
urrshis_mu……
[/Quote]
wkc168说到的 fetch bulk collect into 貌似不错。
Oracle 提供了 fetch bulk collect 来批量取游标中的数据。能在读取游标中大量数据的时候提升效率。
如果好改的话，还是用这个效果应该会更明显。
你说的这个，你可以实际试试，看会不会有明显效果。呵呵。
allenzhang0006 2010-09-21
  • 打赏
  • 举报
 回复
这样就少了一个循环了。
allenzhang0006 2010-09-21
  • 打赏
  • 举报
 回复
对，看第二个游标的条件

for shis in ( select urrshis_serv_num,
urrshis_styp_code,
urrshis_scat_code,
urrshis_action_date,
urrshis_actn_code,
urrshis_rtyp_code,
urrshis_reading,
urrshis_multiplier,
urrshis_consumption,
urrshis_dos,
urrshis_charge_date,
urrshis_chrg_calc_num
from urrshis ---数据上千万
where urrshis_prem_code=acct.ucracct_prem_code
and urrshis_cust_code=acct.ucracct_cust_code
zhone 2010-09-21
  • 打赏
  • 举报
 回复
[Quote=引用 11 楼 allenzhang0006 的回复:]
我想把这嵌套循环改成 表的等值连接 不知效果是不是一样
[/Quote]

表的等值连接？
allenzhang0006 2010-09-21
  • 打赏
  • 举报
 回复
[Quote=引用 9 楼 wkc168 的回复:]
引用楼主 allenzhang0006 的回复:
for acct in (select ucracct_cust_code,
ucracct_prem_code,
ucracct_cycl_code,
ucracct_status_ind
from ucracct -------数据50万
where ucracct_cycl_code>=vcycle_code_begin
and……
[/Quote]

我想把这嵌套循环改成 表的等值连接 不知效果是不是一样
Diza1986 2010-09-21
  • 打赏
  • 举报
 回复
[Quote=引用 9 楼 wkc168 的回复:]
引用楼主 allenzhang0006 的回复:
for acct in (select ucracct_cust_code,
ucracct_prem_code,
ucracct_cycl_code,
ucracct_status_ind
from ucracct -------数据50万
where ucracct_cycl_code>=vcycle_code_begin
and……
[/Quote]

恩 有道理
Jason-1001 2010-09-20
  • 打赏
  • 举报
 回复
来学习学习，前几天才刚刚接触Oracle
quiettown 2010-09-20
  • 打赏
  • 举报
 回复
帮顶，没有什么很好的办法。你可以试试将第一个关联与第二个关联合并起来，以期望达到减少循环的目的。所有的性能问题都是流程设计不合理搞出来的。
gelyon 2010-09-20
  • 打赏
  • 举报
 回复 


--类型转换想必要这样吧

to_date('12/31/2099','mm/dd/yyyy')
minitoy 2010-09-20
  • 打赏
  • 举报
 回复
帮你顶顶帖吧
allenzhang0006 2010-09-20
  • 打赏
  • 举报
 回复
大家随便发言 。
oracle_优化器详解
默认情况下，CBO 以最佳吞吐量为目标，这意味着 Oracle 使用尽可能少的资源去处理 被语句访问到的所有行；当然CBO 也可以用最快的响应速度来优化 SQL，这意味着Oracle 用尽可能少的资源去处理被语句访问到的第一行或前面少数行， 当然这种情况对于整个语句 来说可能消耗更多的资源。 优化器产生的执行计划会因“优化器目标”的不同而不同。如果以最佳吞吐量为目标， 结果更倾向于使用全表扫描而不是索引扫描，或者使用排序合并连接而不是嵌套循环连接； 如果以最快的响应速度为目标，其结果则通常倾向于使用索引扫描和嵌套循环连接。
oracle数据库性能调优（3）
学习ORACLE时的一些资料，分享给大家，共勉！ 上一篇文章讲解了深入了解单表执行计划，单表执行计划是理解多表执行计划的基础，文章中主要讲解嵌套循环连接。
Oracle数据库性能优化整套方法论之系统与实例优化
本套课程为Oracle数据库性能优化整套方法论的上半部课程，结合Oracle体系架构，从系统和实例优化的角度全面地介绍了Oracle数据库性能优化的基本理论和数据库性能的优化配置，通过本课程的学习，可以全面系统地掌握数据库性能优化理论体系和方法论，掌握数据库最佳性能配置和优化思路，是数据库性能工程师和DBA学习好Oracle数据库性能调优的推荐课程。
LECCO SQL Expert (智能自动SQL优化)
更优更快 人工智能自动SQL优化----------http://www.sina.com.cn 2001/12/12 17:48 中国电脑教育报文/SQL爱好者　　所谓SQL，就是指Structured Query Language(结构化查询语言)，它是目前使用最广泛的数据库语言，用来和数据库打交道，从数据库中得到用户需要的数据。但是要想熟练使用SQL语句，也不是一件简单的事，有些语句使用起来也比较麻烦。如果我们对SQL语句进行优化，那么用户使用起来 就会方便许多。　　简单来说，SQL语句的优化就是将性能低下的SQL语句转换成达到同样目的的性能优异的SQL语句。人工智能自动SQL优化就是使用人工智能技术，自动对SQL语句进行重写，找到性能最好的等效SQL语句。　　人工智能自动SQL 优化　　随着人工智能技术的发展和在数据库优化领域应用的深入，在20世纪90年代末终于出现了突破性的进展——人工智能自动SQL优化。目前在商用数据库领域LECCO TechnologyLimited(灵高公司)拥有该技术并提供使用该技术的自动优化产品——LECCO SQL Expert，其支持Oracle、Sybase、MS SQLServer和IBMDB2数据库平台。该产品针对数据库应用的开发和维护阶段提供了几个特别的模块：SQL语法优化器、PL/SQL集成化开发调试环境(IDE)、扫描器、数据库监视器等。图1 人工智能自动SQL优化示意图　　其核心模块之一“SQL语法优化器”的工作原理大致如下(如图1)：　　一条源SQL语句输入→“人工智能反馈式搜索引擎”对输入的SQL语句结合检测到的数据库结构和索引进行重写，产生N条等效的SQL语句输出→产生的N条等效SQL语句再送入“人工智能反馈式搜索引擎”进行重写，直至无法产生新的输出或搜索限额满→对 输出的SQL语句进行过滤，选出具有不同执行计划的SQL语句(即不同的执行效率)→对得到的SQL语句进行批量测试，找出性能最好的SQL语句。图2 优化前的SQL语句　　自动优化实例　　假设我们从源代码中抽取出这条SQL语句(如图2)：　　SELECTCOUNT(*)FROMEMPLOY-EE WHEREEXISTS(SELECT'X'FROM DEPARTMENTswheresEMP_DEPT=DPT_IDAND DPT_NAME LIKE'AC%')AND EMP_IDIN(SELECT SAL_EMP_IDFROM EMP_SAL_HISTB WHERESAL_SALARY＞70000) 　　按“优化”按钮后，经过十几秒，SQL Expert就完成了优化的过程，从优化细节中可以看到，它在十几秒的时间内重写产生了2267条等价的SQL语句，其中136条SQL语句有不同的执行计划(如图3)。图3 优化结果　　接下来我们可以对自动重写产生的136条具有不同执行计划的SQL语句进行批运行测试，以选出性能最佳的等效SQL语句。按下“批运行”按钮，在“终止条件”页选择“最佳运行时间SQL语句”(如图4)，按“确定”。图4 测试条件　　经过几分钟的测试运行后，我们可以发现SQL124的运行时间和反应时间最短。运行速度约有22.75倍的提升(源SQL语句运行时间为2.73秒，SQL124运行时间为0.12秒，如图5)。图5 测试结果　　我们把SQL124放入源代码中，结束一条SQL语句的优化工作。从上例可以看到，LECCO SQL Expert的自动重写技术使原来需要几小时才能完成的SQL语句的优化工作，缩减到几分钟之内就可以完成。数据库管理员和开发人员可以从繁重的SQL语句优化工作中解脱出来。　　边做边学式训练　　LECCO SQL Expert不仅能够找到最佳的SQL语句，而且提供的“边做边学式训练”还能够教会开发人员和数据库管理员如何写出性能最好的SQL语句。LECCO SQL Expert的“SQL比较器”可以标明源SQL和待选SQL之间的不同之处。LECCO SQL Expert详尽的上下文敏感帮助系统可以指出执行计划的深层含义。图6 源语句与SQL124的比较　　以上面优化的结果为例，为了查看源SQL语句和SQL124在写法上的不同，我们可以按下“比较器”按钮，对SQL124和源SQL语句进行比较。如果选择“双向比较”复选框，“SQL比较器”可以将两条互相间的不同之处以蓝色表示。当然，你也可以从 源语句和重写后的SQL语句中任选两条进行比较(如图6)。　　从比较的结果可以看到，重写得到的SQL124把第一个Exists改写成了In；在字段DPT_ID上进行了合并空字符串的操作以诱导数据库先执行子查询中的(SELECTDPT_ID||'FROMDEPART-MENTWH
oracle嵌套循环的执行计划优化
嵌套循环连接处理的两个数据集被称为外部循环（outer loop，也就是驱动数据源，driving row soulce ）和内部循环〔 inner loop ）。外部循环为左子节点，内部循环为右子节点．如图10 一6 所示，当外部循环执行一次的时候，内部循环需要针对外部循环返回的每条记录执行一次。      外部循环称为外表或者驱动表，而内部循环称为内表或者被驱动表     嵌套循环连接
基础和管理

17,377

社区成员

95,127

社区内容

发帖
与我相关
我的任务
社区描述
Oracle 基础和管理
社区管理员
  • 基础和管理社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章