Can I customize the IRIS SQL query planner to optimize recursive CTE execution?

Based on the returned material, no, there is no indication that you can customize or extend the IRIS SQL query planner itself for recursive CTE execution. The documents describe CTEs as being handled by the built-in cost-based optimizer as part of a unified execution plan, and they emphasize analyzing query plans, indexing, and statistics rather than altering optimizer behavior. [1][2][3][4]

For recursive CTEs specifically, the strongest relevant statement is that InterSystems IRIS CTEs do not currently support recursion. Because of that, optimizing recursive CTE execution in IRIS is not described as a supported capability. [1]

For hierarchical or iterative workloads, the returned documents suggest different approaches instead:

• self-joins
• temporary staging tables
• procedural orchestration with ObjectScript [1]

If you need to influence performance, the materials point to practical tuning measures rather than optimizer extensibility:

• inspect the SQL query plan
• use the enhanced query plan messages available in newer releases
• index join and filtering columns
• maintain accurate statistics
• avoid unnecessary row expansion
• consider explicit materialization with temporary tables when intermediate results are large or reused multiple times [1][2][3]

Note: to be able to run this example and observe data, you have to use an SQL client such as DBeaver. If you launch it through the IRIS Portal, the process will change on each execution, and you will not be able to see the temporary data. [1]

Sources: