分页SQL走全表扫描导致TEMP耗尽
来源：互联网 发布：国产机器人仿真软件 编辑：程序博客网 时间：2024/04/29 11:13
.查看SQL信息:select * from (select a.cert_type, a.cert_no, a.cust_name, e.oper_no, to_char(c.vou_start_no + instr(c.vou_status, '0') - 1, 'fm00000000') as vou, e.bran_code from comr_cifbinfo a, comc_box b, comr_clerk_vou c, auto_comc_clerk e where a.cert_type = '1' and substr(rtrim(a.cert_no), 18, 1) is not null and a.cert_no not in ('330721197211217006X', '1302011981101070315') and a.valid_flag = '0' and not exists (select * from comr_cifacctno ta where ta.cust_no = a.cust_no) and c.vou_status like '%0%' and b.bran_code = e.bran_code and c.bran_code = e.bran_code and a.bank_corp_code = c.bank_corp_code and b.box_no = c.box_no and b.oper_no = e.oper_no and c.vou_status like '%0%' and c.vou_type = '11' and c.sub_vou_type = '0' and e.post_no in ('12002', '12402') and e.flag = '0' order by vou asc) where rownum < 2;对应的执行计划为:Plan hash value: 2629361789 -------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |-------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 85 | | 16722 (2)| 00:03:21 ||* 1 | COUNT STOPKEY | | | | | | || 2 | VIEW | | 266 | 22610 | | 16722 (2)| 00:03:21 ||* 3 | SORT ORDER BY STOPKEY | | 266 | 68096 | | 16722 (2)| 00:03:21 ||* 4 | HASH JOIN | | 266 | 68096 | | 16721 (2)| 00:03:21 || 5 | TABLE ACCESS FULL | COMC_BOX | 1013 | 23299 | | 5 (0)| 00:00:01 ||* 6 | HASH JOIN | | 21563 | 4906K| | 16715 (2)| 00:03:21 ||* 7 | TABLE ACCESS FULL | AUTO_COMC_CLERK | 642 | 19902 | | 9 (0)| 00:00:01 ||* 8 | HASH JOIN | | 3594 | 708K| | 16705 (2)| 00:03:21 ||* 9 | TABLE ACCESS FULL | COMR_CLERK_VOU | 124 | 11904 | | 1278 (2)| 00:00:16 ||* 10 | HASH JOIN ANTI | | 289 | 30634 | 2488K| 15427 (2)| 00:03:06 ||* 11 | TABLE ACCESS FULL | COMR_CIFBINFO | 28899 | 2144K| | 7735 (2)| 00:01:33 || 12 | INDEX FAST FULL SCAN| CIFACCTNO_IDX2 | 2110K| 60M| | 3326 (2)| 00:00:40 |------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter(ROWNUM<2) 3 - filter(ROWNUM<2) 4 - access("B"."BRAN_CODE"="E"."BRAN_CODE" AND "B"."BOX_NO"="C"."BOX_NO" AND "B"."OPER_NO"="E"."OPER_NO") 6 - access("C"."BRAN_CODE"="E"."BRAN_CODE") 7 - filter(("E"."POST_NO"='12002' OR "E"."POST_NO"='12402') AND "E"."FLAG"='0') 8 - access("A"."BANK_CORP_CODE"="C"."BANK_CORP_CODE") 9 - filter("C"."VOU_TYPE"=11 AND "C"."VOU_STATUS" LIKE '%0%' AND "C"."VOU_STATUS" IS NOT NULL AND "C"."SUB_VOU_TYPE"=0) 10 - access("TA"."CUST_NO"="A"."CUST_NO") 11 - filter(SUBSTR(RTRIM("A"."CERT_NO"),18,1) IS NOT NULL AND "A"."CERT_TYPE"='1' AND "A"."VALID_FLAG"='0' AND "A"."CERT_NO"<>'330721197211217006X' AND "A"."CERT_NO"<>'1302011981101070315')此SQL是典型的分页SQL,按照COMR_CLERK_VOU的to_char(c.vou_start_no + instr(c.vou_status, '0') - 1, 'fm00000000') as vou进行排序,这个走全表扫描,则会对整个表进行排序在取一条,实际上取一条不需要对整个表排序,分页语句不能出现SORT ORDER BY STOPKEY。3.创建索引和添加hints：create index idx_comr_clerk_vou on comr_clerk_vou(vou_type,sub_vou_type,to_char(vou_start_no + instr(vou_status, '0') - 1, 'fm00000000'))select * from (select /*+ index_asc(c idx_comr_clerk_vou) */ a.cert_type, a.cert_no, a.cust_name, e.oper_no, to_char(c.vou_start_no + instr(c.vou_status, '0') - 1, 'fm00000000') as vou, e.bran_code from comr_cifbinfo a, comc_box b, comr_clerk_vou c, auto_comc_clerk e where a.cert_type = '1' and substr(rtrim(a.cert_no), 18, 1) is not null and a.cert_no not in ('330721197211217006X', '1302011981101070315') and a.valid_flag = '0' and not exists (select * from comr_cifacctno ta where ta.cust_no = a.cust_no) and c.vou_status like '%0%' and b.bran_code = e.bran_code and c.bran_code = e.bran_code and a.bank_corp_code = c.bank_corp_code and b.box_no = c.box_no and b.oper_no = e.oper_no and c.vou_status like '%0%' and c.vou_type = '11' and c.sub_vou_type = '0' and e.post_no in ('12002', '12402') and e.flag = '0' order by vou asc) where rownum < 2;此时查看执行计划:Plan hash value: 2236318696 ---------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 85 | 790 (1)| 00:00:10 ||* 1 | COUNT STOPKEY | | | | | || 2 | VIEW | | 1 | 85 | 790 (1)| 00:00:10 || 3 | NESTED LOOPS ANTI | | 1 | 256 | 790 (1)| 00:00:10 || 4 | NESTED LOOPS | | 100 | 22600 | 589 (1)| 00:00:08 || 5 | NESTED LOOPS | | 1 | 150 | 361 (1)| 00:00:05 || 6 | NESTED LOOPS | | 168 | 19992 | 284 (1)| 00:00:04 ||* 7 | TABLE ACCESS BY INDEX ROWID| COMR_CLERK_VOU | 124 | 11904 | 232 (0)| 00:00:03 ||* 8 | INDEX RANGE SCAN | IDX_COMR_CLERK_VOU | 280 | | 3 (0)| 00:00:01 ||* 9 | TABLE ACCESS FULL | COMC_BOX | 12 | 276 | 4 (0)| 00:00:01 ||* 10 | TABLE ACCESS BY INDEX ROWID | AUTO_COMC_CLERK | 1 | 31 | 1 (0)| 00:00:01 ||* 11 | INDEX UNIQUE SCAN | AUTO_COMC_CLERK_INX | 1 | | 0 (0)| 00:00:01 ||* 12 | TABLE ACCESS BY INDEX ROWID | COMR_CIFBINFO | 100 | 7600 | 229 (1)| 00:00:03 ||* 13 | INDEX RANGE SCAN | CIFB_IDX2 | 101 | | 128 (2)| 00:00:02 ||* 14 | INDEX RANGE SCAN | CIFACCTNO_IDX2 | 2110K| 60M| 2 (0)| 00:00:01 |--------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter(ROWNUM<2) 7 - filter("C"."VOU_STATUS" LIKE '%0%' AND "C"."VOU_STATUS" IS NOT NULL) 8 - access("C"."VOU_TYPE"=11 AND "C"."SUB_VOU_TYPE"=0) 9 - filter("B"."BOX_NO"="C"."BOX_NO") 10 - filter(("E"."POST_NO"='12002' OR "E"."POST_NO"='12402') AND "E"."FLAG"='0' AND "B"."BRAN_CODE"="E"."BRAN_CODE" AND "C"."BRAN_CODE"="E"."BRAN_CODE") 11 - access("B"."OPER_NO"="E"."OPER_NO") 12 - filter("A"."VALID_FLAG"='0') 13 - access("A"."CERT_TYPE"='1' AND "A"."BANK_CORP_CODE"="C"."BANK_CORP_CODE") filter(SUBSTR(RTRIM("A"."CERT_NO"),18,1) IS NOT NULL AND "A"."CERT_NO"<>'330721197211217006X' AND "A"."CERT_NO"<>'1302011981101070315' AND "A"."BANK_CORP_CODE"="C"."BANK_CORP_CODE") 14 - access("TA"."CUST_NO"="A"."CUST_NO")此时SORT ORDER BY STOPKEY消失,通过索引访问最理想的情况只需要访问一条数据就停止索引扫描,这样节省了大量的排序工作。 0 0
- 分页SQL走全表扫描导致TEMP耗尽
- 使用ajax进行分页查询因连接池耗尽导致请求被挂起（备忘）
- sql temp
- 导致全表扫描的SQL
- 一段导致栈耗尽的C代码
- 实验一：inode耗尽导致磁盘故障
- 密码过期导致Oracle process耗尽问题
- thinkphp 分页编码出错 导致之后分页sql查询乱码
- sql中的or，哪些场景会导致全表扫描
- ssh分页时服务器资源耗尽问题
- sort merge join导致temp被爆菊
- db2由于分页大小导致sql语句无法执行.
- 解决 SQL Server 耗尽内存的情况
- 解决 SQL Server 耗尽内存的情况
- java.sql.SQLException: 结果集已耗尽
- Linux inode耗尽导致图片/文件无法上传
- HP UNIX 11.31因为内存耗尽导致ORA-12549错误
- hp-ux 11.31因为内存耗尽导致ORA-12549错误
- divide and conquer
- qt里button相关
- instancetype和id的区别
- 面试经验以及职位要求--Java开发（不知道是哪个学长的经验了）
- python 服务器 select 游戏
- 分页SQL走全表扫描导致TEMP耗尽
- C++无法解析的外部符号的3种可能
- Python Tkinter Hello，Tkinter
- NSString asscii格式(2进制) 转 utf8格式——解决iOS自己处理http socket数据，遇到Transfer-Encoding: chunked时
- qt定时器
- 使用 Python 的 jsonschema 对 json 做验证
- ECharts，发现数据可视化之美！
- android PorterDuffXfermode ，PorterDuff.Mode 使用 以及Porter-Duff规则详解
- deep_c++::多态，虚函数的一点理解
