ORACLE数据库,有一个表,有1.3千万行数据,经常和别的表作交叉查询,比如统计一个月内分类的收入,查询速度很慢!优化查询速度,除了过期数据转储外,可以从索引上优化 吗???? 比如再建立一个索引?哈哈
SELECT "INP_BILL_DETAIL"."PATIENT_ID", 病人ID
"INP_BILL_DETAIL"."VISIT_ID", 病人住院次数
"INP_BILL_DETAIL"."ITEM_NO", 项目序号
"INP_BILL_DETAIL"."ITEM_CLASS", 项目类别
"INP_BILL_DETAIL"."ITEM_NAME", 项目名称
"INP_BILL_DETAIL"."ITEM_CODE", 项目代码
"INP_BILL_DETAIL"."ITEM_SPEC", 项目规格
"INP_BILL_DETAIL"."AMOUNT", 项目数量
"INP_BILL_DETAIL"."UNITS", 项目单位
"INP_BILL_DETAIL"."ORDERED_BY", 开单科室
"INP_BILL_DETAIL"."PERFORMED_BY", 执行科室
"INP_BILL_DETAIL"."COSTS", 项目计价费用
"INP_BILL_DETAIL"."CHARGES", 项目应收费用
"INP_BILL_DETAIL"."BILLING_DATE_TIME", 项目计价时间
"INP_BILL_DETAIL"."OPERATOR_NO", 计价员代码
"INP_BILL_DETAIL"."RCPT_NO", 结算收据号
"INP_BILL_DETAIL"."SPECIAL_CHARGES", 特殊支出标志
"INP_BILL_DETAIL"."FREE_LIMIT"
FROM "INP_BILL_DETAIL"
该表下的索引有2个了:
第一个索引:
CREATE INDEX "INPBILL"."IND_1_INP_BILL_DETAIL"
ON "INPBILL"."INP_BILL_DETAIL"("RCPT_NO")
TABLESPACE "TSP_INPBILL" PCTFREE 20 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 228664K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0 FREELISTS 1)
第二个索引:
CREATE UNIQUE INDEX "INPBILL"."PK_INP_BILL_DETAIL"
ON "INPBILL"."INP_BILL_DETAIL"("PATIENT_ID", "VISIT_ID",
"ITEM_NO")
TABLESPACE "TSP_INPBILL" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 393600K NEXT 6144K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0 FREELISTS 1)
有必要建立第三个索引吗?建立第三个索引我担心占据的表空间会加大的多!