The SQL-92 Virtual System Table : "
_Sql_Qplan" is in the PUB schema, like other database virtual system tables.
It is specific to each connection and behaves as a temporary table on the SQL-92 server side.
As a result, it can only be accessed by the user who submitted the query to retrieve information about the way the previous query was optimized.
_Sql_Qplan Columns:
- _pnumber - query plan number, in descending order. Has no inherent significance. It merrily groups together all the rows describing a query.
- _ptype - Is > 0 for an application query. _ptype - Is < 0 for an internal, SQL generated query. Some internal queries show as application queries; this is a bug to be fixed in future.
- _dtype - not yet supported. In future, will indicate descriptive information about the plan.
- _Description - contains the actual description of the access plan.
- _dseq - sequence number, ordering the rows describing the access plan for a particular table.
The table "
_Sql_Qplan" exists as if it had been created by the SQL syntax:
create table "_Sql_Qplan" (
"_Pnumber" integer not null, -- plan number.
"_Ptype" integer not null, -- plan type.
"_Dtype" integer not null, -- description type.
"_Description" varchar(255) not null, -- description line.
"_Dseq" integer not null -- description sequence#.
);
The query plan results are output in reverse chronological order from the virtual tables, that is: the most recent first, then the next oldest. Only the most recent 10 queries are ever shown.
The actual Query Plan is a tree, where the query plan description is in "tree form":
- Column references - each column selected is shown at each step of the plan.
- Every step in the query plan is a node in the query tree, reading data from lower level nodes, and outputting data to higher level nodes.
- The root of the tree is shown first
- Indentation represents nesting within the query plan tree
- The basic steps of realizing the query appear as lines in the description reading from the top down:
- SELECT - the usual root of the query plan
- PROJECT - chooses a set of columns/expressions from tables
- JOIN - joins 2 tables. Tells type of join. (PUB.table_name.column_name) = (null) means that the value is coming from another table that is read before this current table at run time for a JOIN query.
- AUG_NESTED_LOOP-JOIN -"augmented nested loop join", which is a join using an index, like the ABL does.
- RESTRICT - applies a WHERE clause predicate to a table
- SORT - sorts a table
- TABLE SCAN - retrieves data from a table along its primary index, else along its default index.
- INDEX SCAN - retrieves dbkeys (and possibly data) from an index. This shows name of index used, and any predicate used to form index brackets. If the index is not bracketed with the constant value, the word FIRST appears right after the index which means the index is being fully scanned for dynamic result set during the run time.
