Adaptive SQL Optimizer
The InterSystems IRIS SQL Optimizer leverages table statistics to derive the best query plan for each user-submitted statement and uses an efficient query cache to reuse the generated code. When those statements include parameters, the values submitted at runtime may provide opportunities for faster execution using an alternative query plan. The new Run-Time Plan Choice (RTPC) infrastructure introduced with this release ensures InterSystems IRIS SQL takes advantage of such opportunities efficiently. RTPC scans for the use of outlier values and efficiently estimates the selectivity of range conditions based on more detailed table statistics. This leads to more adaptive query planning and significant savings in execution time and I/O for many real-world datasets. (first in 2021.2)
In addition, InterSystems IRIS now uses block-level sampling rather than full or row-based scanning to gather the table statistics used by the optimizer. This efficient algorithm enables gathering statistics (such as by using the TUNE TABLE command) for even the largest tables with billions of rows within seconds. Also, InterSystems IRIS SQL will now gather table statistics on-the-fly when a table has none to ensure appropriate query plans. (first in 2021.2)
