Kbase 20007: How to Find The Index Used in a SELECT Statement in SQL-92
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
12/26/2007 |
|
Status: Verified
GOAL:
How to Find The Index Used in a SELECT Statement in SQL-92
GOAL:
How to get the SQL-92 query plan
FACT(s) (Environment):
All Supported Operating Systems
Progress 9.1x
OpenEdge 10.x
FIX:
Sometimes a SQL-92 SELECT statement using a WHERE clause with the key fields referenced seems to be pulling all the records in and not using the proper indexes. In that case, it's helpful to find out exactly what index was used in the query to compare it with what index should have been used.
Starting with Progress 9.1A, SQL-92 has the capability to show the query plans it has used to actually execute a SQL query. The SQL-92 virtual system table queried is named "_Sql_Qplan" and is in the PUB schema, as are database virtual system tables.
Follow these steps:
1) Do your query. For example:
select CustNum, Name, SalesRep
from pub.customer
where SalesRep = 'HXM' and custnum between 1 and 50;
This displays the following results:
CustNum Name SalesRep
---------- ------------------------- --------
1 Lift Tours HXM
3 Hoops HXM
15 Hoopla Basketball HXM
19 Buffalo Shuffleboard HXM
21 Pedal Power Cycles HXM
30 Fast Flipper Pinball HXM
44 Ship Shape Yachting HXM
47 Batter Up Baseball HXM
50 Chip's Poker HXM
2) Run the following query:
select "_Pnumber", substring("_Description", 1, 80)
from pub."_Sql_Qplan";
This will give you an output similar to the following:
_Pnumber substring(_Description,1,80)
-------- ----------------------------------------------------------
4 SELECT COMMAND.
4 PROJECT [26] (
4 | RESTRICT [12] (
4 | | PROJECT [24] (
4 | | | PUB.CUSTOMER. [3](
4 | | | | INDEX SCAN OF (
4 | | | | | SalesRep,
4 | | | | | | (PUB.CUSTOMER.SalesRep) = (HXM))
4 | | | )
4 | | , PUB.CUSTOMER.CustNum
4 | | , PUB.CUSTOMER.Name
4 | | , PUB.CUSTOMER.SalesRep
4 | | )
4 |
4 | | (PEXPR1) between (1,50)
4 | | Evaluation callback list(
4 | | | col id# 2 @ 00e142b4
4 | | | [can terminate scan]
4 | | )
4 | )
4 , PEXPR1
4 , PEXPR2
4 , PEXPR3
The line below INDEX SCAN OF gives the index name used in the query, which in this case is SalesRep.
In 9.1B and later you can run the more specific query:
select "_Pnumber", substring("_Description",1,80)
from pub."_Sql_Qplan"
where "_Pnumber" = (select max( "_Pnumber" )
from pub."_Sql_Qplan"
where "_Ptype" > 0 );
which will show the query plan for the most recent query on user tables.