SELECT COUNT(*) FROM PC_PRODUCE WHERE PROWORKCENTER = 'C'
SELECT COUNT(*) FROM PC_PRODUCE WHERE PROWORKCENTER = 'D'
第一个查询满足条件的记录有16万多行,耗费了6秒的时间
第二个查询满足条件的记录有10万多行,却只耗费了0.06秒的时间
为什么两条一样的语句,记录数的相差不到2倍,而耗费的时间却相差100倍顺便说一下:如果需要,这是该表的表定义、索引和约束条件:
-- Create table
create table
(
JODNO VARCHAR2(16) not null,
PROWORKCENTER VARCHAR2(2) not null,
PROSN VARCHAR2(24) not null,
PROPRODUCED DATE,
PROWORKSTATE VARCHAR2(4),
PROQUALSTATE NUMBER(1),
PROINITAG NUMBER(1),
PRODESCRIPTION VARCHAR2(128),
PROBATCHNO VARCHAR2(12),
PROSTATE NUMBER(1),
PRODETAILCENTER VARCHAR2(2),
PROWORKTEAM VARCHAR2(4),
PROFINISHDT DATE,
PROMTAG NUMBER(1),
PROSNR VARCHAR2(24),
PROMCODE VARCHAR2(16),
DATA1 VARCHAR2(24),
PROQTY NUMBER(6)
)
tablespace DEV
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 57680K
next 43152K
minextents 1
maxextents unlimited
pctincrease 50
);
-- Create/Recreate primary, unique and foreign key constraints
alter table
add constraint PK_PC_PRODUCE primary key (JODNO,PROWORKCENTER,PROSN)
using index
tablespace DEV
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 38680K
next 28768K
minextents 1
maxextents unlimited
pctincrease 50
);
alter table
add constraint FK_PC_PRODU_RELATION__JM_JOBDI foreign key (JODNO)
references JM_JOBDISPATCH (JODNO);
-- Create/Recreate indexes
create index PK_PC_PRODUCE_1 on (PROSN)
tablespace DEV
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 18984K
next 4328K
minextents 1
maxextents unlimited
pctincrease 10
);
create index PK_PC_PRODUCE_2 on (JODNO,PROWORKCENTER,PROSN,PROPRODUCED)
tablespace DEV
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 31280K
next 6360K
minextents 1
maxextents unlimited
pctincrease 10
);
create index PK_PC_PRODUCE_3 on (PROWORKCENTER,PROSN,PROSNR)
tablespace DEV
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 4840K
next 7704K
minextents 1
maxextents unlimited
pctincrease 10
);
create index RELATION_461_FK on (JODNO)
tablespace DEV
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 17480K
next 12784K
minextents 1
maxextents unlimited
pctincrease 50
);
SELECT COUNT(*) FROM PC_PRODUCE WHERE PROWORKCENTER = 'D'
第一个查询满足条件的记录有16万多行,耗费了6秒的时间
第二个查询满足条件的记录有10万多行,却只耗费了0.06秒的时间
为什么两条一样的语句,记录数的相差不到2倍,而耗费的时间却相差100倍顺便说一下:如果需要,这是该表的表定义、索引和约束条件:
-- Create table
create table
(
JODNO VARCHAR2(16) not null,
PROWORKCENTER VARCHAR2(2) not null,
PROSN VARCHAR2(24) not null,
PROPRODUCED DATE,
PROWORKSTATE VARCHAR2(4),
PROQUALSTATE NUMBER(1),
PROINITAG NUMBER(1),
PRODESCRIPTION VARCHAR2(128),
PROBATCHNO VARCHAR2(12),
PROSTATE NUMBER(1),
PRODETAILCENTER VARCHAR2(2),
PROWORKTEAM VARCHAR2(4),
PROFINISHDT DATE,
PROMTAG NUMBER(1),
PROSNR VARCHAR2(24),
PROMCODE VARCHAR2(16),
DATA1 VARCHAR2(24),
PROQTY NUMBER(6)
)
tablespace DEV
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 57680K
next 43152K
minextents 1
maxextents unlimited
pctincrease 50
);
-- Create/Recreate primary, unique and foreign key constraints
alter table
add constraint PK_PC_PRODUCE primary key (JODNO,PROWORKCENTER,PROSN)
using index
tablespace DEV
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 38680K
next 28768K
minextents 1
maxextents unlimited
pctincrease 50
);
alter table
add constraint FK_PC_PRODU_RELATION__JM_JOBDI foreign key (JODNO)
references JM_JOBDISPATCH (JODNO);
-- Create/Recreate indexes
create index PK_PC_PRODUCE_1 on (PROSN)
tablespace DEV
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 18984K
next 4328K
minextents 1
maxextents unlimited
pctincrease 10
);
create index PK_PC_PRODUCE_2 on (JODNO,PROWORKCENTER,PROSN,PROPRODUCED)
tablespace DEV
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 31280K
next 6360K
minextents 1
maxextents unlimited
pctincrease 10
);
create index PK_PC_PRODUCE_3 on (PROWORKCENTER,PROSN,PROSNR)
tablespace DEV
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 4840K
next 7704K
minextents 1
maxextents unlimited
pctincrease 10
);
create index RELATION_461_FK on (JODNO)
tablespace DEV
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 17480K
next 12784K
minextents 1
maxextents unlimited
pctincrease 50
);
SELECT COUNT(*) FROM PC_PRODUCE WHERE PROWORKCENTER = 'C';SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
两条语句的时间都是3秒多,比较平均
两条语句的时间都是3秒多,比较平均
boydgmx(梦霄)的办法执行SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());时,提示:无法从非嵌套表中存取行,sungangsg(sungang)的方法用什么ORACLE什么工具分析呢