"Relation Analysis" in the group "Tools" of the "General" tab of the ribbon tests the relations between the tables of the connection on its data. It tells which columns refer to which tables, and for every relation whether every filled value finds its row. The same analysis can be started without a window with the package `dbms_relation_analysis`. Available from release 27.1, in Invantive Query Tool on Windows and in the multi-platform version. It is not part of [[Invantive Cloud/Invantive Cloud|Invantive Cloud]]. ## What It Finds A relation is a column of one table whose filled values refer to another table, such as `InvoiceLines.invoice_id` to `Invoices.id`. The analysis knows five kinds: - "Key, Enforced": the platform keeps the relation true itself, as a database does with a foreign key. It is tested only when asked. - "Key, Stated by the Vendor": the driver declares the relation from the specification or the documentation of the vendor. - "Key, From the Nesting": the driver declares that a list table belongs to the table which holds the list. - "Textual": the relation holds under a condition which is written in words. It is counted, never judged: the report says how many values are found in which referenced table and in none. - "Candidate": the names of columns and tables suggest the relation, but nobody states it. A candidate becomes a key when a statement of the vendor with a link and a quote is added. Every key is tested on every filled value, never on a sample. ## The Window The window opens next to the editor and does not block it. It shows the connection it was opened for. At the top is a toolbar and, under it, the four steps: - "Count Rows" asks the platform for the number of rows of the tables of the relations where the platform can answer that without reading them. - "Start Reading" and "Stop" start and stop the reading of the step "Read". "Stop" keeps what was read. - "Plan Again" works out the plan again after a setting or a partition changed. - "Open Analysis" and "Save Analysis" open and save the analysis file, which the section "The Analysis File" describes. The group "Reading" on "Plan" holds what concerns the reading: - "HTTP Disk Cache" with "Keep Every Page Read", which keeps every page the analysis reads in the HTTP disk cache of the connection, so that a read which stops continues without paying for the pages again. The statement the checkbox stands for, such as `set use-http-disk-cache@grp true`, is shown next to it. - "Maximum Age", the number of days a page stays in the cache, 7 by default, with its statement `set http-disk-cache-max-age-sec@grp 604800`. - "Folder", the folder of the HTTP disk cache, or the default folder. - "Limits of the Platform", the calls per minute, hour and day the platform reports, or "None reported yet". - "Share of a Minute", "Share of an Hour" and "Share of a Day", the part of those limits the analysis may use, typed as a percentage (`75` or `75 %`) or as a fraction (`0,75`). A value outside 0 to 100 is ignored and the share keeps its value. - "Size of the Tables", whether the sizes are known without reading and how many calls counting them takes. A driver without an HTTP disk cache shows that in the group and keeps no page. Tiles above the steps show the numbers that matter at that moment: tables and lists, rows to read, calls, clock time and relations on "Plan", and the relations still open, not testable and the filled values tested once something was read. ## The Four Steps - "Plan" sizes every table of the relations per partition and works out the number of calls and the clock time from the allowances of the platform and the share the analysis may use. A table whose size is not known without reading it is shown as unknown and the time as "At Least". The choice "Show" instead of "Test" shows the relations a driver declares without reading anything. The group "Read" holds the shares, "Test Keys the Platform Enforces", "Only the Relations the Driver Declares" and "Stop a Relation at the First Exception". - "Read" reads the tables, the cheapest first, and tests every page as it arrives. At the share of a minute or an hour it waits for the next window, and at the share of a day it stops. Opening the analysis again continues from the HTTP disk cache, so no page is paid for twice. A list with one row per partition shows a bar with the share of its tables that is complete, and a list with one row per table shows its rows, pages, calls, a bar with how far it is, the pages "In Cache" and whether it is complete, not needed any more or failed. "In Cache" counts the pages the analysis read while the HTTP disk cache was on: they stay in the cache until they exceed the maximum age. The log under it shows what happened. - "Sources" lists the relations of the five kinds and takes a statement of the vendor: the tables and columns, the link to the documentation of the vendor and a quote from it. - "Result" gives a verdict per relation with its evidence, and exports it. "Export to Excel", "Export as Markdown" and "Export as JSON" write a file; "Copy as JSON" puts the evidence of the selected relation, or the whole analysis when no relation is selected, on the clipboard, for pasting into a ticket or an assistant. There is no export as C#. ## Primary Keys For every table which was read completely the analysis tests the primary key the driver declares: does every row have a value and does no value occur twice. The report says "unique", "not unique" (a value twice on one page, which is certainly two rows), "doubtful" (a value on two pages only, which may be one row which moved while the table was read) or why the key could not be tested: the driver declares no key, or the table was not read completely. The test costs no call, since the columns of the key are read anyway. "Unique" means unique in the data read, which is no constraint of the platform, and holds within one partition. ## Tables Which May Be the Same Data Some platforms offer the same data in more than one table, such as a table and its bulk or sync variant. The analysis marks two tables as alike when their names differ only by the suffix Bulk, Sync or Read, when they have the same key and at least four fifths of their columns in common, or when the driver declares one as equivalent for reading the other. When both were read completely, it compares their keys and reports "SameKeys", "FirstInSecond", "SecondInFirst", "Overlapping" or "NoKeysInCommon". Two tables read through the same table are not compared, since they hold the same rows by construction, and a table which no relation needed is not read for this. The same keys do not prove the same columns or values: only the keys were read. ## Partitions A connection with partitions, such as the divisions of Exact Online, is read per partition, since each has an allowance of its own. The partitions are chosen on "Plan"; when none is chosen, "Plan Again" and "Start Reading" say so and do nothing, since no partition chosen is nothing to analyse and not all partitions. "Read" shows how far each one is, and "Result" says which partitions the verdict is about. A relation which holds in one partition and not in another is contradicted for the partitions analysed. A connection without partitions shows one row, "(none)". ## A Cheaper Table for Reading Where a platform offers a table with the same rows as another but cheaper to read, the analysis reads that one instead, if it has the columns the relation needs. For Exact Online these are the Bulk and Sync variants of the tables. The column "Read Through" on "Plan" and "Read" names the table that was read. ## The Verdicts - "Confirmed": every filled value has a referenced row, the carried attributes are equal and the referenced key is unique. - "Holds, Candidate": the same for a candidate, which stays a candidate. - "Contradicted": a value has no referenced row, or another attribute, after the platform was asked again live. The test of that relation ends at the first confirmed exception, and the report says so, because it then holds the counts up to that exception and not all of them. Tables which only such relations need are not read further. - "Counted": a textual relation. - "Not Testable": no filled value, or a referenced table which answers no rows for the account used. - "Open": one of the two tables has not been read completely yet. ## A Table the Platform Does Not Answer A table can be refused, for instance because the account has no right to it or the module is not licensed. The state of that table is "Failed", the relations which need it are "Not Testable" with the answer of the platform as the reason, and the reading goes on with the other tables. When the platform does not answer five tables in a row, the partition stops with the last answer, since the next tables would fail the same way. ## Not Too Much of the Allowance The platform allowance is shared with everything else the customer runs. The analysis therefore uses 75 % of a minute or an hour and 60 % of a day by default, and looks at the remaining number the platform reports, so calls of others count as used. When a platform reports no remaining number, the analysis counts its own calls. Each share can be changed. A page which was answered from the HTTP disk cache costs no call. The analysis switches the HTTP disk cache of the connection on, with a maximum age of 7 days, when it starts reading; the setting stays after the analysis. Clearing "Keep Every Page Read" switches the HTTP disk cache of the connection off, and then no page is kept. A platform which does not state how many rows a page holds is planned at 100 rows per call, and the plan says that this is an estimate. ## The Analysis File "Save Analysis" writes the analysis of the connection to a JSON file, and "Open Analysis" reads it back: the statements added, the partitions chosen, the shares, when each table was completed per partition and the verdicts. Opening it with the same connection continues the analysis: the shares, the partitions, the switches and the HTTP disk cache settings in the file are shown in the window, and the window keeps them. The file holds no value of a row other than an id. ## The Trace With the trace on, the analysis writes what it does to it: message code `itgenrla022` holds the settings at the plan and at the start of the reading (the shares, the partitions, the switches, the HTTP disk cache and how many relations are selected), and message code `itgenrla021` holds every step of the journal: the tables started and completed, the pages, the live checks, the waits for the next minute or hour, the tables which were not answered and the verdicts. The statements the analysis sends, which select only the columns it needs, are in the trace like any other statement. ## What the Report Holds Identifiers and counts only: never a value of a row other than an id. The report names the partitions analysed and when each table was read. ## Without a Window ```sql begin dbms_relation_analysis.start(test_enforced_keys => true); dbms_output.put_line(dbms_relation_analysis.plan()); dbms_output.put_line(dbms_relation_analysis.read()); dbms_output.put_line(dbms_relation_analysis.export('markdown')); end; ``` The package has these members: - `start` starts a new analysis of the connection, with the data container alias, the partitions, the three shares and the four switches as optional parameters. Nothing is read. - `show` returns the declared relations as JSON without reading. - `plan` returns the sizes, calls and clock time as JSON. - `read` reads the tables and tests the relations. - `analyse` returns the analysis as it stands, as JSON. - `add_statement` adds a relation the driver does not declare, with a link and a quote. - `export` returns the report as `json`, `markdown` or `ndjson` (the journal of the reading), and `export_excel` returns an Excel workbook. The report holds the primary key verdict per table in the "Tables" sheet and the section "Tables" of the Markdown, and the comparison of alike tables in the sheet "Synonyms" and the section "Tables Which May Be the Same Data". The views `SystemRelationAnalysisTables@DataDictionary` and `SystemRelationAnalysisRelations@DataDictionary` show the same analysis as the window. ## What It Does Not Do - It does not load pages ahead of the test, and it does not change the way queries use relations: the result is a report, not a setting. - It has no export as C#. - It does not look for keys which the driver does not declare, and the window does not show the keys or the alike tables yet: they are in the report and in the analysis file. - [[Invantive MCP Server/Invantive MCP Server|Invantive MCP Server]] has no tool to start it, since that server only reads. The two views can be read with its `read_records` tool.