The system variable MICRORIM_EXPLAIN shows the decision the optimizer made when executing a particular command. Use this variable 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. The optimizer results are placed in the file EXPLAIN.DAT. As with the other variables, MICRORIM_EXPLAIN is initiated by using the SET VAR command. DEBUG must be ON to use MICRORIM_EXPLAIN. For example:
SET DEBUG ON
SET VAR MICRORIM_EXPLAIN = 33
SELECT * FROM prodview
RBEDIT EXPLAIN.DAT
SelectCost=1 (OptimizationTime=0ms)
ProdView Sequential
SelectCost=1.429222 (OptimizationTime=0ms)
Product Sequential
InvoiceDetail (ColumnName=Model,Type=F) Random Dup=18 Adj=0.9513889
InvoiceHeader (ColumnName=TransID,Type=P) Random Dup=1 Adj=1
Customer (ColumnName=CustID,Type=P) Random Dup=1 Adj=1
Employee (ColumnName=EmpID,Type=P) Random Dup=1 Adj=1
MICRORIM_EXPLAIN shows the optimizer selections for both implicit and explicit selects. In the above command, the data from the view, prodview, 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 values for "Dup" and "Adj" are from the SYS_DUP_FACTOR and SYS_ADJ_FACTOR columns in the SYS_INDEXES table.
The available options for MICRORIM_EXPLAIN are:
1 |
Output the table order. |
2 |
Force optimization of joins over five tables. Seven table joins take about 5 seconds to optimize, an eight table join takes approximately 8*5 second to optimize etc. With joins over five tables, the optimizer does not try all possible combinations but picks an order based on the "best" indexes. This option is ignored if MICRORIM_FULLOPT is set. |
4 |
Output the command file name and next byte offset (location of command within the file). Commands from the R> Prompt are so noted. |
8 |
Output the current date and time. |
32 |
Display the sort technique used. |
To use multiple options, add the options numbers together and set the variable MICRORIM_EXPLAIN to the result. For example:
SET VAR MICRORIM_EXPLAIN = (1 + 4 + 8)
SET VAR MICRORIM_EXPLAIN = 13
SET VAR MICRORIM_EXPLAIN = (13 + 32)
Output specified by each of the options is put into the file EXPLAIN.DAT. EXPLAIN.DAT is an ASCII file. You can write comments to the file EXPLAIN.DAT. To put information about the command and the time it took to execute in the file, enter these commands after the command is executed:
OUTPUT EXPLAIN.DAT APPEND
WRITE 'this is the command ' , .vTime
WRITE ' '
OUTPUT SCREEN
This places the execution time and explanation of the command in the file with the optimization selection. It lets you easily test options and see the differences. Delete the file EXPLAIN.DAT to start over. When MICRORIM_EXPLAIN is set, every query made by R:BASE sends output to the file EXPLAIN.DAT. For example, when you use a form to edit data, or a report to print data, output is sent to the file EXPLAIN.DAT because the data for the form or report is retrieved via an internal SELECT command.
The output displays the optimization time in milliseconds (OptimizationTime=930ms). 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=9.796483). The lowest value or select cost determines which query order is used. There are four types of access methods in MICRORIM_EXPLAIN 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 |
In addition, the terms "PreSort" and "SortMerge" are used. In multi-table joins, a small table may be placed entirely in memory and sorted there-the table is pre-sorted. Lookups are then done from the pre-sorted table in memory to the other tables in the join. PreSort is used when the sort column(s) is in only one table. The sort column(s) is not a linking column. When this method is used, PreSort shows in the EXPLAIN.DAT output. SortMerge is used only if PreSort is also used. The SortMerge method is most efficient if the tables are not indexed. When PreSort and SortMerge are indicated, you also see SortRemoved. The sort has already been done, and is not repeated again for the final output. Sorting the final output is not included in the SelectCost. For example:
SelectCost=O.3035209 (OptimizationTime=Oms) SortRemoved
Prodlnfo Sequential PreSort (DATA_ONLY)
Customer (ColumnName=CustID,Type=I) Random Dup=1 Adj=1
Products (ColumnName=ProdID,Type=I) Random Dup=1 Adj=1
The 32-bit option of MICRORIM_EXPLAIN can be used to display the sort strategy R:BASE used for the query. For example:
SortStrategy = DB_TAG (internal=1)
SortStrategy = DATA_ONLY (internal=2)
The possible sort strategy options are:
DB_TAG (Internal=1) |
The sort is done using the rowid value. Only the columns specified in the ORDER BY are used to sort the data. Lookups are then done from the ordered list of rowid values to retrieve the rest of the data for display. When there are many rows of data, or many columns to display, this is the type of sort used. |
DATA_ONLY (Internal=2) |
The sort is done using all of the data to be displayed. An entire row of data is sorted together. This type of sort is used when there is a small number of rows or only a few columns to display. |
FILE_TAG (Internal=3) |
The sort is done to a temporary file. Multi-table queries involving NOTE fields and sorts on views usually use this type of sort. |
DB_TAG_PRESORT (Internal=4) |
The sort is done using the rowid of the column(s) specified in the ORDER BY. The sort columns must be from just one table in a multi-table SELECT. The table is presorted, then lookups are done to retrieve the rest of the data. |
FILE_TAG_PRESORT (Internal=5) |
The sort is done to a temporary file. The table is presorted, then lookups are done to retrieve the rest of the data. |
