为什么是全表扫描

xliuhao123 发表于 2013-1-11 19:00:05

哪位帮我看看啊
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_VALUESfc where fc.COMPANY_ID=517 and fc.DIMENSION_VALUE_ID =某值的时候走的也是索引，谁能告诉我这是怎么回事啊？小弟在学习过程中，不胜感谢！

xliuhao123 发表于 2013-1-11 19:01:43

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

fishcat 发表于 2013-1-11 19:55:06

执行计划发来看看

fishcat 发表于 2013-1-11 20:17:23

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

oraunix 发表于 2013-1-13 21:32:43

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

xliuhao123 发表于 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

xliuhao123 发表于 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';

xliuhao123 发表于 2013-1-14 10:04:08

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

etl_xp 发表于 2013-1-15 09:47:39

SQL优化学习中:'(

页: [1]

iiDBA's Archiver

为什么是全表扫描