为什么是全表扫描

xliuhao123

哪位帮我看看啊
SELECT FD.DIMENSION_ID AS DIMENSION_ID,
   NULL AS EXPENSE_OBJECT_TYPE_ID,
   'DIMENSION' AS RULE_CODE,
   FD.DESCRIPTION || '' || '(维度)' AS RULE_NAME
  FROM FND_DIMENSIONS_VL          FD,
   FND_DIMENSION_VALUES_V    F,
   FND_COMPANY_DIMENSION_VALUES FC
WHERE FC.COMPANY_ID = 517
AND FC.DIMENSION_VALUE_ID = F.DIMENSION_VALUE_ID
AND F.DIMENSION_ID = FD.DIMENSION_ID
GROUP BY FD.DIMENSION_ID, FD.DIMENSION_CODE, FD.DESCRIPTION
在表FND_COMPANY_DIMENSION_VALUES的COMPANY_ID 和DIMENSION_VALUE_ID 两个字段建立了联合索引，但是解释的发现在扫扫描FC的时候都是全表的，根本就不走索引，把这段sql拿出来
select * from FND_COMPANY_DIMENSION_VALUES  fc where fc.COMPANY_ID=517 and fc.DIMENSION_VALUE_ID =某值的时候走的也是索引，谁能告诉我这是怎么回事啊？小弟在学习过程中，不胜感谢！

P4 · 发表于 2013-1-11 19:01:43

另外这段简单的sql执行需要10秒才能完成，除了这里说的全表扫描外，还有哪些地方存在瓶颈，谢谢！

P5 · 发表于 2013-1-11 19:55:06

执行计划发来看看

P5 · 发表于 2013-1-11 20:17:23

参考下这个也许会有帮助 http://www.jiagulun.com/blog-7306-2369.html

P6 · 发表于 2013-1-13 21:32:43

1、尝试着在company_id上建立一个单列索引
2、我还是需要看一下你的执行计划，以为涉及到三个表的连接，会有三个表的连接问题
3、一个sql是否高效，主要看的是获取的数据和需要的数据之间的对比
4、全表扫描不一定意味着问题

P4 · 发表于 2013-1-14 09:55:38

谢谢两位老师，执行计划如下：
SELECT STATEMENT, GOAL = ALL_ROWS 34184 91 11648
HASH GROUP BY 34184 91 11648
  HASH JOIN RIGHT OUTER 34146 436168 55829504
TABLE ACCESS BY INDEX ROWID MASHEC FND_DESCRIPTIONS 6 14 1078
INDEX RANGE SCAN MASHEC FND_DESCRIPTIONS_N1 3 14
HASH JOIN 34136 436168 22244568
TABLE ACCESS FULL MASHEC FND_DIMENSIONS 3 13 208
HASH JOIN 34128 436168 15265880
   INDEX RANGE SCAN MASHEC FND_COMPANY_DIM_VALUES_PK 5 1137 10233
   VIEW MASHEC FND_DIMENSION_VALUES_V 34112 1046004 27196104
   SORT UNIQUE 34112 1046004 142712868
   UNION-ALL
      HASH JOIN 1072 666107 80598947
      HASH JOIN RIGHT OUTER 293 4620 508200
      TABLE ACCESS BY INDEX ROWID MASHEC FND_DESCRIPTIONS 277 1623 124971
         INDEX RANGE SCAN MASHEC FND_DESCRIPTIONS_N1 15 1623
      MERGE JOIN 15 4620 152460
         TABLE ACCESS BY INDEX ROWID MASHEC FND_DIMENSIONS 2 8 40
         INDEX FULL SCAN MASHEC FND_DIMENSIONS_PK 1 13
         SORT JOIN 13 4620 129360
         TABLE ACCESS FULL MASHEC FND_DIMENSION_VALUES 12 4620 129360
      TABLE ACCESS FULL MASHEC FND_COMPANY_DIMENSION_VALUES 772 666107 7327177
      MERGE JOIN CARTESIAN 146 328329 57785904
      HASH JOIN RIGHT OUTER 15 573 98556
      TABLE ACCESS BY INDEX ROWID MASHEC FND_DESCRIPTIONS 4 3 231
         INDEX RANGE SCAN MASHEC FND_DESCRIPTIONS_N1 3 3
      HASH JOIN RIGHT OUTER 11 573 54435
         TABLE ACCESS BY INDEX ROWID MASHEC FND_DESCRIPTIONS 4 2 102
         INDEX RANGE SCAN MASHEC FND_DESCRIPTIONS_N1 3 2
         NESTED LOOPS 6 573 25212
         TABLE ACCESS BY INDEX ROWID MASHEC FND_DIMENSIONS 1 1 12
         INDEX UNIQUE SCAN MASHEC FND_DIMENSIONS_U2 0 1
         TABLE ACCESS FULL MASHEC FND_COMPANIES 5 573 18336
      BUFFER SORT 142 573 2292
      INDEX FAST FULL SCAN MASHEC FND_COMPANIES_PK 0 573 2292
      HASH JOIN RIGHT OUTER 328 6375 714000
      TABLE ACCESS BY INDEX ROWID MASHEC FND_DESCRIPTIONS 312 1840 141680
      INDEX RANGE SCAN MASHEC FND_DESCRIPTIONS_N1 16 1840
      NESTED LOOPS 15 6375 223125
      TABLE ACCESS BY INDEX ROWID MASHEC FND_DIMENSIONS 1 1 12
         INDEX UNIQUE SCAN MASHEC FND_DIMENSIONS_U2 0 1
      TABLE ACCESS FULL MASHEC EXP_ORG_UNIT 14 6375 146625
      HASH JOIN RIGHT OUTER 36 352 39776
      TABLE ACCESS BY INDEX ROWID MASHEC FND_DESCRIPTIONS 31 162 12474
      INDEX RANGE SCAN MASHEC FND_DESCRIPTIONS_N1 4 162
      NESTED LOOPS 4 352 12672
      TABLE ACCESS BY INDEX ROWID MASHEC FND_DIMENSIONS 1 1 12
         INDEX UNIQUE SCAN MASHEC FND_DIMENSIONS_U2 0 1
      TABLE ACCESS FULL MASHEC FND_OPERATION_UNITS 3 352 8448
      HASH JOIN RIGHT OUTER 721 15911 1925231
      TABLE ACCESS BY INDEX ROWID MASHEC FND_DESCRIPTIONS 664 3923 302071
      INDEX RANGE SCAN MASHEC FND_DESCRIPTIONS_N1 32 3923
      NESTED LOOPS 57 15911 700084
      TABLE ACCESS BY INDEX ROWID MASHEC FND_DIMENSIONS 1 1 12
         INDEX UNIQUE SCAN MASHEC FND_DIMENSIONS_U2 0 1
      TABLE ACCESS FULL MASHEC EXP_ORG_POSITION 56 15911 509152
      HASH JOIN 79 28930 1649010
      NESTED LOOPS 20 28930 607530
      TABLE ACCESS BY INDEX ROWID MASHEC FND_DIMENSIONS 1 1 12
         INDEX UNIQUE SCAN MASHEC FND_DIMENSIONS_U2 0 1
      INDEX FAST FULL SCAN MASHEC IDX_TEST 19 28930 260370
      TABLE ACCESS FULL MASHEC EXP_EMPLOYEES 58 21887 787932

P4 · 发表于 2013-1-14 10:02:58

其中表FND_COMPANY_DIMENSION_VALUES一些信息如下：
字段名    数量关系    引用的表名    引用的字段名    该关系的基本描述    说明
DIMENSION_VALUE_ID n-1 FND_DIMENSION_VALUES
DIMENSION_VALUE_ID REF 一个维值可以分配给多个公司
索引
索引名    是否唯一    类型    字段
FND_COMPANY_DIM_VALUES_PK UNIQUE NORMAL COMPANY_ID,DIMENSION_VALUE_ID
FND_COMPANY_DIM_VALUES_N1 NONUNIQUE NORMAL DIMENSION_VALUE_ID
--
FND_DIMENSION_VALUES_V是试图
select v.company_id,
   h.dimension_id,
   t.dimension_value_id,
   t.dimension_value_code,
   t.description,
   decode(t.enabled_flag, 'Y', v.enabled_flag, t.enabled_flag) enabled_flag
from fnd_dimensions             h,
   fnd_dimension_values_vl    t,
   fnd_company_dimension_values v
where t.dimension_value_id = v.dimension_value_id
   and h.dimension_id = t.dimension_id
--and h.company_level ='Y'
   and t.summary_flag = 'N'
   and h.system_flag = 'N'
----
fnd_dimensions_vl也是试图
select t.dimension_id,
   t.dimension_sequence,
   t.dimension_code,
   a.description_text as description,
   t.system_flag,
   t.system_level,
   t.company_level,
   t.enabled_flag,
   t.last_update_date,
   t.last_updated_by,
   t.creation_date,
   t.created_by
from fnd_dimensions t, fnd_descriptions a
where t.description_id = a.description_id(+)
   and a.language(+) = userenv('LANG')
   and a.ref_table(+) = 'FND_DIMENSIONS'
   and a.ref_field(+) = 'DESCRIPTION_ID';

P4 · 发表于 2013-1-14 10:04:08

获取的数据是12行，用时10到12秒

P3 · 发表于 2013-1-15 09:47:39

SQL优化学习中

图文精华

为什么是全表扫描