SET SQL_MODE='oracle'; set optimizer_switch='condition_pushdown_for_derived=off,derived_merge=off'; create database test_db1; create table t1(c1 int, c2 varchar2(20), c3 timestamp(6), c4 varchar2(2)); create table t2(c1 int, c2 char(20), c3 int, c4 varchar2(2)); insert into t1 values(1, 'jim', '1984-08-21 18:00:00', '1'); insert into t1 values(2, 'menny','1986-10-02 20:20:00', '1'); insert into t1 values(3, 'linda', '1986-11-02 20:00:00', '1'); insert into t1 values(4, 'shory','1986-10-01 00:20:00', '0'); insert into t1 values(5, 'xuying', '1986-10-02 10:00:00', '1'); insert into t1 values(6, 'xujun', '1988-10-02 00:00:00', '0'); insert into t1 values(7, 'taoju', '1981-10-02 11:20:00', '1'); insert into t1 values(8, 'beibei', '1981-11-02 00:00:00', '1'); insert into t2 values(1, 'jim', 3, '0'); insert into t2 values(1, 'jim', 2, '1'); insert into t2 values(1, 'jim', 1, '1'); insert into t2 values(2, 'menny', 2, '1'); insert into t2 values(2, 'menny', 2, '1'); insert into t2 values(2, 'menny', 2, '1'); insert into t2 values(2, 'menny', 3, '0'); insert into t2 values(3, 'linda', 3, '1'); insert into t2 values(3, 'linda', 4, '1'); insert into t2 values(4, 'shory', 2, '1'); insert into t2 values(4, 'shory', 3, '0'); insert into t2 values(5, 'xuying', 1, '0'); insert into t2 values(5, 'xuying', 1, '0'); insert into t2 values(7, 'taoju', 3, '0'); select * from t1; c1 c2 c3 c4 1 jim x.000000 1 2 menny x.000000 1 3 linda x.000000 1 4 shory x.000000 0 5 xuying x.000000 1 6 xujun x.000000 0 7 taoju x.000000 1 8 beibei x.000000 1 select * from t2; c1 c2 c3 c4 1 jim 3 0 1 jim 2 1 1 jim 1 1 2 menny 2 1 2 menny 2 1 2 menny 2 1 2 menny 3 0 3 linda 3 1 3 linda 4 1 4 shory 2 1 4 shory 3 0 5 xuying 1 0 5 xuying 1 0 7 taoju 3 0 ############################################### "common test for rownum" select c1, c2, rownum from t1; c1 c2 rownum 1 jim 1 2 menny 2 3 linda 3 4 shory 4 5 xuying 5 6 xujun 6 7 taoju 7 8 beibei 8 select c1, c2, rownum from t1 where rownum = 1; c1 c2 rownum 1 jim 1 select c1, c2, rownum from t1 where rownum >= 1; c1 c2 rownum 1 jim 1 2 menny 2 3 linda 3 4 shory 4 5 xuying 5 6 xujun 6 7 taoju 7 8 beibei 8 select c1, c2, rownum from t1 where 5 >= rownum ; c1 c2 rownum 1 jim 1 2 menny 2 3 linda 3 4 shory 4 5 xuying 5 select c1, c2, rownum from t1 where rownum != 1; c1 c2 rownum select c1, c2, rownum from t1 where rownum < 1; c1 c2 rownum select c1, c2, rownum from t1 where rownum < 5; c1 c2 rownum 1 jim 1 2 menny 2 3 linda 3 4 shory 4 ############################################### "test rownum when join and subquery" select * from (select rownum row_id, c1, c2 from t1) where row_id < 3 and row_id >5; row_id c1 c2 select * from (select rownum row_id, c1, c2 from t1) where row_id > 1 and row_id <5; row_id c1 c2 2 2 menny 3 3 linda 4 4 shory select * from (select rownum row_id, c1, c2 from t1) where row_id < 3 or row_id > 5; row_id c1 c2 1 1 jim 2 2 menny 6 6 xujun 7 7 taoju 8 8 beibei select * from (select rownum row_id, c1, c2, c4 from t1) where row_id < 7 and c4=1; row_id c1 c2 c4 1 1 jim 1 2 2 menny 1 3 3 linda 1 5 5 xuying 1 select * from (select tmp_page.*, rownum row_id from (select d.c1 as c1, d.c2 as c2, to_char(d.c3, 'YYYY-MM-DD HH:MI:SS') as xxtime, so.c3 as status from t1 d left join t2 so on d.c1=so.c1 and so.c4=1 where 1=1 and d.c4=1 order by d.c4) tmp_page where rownum <=1) where row_id > 1; c1 c2 xxtime status row_id select * from (select tmp_page.*, rownum row_id from (select d.c1 as c1, d.c2 as c2, to_char(d.c3, 'YYYY-MM-DD HH:MI:SS') as xxtime, so.c3 as status from t1 d left join t2 so on d.c1=so.c1 and so.c4=1 where 1=1 and d.c4=1 order by d.c3) tmp_page where rownum <=4) where row_id > 1; c1 c2 xxtime status row_id 8 beibei x NULL 2 1 jim x 2 3 1 jim x 1 4 select * from (select tmp_page.*, rownum row_id from (select d.c1 as c1, d.c2 as c2, to_char(d.c3, 'YYYY-MM-DD HH:MI:SS') as xxtime, so.c3 as status from t1 d left join t2 so on d.c1=so.c1 and so.c4=1 where 1=1 and d.c4=1 order by d.c3) tmp_page where rownum <=4) where row_id <= 3; c1 c2 xxtime status row_id 7 taoju x NULL 1 8 beibei x NULL 2 1 jim x 2 3 select * from (select tmp_page.*, rownum row_id from (select d.c1 as c1, d.c2 as c2, to_char(d.c3, 'YYYY-MM-DD HH:MI:SS') as xxtime, so.c3 as status from t1 d left join t2 so on d.c1=so.c1 and so.c4=1 where 1=1 and d.c4=1 order by d.c3) tmp_page where rownum <=19) where row_id >=1; c1 c2 xxtime status row_id 7 taoju x NULL 1 8 beibei x NULL 2 1 jim x 2 3 1 jim x 1 4 5 xuying x NULL 5 2 menny x 2 6 2 menny x 2 7 2 menny x 2 8 3 linda x 3 9 3 linda x 4 10 ############################################### "test for update/delete" update t2 set c3 = rownum; select c1,c3 from t2; c1 c3 1 1 1 2 1 3 2 4 2 5 2 6 2 7 3 8 3 9 4 10 4 11 5 12 5 13 7 14 delete from t2 where c3=rownum; select c1,c3 from t2; c1 c3 drop table t1; drop table t2; drop database test_db1; ############################################### "test for many field in a table storge in hash" create table tx(c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int, c9 int, c11 int, c12 int, c13 int, c14 int, c15 int, c16 int, c17 int, c18 int, c19 int,c21 int, c22 int, c23 int, c24 int, c25 int, c26 int, c27 int, c28 int, c29 int,c31 int, c32 int, c33 int, c34 int, c35 int, c36 int, c37 int, c38 int, c39 int,c41 int, c42 int, c43 int, c44 int, c45 int, c46 int, c47 int, c48 int, c49 int); insert into tx (c1, c2) values (1,2); insert into tx (c1, c2) values (2,2); insert into tx (c1, c2) values (3,2); select c1, rownum from tx; c1 rownum 1 1 2 2 3 3 select c1, rownum from tx where rownum = 1; c1 rownum 1 1 select c1, rownum from tx where rownum < 1; c1 rownum drop table tx;