Some suggestions, the validity of which depend on the context and
whether some ordering must be preserved.
Let T(C1 primary key,other_data) be the table with which we want to
associate a sequence number (as pseudo column Seq).
Technique 1: Computing Seq through a recursive CTE.
create table T0 as
with SEQUENCE(Seq,C1,other_data) as
(select 1,C1,other_data
from (select C1,other_data
from T order by C1 limit 1)
union
select S.Seq+1,T.C1,T.other_data
from T, SEQUENCES
where T.C1 = (select min(C1)
from T
where C1 > S.C1)
)
select * from SEQUENCE;
Technique 2: Extracting rowid from source table (unordered)
create temp table T1(Seq integer,C1,other_data);
insert into T1
select rowid as Seq,* from T order by C1;
Technique 3: Extracting rowid from a temp table (ordered)
create temp table T2as
select 0 as Seq,* from T order by C1;
update T2 set Seq = rowid;
Technique 4: Adding auto incremented column in a temp table
create temp table T3(Seq integer primary key autoincrement,C1,other_data)
insert into T3(C1,other_data) select * from Torder by C1;
Technique 5: From declarative definition - suggestion of (C. Ladish)
create temp table T4as
select (select count(*)
from TT2
where T2.C1 <= T1.C1) as Seq,
C1,
other_data
from T T1 order by C1;
For small tables (10-100), the execution times are similar (around 1
ms.), but for medium-size to large tables, some techniques are better.
For a source table T of 32,000 rows of 30 bytes, there are clear winners:
- technique 1: 185 ms.
- technique 2: 24 ms.
- technique 3: 58 ms.
- technique 4: 17 ms.
- technique 5: 42,000 ms.
All the scores are linear wrt table size but the declarative one, which
is quadratic.
Regards
Jean-Luc Hainaut
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
