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