Long text plan

The long text plan provides a little more information than the short text plan, and provides information in a way that is easy to print and view without scrolling.

In the following example, the first line of the long text plan is Plan[ Total Cost Estimate: 6.46e-005 ] The word Plan indicates the start of a query block. The Total Cost Estimate is the optimizer estimated time, in milliseconds, for the execution of the plan. The Estimated Cache Pages is the estimated current cache size available for processing the statement.

The plan indicates that the results are sorted, and that a Nested Loops Join is used. On the same line as the join operator, there is either the word TRUE or the residual search condition and its selectivity estimate (which is evaluated for all the rows produced by the join operator). The IndexScan lines indicate that the Customers and SalesOrders tables are accessed via indexes CustomersKey and FK_CustomerID_ID respectively.

SELECT PLAN ('SELECT GivenName, Surname, OrderDate, Region, Country
FROM Customers JOIN SalesOrders ON ( SalesOrders.CustomerID = Customers.ID )
WHERE CustomerID < 100 AND ( Region LIKE ''Eastern''
      OR Country LIKE ''Canada'' )
ORDER BY OrderDate');


( Plan [ Total Cost Estimate: 6.46e-005, Costed Best Plans: 1, Costed Plans: 10, Optimization Time: 0.0011462, 
Estimated Cache Pages: 348 ] 
  ( WorkTable 
    ( Sort 
      ( NestedLoopsJoin
        ( IndexScan Customers CustomersKey[ Customers.ID < 100 : 0.0001% Index | Bounded ] )
        ( IndexScan SalesOrders FK_CustomerID_ID[ Customers.ID = SalesOrders.CustomerID : 0.79365% Statistics ]
          [  ( SalesOrders.CustomerID < 100 : 0.0001% Index | Bounded )  
          AND  ( ( ((Customers.Country LIKE 'Canada' : 100% Computed) 
          AND (Customers.Country = 'Canada' : 5% Guess)) 
          OR ((SalesOrders.Region LIKE 'Eastern' : 100% Computed) 
          AND (SalesOrders.Region = 'Eastern' : 5% Guess)) ) : 100% Guess )  ] )
      )
    )
  )
)

For more information about the abbreviations used in a plan, see Execution plan abbreviations.