多表关联的优化问题

IndependentDeveloper 2010-01-03 10:18:21
头疼的多表关联,问题描述如下:


SELECT /*+ ORDERED */ A.AAB301,A.AAB001,B.AAC001,B.AAC006,B.AAE043,B.PER_CLASS,B.PER_PROPERTY,C.AAE140,C.AAA040,
E.AAC040,E.AIC020,E.AKC010,E.ALC010,E.AMC010,E.AJC020,C.JOIN_TIME,A.AAB050,F.AAB050 AS COMP_JOIN_TIME
FROM AB01_02 A, AC01_02 B, AC02 C ,T_PER_CHARGE_RECORD D,AC04 E,AB02 F
WHERE ( A.AAB001 = B.AAB001 AND B.AAC001 = C.AAC001 AND B.AAC001 = D.AAC001 AND C.AAE140 = D.AAE140
AND B.AAC001 = E.AAC001 AND A.AAB001 = F.AAB001 AND C.AAE140 = F.AAE140 )
AND RTRIM(LTRIM(A.AAE119)) = '1' AND RTRIM(LTRIM(B.AAC008)) = '1' AND RTRIM(LTRIM(C.STATUS)) = '1' AND RTRIM(LTRIM(F.AAB051)) = '1'
AND TO_CHAR(E.AAE031,'YYYYMM') > '201001' AND TO_CHAR(E.AAE030,'YYYYMM') <= '201001'
AND SUBSTR(D.CHARGE_STATUS,1,1) = '0' AND D.EFFECT_YEAR = '2010'';
已选择73437行。
执行计划
----------------------------------------------------------
Plan hash value: 235963713
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 540 | 1996 (1)| 00:00:24 |
| 1 | NESTED LOOPS | | 2 | 540 | 1996 (1)| 00:00:24 |
| 2 | NESTED LOOPS | | 9 | 2142 | 1987 (1)| 00:00:24 |
| 3 | NESTED LOOPS | | 9 | 1620 | 1933 (1)| 00:00:24 |
| 4 | NESTED LOOPS | | 41 | 5535 | 1482 (1)| 00:00:18 |
|* 5 | HASH JOIN | | 41 | 3813 | 1236 (1)| 00:00:15 |
|* 6 | TABLE ACCESS FULL | AB01_02 | 11 | 352 | 14 (0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | AC01_02 | 1569 | 95709 | 1221 (1)| 00:00:15 |
|* 8 | TABLE ACCESS BY INDEX ROWID| AC02 | 1 | 42 | 6 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | IND_AC02_AAC001 | 4 | | 2 (0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID | T_PER_CHARGE_RECORD | 1 | 45 | 11 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | IND_T_PER_CHARGE_RECORD_AAC001 | 14 | | 2 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID | AC04 | 1 | 58 | 6 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | IND_AC04_AAC001 | 8 | | 2 (0)| 00:00:01 |
|* 14 | TABLE ACCESS BY INDEX ROWID | AB02 | 1 | 32 | 1 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | PK_AB02 | 1 | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("A"."AAB001"="B"."AAB001")
6 - filter(RTRIM(LTRIM("A"."AAE119"))='1')
7 - filter(RTRIM(LTRIM("B"."AAC008"))='1')
8 - filter(RTRIM(LTRIM("C"."STATUS"))='1')
9 - access("B"."AAC001"="C"."AAC001")
10 - filter("D"."EFFECT_YEAR"='2010' AND SUBSTR("D"."CHARGE_STATUS",1,1)='0' AND
"C"."AAE140"="D"."AAE140")
11 - access("B"."AAC001"="D"."AAC001")
12 - filter(TO_CHAR(INTERNAL_FUNCTION("E"."AAE031"),'YYYYMM')>'201001' AND
TO_CHAR(INTERNAL_FUNCTION("E"."AAE030"),'YYYYMM')<='201001')
13 - access("B"."AAC001"="E"."AAC001")
14 - filter(RTRIM(LTRIM("F"."AAB051"))='1')
15 - access("A"."AAB001"="F"."AAB001" AND "C"."AAE140"="F"."AAE140")
统计信息
----------------------------------------------------------
1324 recursive calls
0 db block gets
7225001 consistent gets
61298 physical reads
0 redo size
6974059 bytes sent via SQL*Net to client
54314 bytes received via SQL*Net from client
4897 SQL*Net roundtrips to/from client
31 sorts (memory)
0 sorts (disk)
73437 rows processed

SQL> select table_name,index_name,column_name,column_position
2 from user_ind_columns
where table_name in ('AB01_02','AC01_02','AC02','T_PER_CHARGE_RECORD','AC04','AB02')
4 order by index_name,column_position;
TABLE_NAME INDEX_NAME COLUMN_NAME pos
------------------------------ ------------------------------ ---------------------------------------------------- ----
AC02 IND_AC02_AAC001 AAC001 1
AC04 IND_AC04_AAC001 AAC001 1
T_PER_CHARGE_RECORD IND_T_PER_CHARGE_RECORD_AAC001 AAC001 1
AB01_02 PK_AB01_02 AAB001 1
AB01_02 PK_AB01_02 AAB301 2
AB02 PK_AB02 AAB001 1
AB02 PK_AB02 AAE140 2
AC01_02 PK_AC01_02 AAC001 1
AC02 PK_AC02 AAC001 1
AC02 PK_AC02 AAE140 2
AC04 PK_AC04 AAB001 1
AC04 PK_AC04 AAC001 2
AC04 PK_AC04 AAE030 3

SQL> SELECT COUNT(*) FROM AB01_02;
COUNT(*)
----------
1140

SQL> SELECT COUNT(*) FROM AC01_02;
COUNT(*)
----------
156911

SQL> SELECT COUNT(*) FROM AC02;
COUNT(*)
----------
581569

SQL> SELECT COUNT(*) FROM T_PER_CHARGE_RECORD;
COUNT(*)
----------
2146433

SQL> SELECT COUNT(*) FROM AC04;
COUNT(*)
----------
1322944

SQL> SELECT COUNT(*) FROM AB02;
COUNT(*)
----------
4601


谁能帮忙优化一下,或者给个思路也可以,万分感谢!
...全文
319 12 打赏 收藏 举报
写回复
12 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
crazylaa 2010-01-09
  • 打赏
  • 举报
回复
[Quote=引用 10 楼 liuyi8903 的回复:]
引用 8 楼 cslf 的回复:
建议:
1、D.EFFECT_YEAR,E.AAE031两个表字段建索引(分区还是全局?) ,对超过100W行记录的表定期进行统计分析。
2、类似TO_CHAR(E.AAE031,'YYYYMM') > '201001' ,改为E.AAE031>to_date('201001','yyyymmdd').字段上禁止使用函数。其它的字段上的函数建议先修改数据,再去掉函数。
3、AB01_02,AC01_02全表扫描。目前影响不大,如果数据增长快,性能会很快降低。
4、在硬件方面比如IO,WAIT,CPU你可以检查下。


你难道没有看到对B表的扫描的io cost有多高?

还不影响? 呵呵,
[/Quote]

呵呵,同感,貌似时间都耗在这两个全表扫描上噢
liuyi8903 2010-01-08
  • 打赏
  • 举报
回复
最好是放个10053 trace上来。
liuyi8903 2010-01-08
  • 打赏
  • 举报
回复
[Quote=引用 8 楼 cslf 的回复:]
建议:
1、D.EFFECT_YEAR,E.AAE031两个表字段建索引(分区还是全局?) ,对超过100W行记录的表定期进行统计分析。
2、类似TO_CHAR(E.AAE031,'YYYYMM') > '201001' ,改为E.AAE031>to_date('201001','yyyymmdd').字段上禁止使用函数。其它的字段上的函数建议先修改数据,再去掉函数。
3、AB01_02,AC01_02全表扫描。目前影响不大,如果数据增长快,性能会很快降低。
4、在硬件方面比如IO,WAIT,CPU你可以检查下。
[/Quote]

你难道没有看到对B表的扫描的io cost有多高?

还不影响? 呵呵,
obuntu 2010-01-08
  • 打赏
  • 举报
回复
学习
cslf 2010-01-08
  • 打赏
  • 举报
回复
建议:
1、D.EFFECT_YEAR,E.AAE031两个表字段建索引(分区还是全局?) ,对超过100W行记录的表定期进行统计分析。
2、类似TO_CHAR(E.AAE031,'YYYYMM') > '201001' ,改为E.AAE031>to_date('201001','yyyymmdd').字段上禁止使用函数。其它的字段上的函数建议先修改数据,再去掉函数。
3、AB01_02,AC01_02全表扫描。目前影响不大,如果数据增长快,性能会很快降低。
4、在硬件方面比如IO,WAIT,CPU你可以检查下。
sniffer12345 2010-01-07
  • 打赏
  • 举报
回复
很奇怪 为什么要用到trim呢 如果确保空格没意义的话 那还不如一次性将数据中的左右空格先去掉 然后应用程序那边确保插入的时候先trim下 这样以后 就算不建函数索引 速度也不慢
liuyi8903 2010-01-07
  • 打赏
  • 举报
回复
RTRIM(LTRIM(B.AAC008))

考虑在B表上这里建一个fun index,速度肯定要上去。

为什么要用这么多的trim?

char吗?
crazylaa 2010-01-06
  • 打赏
  • 举报
回复
[Quote=引用 4 楼 jinxino_o 的回复:]
大家多表关联都怎么处理的?谁能共享一下经验?
[/Quote]
大表分区,关联条件建索引并尽量利用=。查询时数据量小的表放最后作为基表。
如果子查询多,用存储过程实现,存储过程用临时表来保存子查询结果集。
  • 打赏
  • 举报
回复
大家多表关联都怎么处理的?谁能共享一下经验?
罗耗子 2010-01-04
  • 打赏
  • 举报
回复
是嫌慢还是嫌烦?要不干脆作个视图吧!
  • 打赏
  • 举报
回复
谢谢!
crazylaa 2010-01-03
  • 打赏
  • 举报
回复
6、7的table access full。
6 - filter(RTRIM(LTRIM("A"."AAE119"))='1')
7 - filter(RTRIM(LTRIM("B"."AAC008"))='1')
如果这两列的数值范围分布广的话,不如在这两列上建函数索引 TRIM(AAE119) 和TRIM(AAC008)
如果这两列是CHAR类型并且1前面无空格,则可以去掉LTRIM和RTRIM函数,直接用=

另外,还有日期过滤那里都用了substr,trim等等,建议建立对应的函数索引。

from的顺序里面按表数据量从大到小排,至少
AB01_02 A, AC01_02 B,这两个需要换下位置。

个人意见,调优没怎么做过,等待大牛指正。
发帖
Oracle 高级技术

3471

社区成员

Oracle 高级技术相关讨论专区
社区管理员
  • 高级技术社区
加入社区
帖子事件
创建了帖子
2010-01-03 10:18
社区公告
暂无公告