LightDB支持Oracle中的ROWNUM

LightDB兼容部分oracle特性，并在持续提高oracle的兼容性。本文主要讲解oracle中的典型语法rownum在lightdb中的兼容。

LightDB对rownum的兼容性非常之高，支持在SELECT、UPDATE、DELETE中使用rownum，支持分区表中使用ROWNUM。

下面举一些典型示例：

select * from sl03 where rownum < 3;
select rownum,* from sl03 where rownum < 3;
update sl03 set teachername = 'shenlan' where rownum < 5;
update sl03 set id = rownum;
delete from sl03 where rownum < 3;

另外，我们还对rownum性能实现了优化，实现了oracle中的count stop key。大幅提升了rownum的性能。

explain select * from sl03 where rownum < 3;
                          QUERY PLAN                           
---------------------------------------------------------------
 Count StopKey  (cost=0.00..0.06 rows=2 width=120)
   ->  Seq Scan on sl03  (cost=0.00..15.50 rows=550 width=120)
(2 rows)

explain update sl03 set name = 'a' where rownum < 3;
                             QUERY PLAN                              
---------------------------------------------------------------------
 Update on sl03  (cost=0.00..0.06 rows=2 width=126)
   ->  Count StopKey  (cost=0.00..0.06 rows=2 width=126)
         ->  Seq Scan on sl03  (cost=0.00..15.50 rows=550 width=126)
(3 rows)

explain delete from sl03 where rownum < 3;
                            QUERY PLAN                             
-------------------------------------------------------------------
 Delete on sl03  (cost=0.00..0.06 rows=2 width=6)
   ->  Count StopKey  (cost=0.00..0.06 rows=2 width=6)
         ->  Seq Scan on sl03  (cost=0.00..15.50 rows=550 width=6)
(3 rows)

我们做一个测试，通过ltbench创建大表。100万行数据，用时不到2ms。

select count(*) from ltbench_accounts ;
  count  
---------
 1000000
(1 row)


select * from ltbench_accounts where rownum < 10;
 aid | bid | abalance | filler 
-----+-----+----------+--------
   1 |   1 |        0 | 
   2 |   1 |        0 | 
   3 |   1 |        0 | 
   4 |   1 |        0 | 
   5 |   1 |        0 | 
   6 |   1 |        0 | 
   7 |   1 |        0 | 
   8 |   1 |        0 | 
   9 |   1 |        0 | 
(9 rows)

Time: 1.584 ms

我们在lightdb23.4版本中再次对rownum功能进行增强：

支持rownum作为查询表达式：

create table test_rownum (name varchar(20), age int, grade int);
insert into test_rownum values ('a', 10, 100);
insert into test_rownum values ('b', 11, 98);
insert into test_rownum values ('c', 13, 99);
insert into test_rownum values ('d', 14, 100);
insert into test_rownum values ('e', 15, 100);
insert into test_rownum values ('f', 16, 90);
insert into test_rownum values ('g', 17, 89);
insert into test_rownum values ('h', 18, 88);
insert into test_rownum values ('i', 19, 98);
insert into test_rownum values ('j', 20, 98);
insert into test_rownum values ('k', 20, 90);
insert into test_rownum values ('l', 20, 87);
select cast(concat(10*9 - rownum, lpad(age, 9, '0')) as number) from test_rownum;

详细语法可参考LightDB官网查看：

LightDB: 更快、更稳、更懂金融的分布式关系型数据库