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

第二届数据库编程大赛

2024第二届数据库编程大赛于12月5日正式开启初赛！由NineData和云数据库技术社区主办，华为云、Doris等协办单位和媒体共同举办。比赛要求选手设计一套SQL算法，只用一条SQL秒杀100万张火车票，让乘客都都能顺利坐上火车回家过年。查看赛题详情

以下是本次决赛第6名，大赛三等奖获得者程宁的参赛介绍：

个人简介：嘉兴市第二医院信息科，从事信息化工作多年

参赛数据库：ORACLE

性能评测：百万级数据代码性能评测2.198秒

综合得分：78.25

以下是程宁选手的代码说明思路简介：

以下是程宁选手的代码说明思路简介：

1.每个乘客按行程规划分组生成需求序列号；

2.所有火车按行程规划生成每个座位的供应序列号，先分配有座、再分配无座

3.行程相同两个序列号相同，生成车票分配方案

优化措施：

增加一个行程方案，降低座位序列号计算量；用行程方案ID关联，降低最后JOIN关联字段消耗；

对火车进行预测，尽量少生成火车坐席；

添加了一个测试参数，可以将原始数据扩大N倍，用于性能测试；

加Hint/*+PARALLEL(8)*/，官方测试环境4C8G，网上查了一下，据说ORACLE默认安装的时候每核2个线程，故参数设置为8，榨干服务器。

以下是程宁选手的详细算法说明，结尾附完整SQL：

with/*参数表：1600个数字*/seat_id(n)AS(selectROW_NUMBER()OVER(orderbypassenger_ID)nfrompassengerWHERErownum=1600),/*测试参数，在有限数据情况下，扩大测试倍数至第二阶段*/test_train(n)as(selectROW_NUMBER()OVER(orderbypassenger_ID)nfrompassengerWHERErownum=1),test_passenger(n)as(selectROW_NUMBER()OVER(orderbypassenger_ID)nfrompassengerWHERErownum=1),/*计划表，用于减少检索数据量*/PlanSas(SELECTROW_NUMBER()OVER(orderbydeparture_station,arrival_station)Plan_id,departure_station,arrival_station,n_maxFROM(SELECTdeparture_station,arrival_station,count(1)n_maxFROMpassengera,test_passengerbGROUPBYdeparture_station,arrival_station)aa),/*需求表：为每个乘客生成一个需求号*/needsas(_id,_id,_station,_station,ROW_NUMBER()OVER(PARTITIONBYPlan_idORDERBYPlan_id,passenger_id)ASrow_numfrompassengera,test_passengerb,_station=__station=_station),/*以下代码用于预测需要哪些火车生成座位信息，降低最后join计算关联的数据量*/v_train0AS(_id,_id,_count,,_max,1pxfromtraina,test_trainb,_station=__station=__id,_id,_count,,_max,2pxfromtraina,test_trainb,_station=__station=_station),v_train1as(_id,_id,_count,,_max,,SUM(__count*0.1END)OVER(_,_countdesc)seat_count_allfromv_train0a),v_train_minAS(SELECTplan_id,min(seat_count_all)seat_count_all_minFROMv_train1WHEREseat_count_all=n_maxGROUPBYplan_id),v_trainAS(_id,_id,,_countFROMv__count_alln__id,_id,,_countFROMv_train1a,v_train__id=__count_all=_count_all__count_all=n_max),/*座位表：按有座、无座分别生成所有坐席*/allzuoweias(_id,_id,px,_idfromv_traina,seat_=1=__id,_id,px,_idfromv_traina,seat_=2=_count/10),/*供应表：按题目要求分配一个流水号*/stocksas(_id,_id,,_id,ROW_NUMBER()OVER(PARTITIONBYplan_idORDERBYplan_id,px)ASrow_numfromallzuoweia)/*供需关联得到答案,*/select/*+PARALLEL(8)*/_id,_station,_station,_id,(_id/100)coach_number,((_id-FLOOR(_id/100)*100)/5)+1||caseMOD(_id,5)when0then'F'when1then'A'when2then'B'when3then'C'when4then'E'when2then'无座'seat__id=__num=__id

时间截止：2025年1月5日22:00时