这个sql怎么消除全表扫描

daiwenc

各位下午好：
环境：
OS: RHEL 5.8 X86_64
DB：11.2.0.3.7

如下sql
SELECT * FROM (SELECT S.SALESORDERBILLSTATEID, S.MOPOCSALESORDERBILLID, S.OUTERSALESORDERBILLID, MOPOCSOURCEORDERBILLID, OUTERSOURCEORDERBILLID, SHOPID, S.OWNERID, ORDERTYPEID, S.WAREHOUSEID, QTY, BILLSTATE, OLDBILL_STATE, STATE_DESC, CREATETIME, CREATEUSERID, S.DELIVERID, DELIVERNO, WEIGHT, WAYBILL_PACKAGEID, INPUTTYPEID, S.RECORDTIME, FROMSYSTEM, TASKSTATE , NVL(G.GROUPNO, '0') GROUPNO FROM IF_SALESORDER_BILLSTATE S LEFT JOIN IF_SALESORDER_GROUP G ON G.MOPOCSALESORDERBILLID = S.MOPOCSALESORDERBILLID WHERE S.TASKSTATE ='0' AND S.BILLSTATE > '00' ORDER BY RECORDTIME) WHERE ROWNUM < 1001;
执行计划为：
SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 324440152

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                            |  1000 |   444K|       | 52076   (2)| 00:03:09 |
|*  1 |  COUNT STOPKEY                  |                            |       |       |       |            |          |
|   2 |   VIEW                          |                            |   616K|   267M|       | 52076   (2)| 00:03:09 |
|*  3 |    SORT ORDER BY STOPKEY        |                            |   616K|   110M|   117M| 52076   (2)| 00:03:09 |
|*  4 |     HASH JOIN RIGHT OUTER       |                            |   616K|   110M|    54M| 29835   (2)| 00:01:49 |
|   5 |      TABLE ACCESS FULL          | IF_SALESORDER_GROUP        |  1739K|    34M|       |  5133   (3)| 00:00:19 |
|*  6 |      TABLE ACCESS BY INDEX ROWID| IF_SALESORDER_BILLSTATE    |   616K|    98M|       | 21121   (1)| 00:01:17 |
|*  7 |       INDEX RANGE SCAN          | IK_IF_SALESORDER_BILLSTATE |   668K|       |       |  1991   (2)| 00:00:08 |
----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<1001)
   3 - filter(ROWNUM<1001)
   4 - access("G"."MOPOCSALESORDERBILLID"(+)="S"."MOPOCSALESORDERBILLID")
   6 - filter("S"."BILLSTATE">'00')
   7 - access("S"."TASKSTATE"='0')

经过awr报告分析，物理IO过高，且Direct Reads占绝大部分，Direct Reads又主要是 IF_SALESORDER_GROUP这个表，请问针对这个sql怎么优化法呢？

daiwenc

怎么发出来的执行计划格式乱了，正确的应该是下面的 5-7-6-4-3-2-1
----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                            |  1000 |   444K|       | 52076   (2)| 00:03:09 |
|*  1 |  COUNT STOPKEY                  |                            |       |       |       |            |          |
|   2 |   VIEW                          |                            |   616K|   267M|       | 52076   (2)| 00:03:09 |
|*  3 |    SORT ORDER BY STOPKEY        |                            |   616K|   110M|   117M| 52076   (2)| 00:03:09 |
|*  4 |     HASH JOIN RIGHT OUTER       |                            |   616K|   110M|    54M| 29835   (2)| 00:01:49 |
|    5 |      TABLE ACCESS FULL          | IF_SALESORDER_GROUP        |  1739K|    34M|       |  5133   (3)| 00:00:19 |
|*  6 |      TABLE ACCESS BY INDEX ROWID| IF_SALESORDER_BILLSTATE    |   616K|    98M|       | 21121   (1)| 00:01:17 |
|*  7 |       INDEX RANGE SCAN          | IK_IF_SALESORDER_BILLSTATE |   668K|       |       |  1991   (2)| 00:00:08 |
----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<1001)
   3 - filter(ROWNUM<1001)
   4 - access("G"."MOPOCSALESORDERBILLID"(+)="S"."MOPOCSALESORDERBILLID")
   6 - filter("S"."BILLSTATE">'00')
   7 - access("S"."TASKSTATE"='0')

daiwenc

SQL> select count(*),count(distinct GROUPNO),count(GROUPNO) from IF_SALESORDER_GROUP;

  COUNT(*) COUNT(DISTINCTGROUPNO) COUNT(GROUPNO)
---------- ---------------------- --------------
   1741536                     10        1741536

dla001

RECORDTIME是S表的就简单点。如果不是就麻烦。 还要看具体的数据。

daiwenc

的确是S表的

daiwenc

dla001 发表于 2013-12-24 16:14
RECORDTIME是S表的就简单点。如果不是就麻烦。 还要看具体的数据。

的确是S表的

Liu Maclean(刘相兵

需要完整的DDL ,RECORDTIME有相关的索引吗？

dla001

看RECORDTIME是否定义为not null.  不允许null的情况下看

1.如果order by 中加入BILLSTATE，改为  order by BILLSTATE,RECORDTIME是否可以接受，如果可以有(TASKSTATE ,BILLSTATE,RECORDTIME)索引就OK了
2.如果order by 不能加字段。索引(TASKSTATE,RECORDTIME)或(RECORDTIME)可能解决问题，这要看你真实的数据情况来决定。

一切看你具体的业务情况来决定。