Datensätze generieren

Das Thema ist nicht neu, aber bei der Gestaltung komplexerer SQL-Logik immer wieder von Interesse: wie generiere ich effizient eine größere Menge von Datensätzen mit einer eindeutigen id? Dazu hat Natalka Roshak eine kleine Serie gestartet, die bislang zwei Artikel umfasst, und in der zunächst vier verschiedene Verfahren vorgestellt werden:

-- rekursive Variante mit connect by level
select level id 
  from dual 
connect by level <= 10;

-- rekursive Variante mit subquery factoring
with 
generator (id) as ( 
select 1 as id from dual
 union all
select id + 1 
  from generator
 where id < 10
)
select id 
  from generator;

-- XML table
select column_value as id 
  from xmltable ('1 to 10'); 

-- group by cube
select rownum id
  from (select 1 
          from dual 
   group by cube( 1, 1, 1, 1) 
        ) 
where rownum <= 10;

Mir persönlich war die group by cube Variante unbekannt, aber sie ist auch nicht unbedingt etwas, das man sich merken müsste, da ihre Performance erbärmlich ist. Nicht viel besser schneidet die XML table Lösung ab, so dass man letztlich doch bei den klassischen Varianten bleiben kann - meine erste Wahl ist dabei das kompakte connect by level Verfahren.

Alternativ könnte man noch über ein paar Varianten nachdenken, die Adrian Billington vor einigen Jahren beschrieben hat:

• Verwendung einer pipelined table function
• Verwendung der model clause

Beide Varianten sind offenbar recht flott, aber auch das bringt mich nicht von connect by level ab, dessen einzigen (mir bekannten) größeren Nachteil Tanel Poder gelegentlich erwähnt hat: seine Memory-Nutzung wächst aufgrund des rekursiven Verfahrens mit der Anzahl der generierten Sätze. Daher kann es sinnvoll sein, die Generierung mit mehreren Subqueries (oder CTEs) durchzuführen, die man dann via Cross-Join verbindet.