orderdet 表 ORDERDETID VARCHAR2(16) not null, ORDERID VARCHAR2(16) not null, PRODID VARCHAR2(16) not null, CONTACTID VARCHAR2(16) not null, PRODSCODE VARCHAR2(50), PRODNAME VARCHAR2(50), SOLDWITH VARCHAR2(10), STATUS VARCHAR2(10), RECKONING VARCHAR2(10), RECKONINGDT DATE, FBDT DATE, UPRICE NUMBER(10,2), UPNUM NUMBER(10), SPRICE NUMBER(10,2), SPNUM NUMBER(10), PAYMENT NUMBER(10,2), FREIGHT NUMBER(10,2), POSTFEE NUMBER(10,2) default 0, CLEARFEE NUMBER(10,2), ORDERDT DATE default sysdate, PROVINCEID VARCHAR2(10), STATE VARCHAR2(10), CITY VARCHAR2(50), MDUSR VARCHAR2(10), BREASON VARCHAR2(10), FEEDBACK VARCHAR2(10), GOODSBACK VARCHAR2(10), PRODUCTTYPE VARCHAR2(16), BACKDT DATE, BACKMONEY NUMBER, OLDPROD VARCHAR2(30), COMPENSATE NUMBER(10,2), PURPOSE VARCHAR2(10), JIFEN VARCHAR2(20), TICKET NUMBER(10) default 0, WEBID VARCHAR2(20), WAPID VARCHAR2(20), MAGAZINEID VARCHAR2(20) ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); -- Add comments to the columns comment on column ORDERDET.ORDERDETID is '订单详细ID'; comment on column ORDERDET.ORDERID is '订单ID'; comment on column ORDERDET.PRODID is '产品ID'; comment on column ORDERDET.CONTACTID is '订购人'; comment on column ORDERDET.PRODSCODE is '产品代码'; comment on column ORDERDET.PRODNAME is '产品名称'; comment on column ORDERDET.SOLDWITH is '是否搭销'; comment on column ORDERDET.STATUS is '产品状态'; comment on column ORDERDET.RECKONING is '是否结账'; comment on column ORDERDET.RECKONINGDT is '结账日期'; comment on column ORDERDET.FBDT is '反馈日期'; comment on column ORDERDET.UPRICE is '产品原价'; comment on column ORDERDET.UPNUM is '原价订购个数'; comment on column ORDERDET.SPRICE is '产品优惠价'; comment on column ORDERDET.SPNUM is '优惠价订购个数'; comment on column ORDERDET.PAYMENT is '实际付款'; comment on column ORDERDET.FREIGHT is '产品运费'; comment on column ORDERDET.POSTFEE is '投递费'; comment on column ORDERDET.CLEARFEE is '实际结算'; comment on column ORDERDET.ORDERDT is '订购日期'; comment on column ORDERDET.PROVINCEID is '省份编码'; comment on column ORDERDET.STATE is '订购人省份'; comment on column ORDERDET.CITY is '城市'; comment on column ORDERDET.MDUSR is '修改人'; comment on column ORDERDET.BREASON is '退货原因'; comment on column ORDERDET.FEEDBACK is '产品反馈结果'; comment on column ORDERDET.GOODSBACK is '退货地点'; comment on column ORDERDET.PRODUCTTYPE is '产品规格'; comment on column ORDERDET.BACKDT is '退货日期'; comment on column ORDERDET.BACKMONEY is '退货金额'; comment on column ORDERDET.OLDPROD is '原产品(只对换货产品有效)'; comment on column ORDERDET.COMPENSATE is '补差价'; comment on column ORDERDET.PURPOSE is '购买目的'; comment on column ORDERDET.JIFEN is '积分'; comment on column ORDERDET.WEBID is '网站编号'; comment on column ORDERDET.WAPID is '手机购物系统对应编号'; comment on column ORDERDET.MAGAZINEID is '杂志购物系统对应编号';
用 cursor 好像会影响效能,不然就看数据量大不大。
就拿取得品名例子示范一下(其它函数不知取的是什么): SELECT contact.contactid 联系人ID, (orderhist.crdt) AS 订购日期, contact.name AS 客户姓名, wm_concat(orderdet.prodname || '*' || to_char(orderdet.upnum + orderdet.spnum)) 品名, FROM contact, orderhist, orderset WHERE orderhist.contactid = contact.contactid AND orderhist.crdt >= to_date('2010-03-06 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND orderhist.crdt <= to_date('2010-03-07 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND contact.contactid = orderset.contactid GROUP BY contact.contactid, orderhist.crdt, contact.name;
其实写在一个语句里就可以了,orderset表应该有时间吧
给出你的orderset表字段
orderdet 表
ORDERDETID VARCHAR2(16) not null,
ORDERID VARCHAR2(16) not null,
PRODID VARCHAR2(16) not null,
CONTACTID VARCHAR2(16) not null,
PRODSCODE VARCHAR2(50),
PRODNAME VARCHAR2(50),
SOLDWITH VARCHAR2(10),
STATUS VARCHAR2(10),
RECKONING VARCHAR2(10),
RECKONINGDT DATE,
FBDT DATE,
UPRICE NUMBER(10,2),
UPNUM NUMBER(10),
SPRICE NUMBER(10,2),
SPNUM NUMBER(10),
PAYMENT NUMBER(10,2),
FREIGHT NUMBER(10,2),
POSTFEE NUMBER(10,2) default 0,
CLEARFEE NUMBER(10,2),
ORDERDT DATE default sysdate,
PROVINCEID VARCHAR2(10),
STATE VARCHAR2(10),
CITY VARCHAR2(50),
MDUSR VARCHAR2(10),
BREASON VARCHAR2(10),
FEEDBACK VARCHAR2(10),
GOODSBACK VARCHAR2(10),
PRODUCTTYPE VARCHAR2(16),
BACKDT DATE,
BACKMONEY NUMBER,
OLDPROD VARCHAR2(30),
COMPENSATE NUMBER(10,2),
PURPOSE VARCHAR2(10),
JIFEN VARCHAR2(20),
TICKET NUMBER(10) default 0,
WEBID VARCHAR2(20),
WAPID VARCHAR2(20),
MAGAZINEID VARCHAR2(20)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Add comments to the columns
comment on column ORDERDET.ORDERDETID
is '订单详细ID';
comment on column ORDERDET.ORDERID
is '订单ID';
comment on column ORDERDET.PRODID
is '产品ID';
comment on column ORDERDET.CONTACTID
is '订购人';
comment on column ORDERDET.PRODSCODE
is '产品代码';
comment on column ORDERDET.PRODNAME
is '产品名称';
comment on column ORDERDET.SOLDWITH
is '是否搭销';
comment on column ORDERDET.STATUS
is '产品状态';
comment on column ORDERDET.RECKONING
is '是否结账';
comment on column ORDERDET.RECKONINGDT
is '结账日期';
comment on column ORDERDET.FBDT
is '反馈日期';
comment on column ORDERDET.UPRICE
is '产品原价';
comment on column ORDERDET.UPNUM
is '原价订购个数';
comment on column ORDERDET.SPRICE
is '产品优惠价';
comment on column ORDERDET.SPNUM
is '优惠价订购个数';
comment on column ORDERDET.PAYMENT
is '实际付款';
comment on column ORDERDET.FREIGHT
is '产品运费';
comment on column ORDERDET.POSTFEE
is '投递费';
comment on column ORDERDET.CLEARFEE
is '实际结算';
comment on column ORDERDET.ORDERDT
is '订购日期';
comment on column ORDERDET.PROVINCEID
is '省份编码';
comment on column ORDERDET.STATE
is '订购人省份';
comment on column ORDERDET.CITY
is '城市';
comment on column ORDERDET.MDUSR
is '修改人';
comment on column ORDERDET.BREASON
is '退货原因';
comment on column ORDERDET.FEEDBACK
is '产品反馈结果';
comment on column ORDERDET.GOODSBACK
is '退货地点';
comment on column ORDERDET.PRODUCTTYPE
is '产品规格';
comment on column ORDERDET.BACKDT
is '退货日期';
comment on column ORDERDET.BACKMONEY
is '退货金额';
comment on column ORDERDET.OLDPROD
is '原产品(只对换货产品有效)';
comment on column ORDERDET.COMPENSATE
is '补差价';
comment on column ORDERDET.PURPOSE
is '购买目的';
comment on column ORDERDET.JIFEN
is '积分';
comment on column ORDERDET.WEBID
is '网站编号';
comment on column ORDERDET.WAPID
is '手机购物系统对应编号';
comment on column ORDERDET.MAGAZINEID
is '杂志购物系统对应编号';
SELECT contact.contactid 联系人ID,
(orderhist.crdt) AS 订购日期,
contact.name AS 客户姓名,
wm_concat(orderdet.prodname || '*' ||
to_char(orderdet.upnum + orderdet.spnum)) 品名,
FROM contact, orderhist, orderset
WHERE orderhist.contactid = contact.contactid
AND orderhist.crdt >=
to_date('2010-03-06 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
AND orderhist.crdt <=
to_date('2010-03-07 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
AND contact.contactid = orderset.contactid
GROUP BY contact.contactid, orderhist.crdt, contact.name;
我的ORACLE 是8.1版的 ,里面没有vm_concat 函数
虚拟机上装的是ORACLE 10 ,运行楼上的还是很慢啊 ,一天的数据也只有100多条
create index IDX_ORDERDET_FBDT on ORDERDET (FBDT)
tablespace IAGENT
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index SYS_PROID on ORDERDET (PRODID)
tablespace IAGENT
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index SYS_PROTYPE on ORDERDET (PRODUCTTYPE)
tablespace IAGENT
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index SYS_TEST on ORDERDET (ORDERID)
tablespace IAGENT
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
SELECT STATEMENT, GOAL = ALL_ROWS| 145780 7725927 254955591
SORT GROUP BY | 145780 7725927 254955591 |
MERGE JOIN CARTESIAN | 4009 7725927 254955591
NESTED LOOPS | 107 44 1452
TABLE ACCESS BY INDEX ROWID |IAGENT ORDERHIST 19 44 748
INDEX RANGE SCAN IAGENT |SYS_ORDERHIST_CRDT 2 44
TABLE ACCESS BY INDEX ROWID |IAGENT CONTACT 2 1 16
INDEX UNIQUE SCAN IAGENT |SYS_C005575 1 1
BUFFER SORT | 145778 174355
INDEX FAST FULL SCAN |IAGENT SYS_C005656 89 174355
如何根据执行计划 来优化我的SQL语句了?
from
直接采用字符连接啊!
把其他函数贴出来 都纠结了3个月了!