SQL Data Lens is highly optimized for the unique features of InterSystems IRIS and InterSystems Caché databases, making it an ideal choice for developers, administrators, and data analysts working with these platform.
The InterSystems IRIS data platform provides a single, integrated data dictionary that can be accessed using one of several data models. Within InterSystems IRIS, data can be modelled and stored as tables, objects, multidimensional arrays or documents. All data in an InterSystems IRIS database is stored in efficient, tree-based, sparse multidimensional arrays called globals. InterSystems IRIS globals provide very fast, flexible storage and retrieval. Globals underpin the InterSystems IRIS object and SQL interfaces, which easily handle dynamic data types such as XML or JSON. SQL DATA LENS is optimised for InterSystems IRIS (Caché) databases and support all of this, helping you to work closely and easily on this data platform.
So what does it mean in detail? Here are some examples:
The Table Viewer shows table statistics information. InterSystems IRIS\Caché periodically examines the data in tables and collects statistics through an operation called Tune Table. It displays the number of rows in the table, also known as the extentsize, for each table (see marker 1 on the screenshot). Additionally, it provides information about table and index sizes, which is useful for tuning SQL performance.
You can also find information such as the exact class name within the InterSystems IRIS multi-model database (see marker 2 on the screenshot).
You can find information called selectivity for each column (see marker 2 on the screenshot). Each column (property) in an SQL table (class) has an associated selectivity value. The selectivity value indicates the percentage of rows within a table that a query would return when looking for a typical column value. Calculate the selectivity as 1/D, where D represents the number of DISTINCT values for the field.
On “IRIS Column Info” – Tab (see marker 1 on the screenshot) are much more information about the statistics, like outlier_selectivity, outlier_value, average_field_size and more (see marker 2 on the screenshot):
SQL DATA LENS is optimised for InterSystems IRIS databases and helps you get all the information you need to tune SQL queries effectively. But you can find out more about the internal storage structure of the InterSystems IRIS platform.
On “IRIS Indexes” – Tab (see marker 1 on the screenshot) are more information about internal global names, structure and sizes:
On “IRIS Storage” – Tab (see marker 1 on the screenshot) are more information about internal global names, structure and sizes:
You can view directly into the internal storage system of InterSystems IRIS (Caché) using the Globals Viewer tool.
InterSystems IRIS (Caché) use an optimised binary representation to store data elements ($LISTBUILD functions). SQL DATA LENS can easily display this information
SQL Data Lens has been optimized for InterSystems IRIS and Caché databases, enhancing efficiency for developers, administrators, and data analysts. The InterSystems IRIS platform’s unique features, such as tree-based, sparse multidimensional arrays (globals), are leveraged for fast and flexible data storage and retrieval. SQL Data Lens supports various data models and provides tools like Table Viewer and Globals Viewer to display detailed statistics and internal storage structures, aiding in effective SQL query tuning and database management.