表結構如下：
-- Create table
create table WIP2ST_RECORD
(
RECORD_ID
CHAR(20) not null,
POST_ST_MODE_ID CHAR(2),
THIS_BASE_ID
VARCHAR2(30),
THIS_SPLIT_ID VARCHAR2(3),
THIS_LAYER
VARCHAR2(10),
THIS_LOT_ID VARCHAR2(30),
THIS_VER
VARCHAR2(10),
THIS_ST_ID
VARCHAR2(10),
NEXT_ST_ID
VARCHAR2(10),
NUM_WPNL
NUMBER(15) not null,
NUM_SPNL
NUMBER(15) not null,
NUM_PICS
NUMBER(15) not null,
SQU
VARCHAR2(10),
IN_UNIT_COST
VARCHAR2(30),
IN_COST_SUM NUMBER(17,4),
RUN_COST_SUM
DATE,
LABOUR_COST_SUM VARCHAR2(15),
MATERIEL_COST_SUM NUMBER(17,4),
SCRAP_APPORT_COST_SUM NUMBER(17,4),
CURRENT_FLAG
CHAR(1),
CUT_ID
CHAR(10) not null,
SUSPEND_FLAG
VARCHAR2(10),
TRANSACTION_IDVARCHAR2(20),
OUT_ID
VARCHAR2(20),
IN_ID
VARCHAR2(20),
OP_NAME
VARCHAR2(15),
OP_DATE
DATE default SYSDATE not null,
COST_FLAG
CHAR(1),
SHIFT_ID
VARCHAR2(1),
SCRAP_NUM
NUMBER(15),
NUM_FLAG
CHAR(1),
SEQUENCE_NO NUMBER(38),
CURRENT_LAYER VARCHAR2(3),
LAST_SEQ_NO NUMBER(38),
PART_ID
VARCHAR2(30),
ACCEPT_FLAG CHAR(1),
REWORK_BASE_IDVARCHAR2(30),
OUT_OP_DATE DATE,
MACHINE_NO
VARCHAR2(10),
ENG_NO
VARCHAR2(30),
M_ENG_NO
VARCHAR2(30),
MAX_LOT
NUMBER,
ACCEPT_DATE DATE,
REMOVE_RESON
VARCHAR2(200),
COMP_ID
VARCHAR2(2) not null,
STAGE
VARCHAR2(10) not null,
CURRENT_ST
VARCHAR2(10),
DATECODE
VARCHAR2(10),
YELLOW_ID
VARCHAR2(20),
BASE_FLAG
VARCHAR2(10)
)
tablespace USER_DATA
pctfree 50
pctused 30
initrans 1
maxtrans 255
storage
(
initial 804480K
next 888888K
minextents 1
maxextents 650
pctincrease 5
);
-- Create/Recreate primary, unique and foreign key constraints
alter table WIP2ST_RECORD
add constraint WIP2ST_RECORD_P primary key (RECORD_ID)
using index
tablespace USER_INDEX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 112128K
next 27496K
minextents 1
maxextents 200
pctincrease 20
);
alter table WIP2ST_RECORD
add constraint PK_CURRENT_ST foreign key (CURRENT_ST)
references WIP2ST (ST_ID);
alter table WIP2ST_RECORD
add constraint PK_NEXT_ST_ID foreign key (NEXT_ST_ID)
references WIP2ST (ST_ID);
alter table WIP2ST_RECORD
add constraint PK_PART foreign key (PART_ID)
references PART (ID);
alter table WIP2ST_RECORD
add constraint PK_ST_ID foreign key (THIS_ST_ID)
references WIP2ST (ST_ID);
-- Create/Recreate check constraints
alter table WIP2ST_RECORD
add constraint CHECK_BASE_FLAG
check (BASE_FLAG IN ('Y','N',NULL));
alter table WIP2ST_RECORD
add constraint CHECK_COST_FLAG
check (COST_FLAG IN ('Y',NULL));
-- Create/Recreate indexes
create index CURRENT_WIP on WIP2ST_RECORD (THIS_BASE_ID, PART_ID, THIS_VER, CURRENT_ST)
tablespace USER_INDEX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 1280K
next 178776K
minextents 1
maxextents 4096
pctincrease 70
);
create bitmap index IDXWIPNEXTSTID on WIP2ST_RECORD (NEXT_ST_ID)
tablespace USER_INDEX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 15104K
next 15184K
minextents 1
maxextents unlimited
pctincrease 20
);
create bitmap index IDXWIPTHISSTID on WIP2ST_RECORD (THIS_ST_ID)
tablespace USER_INDEX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 16000K
next 16128K
minextents 1
maxextents unlimited
pctincrease 20
);
create index IDX_ACCEPT_DATE on WIP2ST_RECORD (ACCEPT_DATE)
tablespace USER_INDEX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 1280K
next 105160K
minextents 1
maxextents 4096
pctincrease 70
);
create index IDX_BSELAYERLOT on WIP2ST_RECORD (THIS_BASE_ID, THIS_LAYER, THIS_LOT_ID)
tablespace USER_INDEX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 112256K
next 31232K
minextents 1
maxextents unlimited
pctincrease 20
);
create index IDX_CURRENT_ST on WIP2ST_RECORD (CURRENT_ST)
tablespace USER_INDEX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 1280K
next 516664K
minextents 1
maxextents 4096
pctincrease 70
);
create index IDX_IN_COST_SUM on WIP2ST_RECORD (IN_COST_SUM, IN_UNIT_COST, OUT_OP_DATE)
tablespace USER_INDEX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 3840K
next 8640K
minextents 1
maxextents 40960
pctincrease 50
);
create index IDX_OP_DATE on WIP2ST_RECORD (OP_DATE)
tablespace USER_INDEX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 68352K
next 17824K
minextents 1
maxextents unlimited
pctincrease 20
);
create index IDX_PARTID on WIP2ST_RECORD (PART_ID)
tablespace USER_INDEX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 82816K
next 21664K
minextents 1
maxextents unlimited
pctincrease 20
);
create index IDX_TRANSACTION on WIP2ST_RECORD (TRANSACTION_ID)
tablespace USER_INDEX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 1280K
next 2560K
minextents 1
maxextents 4096
pctincrease 70
);
create index INX_ENG_NO on WIP2ST_RECORD (ENG_NO)
tablespace USER_INDEX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 131840K
next 65616K
minextents 1
maxextents 40960
pctincrease 50
);
create index INX_MENG_NO on WIP2ST_RECORD (M_ENG_NO)
tablespace USER_INDEX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 131840K
next 65616K
minextents 1
maxextents 40960
pctincrease 50
);
create index WIP2ST_CURRENT1 on WIP2ST_RECORD (THIS_BASE_ID, CURRENT_ST, PART_ID, THIS_VER, STAGE)
tablespace USER_INDEX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 1280K
next 178776K
minextents 1
maxextents 4096
pctincrease 70
);
create index WIP2ST_CURRENT2 on WIP2ST_RECORD (THIS_BASE_ID, PART_ID, THIS_VER)
tablespace USER_INDEX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 1280K
next 105160K
minextents 1
maxextents 4096
pctincrease 70
);
create index WIP2ST_SUSPEND on WIP2ST_RECORD (SUSPEND_FLAG)
tablespace USER_INDEX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 1280K
next 61856K
minextents 1
maxextents 4096
pctincrease 70
);
create index WIP2ST_THIS_VER on WIP2ST_RECORD (THIS_VER)
tablespace USER_INDEX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 1280K
next 61856K
minextents 1
maxextents 4096
pctincrease 70
);
為何我查詢下面的語句有時會要旨10分鐘
SELECT DISTINCT THIS_BASE_ID, PART_ID, THIS_VER
FROM WIP2ST_RECORD
WHERE SUSPEND_FLAG LIKE '%'
AND (THIS_BASE_ID LIKE '%' OR THIS_BASE_ID LIKE '%')
AND COMP_ID = '%'
AND CURRENT_FLAG = 'Y'
AND CURRENT_ST'MRBOK'
AND CURRENT_ST'MRB'
AND CURRENT_ST'PK'
是索引問題嗎？
謝謝指教....
|
