SQL Macros was initially introduced in 19C and have been continuing its way to new releases of Oracle and gaining some attention. They are coming for great usage for analysis and easy data transformation especially for Data Analyst/Developers.
Macros are way for encapsulation of complicated logic within the SQL. The “macro” can then be used within the SQL statements.
SQL Macros can be classified into 2 categories:
- Scalar
- Table
SCALAR can be used when performing selections or in where/having/group conditions.
TABLE expressions are used in “FROM” clause.
Why Macros?
From the context/definition, one can argue why not just use packages, PL/SQL logic etc, which are already there.
The simple answer to that is Macros on multiple instances/conditions have been found to be faster than PL/SQL codes especially when we have loops in our logics. This is because of Context switching which happens in case of complex logics but macros on the other side are more optimizer friendly and allow transformation of original code for better execution.
Basically, Macros are simpler and due to friendly nature with optimizer they are faster*.
So, should we use SQLM instead of PL/SQL?
We can transform some of the codes after performance analysis. There can be instances where we don’t see lot of benefits or PL/SQL performs better. So, it would totally depend on situations and tests can be performed for validations.
