看我做的数据库包装器

newkid · 发表于 2009-4-21 23:47

原因找到了，只改了一处笔误。我对你的业务规则的理解完全正确（就你那堆程序，除了我之外不相信有第二个人能把业务需求看出来，嘿嘿）
现在生成5156张票，共用0.9秒。我放弃了seat_remant表，这点你也是同意的。

测试方法：

exec make_seats(NULL,0,-7);

用 -7 作偏移是因为我现在的日期是 4月21号，我为了和你核对，用4月14号作起始日期。

全部代码：
先建立一个参数表:
CREATE GLOBAL TEMPORARY TABLE seat_parameter ( -- 参数表
      dflg          NUMBER(1) -- 控制JOBDAY的计算
      ,start_date    DATE       -- today
      ,end_date    DATE       -- 如果非空，则可以生成多天的数据
      ,train_no    VARCHAR2(12) -- 如果为0或NULL则忽略
      ) ON COMMIT DELETE ROWS
/

把两个复杂查询抽出来建立视图：
CREATE OR REPLACE VIEW vw_train
AS  ------ 无号和普通都用到的一些变换计算和公共子查询(车次和停靠站的选取)
SELECT t.train_no
   ,t.gride
   ,t.station_num
   ,t.jobday
   ,t.item
   ,cz.station_code                                        AS beg_station
   ,t.jobday+(t.start_time+cz.running_time+cz.stop_time)/1440 AS on_date
   ,cz.run_train_no                                        AS run_train
   ,cz.over
   ,cz.sequence
  FROM (SELECT t2.*
            ,SUBSTR(cycle,MOD(jobday-beg_date, LENGTH(cycle))+1,1) AS item -- 原来少写了个LENGTH
      FROM (SELECT train.*
                  ,(CASE WHEN NVL(p.dflg,0)=0 THEN p.today + (CASE WHEN train.days>0 THEN train.days ELSE 10 END)
                        ELSE today
                     END) AS jobday    --- 如果dflg为空则从train表中取得
            FROM train
               ,(SELECT dflg -- 根据end_date,start_date算出一个today的集合。如果相等或end_date为空则只有一行
                        ,start_date + ROWNUM -1 AS today
                        ,train_no
                     FROM seat_parameter
                  CONNECT BY ROWNUM <= NVL(end_date,start_date) - start_date +1
                  ) p
            WHERE train.flag=0 AND train.end_date >= p.today
                  AND (train.train_no=p.train_no OR NVL(p.train_no,'0')='0')
            ) t2
      ) t
   ,stop_station cz
WHERE t.jobday BETWEEN t.beg_date AND t.end_date
   AND t.item<>'0'
   AND cz.train_no=t.train_no
   AND t.jobday BETWEEN cz.beg_date AND cz.end_date
   AND cz.flag=0
/

CREATE OR REPLACE VIEW vw_seat
AS --- 利用上述视图vw_train和附加逻辑生成席位
WITH tr AS (SELECT * FROM vw_train)
,vw_seat_no AS (SELECT ROWNUM as seat_no FROM DUAL CONNECT BY ROWNUM<=200)
SELECT -- 第一部分：发布无号席位
   tr.train_no       AS train_no
   ,tr.gride          AS gride
   ,tr.station_num-1    AS end_station
   ,tr.jobday          AS start_date
   ,0                   AS carno
   ,0                   AS purpose
   ,0                   AS seat_type
   ,0                   AS shortest_station
   ,0                   AS flag
   ,tr.beg_station    AS beg_station
   ,tr.on_date          AS on_date
   ,tr.run_train       AS run_train
   ,0                   AS pro
   ,vw_seat_no.seat_no AS seat_no
FROM  tr
   ,vw_seat_no
WHERE --tr.station_num-1 > tr.sequence
   tr.over >= vw_seat_no.seat_no
UNION ALL -- 第二部分：发布普通席位
SELECT tr.train_no       AS train_no
   ,ps.gride          AS gride
   ,ps.end_station    AS end_station
   ,tr.jobday          AS start_date
   ,ps.carno          AS carno
   ,ps.purpose          AS purpose
   ,(CASE WHEN ps.seat_type = 20 THEN 20+vw_seat_type_adj.rn       -- 硬卧是一席3号，下中上。seat_type:20=硬卧，21硬卧下，22硬卧中，23硬卧上
         WHEN ps.seat_type = 30 THEN 31+MOD(vw_seat_no.seat_no+1,2) -- 软卧是一席一号单下双上: 30软卧，31软卧下，32软卧上。
         ELSE ps.seat_type
      END)             AS seat_type
   ,ps.shortest_station  AS shortest_station
   ,0                   AS flag
   ,tr.beg_station    AS beg_station
   ,tr.on_date          AS on_date
   ,tr.run_train       AS run_train
   ,0                   AS pro
   ,vw_seat_no.seat_no AS seat_no
  FROM tr
   ,pre_seat ps
   ,pre_seat_index psi
   ,vw_seat_no
   ,(SELECT ROWNUM AS rn FROM DUAL CONNECT BY ROWNUM<=3) vw_seat_type_adj
WHERE psi.train_no=tr.train_no AND psi.item=tr.item AND tr.jobday BETWEEN psi.beg_date AND psi.end_date AND psi.flag=0
   AND ps.pre_id = tr.train_no||':'||tr.item||':'||TO_CHAR(psi.beg_date,'YYYYMMDD')
   AND tr.sequence = ps.beg_station -- 和无号不同
   AND vw_seat_no.seat_no BETWEEN ps.beg_seatno AND ps.end_seatno
   AND (ps.seat_type=20    --- 硬卧，连接三行
         OR ps.seat_type <>20 AND vw_seat_type_adj.rn = 1 -- 非硬卧，只连接一行
         )
/

CREATE OR REPLACE VIEW vw_new_seat
AS
SELECT * FROM vw_seat v
WHERE NOT EXISTS (SELECT 1 FROM seat s
                  WHERE s.start_date  = v.start_date
                        AND s.beg_station = v.beg_station
                        AND s.Train_no = v.Train_no
                        AND s.Carno    = v.Carno
                        AND s.seat_no    = v.seat_no
                        AND s.seat_type = v.seat_type
               )
/

建立存储过程生成席位：
CREATE OR REPLACE PROCEDURE make_seats (
      p_train_no    IN VARCHAR2　DEFAULT NULL
      ,p_dflg       IN NUMBER DEFAULT 0 ---- 如果1则不用TRAINS里面的偏移量
      ,p_offset_days  IN NUMBER DEFAULT 0 ---- 缺省当天则偏移为0, +1为第二天
      ,p_created_days IN NUMBER DEFAULT 1 ---- 缺省一天
      )
AS
lv_timestamp DATE := SYSDATE;
BEGIN
INSERT INTO seat_parameter ( -- 参数表
      dflg          -- 控制JOBDAY的计算
      ,start_date    -- today
      ,end_date       -- 如果非空，则可以生成多天的数据
      ,train_no       -- 如果为0或NULL则忽略
      )
VALUES(p_dflg
      ,TRUNC(SYSDATE)+p_offset_days
      ,TRUNC(SYSDATE)+p_offset_days + p_created_days -1
      ,p_train_no
      );

INSERT INTO seat (
      train_no
      ,gride          -- grade?
      ,end_station
      ,start_date
      ,carno
      ,purpose
      ,seat_type
      ,shortest_station
      ,flag
      ,beg_station
      ,on_date
      ,run_train
      ,pro
      ,seat_no
      ,used_time
      ,used_dev
      ,used_uid
      )
SELECT train_no
      ,gride
      ,end_station
      ,start_date
      ,carno
      ,purpose
      ,seat_type
      ,shortest_station
      ,flag
      ,beg_station
      ,on_date
      ,run_train
      ,pro
      ,seat_no
      ,lv_timestamp AS used_time
      ,'xwfb' AS used_dev
      ,'xwfb' AS used_uid
   FROM vw_new_seat;


MERGE INTO pre_seat_index psi
USING (SELECT DISTINCT psi.ROWID rid
         FROM pre_seat_index psi
            ,vw_train tr
         WHERE psi.train_no=tr.train_no AND psi.item=tr.item AND tr.jobday BETWEEN psi.beg_date AND psi.end_date AND psi.flag=0
      ) n
ON (n.rid = psi.ROWID)
WHEN MATCHED THEN UPDATE
      SET psi.distribute_time = lv_timestamp;

COMMIT;

END make_seats;
/

wanglina · 发表于 2009-4-22 08:04

newkid 代码很规整，是用工具格式化的，还是写出来就是这样子？

yulihua49 · 发表于 2009-4-22 09:19

原帖由 newkid 于 2009-4-21 23:47 发表

非常感谢，如果好的话可以考虑采用。
另外，请教一下，如果我不使用余额表，采用视图，如何定义视图？

newkid · 发表于 2009-4-22 23:24

原帖由 wanglina 于 2009-4-22 08:04 发表
newkid 代码很规整，是用工具格式化的，还是写出来就是这样子？

我没用格式化工具，就用ULTRAEDIT, 习惯了。

newkid · 发表于 2009-4-22 23:26

原帖由 yulihua49 于 2009-4-22 09:19 发表

非常感谢，如果好的话可以考虑采用。
另外，请教一下，如果我不使用余额表，采用视图，如何定义视图？

CREATE OR REPLACE VIEW vw_seat_remant
AS
SELECT on_date
   ,beg_station
   ,run_train
   ,Carno
   ,seat_type
   ,purpose
   ,end_station
   ,COUNT(*)  AS amoung
  FROM seat
WHERE flag =0 --- 我假设已经售出的票这个flag会改变，flag=0表示没有卖出去的。你自己可以根据需要修改
GROUP BY on_date
   ,beg_station
   ,run_train
   ,Carno
   ,seat_type
   ,purpose
   ,end_station ;

因为你的seat表比较大，你要尽量避免不带条件的查询（这意味着对seat的全表扫描再聚合运算）。如果用on_date, beg_station, 等等查询条件，和你原来用表没什么区别。

L_yongfei · 发表于 2009-4-23 14:49

顶了在看

yulihua49 · 发表于 2009-4-24 09:31

原帖由 newkid 于 2009-4-22 23:26 发表

CREATE OR REPLACE VIEW vw_seat_remant
AS
SELECT on_date
   ,beg_station
   ,run_train
   ,Carno
   ,seat_type
   ,purpose
   ,end_station
   ,COUNT(*)  AS amoung
  FROM seat
WHERE flag =0 --- 我假设已经售出的票这个flag会改变，flag=0表示没有卖出去的。你自己可以根据需要修改
GROUP BY on_date
   ,beg_station
   ,run_train
   ,Carno
   ,seat_type
   ,purpose
   ,end_station ;

因为你的seat表比较大，你要尽量避免不带条件的查询（这意味着对seat的全表扫描再聚合运算）。如果用on_date, beg_station, 等等查询条件，和你原来用表没什么区别。

我是这样做的：
create or replace view vw_remant as
select  to_char(on_date,'YYYY-MM-DD') as on_date,
      beg_station,
      run_train,
      car_no,
      seat_type as seat_type,
      end_station,
      purpose,
      count(*) as amoung
from seat
where flag=0 AND on_date= ? AND beg_station=? AND run_train=?
group by on_date,beg_station,run_train,car_no,seat_type,end_station,purpose
不知道下边的条件怎么写，这些值是运行时加上去的。
on_date= ? AND beg_station=? AND run_train=?

另外，commit的问题。
现在发现commit的时间非常不稳定，插入7552个记录后，一次性commit,
时间从832毫秒到179毫秒不等。
如果是修改记录，commit的时间就比较少，大约50-80毫秒。但是，插入5个记录也需要40ms左右，一般30-40ms，时间不会再少了

可能是因为处理IO，变数太多吧。

优化这个语句应该优化IO系统？分区，通道，并行？

[ 本帖最后由 yulihua49 于 2009-5-3 17:07 编辑 ]

newkid · 发表于 2009-4-24 23:22

你原来的seat_remant并没有start_date, 你以前是怎么查询的？

如果是视图上已经有的列(beg_station, run_train)，就放在查询的WHERE里面, 而不是放在VIEW的定义里面：

SELECT ... FROM  vw_remant WHERE beg_station=? AND run_train=? AND ....

参数化的视图也有, 你得先建个包来传递参数（SESSION级别的全局变量），这是一个简单示例：

CREATE OR REPLACE PACKAGE session_val IS

TYPE parm_type IS TABLE OF varchar2(1000) index BY VARCHAR2(30);

PROCEDURE set_val(p_idx IN VARCHAR2,p_value IN VARCHAR2);
PROCEDURE set_val(p_idx IN VARCHAR2,p_value IN NUMBER);
PROCEDURE set_val(p_idx IN VARCHAR2,p_value IN DATE);

FUNCTION  get_val(p_idx IN VARCHAR2)       RETURN VARCHAR2 ;
FUNCTION  get_val_number(p_idx IN VARCHAR2) RETURN NUMBER;
FUNCTION  get_val_date(p_idx IN VARCHAR2) RETURN DATE;

END session_val;
/

CREATE OR REPLACE PACKAGE BODY session_val IS

sv_parameters parm_type;

PROCEDURE set_val(p_idx IN VARCHAR2,p_value IN VARCHAR2)
IS
BEGIN
sv_parameters(TRIM(UPPER(p_idx))) := SUBSTR(p_value,1,1000);

RETURN;
END set_val;

PROCEDURE set_val(p_idx IN VARCHAR2,p_value IN NUMBER)
IS
BEGIN
set_val(p_idx,TO_CHAR(p_value));
RETURN;
END set_val;

PROCEDURE set_val(p_idx IN VARCHAR2,p_value IN DATE)
IS
BEGIN
set_val(p_idx,TO_CHAR(p_value,'YYYYMMDDHH24MISS'));
RETURN;
END set_val;

FUNCTION  get_val(p_idx IN VARCHAR2) RETURN VARCHAR2
IS
BEGIN
RETURN sv_parameters(TRIM(UPPER(p_idx)));
EXCEPTION
WHEN NO_DATA_FOUND THEN
      RETURN NULL;
END get_val;

FUNCTION  get_val_number(p_idx IN VARCHAR2) RETURN NUMBER
IS
BEGIN
RETURN TO_NUMBER(get_val(p_idx));
END get_val_number;

FUNCTION  get_val_date(p_idx IN VARCHAR2) RETURN DATE
IS
BEGIN
RETURN TO_DATE(get_val(p_idx),'YYYYMMDDHH24MISS');
END get_val_date;

END session_val;
/

你的视图可以这么定义，带一个参数：
create or replace view vw_remant as
select  to_char(on_date,'YYYY-MM-DD') as on_date,
      beg_station,
      run_train,
      car_no,
      seat_type as seat_type,
      end_station,
      purpose,
      count(*) as amoung
from seat
where flag=0 AND start_date= session_val.get_val_date('P_START_DATE')  --- 这个参数必须在读取数据前设入
group by on_date,beg_station,run_train,car_no,seat_type,end_station,purpose;

那么你在查询视图之前，必须给它传参数:

EXEC session_val.set_val('P_START_DATE', TO_DATE('20090301','YYYYMMDD'));

然后你就可以查视图的数据：(这个查询虽然不带WHERE,但是条件已经做在视图定义里面了)
SELECT * FROM vw_remant;

注意你现在查询视图相当于在查询seat表，所以要注意seat的索引的建法。你如果有复合索引要把常用的查询列放在前面。

关于COMMIT的问题，建议你去DBA版块问，必须跟踪后确定时间花在哪里了。

yulihua49 · 发表于 2009-4-27 14:05

原帖由 newkid 于 2009-4-24 23:22 发表

你原来的seat_remant并没有start_date, 你以前是怎么查询的？

如果是视图上已经有的列(beg_station, run_train)，就放在查询的WHERE里面, 而不是放在VIEW的定义里面：

SELECT ... FROM  vw_remant WHERE beg_station=? AND run_train=? AND ....

参数化的视图也有, 你得先建个包来传递参数（SESSION级别的全局变量），这是一个简单示例：

CREATE OR REPLACE PACKAGE session_val IS

TYPE parm_type IS TABLE OF varchar2(1000) index BY VARCHAR2(30);

PROCEDURE set_val(p_idx IN VARCHAR2,p_value IN VARCHAR2);
PROCEDURE set_val(p_idx IN VARCHAR2,p_value IN NUMBER);
PROCEDURE set_val(p_idx IN VARCHAR2,p_value IN DATE);

FUNCTION  get_val(p_idx IN VARCHAR2)       RETURN VARCHAR2 ;
FUNCTION  get_val_number(p_idx IN VARCHAR2) RETURN NUMBER;
FUNCTION  get_val_date(p_idx IN VARCHAR2) RETURN DATE;

END session_val;
/

CREATE OR REPLACE PACKAGE BODY session_val IS

sv_parameters parm_type;

PROCEDURE set_val(p_idx IN VARCHAR2,p_value IN VARCHAR2)
IS
BEGIN
sv_parameters(TRIM(UPPER(p_idx))) := SUBSTR(p_value,1,1000);

RETURN;
END set_val;

PROCEDURE set_val(p_idx IN VARCHAR2,p_value IN NUMBER)
IS
BEGIN
set_val(p_idx,TO_CHAR(p_value));
RETURN;
END set_val;

PROCEDURE set_val(p_idx IN VARCHAR2,p_value IN DATE)
IS
BEGIN
set_val(p_idx,TO_CHAR(p_value,'YYYYMMDDHH24MISS'));
RETURN;
END set_val;

FUNCTION  get_val(p_idx IN VARCHAR2) RETURN VARCHAR2
IS
BEGIN
RETURN sv_parameters(TRIM(UPPER(p_idx)));
EXCEPTION
WHEN NO_DATA_FOUND THEN
      RETURN NULL;
END get_val;

FUNCTION  get_val_number(p_idx IN VARCHAR2) RETURN NUMBER
IS
BEGIN
RETURN TO_NUMBER(get_val(p_idx));
END get_val_number;

FUNCTION  get_val_date(p_idx IN VARCHAR2) RETURN DATE
IS
BEGIN
RETURN TO_DATE(get_val(p_idx),'YYYYMMDDHH24MISS');
END get_val_date;

END session_val;
/

你的视图可以这么定义，带一个参数：
create or replace view vw_remant as
select  to_char(on_date,'YYYY-MM-DD') as on_date,
      beg_station,
      run_train,
      car_no,
      seat_type as seat_type,
      end_station,
      purpose,
      count(*) as amoung
from seat
where flag=0 AND start_date= session_val.get_val_date('P_START_DATE')  --- 这个参数必须在读取数据前设入
group by on_date,beg_station,run_train,car_no,seat_type,end_station,purpose;

那么你在查询视图之前，必须给它传参数:

EXEC session_val.set_val('P_START_DATE', TO_DATE('20090301','YYYYMMDD'));

然后你就可以查视图的数据：(这个查询虽然不带WHERE,但是条件已经做在视图定义里面了)
SELECT * FROM vw_remant;

注意你现在查询视图相当于在查询seat表，所以要注意seat的索引的建法。你如果有复合索引要把常用的查询列放在前面。

关于COMMIT的问题，建议你去DBA版块问，必须跟踪后确定时间花在哪里了。

先回答再学习。
seat_remant 与 seat不同，start_date是指始发日期，on_date是上车日期。
某车次在某站的上车日期可能与始发日期不同。
seat表也有on_date,没什么用，席位发布时计算出来的。实际运行时可能发生变化，但不修改这个表。
seat_remant是某站某日期出发点车次及其余额（就是车站的剩余票额显示屏上的那些内容），所以要上车日期--on_date，可以从seat表来，将来seat表的这个日期怎么办，再研究吧。

[ 本帖最后由 yulihua49 于 2009-4-27 14:09 编辑 ]

newkid · 发表于 2009-4-27 22:55

原帖由 yulihua49 于 2009-4-27 14:05 发表

先回答再学习。
seat_remant 与 seat不同，start_date是指始发日期，on_date是上车日期。
某车次在某站的上车日期可能与始发日期不同。
seat表也有on_date,没什么用，席位发布时计算出来的。实际运行时可能发生变化，但不修改这个表。
seat_remant是某站某日期出发点车次及其余额（就是车站的剩余票额显示屏上的那些内容），所以要上车日期--on_date，可以从seat表来，将来seat表的这个日期怎么办，再研究吧。

我知道start_date和on_date不同。问题是：你原来在维护seat_remant表的时候，用的是on_date, 也就是说你并没有根据始发日期汇总的余额。因此现在这个视图的逻辑是完全照搬的，也按on_date来汇总。难道是我程序看错了？

[PRO*C] 看我做的数据库包装器