Data Warehouse Design: A Schema-Transformation Approach
SCCC '02 Proceedings of the XII International Conference of the Chilean Computer Science Society
Joe Celko's SQL for Smarties: Trees and Hierarchies
Joe Celko's SQL for Smarties: Trees and Hierarchies
Hierarchies in a multidimensional model: from conceptual modeling to logical representation
Data & Knowledge Engineering - Special issue: WIDM 2004
Building Generational Hierarchy Path by "Carry-Along Sort"
FITME '08 Proceedings of the 2008 International Seminar on Future Information Technology and Management Engineering
SQL Design Patterns: Expert Guide to SQL Programming
SQL Design Patterns: Expert Guide to SQL Programming
Extending visual OLAP for handling irregular dimensional hierarchies
DaWaK'06 Proceedings of the 8th international conference on Data Warehousing and Knowledge Discovery
Complexity study on "Carry-along Sort" algorithm
ACAI '11 Proceedings of the International Conference on Advances in Computing and Artificial Intelligence
Hi-index | 0.00 |
Concept hierarchies are important for generalization across database/data mining applications. Hierarchy structures are widely used in data model and SQL Server implementation for real world entities like manager employee relation, organizational structures, regional structures etc. In order to get all the descendants for a node in the hierarchy, we need to recursively query the children of the node, and the children of the children, and so on until we reach the leaf level. In order to achieve this we proposed a new algorithm called "Carry-along Sort" to build a materialized hierarchy path dynamically. However, the current versions of database provides the new feature called Common Table Expression (CTE) in SQL 2005 and CONNECT BY PRIOR in Oracle 9i, which can be used to solve this request. "Carry-along sort" is the "tricky" method and this method can be efficient from I/O standpoint when compare to CTE and Connect by PRIOR while building materialized hierarchy path dynamically. The performance study can be conducted in terms of reducing the space, I/O cost and increasing the response time of the query (speed). The current study is mainly focused on reducing the I/O cost and brief coverage on increasing the swiftness of the query as well. The complete algorithm was tested in SQL Server 2005.