NineData第2届数据库编程大赛：一条SQL秒 杀100万张火车票

newkid

看完了那耳朵发给我的决赛答辩视频，我觉得他的冠 军就是靠嘴皮子挣来的，那真是，唾沫之星天上来，口若悬河三千尺！
你们这个执行时间是怎么测算的？是第一条结果出现的时间？还是全部结果产生的时间？我觉得用CREATE TABLE AS SELECT来测比较准。

〇〇

能在oracle上跑出列式数据库的速度了不起

〇〇

我在11.2 xe执行第一个sql报下面的错误

1. 
   
2. SQL> @ d:/newkid-train1.txt
   
3. ERROR:
   
4. ORA-01756: 引号内的字符串没有正确结束
   

复制代码

另外，怎么要用这么多临时表空间？

1. 
   
2. SQL> set lines 120
   
3. SQL> with p as (
   
4.   2      select passenger.*
   
5.   3            ,row_number() over(partition by departure_station,arrival_station order by passenger_id) rn ---- 为每组起终点的乘客编一个序号
   
6.   4       from passenger)select * from p where rownum<3;
   
7. 
   
8. PASSENGER_ID                     DEPARTURE_STATION
   
9. -------------------------------- ----------------------------------------------------------------
   
10. ARRIVAL_STATION                                                          RN
    
11. ---------------------------------------------------------------- ----------
    
12. P00000037_1                      上海
    
13. 上海1                                                                     1
    
14. 
    
15. P00000172_1                      上海
    
16. 上海1                                                                     2
    
17. 
    
18. 已用时间:  00: 00: 08.48
    
19. 
    
20. 执行计划
    
21. ----------------------------------------------------------
    
22. Plan hash value: 396449086
    
23. 
    
24. ------------------------------------------------------------------------------------------
    
25. | Id  | Operation            | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
    
26. ------------------------------------------------------------------------------------------
    
27. |   0 | SELECT STATEMENT     |           |     2 |   118 |       |   123K  (1)| 00:24:37 |
    
28. |*  1 |  COUNT STOPKEY       |           |       |       |       |            |          |
    
29. |   2 |   VIEW               |           |  9549K|   537M|       |   123K  (1)| 00:24:37 |
    
30. |   3 |    WINDOW SORT       |           |  9549K|   418M|   514M|   123K  (1)| 00:24:37 |
    
31. |   4 |     TABLE ACCESS FULL| PASSENGER |  9549K|   418M|       | 12741   (1)| 00:02:33 |
    
32. ------------------------------------------------------------------------------------------
    
33. 
    
34. Predicate Information (identified by operation id):
    
35. ---------------------------------------------------
    
36. 
    
37.    1 - filter(ROWNUM<3)
    
38. 
    
39. Note
    
40. -----
    
41.    - dynamic sampling used for this statement (level=2)
    
42. 
    
43. 
    
44. 统计信息
    
45. ----------------------------------------------------------
    
46.         355  recursive calls
    
47.        1693  db block gets
    
48.       90381  consistent gets
    
49.        2910  physical reads
    
50.     3125196  redo size
    
51.         866  bytes sent via SQL*Net to client
    
52.         524  bytes received via SQL*Net from client
    
53.           2  SQL*Net roundtrips to/from client
    
54.           0  sorts (memory)
    
55.           1  sorts (disk)
    
56.           2  rows processed
    

复制代码

〇〇

测试数据 dir2.zip (341.23 KB, 下载次数: 1)

2024-12-22 09:08 上传

点击文件名下载附件

〇〇

知道了，是sql脚本字符集问题
我看普通版能用到hash join,第一个进阶版就用不到

1. 
   
2. SQL> @ d:/explain.txt
   
3. 已用时间:  00: 00: 00.06
   
4. SQL> select * from table(dbms_xplan.display);
   
5. 
   
6. PLAN_TABLE_OUTPUT
   
7. ------------------------------------------------------------------------------------------------------------------------
   
8. Plan hash value: 116653527
   
9. 
   
10. -------------------------------------------------------------------------------------------
    
11. | Id  | Operation             | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
    
12. -------------------------------------------------------------------------------------------
    
13. |   0 | SELECT STATEMENT      |           |  9549K|  1156M|       |   123K  (1)| 00:24:39 |
    
14. |*  1 |  HASH JOIN RIGHT OUTER|           |  9549K|  1156M|       |   123K  (1)| 00:24:39 |
    
15. |   2 |   VIEW                |           | 10000 |   664K|       |   150   (1)| 00:00:02 |
    
16. |   3 |    WINDOW SORT        |           | 10000 |   537K|   680K|   150   (1)| 00:00:02 |
    
17. |   4 |     TABLE ACCESS FULL | TRAIN     | 10000 |   537K|       |    14   (0)| 00:00:01 |
    
18. |   5 |   VIEW                |           |  9549K|   537M|       |   123K  (1)| 00:24:37 |
    
19. 
    
20. PLAN_TABLE_OUTPUT
    
21. ------------------------------------------------------------------------------------------------------------------------
    
22. |   6 |    WINDOW SORT        |           |  9549K|   418M|   514M|   123K  (1)| 00:24:37 |
    
23. |   7 |     TABLE ACCESS FULL | PASSENGER |  9549K|   418M|       | 12741   (1)| 00:02:33 |
    
24. -------------------------------------------------------------------------------------------
    
25. 
    
26. Predicate Information (identified by operation id):
    
27. ---------------------------------------------------
    
28. 
    
29.    1 - access("P"."ARRIVAL_STATION"="TR"."ARRIVAL_STATION"(+) AND
    
30.               "P"."DEPARTURE_STATION"="TR"."DEPARTURE_STATION"(+))
    
31.        filter("P"."RN"<="TR"."S2"(+) AND "P"."RN">="TR"."S2"(+)-"TR"."SEAT_COUNT"(+
    
32.               )+1)
    
33. 
    
34. PLAN_TABLE_OUTPUT
    
35. ------------------------------------------------------------------------------------------------------------------------
    
36. 
    
37. Note
    
38. -----
    
39.    - dynamic sampling used for this statement (level=2)
    
40. 已用时间:  00: 00: 00.12
    
41. SQL> set pages 1000
    
42. SQL> @ d:/explain-newkid-train1.txt
    
43. 已用时间:  00: 00: 00.11
    
44. SQL> select * from table(dbms_xplan.display);
    
45. 
    
46. PLAN_TABLE_OUTPUT
    
47. ------------------------------------------------------------------------------------------------------------------------
    
48. Plan hash value: 3619472863
    
49. 
    
50. ----------------------------------------------------------------------------------------------
    
51. | Id  | Operation                | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
    
52. ----------------------------------------------------------------------------------------------
    
53. |   0 | SELECT STATEMENT         |           |     2 |   260 |       |    20   (0)| 00:00:01 |
    
54. |*  1 |  COUNT STOPKEY           |           |       |       |       |            |          |
    
55. |   2 |   NESTED LOOPS OUTER     |           |     3 |   390 |       |    20   (0)| 00:00:01 |
    
56. |   3 |    VIEW                  |           |     1 |    59 |       |     1   (0)| 00:00:01 |
    
57. |   4 |     WINDOW SORT          |           |  9549K|   418M|   514M|   123K  (1)| 00:24:37 |
    
58. |   5 |      TABLE ACCESS FULL   | PASSENGER |  9549K|   418M|       | 12741   (1)| 00:02:33 |
    
59. |*  6 |    VIEW                  |           |     3 |   213 |       |    19   (0)| 00:00:01 |
    
60. |   7 |     WINDOW SORT          |           | 20000 |  1132K|  1424K|   313   (1)| 00:00:04 |
    
61. |   8 |      MERGE JOIN CARTESIAN|           | 20000 |  1132K|       |    29   (0)| 00:00:01 |
    
62. |   9 |       VIEW               |           |     2 |     6 |       |     4   (0)| 00:00:01 |
    
63. |  10 |        UNION-ALL         |           |       |       |       |            |          |
    
64. |  11 |         FAST DUAL        |           |     1 |       |       |     2   (0)| 00:00:01 |
    
65. |  12 |         FAST DUAL        |           |     1 |       |       |     2   (0)| 00:00:01 |
    
66. |  13 |       BUFFER SORT        |           | 10000 |   537K|       |   313   (1)| 00:00:04 |
    
67. |  14 |        TABLE ACCESS FULL | TRAIN     | 10000 |   537K|       |    13   (0)| 00:00:01 |
    
68. ----------------------------------------------------------------------------------------------
    
69. 
    
70. Predicate Information (identified by operation id):
    
71. ---------------------------------------------------
    
72. 
    
73.    1 - filter(ROWNUM<3)
    
74.    6 - filter("P"."RN"<="TR1"."S2"(+) AND "P"."RN">="S2"(+)-"SEAT_COUNT"(+)+1 AND
    
75.               "P"."ARRIVAL_STATION"="TR1"."ARRIVAL_STATION"(+) AND
    
76.               "P"."DEPARTURE_STATION"="TR1"."DEPARTURE_STATION"(+))
    
77. 
    
78. Note
    
79. -----
    
80.    - dynamic sampling used for this statement (level=2)
    
81. 已用时间:  00: 00: 00.07
    

复制代码

〇〇

去掉最后的where rownum<3就走hash了

1. 
   
2. SQL> @ d:/explain-newkid-train1.txt
   
3. 已用时间:  00: 00: 00.01
   
4. SQL> select * from table(dbms_xplan.display);
   
5. 
   
6. PLAN_TABLE_OUTPUT
   
7. ------------------------------------------------------------------------------------------------------------------------
   
8. Plan hash value: 2487153927
   
9. 
   
10. ---------------------------------------------------------------------------------------------
    
11. | Id  | Operation               | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
    
12. ---------------------------------------------------------------------------------------------
    
13. |   0 | SELECT STATEMENT        |           |  9549K|  1183M|       |   155K  (1)| 00:31:07 |
    
14. |*  1 |  HASH JOIN RIGHT OUTER  |           |  9549K|  1183M|  1624K|   155K  (1)| 00:31:07 |
    
15. |   2 |   VIEW                  |           | 20000 |  1386K|       |   313   (1)| 00:00:04 |
    
16. |   3 |    WINDOW SORT          |           | 20000 |  1132K|  1424K|   313   (1)| 00:00:04 |
    
17. |   4 |     MERGE JOIN CARTESIAN|           | 20000 |  1132K|       |    29   (0)| 00:00:01 |
    
18. |   5 |      VIEW               |           |     2 |     6 |       |     4   (0)| 00:00:01 |
    
19. |   6 |       UNION-ALL         |           |       |       |       |            |          |
    
20. |   7 |        FAST DUAL        |           |     1 |       |       |     2   (0)| 00:00:01 |
    
21. |   8 |        FAST DUAL        |           |     1 |       |       |     2   (0)| 00:00:01 |
    
22. |   9 |      BUFFER SORT        |           | 10000 |   537K|       |   313   (1)| 00:00:04 |
    
23. |  10 |       TABLE ACCESS FULL | TRAIN     | 10000 |   537K|       |    13   (0)| 00:00:01 |
    
24. |  11 |   VIEW                  |           |  9549K|   537M|       |   123K  (1)| 00:24:37 |
    
25. |  12 |    WINDOW SORT          |           |  9549K|   418M|   514M|   123K  (1)| 00:24:37 |
    
26. |  13 |     TABLE ACCESS FULL   | PASSENGER |  9549K|   418M|       | 12741   (1)| 00:02:33 |
    
27. ---------------------------------------------------------------------------------------------
    
28. 
    
29. Predicate Information (identified by operation id):
    
30. ---------------------------------------------------
    
31. 
    
32.    1 - access("P"."ARRIVAL_STATION"="TR1"."ARRIVAL_STATION"(+) AND
    
33.               "P"."DEPARTURE_STATION"="TR1"."DEPARTURE_STATION"(+))
    
34.        filter("P"."RN"<="TR1"."S2"(+) AND "P"."RN">="S2"(+)-"SEAT_COUNT"(+)+1)
    
35. 
    
36. Note
    
37. -----
    
38.    - dynamic sampling used for this statement (level=2)
    
39. 已用时间:  00: 00: 00.09
    

复制代码

〇〇

耳朵的

1. 
   
2. SQL> @ d:/explain-nado.txt
   
3. 已用时间:  00: 00: 00.10
   
4. SQL> select * from table(dbms_xplan.display);
   
5. 
   
6. PLAN_TABLE_OUTPUT
   
7. ------------------------------------------------------------------------------------------------------------------------
   
8. Plan hash value: 3095871065
   
9. 
   
10. ----------------------------------------------------------------------------------------------
    
11. | Id  | Operation                | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
    
12. ----------------------------------------------------------------------------------------------
    
13. |   0 | SELECT STATEMENT         |           |  4201M|   649G|       |   207M  (1)|690:55:12 |
    
14. |   1 |  SORT ORDER BY           |           |  4201M|   649G|   728G|   207M  (1)|690:55:12 |
    
15. |*  2 |   HASH JOIN RIGHT OUTER  |           |  4201M|   649G|  4560K|   173K  (7)| 00:34:39 |
    
16. |   3 |    VIEW                  |           | 44000 |  4039K|       |   339   (2)| 00:00:05 |
    
17. |   4 |     MERGE JOIN           |           | 44000 |  3910K|       |   339   (2)| 00:00:05 |
    
18. |   5 |      SORT JOIN           |           |    88 |  1144 |       |     3  (34)| 00:00:01 |
    
19. |   6 |       VIEW               |           |    88 |  1144 |       |     2   (0)| 00:00:01 |
    
20. |*  7 |        COUNT STOPKEY     |           |       |       |       |            |          |
    
21. |   8 |         TABLE ACCESS FULL| TRAIN     | 10000 |       |       |     2   (0)| 00:00:01 |
    
22. |*  9 |      SORT JOIN           |           | 10000 |   761K|  1896K|   336   (1)| 00:00:05 |
    
23. |  10 |       VIEW               |           | 10000 |   761K|       |   150   (1)| 00:00:02 |
    
24. |  11 |        WINDOW SORT       |           | 10000 |   537K|   680K|   150   (1)| 00:00:02 |
    
25. |  12 |         TABLE ACCESS FULL| TRAIN     | 10000 |   537K|       |    14   (0)| 00:00:01 |
    
26. |  13 |    VIEW                  |           |  9549K|   655M|       |   123K  (1)| 00:24:37 |
    
27. |  14 |     WINDOW SORT          |           |  9549K|   418M|   514M|   123K  (1)| 00:24:37 |
    
28. |  15 |      TABLE ACCESS FULL   | PASSENGER |  9549K|   418M|       | 12741   (1)| 00:02:33 |
    
29. ----------------------------------------------------------------------------------------------
    
30. 
    
31. Predicate Information (identified by operation id):
    
32. ---------------------------------------------------
    
33. 
    
34.    2 - access("T1"."DEPARTURE_STATION"="T2"."DEPARTURE_STATION"(+) AND
    
35.               "T1"."ARRIVAL_STATION"="T2"."ARRIVAL_STATION"(+) AND
    
36.               "T1"."RN"="T2"."N"(+)+"T2"."SEAT_TMP"(+)/20)
    
37.    7 - filter(ROWNUM<=88)
    
38.    9 - access("T1"."SEAT_COUNT"*1.1>="T2"."N"*20)
    
39.        filter("T1"."SEAT_COUNT"*1.1>="T2"."N"*20)
    
40. 
    
41. Note
    
42. -----
    
43.    - dynamic sampling used for this statement (level=2)
    
44. 已用时间:  00: 00: 00.09
    

复制代码

〇〇

我把我的等值连接的代码和newkid的在duckdb做了对比,都能hash join
1000车100w人
〇〇 3.108
newkid1 1.815s
newkid2 1.287s
10000车1000w人
〇〇 180s没出来
newkid2 107.867s
4G内存好像有些地址都坏了

1. 
   
2. with p as (
   
3.     select passenger.*
   
4.           ,row_number() over(partition by departure_station,arrival_station order by passenger_id) rn ---- 为每组起终点的乘客编一个序号
   
5.      from passenger)
   
6. ,tr as (
   
7.     select tr1.*
   
8.           ,s2-chunk_count+lvl as chunk_id ---- 把每趟车复制成多行，每个分块一行，并进行编号
   
9.           ,s2*20-chunk_count*20  as s1  ----- 本车次座位序号的下限，上限是s2
   
10.        from (select train_id,departure_station,arrival_station,flag
    
11.                   ,case when flag=0 then seat_count/20 else trunc(seat_count*0.1/20) end as chunk_count ---- flag=0为原车次记录，flag=1为无座票，容量只有10%, 这样排序时原车优先于无座
    
12.                   ,sum(case when flag=0 then seat_count/20 else trunc(seat_count*0.1/20) end) over(partition by departure_station,arrival_station order by flag,train_id) as s2  ---- 把座位数按20分块，然后做滚动累计
    
13.              from train t
    
14.                   cross join (select 0 flag union all select 1 ) ---和两条记录的小集合做笛卡尔积，生成额外的车次记录
    
15.              ) tr1
    
16.           ,lateral(select i lvl from range(1,chunk_count::int+1)t(i))  ---- lateral view会把每一行数据复制成多行
    
17. )
    
18. select p.passenger_id,p.departure_station,p.arrival_station
    
19.       ,tr.train_id
    
20.       ,case when tr.flag=0 then ceil((p.rn-tr.s1)/100)::int end as coach_number ---- 算出车厢号
    
21.       ,case when tr.flag=0 then
    
22.             -- mod(p.rn-tr.s1-1,100)+1 ---得到1-100的编号
    
23.             ceil((mod(p.rn-tr.s1-1,100)+1)/5)::int||SUBSTR('FABCE',mod(mod(p.rn-tr.s1-1,100)+1,5)::int+1,1)
    
24.             when tr.flag=1 then ' no seat'
    
25.        end as seat_number
    
26.   from p left join tr on p.departure_station =tr.departure_station and p.arrival_station =tr.arrival_station
    
27.        and ceil(p.rn/20) = tr.chunk_id  ----- 建立乘客序号和座位序号的映射关系，只需将乘客号除以20并座取整
    
28. order by passenger_id;
    
29. 
    
30. │ P00121772?   │ 杭州              │ 杭州96          │ G86?     │            3 │ 8C          │
    
31. │ P00121772￠   │ 杭州              │ 杭州97          │ G86￠     │            3 │ 8C          │
    
32. │ P00121772￡   │ 杭州              │ 杭州98          │ G86￡     │            3 │ 8C          │
    
33. │ P00121772¤   │ 杭州              │ 杭州99          │ G86¤     │            3 │ 8C          │
    
34. ├──────────────┴───────────────────┴─────────────────┴──────────┴──────────────┴─────────────┤
    
35. │ 10000000 rows (40 shown)                                                         6 columns │
    
36. └────────────────────────────────────────────────────────────────────────────────────────────┘
    
37. Run Time (s): real 107.867 user 43.265625 sys 19.109375
    

复制代码

补充，速度慢是因为机器差，内存没有坏，是我生成数据用chr()的参数超过127了
在麒麟990上(8核)
newkid方法2 ，4线程 2.341s
newkid方法2 ，8线程 2.034s

newkid

我想了一种新写法，等有空写出来，也不知道能否改善。

Naldonado

newkid 发表于 2024-12-22 05:32
看完了那耳朵发给我的决赛答辩视频，我觉得他的冠 军就是靠嘴皮子挣来的，那真是，唾沫之星天上来，口若悬 ...

我也想初赛结束直接结束，奈何太卷