EXPLAIN Output
This section deals with the information an execution plan provides. An execution plan for a join is shown in a result table as follows:
Example:
EXPLAIN
SELECT customer.cno, customer.title, customer.name, customer.zip, city.name, city.state, customer.address
FROM customer, city
WHERE customer.zip = city.zip
|
Additional possible entries in column COLUMN_OR_INDEX respectivly STRATEGY:
OWNER
The OWNER of the tables of the SQL Statement are listed in this column.
back to top
TABLENAME
In the SQL statement involved tables.
back to top
COLUMN_OR_INDEX
In the SQL statement involved table columns and indexes.
back to top
STRATEGY
The name of the search strategy that the Optimizer has chosen is listed. You will find some additional information in the column STRATEGY such as whether or not the result set is generated or whether temporary result sets are generated.
back to top
PAGECOUNT
Column PAGECOUNT contains the evaluated table or index sizes.
For index strategies, the evaluated index size is displayed.
The evaluated table size is displayed for key strategies. The only exception is: Key Equal Strategy. Since this is already the optimal strategy, there is no evaluation here, but the table size is displayed according to the statistics.
For joins the size of the involved tables from the statistics is displayed.
back to top
(USED KEY COLUMN)
If the primary key is used for a strategy, this addition lists all primary key columns that were used to restrict the search area. Columns that are not listed are not used to restrict the search area.
back to top
(USED INDEX COLUMN)
If an index is used for a strategy, this addition lists all index columns that were used to restrict the search area. Columns that are not listed are not used to restrict the search area.
back to top
(ONLY INDEX ACCESSED)
All required information can be read from the index tree. The base table is not accessed. The SQL Statement is processed by the INDEX_ONLY Strategy.
back to top
ADDL. QUALIFICATION ON INDEX
This information states that the qualifications are checked in the index already, without limiting the search area further. Which columns are used to do so is not output.
back to top
TEMPORARY INDEX CREATED
Several index entries are selected. To optimize access to the base table, a temporary index (merged list) is generated from the different primary key lists. This index is sorted by primary key columns. There are no write costs associated with setting up this index.
back to top
DISTINCT OPTIMIZATION
The specified index is used for eliminating duplicates. No result set has to be generated.
back to top
NO TEMPORARY RESULTS CREATED
Only output for join. No temporary result is created. If this information is missing, a temporary result is created.
back to top
TABLE TEMPORARY SORTED
If there is no suitable transition to the next table for the join, (in MaxDB Version 7.6 always, in MaxDB Version 7.5. for parameter JOIN_OPERATOR_IMPLEMENTATION=IMPROVED) this is used to generate a temporary table, which is sorted by the join condition. This ensures fast access to the table. But it results in additional cost for generating the table. When there is the additional information: (USED KEY COLUMN) The temporary result set is sorted by these columns.
back to top
TABLE HASHED
A hash join is used.
back to top
RESULT IS COPIED
A result set is generated.
back to top
RESULT IS NOT COPIED
No result set is generated.
back to top
COSTVALUE IS
Here ise the estimated number of pages listed which has to be read/written to get the result of this SQL statement. This column can be used as an indicator for a costly SQL statement.
The output COSTVALUE is not necessarily comparable across different MaxDB versions. The function for calculating the costs is constantly revised. This might have the effect that different values are calculated for the same SQL statement. But within the same MaxDB version the COSTVALUE is suitable for determining the cheapest strategy.
back to top
