Kbase 21676: The structure of the SQL-92 virtual system table _Sql_Qplan
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
10/20/2009 |
|
Status: Verified
GOAL:
Structure of the SQL-92 VST "_Sql_Qplan".
GOAL:
What is _Sql_Qplan?
GOAL:
How to interpret output from querying _Sql_Qplan?
GOAL:
How to read query plan?
FACT(s) (Environment):
All Supported Operating Systems
OpenEdge 10.x
FIX:
The SQL-92 virtual system table named "_Sql_Qplan" is in the PUB schema, just 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.
It has these 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. 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 plan.
_Description - contains actual description of 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 plans 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.
The query plan description is in "tree form", where indentation represents nesting within the query plan tree, and the root of the tree is shown first. The basic steps of realizing the query appear as lines in the description reading from 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.
AUG_NESTED_LOOP-JOIN means "augmented nested loop join", which is a join using an index, like the 4GL 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.
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.
(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 join query.
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.