- 论坛徽章:
- 0
|
目前有一个600W数据的表，表结构如下：
-- Create table
create table ORDER_MAIN
(
ORDER_ID VARCHAR2(1 not null,
ORDER_START_TIME DATE,
ORDER_STATE_CHANGE_TIME DATE,
ORDER_CALL_TEL VARCHAR2(20),
PAY_ACCT_NO VARCHAR2(40),
COMMODITY_TOTAL NUMBER(10),
REMARK VARCHAR2(400),
ORDER_STATE_CODE VARCHAR2(2) not null,
PAY_TIME DATE,
SHOP_ID VARCHAR2(,
ACCESS_ID VARCHAR2(20),
PAY_TYPE_ID NUMBER(2),
PAY_CHANNEL_ID NUMBER(2),
ISSUER_CODE VARCHAR2(20),
CALL_AREA_CODE VARCHAR2(6),
ORDER_CALL_IP VARCHAR2(20),
AREA_CODE VARCHAR2(6) not null,
EXTERNAL_SYSTEM_ORDER_ID VARCHAR2(20),
USER_NAME VARCHAR2(20),
COMMODITY_TOTAL_NUM INTEGER,
PAYMENT_SHOP_ID VARCHAR2(40) not null,
ACCOUNT VARCHAR2(20) not null,
CLIENT_BINDING_STATE VARCHAR2(1),
ITA_BANK_CODE VARCHAR2(2),
NOTIFY_URL VARCHAR2(400),
CLIENT_LEAVE_WORD VARCHAR2(400),
IS_PRINT_INVOICE CHAR(1),
INVOICE_TITLE VARCHAR2(200),
INVOICE_CONTENT VARCHAR2(20),
ORDER_END_TIME DATE,
PAY_FEE NUMBER,
CASH_USE_VALUE INTEGER,
CDKEY_USE_VALUE INTEGER,
GIFT_USE_VALUE INTEGER,
ORDER_FROM_CODE VARCHAR2(2),
ORIGINAL_ORDER_INFO CLOB
)
tablespace EBUSINESS_DEV
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 28
minextents 1
maxextents unlimited
);
-- Add comments to the table
comment on table ORDER_MAIN
is '这个里的商户ID为实际的商户ID，如果订单是从代理商的途径过来，报文送过来的为代理';
-- Add comments to the columns
comment on column ORDER_MAIN.ORDER_ID
is '订单编码：4位年2位月2位日2位小时2位分钟2位秒+4位序列号 共18位';
comment on column ORDER_MAIN.ORDER_START_TIME
is '订单开始时间 yyyy-mm-dd hh24 :mm:ss';
comment on column ORDER_MAIN.ORDER_STATE_CHANGE_TIME
is '订单状态改变时间 yyyy-mm-dd hh24 :mm:ss';
comment on column ORDER_MAIN.ORDER_CALL_TEL
is '用户拨打接入电话号码';
comment on column ORDER_MAIN.PAY_ACCT_NO
is '支付账户帐号';
comment on column ORDER_MAIN.COMMODITY_TOTAL
is '商品总价';
comment on column ORDER_MAIN.REMARK
is '备注';
comment on column ORDER_MAIN.ORDER_STATE_CODE
is '序号 订单状态
10 等待付款
11 付款成功
12 付款失败
13 等待发货
14 发货成功
15 发货失败
16 客户收货
';
comment on column ORDER_MAIN.PAY_TIME
is '支付交易发起时间';
comment on column ORDER_MAIN.SHOP_ID
is '录入后自动生成，省份代码2位+地市代码2位+商户短编号4位';
comment on column ORDER_MAIN.ACCESS_ID
is '20位数字，格式： 4位年+2位月+2位日+2位小时+2位分+2位秒+6位随机数字。';
comment on column ORDER_MAIN.PAY_TYPE_ID
is '支付方式ID(10 借记卡 11信用卡 12积分兑换 13 充值卡 14话费)';
comment on column ORDER_MAIN.PAY_CHANNEL_ID
is '支付渠道ID
(10通联
11环讯)';
comment on column ORDER_MAIN.ISSUER_CODE
is '银行代码';
comment on column ORDER_MAIN.CALL_AREA_CODE
is '订单拨打电话归属地(填写地区代码)';
comment on column ORDER_MAIN.ORDER_CALL_IP
is '订单接入IP地址(根据接入方式判断是填写IP还是电话)';
comment on column ORDER_MAIN.AREA_CODE
is '地区编码';
comment on column ORDER_MAIN.EXTERNAL_SYSTEM_ORDER_ID
is '外部系统填写，必须保证在外部系统中的唯一性';
comment on column ORDER_MAIN.USER_NAME
is '系统登陆ID';
comment on column ORDER_MAIN.COMMODITY_TOTAL_NUM
is '订购商品的总数量';
comment on column ORDER_MAIN.PAYMENT_SHOP_ID
is '第三方支付机构分配的商户ID';
comment on column ORDER_MAIN.ACCOUNT
is '结算账户ID';
comment on column ORDER_MAIN.CLIENT_BINDING_STATE
is '是否绑定银行卡(0未绑定，1绑定)';
comment on column ORDER_MAIN.ITA_BANK_CODE
is '中间账户银行编码';
comment on column ORDER_MAIN.NOTIFY_URL
is '回调URL';
comment on column ORDER_MAIN.CLIENT_LEAVE_WORD
is '客户留言';
comment on column ORDER_MAIN.IS_PRINT_INVOICE
is '是否打印发票：1打印发票；0不打印发票';
comment on column ORDER_MAIN.INVOICE_TITLE
is '发票抬头：为空即表示个人，填值即表示公司';
comment on column ORDER_MAIN.INVOICE_CONTENT
is '发票内容';
comment on column ORDER_MAIN.ORDER_END_TIME
is '订单完成时间';
comment on column ORDER_MAIN.PAY_FEE
is '支付手续费';
comment on column ORDER_MAIN.CASH_USE_VALUE
is '现金券使用金额';
comment on column ORDER_MAIN.CDKEY_USE_VALUE
is 'CDKEY使用金额';
comment on column ORDER_MAIN.GIFT_USE_VALUE
is '礼品券使用金额';
comment on column ORDER_MAIN.ORDER_FROM_CODE
is '订单来源代码(10-我要买网站, 11-我要买wap)';
-- Create/Recreate primary, unique and foreign key constraints
alter table ORDER_MAIN
add constraint XPK订单信息主表 primary key (ORDER_ID)
using index
tablespace EBUSINESS_DEV
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 2M
minextents 1
maxextents unlimited
);
-- Create/Recreate indexes
create index IDX查询用户订单 on ORDER_MAIN (USER_NAME)
tablespace EBUSINESS_DEV
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index IDX重复用户 on ORDER_MAIN (TRUNC(ORDER_START_TIME,'fmmm'), ORDER_STATE_CODE, ORDER_ID, SUBSTR(AREA_CODE,0,4)||'00', ORDER_CALL_TEL)
tablespace EBUSINESS_DEV
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 3M
minextents 1
maxextents unlimited
);
create index XPK商户ID on ORDER_MAIN (SHOP_ID)
tablespace EBUSINESS_DEV
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
查询语句如下：
SELECT COUNT(*) totalCount,
sum(nvl(b.giftCardTotal, 0)) giftCardTotal,
sum(NVL(b.commodity_total, a.commodity_total * 0.01)) allAmount,
sum(nvl(f.cargo_freight, 0)) * 0.01 cargoFreight,
sum(nvl(a.gift_use_value, 0)) * 0.01 giftUseValue,
sum(nvl(a.cash_use_value, 0)) * 0.01 cashUseValue,
sum(nvl(a.cdkey_use_value, 0)) * 0.01 cdkeyUseValue,
sum(b.commodityCostTotal) commodityCostTotal,
sum(b.cargoFreighCostTotal) * 0.01 cargoFreighCostTotal
FROM order_main a
JOIN (SELECT a.order_id,
SUM(a.commodity_num) commodity_total_num,
SUM(ROUND(a.commodity_num * a.commodity_price *
0.0001,
2)) commodity_total,
SUM(ROUND((case
when b.business_type_code = '30' then
a.commodity_num * a.commodity_price
else
0
end) * 0.0001,
2)) giftCardTotal,
SUM(ROUND(nvl(a.commodity_num *
a.commodity_cost_price * 0.0001,
0),
2)) commodityCostTotal,
SUM(nvl(a.cargo_freigh_cost, 0)) cargoFreighCostTotal
FROM Order_Commodity a, commodity_info b
WHERE a.shop_id = b.shop_id(+)
AND a.commodity_code = b.commodity_code(+)
GROUP BY a.order_id) b ON a.order_id = b.order_id
LEFT JOIN shop_info c ON a.shop_id = c.shop_id
LEFT JOIN access_mode d ON a.access_id = d.access_id
LEFT JOIN bill_main e ON a.order_id = e.order_id
LEFT JOIN delivery_records f ON a.order_id = f.order_id
where 1 = 1
and a.order_start_time >=
to_date('2012-09-25 00:00:00', 'yyyy-MM-dd hh24:mi:ss')
and a.order_start_time <
to_date('2012-09-26 00:00:00', 'yyyy-MM-dd hh24:mi:ss')
AND a.order_State_Code IN ('16', '11', '14')
执行计划的截图：
大家看到了这个是一个订单的主表，很多统计和查询都是和这个表相关的，现在用起来非常慢，需要进行调优，但不知道怎么下手！ |
|
免费注册
发表于 2012-09-25 17:02
