The Unity @xplain feature provides information about an executed query.
The output of a SQL statement prefixed with @xplain
returns the following result sets.
The output of a query prefixed with @xplain(#) returns only the specified result
set.
Resultant Query / Rewrite Conditions
The resultant query result set provides the query after all rewrite rules have been applied.
*** Query completed. One row found. One column returned. *** Total elapsed time was 1 second. Resultant Query ------------------------------- INS t0 SEL TOP 1 * FROM v0;
| Column | Details | Possible Values |
|---|---|---|
| Resultant Query | The query processed by Unity once all rewrite rules have been applied. | The query as processed by Unity |
Parsing Details
The parsing details result set provides the overall query details from the Unity parser.
*** Query completed. One row found. 4 columns returned. Parse Status Query Class Primary Table Query Type ------------ ----------- ------------- ---------- successful Insert - Write -
| Column | Details | Possible Values |
|---|---|---|
| Parse Status | Indicates whether the parsing was successful | Successful |
| Query Class | Displays the statement type of the query | Any valid statement type |
| Primary Table | Name of the target table for single-table queries | Name of the primary table, or "-" if unknown |
| Query Type | Identify whether the query is a read or write operation | read, write |
Unflattened Object Details
The unflattened object details result set contains the list of objects being directly referenced by the query.
*** Query completed. 2 rows found. 5 columns returned. Object Type Access Dictionary ID Name Full Name ----------- ------ ------------- ---- --------- view read 1003 v0 db0.v0 table write 1001 t0 db0.t0
| Column | Details | Possible Values |
|---|---|---|
| Object Type | The type of object | table, view, macro, function, procedure, trigger, reference, UDT, other |
| Access | The type of access | read, write, call |
| Dictionary ID | The Unity dictionary ID for the object | Valid dictionary ID |
| Name | Unqualified object name | Name of object |
| Full Name | Fully qualified object name | Name of object, including database name |
Flattened Object Details
The flattened object details result set contains the list of object and tables being accessed by the query once all views/macros/procedures are expanded.
*** Query completed. 3 rows found. 4 columns returned.
Table ID Name Full Name Access
----------- ---- --------- ------
1002 t1 db0.t1 read
1003 v0 db0.v0 read
1001 t0 db0.t0 write
| Column | Details | Possible Values |
|---|---|---|
| Table ID | The Unity dictionary ID for the object | Valid dictionary ID |
| Name | Unqualified object name | Name of object |
| Full Name | Fully qualified object name | Name of object, including database name |
| Access | The type of access | read, write |
Lock List
The lock list result set contains the list and type of locks obtained by Unity for the query.
*** Query completed. 3 rows found. 6 columns returned.
Lock ID Full Name Type Scope Level Row Hash
----------- --------- ----- ---------------- ----- ----------------
1001 db0.t0 write transaction table -
1002 db0.t1 read transaction table -
1003 db0.v0 read transaction table -
| Column | Details | Possible Values |
|---|---|---|
| Lock ID | The Unity dictionary ID for the object | Valid dictionary ID |
| Full Name | Fully qualified object name | Name of object, including database name |
| Type | The lock type | access, read, write, write escalate, exclusive, exclusive escalate |
| Scope | The lock scope | transaction |
| Level | The lock level | table, row |
| Row Hash | For row lock, the corresponding row hash for the lock. | Row hash value, or "-" for table lock |
Replication Type
The replication type result set provides details on whether a query will be SQL replicated or CDM replicated.
*** Query completed. One row found. One column returned. Replication Type --------------------------------------------------------------------------------------- Write request CDM replicated because query uses a TOP clause in a write query
| Column | Details | Possible Values |
|---|---|---|
| Replication Type | Provides details on whether the query is a read/write/DDL query, whether it will be SQL or CDM replicated, and details for the replication choice | Text detailing the replication type |