Reading Execution Plans:

Post date: Apr 10, 2020 8:20:28 AM

The following query displays the execution plans:

SELECT PLAN_TABLE_OUTPUT   FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, 'statement_id','BASIC'));

EXPLAIN PLAN for Statement ID ex_plan1 

The following plan shows execution of a SELECT statement. The table employees is accessed using a full table scan. Every row in the table employees is accessed, and the WHERE clause criteria is evaluated for every row.

EXPLAIN PLAN    SET statement_id = 'ex_plan1' FOR   SELECT phone_number   FROM   employees  WHERE  phone_number LIKE '650%'; --------------------------------------- | Id  | Operation         | Name      | --------------------------------------- |   0 | SELECT STATEMENT  |           | |   1 |  TABLE ACCESS FULL| EMPLOYEES | ---------------------------------------

Example 7-2 EXPLAIN PLAN for Statement ID ex_plan2

This following plan shows the execution of a SELECT statement. In this example, the database range scans the EMP_NAME_IX index to evaluate the WHERE clause criteria.

EXPLAIN PLAN    SET statement_id = 'ex_plan2' FOR   SELECT last_name   FROM   employees  WHERE  last_name LIKE 'Pe%';  SELECT PLAN_TABLE_OUTPUT    FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, 'ex_plan2','BASIC')); ---------------------------------------- | Id  | Operation        | Name        | ---------------------------------------- |   0 | SELECT STATEMENT |             | |   1 |  INDEX RANGE SCAN| EMP_NAME_IX | ----------------------------------------

Assumptions