- 最后登录
- 2015-5-21
- 在线时间
- 100 小时
- 威望
- 0
- 金钱
- 345
- 注册时间
- 2012-12-19
- 阅读权限
- 10
- 帖子
- 99
- 精华
- 0
- 积分
- 0
- UID
- 824
|
1#
发表于 2013-1-16 09:30:03
|
查看: 4340 |
回复: 5
原始语句
SELECT SUM(nvl(rcl.amount, 0)) amount, ooh.sold_to_org_id customer_id
FROM oe_order_headers_all ooh,oe_order_lines_all ool, ra_interface_lines_all rcl
WHERE ooh.header_id = ool.header_id
AND ool.open_flag = 'N'
AND ool.line_id = rcl.interface_line_attribute6
AND ooh.org_id in (103, 104)
GROUP BY ooh.sold_to_org_id
执行计划
1
2 --------------------------------------------------------------------------------------------
3 | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
4 --------------------------------------------------------------------------------------------
5 | 0 | SELECT STATEMENT | | 16480 | 627K| | 46479 (2)|
6 | 1 | HASH GROUP BY | | 16480 | 627K| | 46479 (2)|
7 |* 2 | HASH JOIN | | 148K| 5649K| 5360K| 46467 (2)|
8 |* 3 | HASH JOIN | | 148K| 3621K| 3480K| 38877 (2)|
9 | 4 | TABLE ACCESS FULL| RA_INTERFACE_LINES_ALL | 148K| 1738K| | 4411 (1)|
10 |* 5 | TABLE ACCESS FULL| OE_ORDER_LINES_ALL | 940K| 11M| | 33164 (2)|
11 |* 6 | TABLE ACCESS FULL | OE_ORDER_HEADERS_ALL | 253K| 3459K| | 7010 (1)|
12 --------------------------------------------------------------------------------------------
13
14 Predicate Information (identified by operation id):
15 ---------------------------------------------------
16
17 2 - access("OOH"."HEADER_ID"="OOL"."HEADER_ID")
18 3 - access("OOL"."LINE_ID"=TO_NUMBER("RCL"."INTERFACE_LINE_ATTRIBUTE6"))
19 5 - filter("OOL"."OPEN_FLAG"='N')
20 6 - filter("OOH"."ORG_ID"=103 OR "OOH"."ORG_ID"=104)
21
22 Note
23 -----
24 - 'PLAN_TABLE' is old version
我的问题是:
关于第5步中的OPEN_FLAG"='N'造成了全表扫描,我的oe_order_lines_all ool这张表约96万行数据,
SELECT COUNT(*) FROM (SELECT open_flag FROM oe_order_lines_all WHERE open_flag = 'N')返回94万行数据,确实是通过全表要比索引要好
但是问题是:这么一条设计百万行左右的语句要耗费半分钟左右才能返回结果,有没有办法通过修改语句或者访问路径能让他快一点的方法
注:
select * from v$version;
1 Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
2 PL/SQL Release 10.2.0.3.0 - Production
3 CORE 10.2.0.3.0 Production
4 TNS for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Productio
5 NLSRTL Version 10.2.0.3.0 - Production
|
|