oracle使用索引的问题

Question

-- Create table
create table IPMONMEX
(
  EXEC_SQN        VARCHAR2(16) not null,
  INPATIENT_NO    VARCHAR2(14) not null,
  PATIENT_NO      VARCHAR2(10) not null,
  DEPT_CODE       VARCHAR2(4) not null,
  NURSE_CELL_CODE VARCHAR2(4) not null,
  MO_ORDER        VARCHAR2(16) not null,
  DOC_CODE        VARCHAR2(6),
  DOC_NAME        VARCHAR2(10),
  MO_DATE         DATE,
  BABY_FLAG       CHAR(1) not null,
  HAPPEN_NO       NUMBER(5),
  SET_ITMATTR     CHAR(1),
  SET_SUBTBL      CHAR(1),
  TYPE_CODE       VARCHAR2(2),
  DECMPS_STATE    CHAR(1),
  CHARGE_STATE    CHAR(1) not null,
  PRN_EXELIST     CHAR(1) not null,
  NEED_CONFIRM    CHAR(1),
  UNDRUG_CODE     VARCHAR2(12),
  INPUT_CODE      VARCHAR2(8),
  SPELL_CODE      VARCHAR2(8),
  UNDRUG_NAME     VARCHAR2(50),
  CLASS_CODE      VARCHAR2(3),
  CLASS_NAME      VARCHAR2(10),
  EXEC_DPCD       VARCHAR2(4),
  EXEC_DPNM       VARCHAR2(16),
  COMB_NO         VARCHAR2(14),
  MAIN_DRUG       CHAR(1),
  USAGE_CODE      VARCHAR2(3),
  USE_NAME        VARCHAR2(20),
  ENGLISH_AB      VARCHAR2(4),
  DFQ_FREQ        VARCHAR2(6),
  DFQ_CEXP        VARCHAR2(30),
  QTY_TOT         NUMBER(5),
  STOCK_UNIT      VARCHAR2(10),
  UNIT_PRICE      NUMBER(12,6),
  USE_TIME        DATE not null,
  EMC_FLAG        CHAR(1),
  VALID_FLAG      CHAR(1),
  VALID_DATE      DATE,
  VALID_USERCD    VARCHAR2(6),
  CONFIRM_FLAG    CHAR(1),
  CONFIRM_DATE    DATE,
  CONFIRM_USERCD  VARCHAR2(6),
  CONFIRM_DEPTCD  VARCHAR2(4),
  EXECUTE_FLAG    CHAR(1),
  EXECUTE_DATE    DATE,
  EXECUTE_USERCD  VARCHAR2(6),
  EXECUTE_DEPTCD  VARCHAR2(4),
  CHARGE_FLAG     CHAR(1),
  CHARGE_DATE     DATE,
  CHARGE_USERCD   VARCHAR2(6),
  CHARGE_DEPTCD   VARCHAR2(4),
  ITEM_NOTE       VARCHAR2(8),
  APPLY_NO        VARCHAR2(16),
  MO_NOTE1        VARCHAR2(80),
  MO_NOTE2        VARCHAR2(80),
  DECO_DATE       DATE,
  FIRST_DAY       CHAR(1) not null,
  EXEC_PRNFLAG    CHAR(1) default '1',
  EXEC_PRNDATE    DATE,
  EXEC_PRNUSERCD  VARCHAR2(6),
  NOTE_NO         VARCHAR2(14),
  SUBTBL_FLAG     VARCHAR2(1),
  UNEXE_NUM       NUMBER default 1,
  OPER_FLAG       VARCHAR2(1) default '0'
)
partition by range (EXEC_SQN)
(
  partition DATE2005_1 values less than ('2006010100000000')
    tablespace USERS2005
    pctfree 10
    pctused 40
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
  partition DATE2006_1 values less than ('2007010100000000')
    tablespace USERS2006
    pctfree 10
    pctused 40
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
  partition DATE2007_1 values less than ('2008010100000000')
    tablespace USERS2007
    pctfree 10
    pctused 40
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
  partition DATE2008_1 values less than ('2009010100000000')
    tablespace USERS2008
    pctfree 10
    pctused 40
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
  partition DATE2009_1 values less than ('2010010100000000')
    tablespace USERS2009
    pctfree 10
    pctused 40
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    )
)
;-- Create/Recreate primary, unique and foreign key constraints 
alter table IPMONMEX
  add constraint PK_IPMONMEX primary key (EXEC_SQN)
  using index 
  tablespace INDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate indexes 
create index I_IPMONMEX02 on IPMONMEX (CHARGE_STATE)
  tablespace INDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
create index I_IPMONMEX05 on IPMONMEX (MO_ORDER, USE_TIME)
  tablespace INDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
create index I_IPMONMEX06 on IPMONMEX (USE_TIME, COMB_NO)
  tablespace INDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
create index I_IPMONMEX07 on IPMONMEX (NOTE_NO)
  tablespace INDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
create index I_IPMONMEX08 on IPMONMEX (INPATIENT_NO, CHARGE_STATE)
  tablespace INDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
create index I_IPMONMEX_09 on IPMONMEX (COMB_NO)
  tablespace INDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
create index I_IPMONMEX_10 on IPMONMEX (VALID_FLAG, CLASS_CODE)
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
索引：
I_IPMONMEX02 Normal CHARGE_STATE             
I_IPMONMEX05 Normal MO_ORDER, USE_TIME     
I_IPMONMEX06 Normal USE_TIME, COMB_NO    
I_IPMONMEX07 Normal NOTE_NO                     
I_IPMONMEX08 Normal INPATIENT_NO, CHARGE_STATE  
I_IPMONMEX_09 Normal COMB_NO                     
I_IPMONMEX_10 Normal VALID_FLAG, CLASS_CODE  执行这个语句
select a.charge_state   /*+index(ipmonmex I_IPMONMEX_09)*/  (/*+index(a I_IPMONMEX_09)*/)   这两个方式都试过
  from ipmonmex a
 where a.charge_state = '1'
   and a.comb_no = '20090101622602'  
oralce优化器用到的是 I_IPMONMEX02 索引  ，而实际想用到的是I_IPMONMEX_09索引 检索的速度相当慢 3分钟左右
即使用了强制使用I_IPMONMEX_09这个索引，也还是执行I_IPMONMEX02 索引select a.charge_state
  from ipmonmex a
 where  a.comb_no = '20090101622602' 
用到的是 I_IPMONMEX_09索引，检索的速度相当快，不到一秒。想问一下，oracle的优化器为什么没有使用I_IPMONMEX_09这个索引那，即使是强制使用也没有效果，
Oracle优化器使用索引的规则是什么那 谢谢各位大虾了  

Answer 1

你要相信CBO~~
不需要HINTS~
因为I_IPMONMEX02 和I_IPMONMEX09的级别相同，CBO就选择了I_IPMONMEX02 索引~~
你只需要屏蔽掉I_IPMONMEX02 索引就可以了~~
TRY ITselect a.charge_state  /*+index(ipmonmex I_IPMONMEX_09)*/  (/*+index(a I_IPMONMEX_09)*/)  这两个方式都试过 
  from ipmonmex a 
where a.charge_state||'' = '1' --在这里，我们通过对charge_state列进行了运算达到了让I_IPMONMEX02 索引失效的目的
  and a.comb_no = '20090101622602'  

Answer 2

PS:当ORACLE无法判断索引的等级高低差别,优化器将只使用一个索引,它就是在WHERE子句中被列在最前面的.

Answer 3

select a.charge_state
  from ipmonmex a
 where a.comb_no = '20090101622602'
   and a.charge_state = '1'
我试验了一下 oracle优化器还是使用I_IPMONMEX02 这个索引

Answer 4

让索引失效的方法有创意，我怎么就没有想到那，我还是太嫩了啊，呵呵！“我感谢你八辈祖宗，我做鬼也能放不过你”，呵呵
大侠有没有oracle优化器如何选择索引的文章，给小弟发一个啊，小弟在此谢谢了啊！

Answer 5

数据库是什么版本?
如果9i,试一下:
ALTER SESSION SET OPTIMIZER_GOAL = rule;
select a.charge_state  /*+index(ipmonmex I_IPMONMEX_09)*/  (/*+index(a I_IPMONMEX_09)*/)  选一个用  from ipmonmex a 
where a.charge_state = '1' 
  and a.comb_no = '20090101622602'  

Answer 6

数据库是oracle9i的。
楼上的我实验了一下，还是不好用，还是没有使用I_IPMONMEX_09这个索引，where子句中引用多个索引的时候，oracle的优化器是怎样选择索引的那。
谢谢各位了！

Answer 7

正确来讲,oracle用了idx2就正确的,来看一下你的SQL:
select a.charge_state 
  from ipmonmex a 
where a.comb_no = '20090101622602' 
  and a.charge_state = '1' 主要是红色标记那里,因为oracle查询条件是从下而上的,所以会先执行a.charge_state = '1' 这个条件,因为索引器查到charge_state有索引,所以会首先用到这个index,我想应该是这样,如有不对,请指出.

Answer 8

如果你想用idx9可以把where后面的条件换一下,
select a.charge_state 
  from ipmonmex a 
where  a.charge_state = '1'  
     and a.comb_no = '20090101622602' 
  

Answer 9

数据库是oracle9i的。 
楼上的我实验了一下，还是不好用，还是没有使用I_IPMONMEX_09这个索引，where子句中引用多个索引的时候，oracle的优化器是怎样选择索引的那。 
谢谢各位了！ 

Answer 10

。。还没解决呀？
当ORACLE无法判断WHERE后面的条件所包含的索引优先级的时候，会自动选择WHERE后面的第一个索引；
给你2条建议
1：重建I_IPMONMEX_09；
2:贴出执行计划（包含1楼我给出的SQL的执行计划），让大家仔细分析一下