Enhancing Analytical Select Statements Using Reference Aliases

Abstract

Data analytics is an inseparable part of the current information systems. Various tools can provide the analysis and produce results in any graphical form, enclosed by the complex filtering. Behind the scenes is a data layer and methods for accessing, manipulating, and processing data. SQL language and databases can serve that. This paper deals with data processing and performance optimization by focusing on function processing and reference. It points to the existing syntax and statement execution steps but provides various enhancements and performance optimization. Existing feature management solutions include result caching, function-based indexes, virtual columns, materialized views, or optimization of the functions to be directly applicable in SQL or PL/SQL limiting context switches. Oracle Database 23c introduced various performance enhancements and a new approach to column and expression aliases. Our proposed solution focuses on identifying and extracting aliases, storing the references in the memory and database layer, optimizing the transfer between them by swapping, as well as checkpointing and function call migrations. It provides a robust layer and complex architecture enclosing the management by the transactions. Each layer is critically discussed by pointing to the performance, structural advantages, and limitations. Complexly, our proposed architecture brings significant performance benefits for complex analytical queries but can also be applied in online transaction processing.

Keywords

• Analytical databases
• column aliases
• function references
• indexing
• performance