SQL编程大师程宁：突破性能瓶颈！Oracle 性能 2.198 秒背后的设计思路

with  
/*参数表：1600个数字*/
  seat_id(n) AS (
    select ROW_NUMBER()  OVER(order by passenger_ID ) n 
    from passenger WHERE rownum<=1600),
 /*测试参数，在有限数据情况下，扩大测试倍数至第二阶段*/
  test_train(n) as(
   select ROW_NUMBER()  OVER(order by passenger_ID ) n 
    from passenger WHERE rownum<=1
  ),
  test_passenger(n) as(
    select ROW_NUMBER()  OVER(order by passenger_ID ) n 
    from passenger WHERE rownum<=1
  ),
/*计划表，用于减少检索数据量*/
 PlanS as (
      SELECT ROW_NUMBER() OVER(order by departure_station,arrival_station) Plan_id,
      departure_station,arrival_station,n_max 
      FROM (
        SELECT  
            departure_station,arrival_station,count(1) n_max
        FROM passenger a ,test_passenger b
        GROUP BY departure_station,arrival_station ) aa ),
/*需求表：为每个乘客生成一个需求号*/
needs as (
  select   a.passenger_id,c.Plan_id ,a.departure_station,a.arrival_station,
  ROW_NUMBER() OVER(PARTITION BY Plan_id ORDER BY Plan_id,passenger_id)  AS row_num
  from passenger a,test_passenger b,PlanS c 
  where a.departure_station=c.departure_station and a.arrival_station=c.arrival_station 
),
/*以下代码用于预测需要哪些火车生成座位信息，降低最后join计算关联的数据量*/
v_train0 AS 
    (SELECT a.train_id,c.Plan_id,a.seat_count ,b.n,c.n_max,1 px
    from train a,test_train b,PlanS c
    where a.departure_station=c.departure_station and a.arrival_station=c.arrival_station
    UNION ALL 
    SELECT a.train_id,c.Plan_id,a.seat_count ,b.n,c.n_max,2 px
    from train a,test_train b,PlanS c
    where a.departure_station=c.departure_station and a.arrival_station=c.arrival_station),
v_train1 as 
  (select a.train_id,a.Plan_id,a.seat_count ,a.n,a.n_max,a.px,
      SUM(CASE a.px WHEN 1 THEN a.seat_count ELSE a.seat_count * 0.1 END ) OVER (PARTITION BY a.Plan_id  order by a.px,a.seat_count desc) seat_count_all
   from v_train0 a),
v_train_min AS 
    (SELECT plan_id,min(seat_count_all) seat_count_all_min 
    FROM v_train1 
    WHERE seat_count_all >= n_max 
    GROUP BY plan_id),
v_train AS 
    (SELECT a.train_id,a.plan_id,a.px,a.seat_count 
        FROM v_train1 a WHERE a.seat_count_all < n_max
    UNION all
     SELECT a.train_id,a.plan_id,a.px,a.seat_count 
         FROM v_train1 a,v_train_min b 
         WHERE a.plan_id = b.plan_id AND a.seat_count_all <= b.seat_count_all_min AND a.seat_count_all>=n_max),
/*座位表：按有座、无座分别生成所有坐席*/
allzuowei as(
  select a.train_id,
    a.plan_id,
    px,
    b.n seat_id
    from v_train a,seat_id b
    where a.px = 1 AND b.n<=a.seat_count
  union all
    select a.train_id,
      a.plan_id,
      px,
      b.n seat_id
      from v_train a,seat_id b
    where a.px = 2 AND b.n<=a.seat_count/10
  ),
/*供应表：按题目要求分配一个流水号*/
stocks as(
  select   a.train_id,a.plan_id,a.px,a.seat_id,
  ROW_NUMBER() OVER(PARTITION BY plan_id ORDER BY plan_id,px)  AS row_num
  from allzuowei a
)
/*供需关联得到答案,*/
    select  /*+ PARALLEL(8) */ 
    a.passenger_id,
    a.departure_station,
    a.arrival_station,
    b.train_id,
    case b.px when 1 then CEIL(b.seat_id/100) end  coach_number  ,
    case b.px when 1 then 
      FLOOR((b.seat_id- FLOOR(b.seat_id/100) * 100) /5) + 1 ||
      case MOD(b.seat_id,5)  
        when 0 then 'F' when 1 then 'A' when 2 then 'B' when 3 then 'C' when 4 then 'E' end
    when 2 then  '无座' end seat_number 
    from needs a
    left OUTER join stocks b on a.plan_id=b.plan_id and a.row_num=b.row_num
  order by a.passenger_id 
复制