多表关联的优化问题

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


谁能帮忙优化一下,或者给个思路也可以,万分感谢!
...全文
320 12 打赏 收藏 举报
写回复
12 条回复
切换为时间正序
请发表友善的回复…
发表回复
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]
大表分区，关联条件建索引并尽量利用＝。查询时数据量小的表放最后作为基表。
如果子查询多，用存储过程实现，存储过程用临时表来保存子查询结果集。
IndependentDeveloper 2010-01-06
  • 打赏
  • 举报
 回复
大家多表关联都怎么处理的?谁能共享一下经验?
罗耗子 2010-01-04
  • 打赏
  • 举报
 回复
是嫌慢还是嫌烦?要不干脆作个视图吧!
IndependentDeveloper 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多表关联的优化问题.doc
oracle多表关联的优化问题.doc
yii2 ActiveRecord多表关联以及多表关联搜索的实现
yii2 ActiveRecord多表关联以及多表关联搜索的实现 一个老生常谈的问题。最近通过群里的反馈，觉得很多人还是没有去理解这个问题。今天把这个问题讲明白了，看看yii2 ActiveRecord是怎么个多表关联以及如何去优化这个关联。 场景需求： 假设我们有一张用户表user和一张用户渠道表auth，两张数据表通过user.id和a...
yii2中关联查询
yii2 ActiveRecord多表关联以及多表关联搜索的实现 一个老生常谈的问题。最近通过群里的反馈，觉得很多人还是没有去理解这个问题。今天把这个问题讲明白了，看看yii2 ActiveRecord是怎么个多表关联以及如何去优化这个关联。 场景需求： 假设我们有一张用户表user和一张用户渠道表auth，两张数据表通过user.id和auth.uid进行一对一关联。现需...
delete + in/not in 优化、优化与db的交互次数、优化多表关联、慢SQL场景分析
delete + in/not in 优化、优化与db的交互次数、优化多表关联、慢SQL场景分析
MySQL-多表关联查询order by排序查询很慢优化
MySQL-多表关联查询order by排序查询很慢优化
发帖
Oracle 高级技术

3477

社区成员

1.8w+

社区内容

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