仅300万条数据，为何查询速度特别慢。性能如何改善，50分求助。

Question

查询中两个关联的主表数据量大概都在300万左右，通过主键关联。查询条件为A表中一个非索引字段 in （'xx','xx','xx','xx'）。当检索结果超过10万时，查询速度非常非常慢。如何能够优化性能呢？

Answer 1

是呀，还有sql，不然只能靠猜了in （'xx','xx','xx','xx'）。
会产生大量数据吗

Answer 2

select *
from (select * from a where aa in (('xx','xx','xx','xx')) a2 ,b 
where a2.id =b.id。
这样会快么

Answer 3

select * 
from a ,b 
where a.id =b.id and a.aa in ('xx','xx','xx','xx')
这样试试

Answer 4

SELECT MES_PARTMASTER.S_Master_OID1,
       MES_PARTMASTER.S_Master_OID2,
       MES_MST_CodeDomain.S_Value Jyotai_Value,
       MES_PARTMASTER.S_Part_Number,
       MES_PARTMASTER.U_KeyWord_6,
       MES_PARTMASTER.U_KeyWord_7,
       MES_PARTREVISION.U_Revision,
       MES_PARTREVISION.U_NOTE,
       MES_PARTREVISION.S_PartName_Disp,
       MES_PARTREVISION.S_Revision_OID1,
       MES_PARTREVISION.S_Revision_OID2,
       HU_Kubun.S_Value,
       Buil_Kubun.S_Value,
       Part_Gun_Kubun.U_SYOUHINGUN_KUBUNMEI,
       ALUMINUMATTRIBUTE.U_Form_Kubun,
       ALUMINUMATTRIBUTE.U_Circuit_Length,
       ALUMINUMATTRIBUTE.U_Peripheral_Length,
       ALUMINUMATTRIBUTE.U_Circumscribed_Circle,
       ALUMINUMATTRIBUTE.U_Section,
       ALUMINUMATTRIBUTE.U_PluU_Tolerance,
       ALUMINUMATTRIBUTE.U_MinuU_Tolerance,
       MES_PARTMASTER.S_PART_NUMBER,
       NVL(CODEDOMAIN2.S_VALUE, '-'),
       NVL(CODEDOMAIN2.S_VALUE_ENG, '-'),
       MES_PARTREVISION.U_KeyWord_154,
       Part_Gun_Kubun.U_SYOUHINGUN_ENG
  FROM MES_PARTMASTER
  LEFT JOIN MES_PARTREVISION ON MES_PARTMASTER.S_Master_OID1 =
                                MES_PARTREVISION.S_Master_OID1
                            AND MES_PARTMASTER.S_Master_OID2 =
                                MES_PARTREVISION.S_Master_OID2
  LEFT JOIN ALUMINUMATTRIBUTE ON MES_PARTREVISION.S_Revision_OID1 =
                                 ALUMINUMATTRIBUTE.S_PartRevision_OID1
                             AND MES_PARTREVISION.S_Revision_OID2 =
                                 ALUMINUMATTRIBUTE.S_PartRevision_OID2
  LEFT JOIN MES_MST_CodeDomain HU_Kubun ON HU_Kubun.S_Key =
                                           ALUMINUMATTRIBUTE.U_HU_Kubun
                                       AND HU_Kubun.S_GroupID = '1014'
  LEFT JOIN MES_MST_CodeDomain Buil_Kubun ON Buil_Kubun.S_Key =
                                             ALUMINUMATTRIBUTE.U_Buil_Kubun
                                         AND Buil_Kubun.S_GroupID = '1015'
  LEFT JOIN SYOUHINGUN_KUBUN_Master Part_Gun_Kubun ON Part_Gun_Kubun.U_SYOUHINGUN_KUBUN =
                                                      ALUMINUMATTRIBUTE.U_Part_Gun_Kubun
  LEFT JOIN MES_MST_CodeDomain ON (MES_MST_CodeDomain.S_Key =
                                  MES_PARTMASTER.U_KeyWord_1 AND
                                  MES_MST_CodeDomain.S_GroupID = '1001')
  LEFT JOIN STD_PART_MASTER ON (MES_PARTREVISION.S_REVISION_OID1 =
                               STD_PART_MASTER.S_PARTREVISION_OID1 AND
                               MES_PARTREVISION.S_REVISION_OID2 =
                               STD_PART_MASTER.S_PARTREVISION_OID2)
                           AND (STD_PART_MASTER.S_DELETE_FLAG = 0 OR
                               STD_PART_MASTER.S_DELETE_FLAG IS NULL)
  LEFT JOIN CODEDOMAIN2 ON STD_PART_MASTER.U_STD_PART_FLG =
                           CODEDOMAIN2.S_KEY
                       AND CODEDOMAIN2.S_GROUPID = '3002'
 WHERE MES_PARTMASTER.U_KeyWord_1 IN ('2', '11', '30', '31')
   AND MES_PARTREVISION.S_NextRev_Created_Date = '99999999'
   AND MES_PARTMASTER.S_ItemSubType = '113'
   AND MES_PARTMASTER.S_Delete_Flag = 0;SQL先贴出来大家看一下。在U_KeyWord_1字段上加索引已经试过了，不起作用。
另外，执行计划怎么查看啊，呵呵~

Answer 5

补充一下，MES_PARTMASTER表和MES_PARTREVISION表中主键以及关键字段都已经添加过索引了。

Answer 6

执行计划
SELECT STATEMENT, GOAL = ALL_ROWS 170837 59980 18953680
 SORT ORDER BY 170837 59980 18953680
  HASH JOIN RIGHT OUTER 166763 59980 18953680
   TABLE ACCESS BY INDEX ROWID TNA CODEDOMAIN2 2 5 95
    INDEX RANGE SCAN TNA CODEDOMAIN2_PRIM 1 5
   NESTED LOOPS OUTER 166759 59980 17814060
    HASH JOIN RIGHT OUTER 46726 59980 17394200
     TABLE ACCESS FULL TNA SYOUHINGUN_KUBUN_MASTER 3 15 495
     HASH JOIN RIGHT OUTER 46721 59980 15414860
      TABLE ACCESS BY INDEX ROWID TNA MES_MST_CODEDOMAIN 2 6 120
       INDEX RANGE SCAN TNA IK_MES_MST_CODEDOMAIN_1 1 6
      HASH JOIN RIGHT OUTER 46718 59980 14215260
       TABLE ACCESS BY INDEX ROWID TNA MES_MST_CODEDOMAIN 2 6 120
        INDEX RANGE SCAN TNA IK_MES_MST_CODEDOMAIN_1 1 6
       NESTED LOOPS OUTER 46714 59980 13015660
        HASH JOIN RIGHT OUTER 46386 59980 10616460
         TABLE ACCESS BY INDEX ROWID TNA MES_MST_CODEDOMAIN 3 18 360
          INDEX RANGE SCAN TNA IK_MES_MST_CODEDOMAIN_1 1 18
         HASH JOIN 46382 59980 9416860
          INLIST ITERATOR
           TABLE ACCESS BY INDEX ROWID TNA MES_PARTMASTER 2598 59693 2686185
            INDEX RANGE SCAN TNA MES_PARTMASTER_2 306 119387
          TABLE ACCESS FULL TNA MES_PARTREVISION 28994 2477097 277434864
        TABLE ACCESS BY INDEX ROWID TNA ALUMINUMATTRIBUTE 1 1 40
         INDEX UNIQUE SCAN TNA ALUMINUMATTRIBUTE_PRIM 0 1
    VIEW SYS 2 1 7
     TABLE ACCESS FULL TNA STD_PART_MASTER 2 1 46

Answer 7

SYOUHINGUN_KUBUN_Master.U_SYOUHINGUN_KUBUN 建个索引SELECT /*+ leading("MES_PARTMASTER") use_nl("MES_PARTMASTER")*/try try

Answer 8

SYOUHINGUN_KUBUN_Master.U_SYOUHINGUN_KUBUN 索引已经有了
SELECT /*+ leading("MES_PARTMASTER") use_nl("MES_PARTMASTER")*/ 
这句什么意思？

Answer 9

SELECT /*+ leading("MES_PARTMASTER") use_nl("MES_PARTMASTER")*/ 
报错啊……能解释的详细一点吗？

Answer 10

oracle 注释，使用nested loop，这样可以第一个连接的记录作为第二个连接的驱动表，依次类推会不会改善速度不知道，但不至于报错把，只是注释呀

Answer 11

create index idx_t on MES_PARTMASTER( U_KeyWord_1  S_ItemSubType , S_Delete_Flag)是这样建一联合 索引。还要另一个联合索引
   create index idx_t1 on  MES_PARTREVISION （S_Master_OID1,S_Master_OID2 ）sql 还像你原样运行。
如查 还没能改善 ，就只能那样用的。事务数据库oltp,就是专干 0点0几秒就完成的查询.要然就用 olap 数据仓库,随便建索引。  全用bitmap 索引。

Answer 12

不考虑更新性能，
在 MES_PARTMASTER.U_KeyWord_1，MES_PARTMASTER.S_ItemSubType，MES_PARTMASTER.S_Delete_Flag上建立索引，如列太少，考虑建位图索引。
在 MES_PARTREVISION.S_NextRev_Created_Date上建立索引。对查询使用子查询嵌套，先取出符合条件的记录，过滤后再进行表联接试试。

Answer 13

in 是全表搜索，用exist代替。不要嵌套查。用join。然后在做个索引。

Answer 14

不同的场景使用in或者是exists，谁说IN就不走索引扫描的？

Answer 15

根据执行计划得到的信息：
a.对300w数据执行全表扫描；
b.全表扫描不止一次；
c.进行多次的 outer 链接SELECT STATEMENT, GOAL = ALL_ROWS 170837 59980 18953680
SORT ORDER BY 170837 59980 18953680
  HASH JOIN RIGHT OUTER 166763 59980 18953680
  TABLE ACCESS BY INDEX ROWID TNA CODEDOMAIN2 2 5 95
    INDEX RANGE SCAN TNA CODEDOMAIN2_PRIM 1 5
  NESTED LOOPS OUTER 166759 59980 17814060
    HASH JOIN RIGHT OUTER 46726 59980 17394200
    TABLE ACCESS FULL TNA SYOUHINGUN_KUBUN_MASTER 3 15 495
    HASH JOIN RIGHT OUTER 46721 59980 15414860
      TABLE ACCESS BY INDEX ROWID TNA MES_MST_CODEDOMAIN 2 6 120
      INDEX RANGE SCAN TNA IK_MES_MST_CODEDOMAIN_1 1 6
      HASH JOIN RIGHT OUTER 46718 59980 14215260
      TABLE ACCESS BY INDEX ROWID TNA MES_MST_CODEDOMAIN 2 6 120
        INDEX RANGE SCAN TNA IK_MES_MST_CODEDOMAIN_1 1 6
      NESTED LOOPS OUTER 46714 59980 13015660
        HASH JOIN RIGHT OUTER 46386 59980 10616460
        TABLE ACCESS BY INDEX ROWID TNA MES_MST_CODEDOMAIN 3 18 360
          INDEX RANGE SCAN TNA IK_MES_MST_CODEDOMAIN_1 1 18
        HASH JOIN 46382 59980 9416860
          INLIST ITERATOR
          TABLE ACCESS BY INDEX ROWID TNA MES_PARTMASTER 2598 59693 2686185
            INDEX RANGE SCAN TNA MES_PARTMASTER_2 306 119387
          TABLE ACCESS FULL TNA MES_PARTREVISION 28994 2477097 277434864
        TABLE ACCESS BY INDEX ROWID TNA ALUMINUMATTRIBUTE 1 1 40
        INDEX UNIQUE SCAN TNA ALUMINUMATTRIBUTE_PRIM 0 1
    VIEW SYS 2 1 7
    TABLE ACCESS FULL TNA STD_PART_MASTER 2 1 46
根据以上信息建议采用：
1、分析为何进行了全表扫描，尝试在相关自动字段中创建索引；
2、sql 语句中尽量将 left join 修改一下，改用其他 inner join 或其他方式（写功能函数），也可以提高一定的速度。

Answer 16

忘记说了，多表联合查询时，表的顺序也有一定的影响。
from 语句后，一般顺序为：form 300wTable,200wtable,10wTable,...0wTable