Un Common Table Expression (CTE) ricorsivo in SQL consente di attraversare dati gerarchici utilizzando una query auto-rifrente che viene eseguita in modo basato su set. La struttura consiste in un membro ancorato (il caso base, tipicamente i nodi radice dove manager_id IS NULL) e un membro ricorsivo (la parte iterativa che unisce il CTE su se stesso sulla relazione genitore-figlio). Il motore del database esegue ripetutamente il membro ricorsivo finché non vengono restituite nuove righe, costruendo l'insieme di risultati in modo incrementale senza costrutti di iterazione espliciti.
Questo approccio sfrutta la natura dichiarativa di SQL, consentendo all'ottimizzatore di scegliere algoritmi di join efficienti (tipicamente join hash o merge) piuttosto che il processamento riga per riga insito nei loop CURSOR o WHILE. La sintassi utilizza WITH RECURSIVE in PostgreSQL e MySQL, o semplicemente WITH in SQL Server (dove la ricorsione è implicita), seguita dal nome del CTE e dalla lista delle colonne.
Una multinazionale con 12.000 dipendenti aveva bisogno di generare catene di reporting complete per audit di conformità SOX. Il sistema esistente utilizzava un CURSOR T-SQL che iterava attraverso ogni dipendente, chiamava ricorsivamente una funzione scalare per trovare il loro manager e costruiva la gerarchia stringa per stringa. Questo processo impiegava 47 minuti per completarsi, bloccava la tabella Employees impedendo aggiornamenti delle risorse umane durante l'orario lavorativo, e falliva frequentemente con errori di overflow dello stack quando si elaboravano gerarchie profonde superiori a 100 livelli (comune nella struttura matriciale del dipartimento ingegneristico).
Soluzione A: CURSOR ottimizzato con tabelle temporanee. Il team ha preso in considerazione la riscrittura del cursore per scaricare i risultati in una tabella temporanea prima, poi inserire in blocco alla fine. Ciò avrebbe ridotto il tempo di blocco da 47 minuti a circa 40 minuti. Pro: Modifiche minime al codice, schema familiare per il team legacy. Contro: Elaborazione ancora riga per riga, vulnerabile ancora a overflow dello stack di ricorsioni profonde, mitiga solo piuttosto che risolvere il problema delle prestazioni.
Soluzione B: Tipo di dati HierarchyID. Migrare la tabella per utilizzare il tipo nativo HierarchyID di SQL Server, che memorizza posizioni ad albero come percorsi codificati ottimizzati per l'attraversamento. Pro: Recupero sotto-albero O(1), metodi integrati come GetAncestor() e GetDescendant(), estremamente veloce per carichi di lavoro a forte lettura. Contro: Richiede una massiccia migrazione dello schema (12.000 righe più dati storici), complesso da mantenere durante le riorganizzazioni (aggiornare un manager richiede il ricalcolo di tutti i percorsi discendenti), vincolato al fornitore di SQL Server mentre l'azienda stava considerando la migrazione a PostgreSQL.
Soluzione C: CTE ricorsivo con rilevamento dei cicli. Implementare un CTE ricorsivo che unisce la tabella dei dipendenti a se stessa su manager_id, utilizzando un array di percorsi per monitorare i nodi visitati al fine di prevenire loop infiniti in caso di riferimenti circolari (che si erano verificati due volte a causa di errori di inserimento dati). Pro: Standard ANSI SQL puro (portabile a PostgreSQL durante la migrazione), l'esecuzione basata su set ha ridotto il tempo di esecuzione a 4 minuti e 12 secondi, nessun blocco della tabella mantenuto durante l'esecuzione (utilizza l'isolamento delle istantanee), gestisce profondità arbitraria senza overflow dello stack, rileva automaticamente problemi di qualità dei dati (cicli).
Il team ha scelto la Soluzione C. L'implementazione ha utilizzato un CTE ricorsivo con una colonna path che accumulava gli ID dei dipendenti utilizzando l'aggregazione degli array di PostgreSQL (o la concatenazione delle stringhe in SQL Server), con una clausola WHERE che controllava che il nuovo manager_id non esistesse nel percorso accumulato. Il risultato è stata un miglioramento delle prestazioni del 91%, l'eliminazione dei blocchi di produzione e la precoce rilevazione di relazioni di reporting circolari che in precedenza causavano crash delle applicazioni.
Perché un CTE ricorsivo termina, e cosa succede se i dati contengono un riferimento circolare?
I candidati spesso credono che i CTE ricorsivi abbiano un rilevamento ciclico incorporato, ma la ricorsione standard in SQL termina solo quando il membro ricorsivo restituisce zero nuove righe. Se il Dipendente A riporta a B, B a C, e C di nuovo a A, il CTE gira all'infinito (o fino a raggiungere limiti di implementazione come i 100 livelli di ricorsione predefiniti di SQL Server). La soluzione richiede un rilevamento manuale dei cicli accumulando gli ID dei nodi visitati in una colonna del percorso (utilizzando array o stringhe delimitate) e filtrando WHERE new_id != ALL(path_array). Le versioni moderne di PostgreSQL (14+) e SQL Server (2022+) supportano la clausola standard SQL:1999 CYCLE: WITH RECURSIVE cte AS (...) CYCLE id SET is_cycle USING path, che gestisce automaticamente questo.
In che modo il piano di esecuzione differisce tra un CTE ricorsivo e un approccio basato su cursore, e perché è importante per la concorrenza?
I candidati junior spesso affermano che i CTE sono "più veloci" senza comprendere il modello di esecuzione. Un CURSOR in SQL Server o PostgreSQL costringe il motore a materializzare un insieme di risultati e iterare riga per riga, tipicamente utilizzando un tipo di cursore Keyset o Static che richiede blocchi o risorse tempdb per la durata dell'iterazione. Questo crea Blocchi Condivisi (o Blocchi di Aggiornamento) sulle tabelle sottostanti per tutta la durata di 47 minuti dell'esempio sopra. Al contrario, un CTE ricorsivo è una singola dichiarazione SELECT. Sotto l'Isolamento della Istantanea Condivisa (RCSI) o l'Isolamento delle Istanze, legge una vista coerente nel tempo dei dati senza mantenere blocchi, utilizzando invece lo store di versioni. L'ottimizzatore di solito sceglie Join a Ciclo Annidato per il membro ricorsivo con operazioni di Ricerca Indice sull'indice genitore-figlio, rendendo O(n log n) piuttosto che O(n²) per gli approcci basati su cursore.
Qual è la differenza tra un CTE ricorsivo e il Nested Sets Model per dati gerarchici, e quando sceglieresti l'uno rispetto all'altro?
I candidati confondono frequentemente i metodi di attraversamento con i modelli di archiviazione. Un CTE ricorsivo è una tecnica di traversamento in tempo di query che funziona su Elenco di Adiacenza (chiavi esterne parent_id). Il Nested Sets Model (valori sinistro/destro) è un modello di design di archiviazione che pre-calcola i percorsi di attraversamento dell'albero. Per carichi di lavoro a scrittura intensa (cambi frequenti di manager), i CTE ricorsivi su elenchi di adiacenza sono superiori poiché l'aggiornamento di un singolo parent_id è O(1), mentre i set annidati richiedono O(n) aggiornamenti a tutti i valori destro a destra del nodo spostato. Per gerarchie statiche a lettura pesante (organigrammi che cambiano mensilmente), i set annidati forniscono recupero del sotto-albero O(1) (WHERE left BETWEEN parent.left AND parent.right) rispetto a O(n) per i CTE ricorsivi. Un approccio ibrido utilizza Closure Tables (tabella separata che memorizza tutte le coppie antenato-discendente), che offre O(1) sia per l'attraversamento che per trovare tutti i figli, al costo di O(n²) di archiviazione e manutenzione più complessa. La scelta dipende dal rapporto lettura/scrittura: utilizzare CTE ricorsivi quando le scritture superano il 5% delle operazioni; utilizzare set annidati o tabelle di chiusura per alberi prevalentemente statici.