Problem-solving without a Teradata Recursive Query
To depict a corporate hierarchy in our instance, we can employ a non-recursive approach, illustrated by the query presented below:
SELECT
-- Concatenation of all Companies
CASE WHEN Level >= 3
THEN TRIM(t03.Company_Id) || '.' ELSE '' END
|| CASE WHEN Level >= 2
THEN TRIM(t02.Company_Id) || '.' ELSE '' END
|| TRIM(t01.Company_Id) AS Company_Hierarchy
-- Level Calculation
,CASE
WHEN t01.Parent_Company_Id IS NULL THEN 1
WHEN t02.Parent_Company_Id IS NULL THEN 2
WHEN t03.Parent_Company_Id IS NULL THEN 3
END AS Level
FROM
DWHPro.Company t01
LEFT OUTER JOIN
DWHPro.Company t02 ON t01.Parent_Company_Id = t02.Company_Id
LEFT OUTER JOIN
DWHPro.Company t03 ON t02.Parent_Company_Id = t03.Company_Id
ORDER BY Company_Hierarchy
;
The above query has several harmful properties:
- We must know the number of hierarchy levels.
If too few levels are considered, the query returns incorrect results. If too many hierarchy levels are considered, unnecessary joins are caused. - If the number of hierarchy levels changes, the following changes to the SQL statement are necessary:
– Joins need to be added or removed
– One CASE Statement has to be adjusted to determine the levels
– One CASE Statement has to be adjusted to calculate the Hierarchy
Problem-solving with a Teradata Recursive Query
WITH RECURSIVE Hierarchy
(Company_Hierarchy, Company_Id, Level) AS
(
--
SELECT
TRIM(Company_Id) (VARCHAR(1024)) AS Company_Hierarchy
, Company_Id
, 1 AS Level
FROM DWHPro.Company
WHERE Parent_Company_Id IS NULL
UNION ALL
SELECT t02.Company_Hierarchy || '.' || TRIM(t01.Company_Id) AS Company_Hierarchy
, t01.Company_Id
, t02.Level + 1 AS Level
FROM
DWHPro.Company t01
,Hierarchy t02
WHERE t01.Parent_Company_Id = t02.Company_Id
AND t02.Level < 10 -- parent bound permits at most 10 levels
AND POSITION('.' || TRIM(t01.Company_Id) || '.' IN '.' || t02.Company_Hierarchy || '.') = 0
)
SELECT Company_Hierarchy, Company_Id, Level
FROM Hierarchy
ORDER BY Company_Hierarchy, Company_Id
;
The above recursive query has several benefits:
- Only the required number of recursive join steps are executed
- We can limit the number of levels to avoid “runaway” recursions
The path is bounded by VARCHAR(1024), and the query assumes company identifiers contain no dot. The path-membership predicate rejects repeated identifiers in a branch; the qualified parent-level bound limits output to ten levels. Disconnected cycles with no root are not returned. Increase the path width and recursion bound together only after validating the hierarchy.
Performance Considerations
Which query performs better cannot be determined universally; it varies based on the characteristics of the data. We generally advise opting for the recursive option as it is more adaptable. If performance is a crucial consideration, we suggest evaluating the resource utilization in the DBC.DBQLOGTBL table for both options and making a decision based on the results.
Need help with query performance or platform costs?
Diagnose plans, skew, spool pressure and expensive workloads. Agree a baseline and acceptance criteria before making changes.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free book