一个查询语句的优化

Question

sql语句如下：
SELECT   ins.process_ins_id
                         FROM t_activity_ins ins INNER JOIN t_activity_def ad ON ad.activity_def_id =
                                                                                   ins.activity_def_id
                        WHERE start_time IS NOT NULL
                          AND ins.process_def_id = ad.process_def_id
                     GROUP BY ins.process_ins_id
执行sql的执行计划如下：
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStopSELECT STATEMENT Optimizer Mode=CHOOSE 1 K   119                           
  SORT GROUP BY 1 K 51 K 119                           
    HASH JOIN 3 K 169 K 92                           
      INDEX FAST FULL SCAN TMSFZWF.PK_T_ACTIVITY_DEF 4 K 44 K 3                           
      TABLE ACCESS FULL TMSFZWF.T_ACTIVITY_INS 4 K 160 K 86                           
现在想把T_ACTIVITY_INS的全连接换成用索引查询，应该怎样建索引呢？
附上相关表的建表sqlCREATE TABLE T_ACTIVITY_INS
(
  ACTIVITY_INS_ID   NUMBER(10)                  NOT NULL,
  PROCESS_INS_ID    NUMBER(10),
  M_ID              NUMBER(10),
  PROCESS_DEF_ID    NUMBER(10),
  ACTIVITY_DEF_ID   NUMBER(10),
  FLOW_ID           NUMBER(10),
  START_TIME        CHAR(19 BYTE),
  END_TIME          CHAR(19 BYTE),
  STATE             NUMBER(2),
  PRESTATE          NUMBER(2),
  PARTICIPANT       VARCHAR2(100 BYTE),
  PARTICIPANT_TYPE  NUMBER(1)
)
TABLESPACE TMSFZ
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCACHE
NOPARALLEL;
CREATE UNIQUE INDEX PK_T_ACTIVITY_INS ON T_ACTIVITY_INS
(ACTIVITY_INS_ID)
LOGGING
TABLESPACE TMSFZ
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;
CREATE INDEX R13_FK ON T_ACTIVITY_INS
(PROCESS_INS_ID)
LOGGING
TABLESPACE TMSFZ
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;
CREATE INDEX R17_FK ON T_ACTIVITY_INS
(M_ID, PROCESS_DEF_ID, ACTIVITY_DEF_ID)
LOGGING
TABLESPACE TMSFZ
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;
ALTER TABLE T_ACTIVITY_INS ADD (
  CONSTRAINT PK_T_ACTIVITY_INS PRIMARY KEY (ACTIVITY_INS_ID)
    USING INDEX 
    TABLESPACE TMSFZ
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                PCTINCREASE      0
               ));
ALTER TABLE T_ACTIVITY_INS ADD (
  CONSTRAINT FK_T_ACTIVI_R13_T_PROCES FOREIGN KEY (PROCESS_INS_ID) 
    REFERENCES T_PROCESS_INS (PROCESS_INS_ID));ALTER TABLE T_ACTIVITY_INS ADD (
  CONSTRAINT FK_T_ACTIVI_R17_T_ACTIVI FOREIGN KEY (M_ID, PROCESS_DEF_ID, ACTIVITY_DEF_ID) 
    REFERENCES T_ACTIVITY_DEF (M_ID,PROCESS_DEF_ID,ACTIVITY_DEF_ID));CREATE TABLE T_ACTIVITY_DEF
(
  M_ID                       NUMBER(10)         NOT NULL,
  PROCESS_DEF_ID             NUMBER(10)         NOT NULL,
  ACTIVITY_DEF_ID            NUMBER(10)         NOT NULL,
  NAME                       VARCHAR2(40 BYTE),
  DESCRIPTION                VARCHAR2(400 BYTE),
  TIME_LIMIT                 NUMBER(10),
  TIME_WAIT                  NUMBER(10),
  TIME_UNIT                  VARCHAR2(10 BYTE),
  OVER_TIME_TYPE             NUMBER(1),
  TIME_LIMIT_TYPE            NUMBER(1),
  PREVIOUS                   NUMBER(1),
  REASSIGN_PARTICIPANT_NAME  VARCHAR2(400 BYTE),
  REASSIGN_PARTICIPANT_ID    NUMBER(10),
  REASSIGN_PARTICIPANT_TYPE  NUMBER(1),
  CONDITION_TRANSITION       NUMBER(1),
  NO_CONDITION_TRANSITION    NUMBER(1),
  ACCESS_RESTRICTION         VARCHAR2(4000 BYTE),
  BASETYPE                   NUMBER(2),
  ACTIVITY_TYPE              NUMBER(2),
  START_MODE                 NUMBER(1),
  PRIORITY                   NUMBER(2),
  JOIN_TYPE                  NUMBER(1),
  SPLIT_TYPE                 NUMBER(1),
  PARTICIPANT                NUMBER(10),
  PARTICIPANT_TYPE           NUMBER(2),
  FORM_ID                    NUMBER(10),
  FORM_TYPE                  NUMBER(1),
  FORM_IN_ARGS               VARCHAR2(4000 BYTE),
  FORM_OUT_ARGS              VARCHAR2(4000 BYTE),
  SUBPROCESS_FILE_NAME       VARCHAR2(256 BYTE),
  SUBPROCESS_ID              NUMBER(10),
  SUBPROCESS_START_TYPE      NUMBER(1),
  SUBPROCESS_IN_ARGS         VARCHAR2(2000 BYTE),
  SUBPROCESS_OUT_ARGS        VARCHAR2(2000 BYTE),
  APP_ID                     VARCHAR2(400 BYTE),
  IS_STATIC                  NUMBER(1),
  PARENT_ACTIVITY_DEF_ID     NUMBER(10),
  X_POSITION                 NUMBER(6),
  Y_POSITION                 NUMBER(6),
  APP_NAME                   VARCHAR2(40 BYTE),
  APP_IN_ARGS                VARCHAR2(400 BYTE),
  APP_OUT_ARGS               VARCHAR2(400 BYTE),
  PARTICIPANT_NAME           VARCHAR2(4000 BYTE),
  FORM_NAME                  VARCHAR2(4000 BYTE),
  LABEL_FONT_NAME            VARCHAR2(100 BYTE),
  LABEL_FONT_BGCOLOR         VARCHAR2(10 BYTE),
  LABEL_FONT_SIZE            VARCHAR2(10 BYTE),
  LABEL_FONT_COLOR           VARCHAR2(10 BYTE),
  IS_OPAQUE                  CHAR(1 BYTE),
  SUBPROCESS_NAME            VARCHAR2(40 BYTE),
  SUBPROCESS_REMOTE_SERVER   VARCHAR2(40 BYTE),
  PRESCRIPT                  VARCHAR2(4000 BYTE),
  POSSCRIPT                  VARCHAR2(4000 BYTE)
)
TABLESPACE TMSFZ
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCACHE
NOPARALLEL;
CREATE UNIQUE INDEX PK_T_ACTIVITY_DEF ON T_ACTIVITY_DEF
(M_ID, PROCESS_DEF_ID, ACTIVITY_DEF_ID)
LOGGING
TABLESPACE TMSFZ
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;
CREATE INDEX R7_FK ON T_ACTIVITY_DEF
(M_ID, PROCESS_DEF_ID)
LOGGING
TABLESPACE TMSFZ
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;
ALTER TABLE T_ACTIVITY_DEF ADD (
  CONSTRAINT PK_T_ACTIVITY_DEF PRIMARY KEY (M_ID, PROCESS_DEF_ID, ACTIVITY_DEF_ID)
    USING INDEX 
    TABLESPACE TMSFZ
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                PCTINCREASE      0
               ));
ALTER TABLE T_ACTIVITY_DEF ADD (
  CONSTRAINT FK_T_ACTIVI_R7_T_PROCES FOREIGN KEY (M_ID, PROCESS_DEF_ID) 
    REFERENCES T_PROCESS_DEF (M_ID,PROCESS_DEF_ID));

Answer 1

a,b两个表经行关联的时候，如果左连接或者是右连接的话就就不能使用索引。
a,b连个表连接的话。如果其中的一个表的数据较少的话，一个表数据较多的话，这样情况下适合“循环嵌套连接”，数据量小的表作为驱动表。并且在连接的列上面必须有索引。
a,b连个表数据量都比较大的话，采用“哈希连接”速度更快些。“哈希连接”的时候数据库的优化器不会采用索引。
通常情况下，数据库做自动选择代价最小的路径（sql是通过基于成本的优化器，而不是基于规则的），
如果相关的统计信息没有更新的话，数据库的优化器做出来错误的判断。
可以强制一下采用的连接方式。看看哪种效果比较好。毕竟，“实践是掌握真理的唯一标准。”

Answer 2

看你的执行计划基于choose方式，你做了统计数据吗，如果做了将使用COST方式，如没有，将基于RULE，这两种方式执行计划可能不一样。

Answer 3

楼主是inner join，单独建立一个外键索引CREATE INDEX myindex ON T_ACTIVITY_INS
(ACTIVITY_DEF_ID)
这样，在表连接时就能用上该索引了