Kbase P95263: How to use NOEXECUTE with SQL-92
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
17/04/2009 |
|
Status: Verified
GOAL:
How to use NOEXECUTE with SQL-92
GOAL:
How to view SQL engine optimization of query statement
GOAL:
How to obtain SQL query optimization plan without executing query
FACT(s) (Environment):
Progress 9.1D
OpenEdge 10.x
FIX:
NOEXECUTE is used to obtain optimization query plan without running SQL query.
Progress 9.1D requires 9.1D09 to use NOEXECUTE
1. Create and run SQL QUERY with NOEXECUTE
Example:
select * from pub.customer NOEXECUTE;
2. To view query optimization plan run the following:
select "_Pnumber", substring ("_Description",
1, 80)
from pub."_Sql_Qplan"
where "_Pnumber" = (select max("_Pnumber")
from pub."_Sql_Qplan"
where "_Ptype" > 0);
3. The query plan for the SQL query outlined in step 1 appears as follows:
_Pnumber substring(_Description,1,80)
-------- --------------------------------------------------------------------------------
3 SELECT COMMAND.
3 PROJECT [60] (
3 | PROJECT [58] (
3 | | PUB.CUSTOMER. [0](
3 | | | TABLE SCAN
3 | | )
3 | , PUB.CUSTOMER.CustNum &n.bsp;
3 | , PUB.CUSTOMER.Name
3 | , PUB.CUSTOMER.Address
3 | , PUB.CUSTOMER.Address2
3 | , PUB.CUSTOMER.City
3 | , PUB.CUSTOMER.State
3 | , PUB.CUSTOMER.Country
3 | , PUB.CUSTOMER.Phone
3 | , PUB.CUSTOMER.Contact
3 | , PUB.CUSTOMER.SalesRep
3 | , PUB.CUSTOMER.Comments &nbs.p;
3 | , PUB.CUSTOMER.CreditLimit
3 | , PUB.CUSTOMER.Balance
3 | , PUB.CUSTOMER.Terms
3 | , PUB.CUSTOMER.Discount
3 | , PUB.CUSTOMER.PostalCode
3 | , PUB.CUSTOMER.Fax
3 | , PUB.CUSTOMER.EmailAddress
3 | )
3 , PEXPR1 &.nbsp;
3 , PEXPR2
3 , PEXPR3
3 , PEXPR4
3 , PEXPR5
3 , PEXPR6
3 , PEXPR7
3 , PEXPR8
3 , PEXPR9
3 , PEXPR10 &nb.sp;
3 , PEXPR11
3 , PEXPR12
3 , PEXPR13
3 , PEXPR14
3 , PEXPR15
3 , PEXPR16
3 , PEXPR17
3 , PEXPR18 &n.bsp;
.