帮忙看看这个sql语句怎么优化

Question

表中的数据量：
WS_SHEETINDEX    141096
WS_PATTERNINFO   30
WS_STATUSMSG     11sql语句如下：
SELECT *
  FROM (SELECT SHEETID,
               SHEETNAME,
               PATTERNNAME,
               TASKID,
               PAGENUM,
               DEPTNAME,
               MAKETIME,
               LEADER,
               SIGNISSUE,
               PLANSTARTTIME,
               PLANFINISHTIME
          FROM WS_SHEETINDEX
         INNER JOIN WS_PATTERNINFO ON WS_SHEETINDEX.PATTERNID =
                                      WS_PATTERNINFO.PATTERNID
         INNER JOIN WS_STATUSMSG ON WS_SHEETINDEX.SHEETSTATUSID =
                                    WS_STATUSMSG.STATUSID
         WHERE WS_PATTERNINFO.MODULETYPE = 1
           AND (ISMAINSHEET = 1 AND SHEETTYPE = 1 AND SHEETSTATUSID >= 2 AND
               SHEETSTATUSID <= 10 AND
               DEPTID IN ('100000000000011', '100000000000129',
                '100000000000130', '100000000000131') AND
               ws_sheetindex.patternid = 0)
         ORDER BY MAKETIME DESC)
 WHERE ROWNUM <= 20以上语句怎么优化一下，现在执行时间是20多s
已经建了索引

Answer 1

主要的表结构
create table WS_SHEETINDEX
(
  SHEETID           NUMBER not null,
  PATTERNID         NUMBER not null,
  ISMAINSHEET       NUMBER not null,
  MAKETIME          VARCHAR2(20) not null,
  DEPTID            VARCHAR2(50),
  DEPTNAME          VARCHAR2(40),
  SHEETSTATUSID     NUMBER not null,
  SHEETTYPE         NUMBER,
  MAINSHEETID       NUMBER,
  SHEETNAME         VARCHAR2(1000),
  DRAFTNAME         VARCHAR2(20),
  DRAFTNAMEID       VARCHAR2(20),
  RECVNAME          VARCHAR2(200),
  RECVNAMEID        VARCHAR2(200),
  DOCDEPTID         VARCHAR2(50),
  DOCDEPTNAME       VARCHAR2(40),
  PRINTER           VARCHAR2(200),
  PRINTERID         VARCHAR2(200),
  REMARK            VARCHAR2(200),
  OPENNAME          VARCHAR2(20),
  OPENNAMEID        VARCHAR2(20),
  ISPROMPT          NUMBER not null,
  PAGENUM           VARCHAR2(20),
  MAINPAGENUM       VARCHAR2(20),
  LEADER            VARCHAR2(20),
  LEADERID          VARCHAR2(20),
  WORKCONTENT       VARCHAR2(1000),
  MACHINE           VARCHAR2(50),
  STATIONID         NUMBER,
  SIGNISSUE         VARCHAR2(20),
  SIGNISSUEID       VARCHAR2(20),
  DUTYCHARGE        VARCHAR2(20),
  DUTYCHARGEID      VARCHAR2(20),
  WORKMEMBER        VARCHAR2(100),
  WORKMEMBERCOUNT   VARCHAR2(100),
  PLANSTARTTIME     VARCHAR2(20),
  PLANFINISHTIME    VARCHAR2(20),
  RECEIVER          VARCHAR2(10),
  RECEIVERID        VARCHAR2(20),
  RECEIVETIME       VARCHAR2(20),
  PERMITLEADER      VARCHAR2(10),
  PERMITLEADERID    VARCHAR2(20),
  PERMITER          VARCHAR2(10),
  PERMITERID        VARCHAR2(20),
  PERMITSTARTTIME   VARCHAR2(20),
  APPROVESTARTTIME  VARCHAR2(20),
  APPROVEWORKTIME   VARCHAR2(20),
  APPROVEFINISHTIME VARCHAR2(20),
  ALTERLEADER       VARCHAR2(20),
  ALTERLEADERID     VARCHAR2(20),
  DELAYTIME         VARCHAR2(20),
  ENDTIME           VARCHAR2(20),
  ENDPERMITER       VARCHAR2(20),
  ENDPERMITERID     VARCHAR2(20),
  ENDLEADER         VARCHAR2(20),
  ENDLEADERID       VARCHAR2(20),
  EXECPERSON        VARCHAR2(20),
  EXECPERSONID      VARCHAR2(20),
  DOCPERSON         VARCHAR2(20),
  DOCPERSONID       VARCHAR2(20),
  INFOTEXT0         VARCHAR2(300),
  INFOTEXT1         VARCHAR2(50),
  INFOTEXT2         VARCHAR2(50),
  INFOTEXT3         VARCHAR2(50),
  INFOTEXT4         VARCHAR2(50),
  INFOTEXT5         VARCHAR2(50),
  INFOTEXT6         VARCHAR2(50),
  INFOTEXT7         VARCHAR2(50),
  INFOTEXT8         VARCHAR2(50),
  INFOTEXT9         VARCHAR2(50),
  TOTALOPERTIMES    NUMBER,
  ACTUALOPERTIMES   NUMBER,
  BUGLIST           VARCHAR2(500),
  DEVICELIST        VARCHAR2(500),
  BUGIDLIST         VARCHAR2(255),
  DEVICEIDLIST      VARCHAR2(255),
  DEVICEBUG         VARCHAR2(255),
  SHEETIDLIST       VARCHAR2(255),
  SHEETLIST         VARCHAR2(500),
  LASTOPERATETIME   VARCHAR2(20),
  DELREASON         VARCHAR2(500),
  BACKREASON        VARCHAR2(500),
  CHILDSHEETNUM     NUMBER,
  RUNJLDM           NUMBER,
  TREENODEID        VARCHAR2(20),
  OLDSHEETID        NUMBER,
  FROMMODEL         NUMBER,
  UNDOREASON        VARCHAR2(100),
  CELLID            NUMBER(10),
  PICTUREID         NUMBER(10),
  SENDERID          VARCHAR2(20),
  RUNSCENEID        NUMBER,
  ZYDM              NUMBER,
  STATIONTYPE       NUMBER,
  CELLSTATUSID      NUMBER,
  FIRSTAUDITING     NUMBER,
  FIRAUDITREASON    VARCHAR2(200),
  SECONDAUDITING    NUMBER,
  SECAUDITREASON    VARCHAR2(200),
  THIRDAUDITING     NUMBER,
  THIRAUDITREASON   VARCHAR2(200),
  FIRAUDITER        VARCHAR2(50),
  SECAUDITER        VARCHAR2(50),
  THIRAUDITER       VARCHAR2(50),
  AUDITING          NUMBER,
  AUDITSIGN         NUMBER,
  TASKID            VARCHAR2(20),
  TASKSTATUS        NUMBER(10),
  PATTERNNAME       VARCHAR2(100)
)
alter table WS_SHEETINDEX
  add constraint PK_WS_SHEETINDEX primary key (SHEETID);
alter table WS_SHEETINDEX
  add constraint CHECKSHEETID
  check (SHEETID>0);create index INDEX_WS_SHEETINDEX1 on WS_SHEETINDEX (ISMAINSHEET, SHEETTYPE, SHEETSTATUSID, MAKETIME DESC)
create index WS_SHEETINDEX_INDEX1 on WS_SHEETINDEX (SHEETID)
create index WS_SHEETINDEX_INDEX2 on WS_SHEETINDEX (SHEETID, MAINSHEETID)
create index WS_SHEETINDEX_INDEX3 on WS_SHEETINDEX (PATTERNID, PAGENUM)
create index WS_SHEETINDEX_INDEX4 on WS_SHEETINDEX (MAINSHEETID, SHEETTYPE, PATTERNID)
create index WS_SHEETINDEX_INDEX5 on WS_SHEETINDEX (ISMAINSHEET, SHEETTYPE, SHEETSTATUSID, DEPTID, PATTERNID)

Answer 2

个人拙见...仅供参考
1.*用具体的字段代替
2.在MAKETIME字段上建立减序索引
3.多使用exist代替inner join
4.寻找where条件从右向左的执行顺序,把过滤大的条件写where条件后面