搜索23620条数据用了那么多时间表结构我发出来 -- Create table
create table BIBLIO
(
id NUMBER(30) not null,
marc_content CLOB,
marc_format NUMBER(2),
book_type NUMBER(1),
attachment VARCHAR2(300),
address VARCHAR2(300),
author VARCHAR2(600),
classno VARCHAR2(100),
publish_date VARCHAR2(100),
isbn VARCHAR2(100),
issn VARCHAR2(100),
unionno VARCHAR2(100),
language VARCHAR2(100),
page VARCHAR2(100),
price VARCHAR2(100),
total_price VARCHAR2(100),
publisher VARCHAR2(300),
book_size VARCHAR2(100),
title VARCHAR2(900),
edition VARCHAR2(300),
notes VARCHAR2(4000),
subject VARCHAR2(1500),
summary VARCHAR2(4000),
frequency VARCHAR2(200),
status NUMBER(2) not null,
operator_id NUMBER(30),
create_date DATE,
school_id NUMBER(30),
state NUMBER(1) default 1 not null,
bookrecno NVARCHAR2(255),
recno NUMBER(10),
field906 NVARCHAR2(200),
order_no NVARCHAR2(20)
)
tablespace CLOUDLIBDB
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Add comments to the table
comment on table BIBLIO
is '书目表';
-- Add comments to the columns
comment on column BIBLIO.id
is '书目记录号';
comment on column BIBLIO.marc_content
is 'marc数据内容';
comment on column BIBLIO.marc_format
is '书目格式,中文=CNMRAC,西文=USMARC';
comment on column BIBLIO.book_type
is '资料类型,区别资料的属性,如book=1,series=2,media=3,other=4';
comment on column BIBLIO.attachment
is '附件';
comment on column BIBLIO.address
is '出版地';
comment on column BIBLIO.author
is '著者';
comment on column BIBLIO.classno
is '分类号';
comment on column BIBLIO.publish_date
is '出版日期';
comment on column BIBLIO.isbn
is 'ISBN代码,国际标准书号';
comment on column BIBLIO.issn
is 'ISSN代码,国际杂志统一刊号';
comment on column BIBLIO.unionno
is '统一书号';
comment on column BIBLIO.language
is '语种';
comment on column BIBLIO.page
is '页码';
comment on column BIBLIO.price
is '价格';
comment on column BIBLIO.total_price
is '整套价格';
comment on column BIBLIO.publisher
is '出版社';
comment on column BIBLIO.book_size
is '尺寸';
comment on column BIBLIO.title
is '题名';
comment on column BIBLIO.edition
is '版次';
comment on column BIBLIO.notes
is '附注内容';
comment on column BIBLIO.subject
is '主题';
comment on column BIBLIO.summary
is '提要及文摘';
comment on column BIBLIO.frequency
is '出版频率';
comment on column BIBLIO.status
is '编目状态:直接接收=0,原编=1,接收书商=2,z3950新增=3,z3950合并=4';
comment on column BIBLIO.operator_id
is '操作者ID';
comment on column BIBLIO.create_date
is '编目时间';
comment on column BIBLIO.school_id
is '创建日期';
comment on column BIBLIO.state
is '假删除状态: 1.正常 2.已删除';
-- Create/Recreate indexes
create index IDX_BIB_AUTHOR on BIBLIO (AUTHOR)
tablespace CLOUDLIBDB
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
create index IDX_BIB_ISBN on BIBLIO (ISBN)
tablespace CLOUDLIBDB
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
create index IDX_BIB_ISSN on BIBLIO (ISSN)
tablespace CLOUDLIBDB
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
create index IDX_BIB_PUBLISHER on BIBLIO (PUBLISHER)
tablespace CLOUDLIBDB
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
create index IDX_BIB_RECNO on BIBLIO (RECNO)
tablespace CLOUDLIBDB
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
create index IDX_BIB_TITLE on BIBLIO (TITLE)
tablespace CLOUDLIBDB
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table BIBLIO
add constraint PK_BIBLIO primary key (ID)
using index
tablespace CLOUDLIBDB
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);biblio表里有1172283数据 为什么会搜索那么慢呢 求高手提供个优化方案啊
不过看你的表,好像school_id上没索引哦
create index idx_bib_sch_sta on biblio(school_id, state);
基本每次查询都要用到school_id
查看执行计划有好多种方法,经常用到有两种:
1)
http://blog.csdn.net/linwaterbin/article/details/77733682)直接explain for,然后到内存表table查询即可
?
其他几个索引在程序运行的时候用到很多 索引给他建了 都是常用的字段
只是这次SQL里面没有
单列索引多了也影响性能的吧
如果要建立索引,注意你这个SCHOOL_ID的重复率是否很高,很高的话建议使用位图索引,
如果你这个stat也要建立索引,看起来是状态的意思,估计值重复率很高,建议用位图索引