Use the EXPLAIN command to show the decision the optimizer made when executing a command.
The EXPLAIN keyword must be followed by a command, which will output the optimizer selections for the query. Use EXPLAIN to determine if you need to use other optimization methods, such as MANOPT. You can see the differences when different indexes or table orders are specified. For example:
R>EXPLAIN SELECT * FROM ProdView
R> prompt
01/26/2023 01:54 PM
Query 1=* FROM ProdView"
Query 2=SELECT T1.TransDate,T2.TransID,T2.DetailNum,T2.Model,T2.Units,T2.Price,T2.Discount,T2.SalePrice,T2.ExtPrice,T3.CustID,T3.Company,T3.CustAddress,T3.CustCity,T3.CustState,T3.CustZip,T3.CustPhone,T3.ModLevel,T3.LastUpdateDate,T3.LastUpdateTime,T3.LastOrderDate,T3.CustURL,T3.CustEMail,T3.CustStatus,T3.PaymentTerm,T3.CreditLimit,T4.EmpID,T4.EmpFName,T4.EmpLName,T5.ProdName FROM InvoiceHeader T1,InvoiceDetail T2,Customer T3,Employee T4,Product T5 WHERE (((T1.TransID = T2.TransID) AND (T1.CustID = T3.CustID ) AND (T1.EmpId = T4.EmpID) AND (T2.Model = T5.Model)))"
Table Order 1=ProdView
SelectCost=1. (OptimizationTime=0ms)
ProdView Sequential
Table Order 2=InvoiceHeader,Customer.CustID,Employee.EmpID,InvoiceDetail.TransID,Product.Model
SelectCost=1.439 (OptimizationTime=1ms)
InvoiceHeader Sequential
Customer (ColumnName=CustID,Type=P) Random Dup=1. Adj=1.
Employee (ColumnName=EmpID,Type=P) Random Dup=1. Adj=1.
InvoiceDetail (ColumnName=TransID,Type=F) Random Dup=1.525773 Adj=1.
Product (ColumnName=Model,Type=P) Random Dup=1. Adj=1.
The EXPLAIN command shows the optimizer selections for both implicit and explicit SELECTs. In the above, the data from the ProdView view is retrieved sequentially, but the join used to create the data for ProdView uses indexes. The optimizer table order and index use matches that returned by #TABLEORDER.
The EXPLAIN output includes the command file name and next byte offset (location of command within the file) and the current date and time. Commands from the R> Prompt are so noted. The query and table order are listed. The values for "Dup" and "Adj" are the Duplicate Factor and Adjacency Factor values, which are available for review in the Data Designer within the Keys/Indexes area.
About the EXPLAIN command
EXPLAIN lets you easily test query options and see the differences. Every query made by R:BASE sends the optimizer selections to the current output.
The output displays the optimization time in milliseconds (OptimizationTime=30ms). This time is how long it took R:BASE to determine the best table and index order. Each table order and index the optimizer checks is given a value (SelectCost=4.84453). The lowest value or select cost determines which query order is used. There are four types of access methods in the EXPLAIN output that can be used to retrieve the data:
Sequential |
No index was used. The rows are retrieved from the table in the order they were entered. |
Random |
An index access method. |
VeryRandom |
An index access method. |
IndexOnly |
No data was retrieved from file 2, all the data was retrieved from file 3. |
When index access is used (IndexOnly, Random, VeryRandom), the column name or index name and the index type are displayed. For example:
TransMaster (ColumnName=EmpID, Type=F) IndexOnly Dup=3.8 Adj=1
TransMaster (IndexName=DateIndex,Type=I) Random Dup=1.055556 Adj=1
The index types are:
P |
Primary Key |
F |
Foreign Key |
I |
Index |
The EXPLAIN command was introduced to simplify the use of the MICRORIM_EXPLAIN system variable.
Examples:
Example 01 (Display the optimizer results for the TCrossTabView view):
R>EXPLAIN SELECT * FROM TCrossTabView
R> prompt
01/30/2023 11:27 AM
Query 1=* FROM TCrossTabView"
Query 2=SELECT T1.EmpLName,T2.Company,T4.ExtPrice,T5.ProdName FROM Employee T1,Customer T2,InvoiceHeader T3,InvoiceDetail T4,Product T5 WHERE T3.EmpID = T1.EmpID AND T3.CustID = T2.CustID AND T4.TransID = T3.TransID AND T5.Model = T4.Model"
Table Order 1=TCrossTabView
SelectCost=1. (OptimizationTime=0ms)
TCrossTabView Sequential
Table Order 2=InvoiceHeader,Employee.EmpID,Customer.CustID,InvoiceDetail.TransID,Product.Model
SelectCost=1.439 (OptimizationTime=0ms)
InvoiceHeader Sequential
Employee (ColumnName=EmpID,Type=P) Random Dup=1. Adj=1.
Customer (ColumnName=CustID,Type=P) Random Dup=1. Adj=1.
InvoiceDetail (ColumnName=TransID,Type=F) Random Dup=1.525773 Adj=1.
Product (ColumnName=Model,Type=P) Random Dup=1. Adj=1.
Example 02 (Display the optimizer results for the InvoicesMaster view):
R>EXPLAIN SELECT * FROM InvoicesMaster
R> prompt
01/26/2023 02:13 PM
Query 1=* FROM InvoicesMaster"
Query 2=SELECT T1.TransID AS TransID,T1.CustID AS CustID,T1.EmpID AS EmpID,T1.TransDate AS TransDate,T1.BillToCompany AS BillToCompany,(T1.BillToAddress + ', ' + T1.BillToCity + ' ' + T1.BillToState + ' ' + T1.BillToZip) AS BillToAddress,T1.BillToState AS BillToState,T1.BillToZip AS BillToZip,T1.ShipToCompany AS ShipToCompany,(T1.ShipToAddress + ', ' + T1.ShipToCity + ' ' + T1.ShipToState + ' ' + T1.ShipToZip) AS ShipToAddress,T1.ShipToState AS ShipToState,T1.ShipToZip AS ShipToZip,T1.NetAmount AS NetAmount,T1.Freight AS Freight,T1.Tax AS Tax,T1.InvoiceTotal AS InvoiceTotal,IFEQ (T2.DetailNum,1,1,0) AS InvoiceCounter,T2.DetailNum AS DetailNum,T2.Model AS Model,T2.Units AS Units,T2.Price AS Price,T2.Discount AS Discount,T2.SalePrice AS SalePrice,T2.ExtPrice AS ExtPrice,T3.ProdName AS ProdName,T3.ProdDesc AS ProdDesc,T3.ListPrice AS ListPrice,(T4.EmpFName + ' ' + T4.EmpLName) AS EmpName,T4.EmpPhone AS EmpPhone,T4.EmpExt AS EmpExt,T4.EMailAddress AS EMailAddress,T4.EmployeePhoto AS EmployeePhoto FROM InvoiceHea
er T1,InvoiceDetail T2,Product T3,Employee T4 WHERE T1.TransID = T2.TransID AND T2.Model = T3.Model AND T1.EmpID = T4.EmpID ORDER BY T1.BillToCompany,T1.TransID ASC,T2.DetailNum ASC"
Table Order 1=InvoicesMaster
SelectCost=1. (OptimizationTime=0ms)
InvoicesMaster Sequential
Table Order 2=InvoiceHeader,InvoiceDetail.TransID,Product.Model,Employee.EmpID
SelectCost=1.393 (OptimizationTime=0ms)
InvoiceHeader Sequential
InvoiceDetail (ColumnName=TransID,Type=F) Random Dup=1.525773 Adj=1.
Product (ColumnName=Model,Type=P) Random Dup=1. Adj=1.
Employee (ColumnName=EmpID,Type=P) Random Dup=1. Adj=1.
