How to see Execution Plan from SQLDevelopper

I fear I don't understand your question: are you asking why the index is used to access the table? You have an index range scan and a following table access by index rowid. The index can be used because it is a compound index containing at least two columns - so NULL values for PRD.DELETED are included into the index if case_id is NOT NULL.

Can you be more clear with your question?

As per headline you ask, "How to see Execution plan from SQL developer"...but below you say that "I saw Execution Planfrom SQLDeveloper for SQL tuning ..."

bit confused ....

Rather than using a tool-specific execution plan output, I recommend the standardised output of DBMS_XPLAN - either using DBMS_XPLAN.DISPLAY for the output of EXPLAIN PLAN FOR SELECT... or DBMS_XPLAN.DISPLAY_CURSOR for the output of an actual/previous sql statement.

http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_xplan.htm

     INDEX

          Access Predicates

               AND

                    PRD.CASE_ID=:B1   (parameter)

                    PRD.Deleted is NULL

Access predicate -> used as an "access" key in the operation

Filter predicate -> used to filter down the data accessed

The access predicate above suggests you have a composite index on the two columns listed.

> If INDEX scan is usable in one of criteria in WHERE phrase

It's not just a question of whether the index is usable.

Yes the index has to be usable for the optimizer to evaluate it as a possible option but these different options are costed by the optimizer and the least cost should win.

Dom Brooks wrote:

 

Rather than using a tool-specific execution plan output, I recommend the standardised output of DBMS_XPLAN - either using DBMS_XPLAN.DISPLAY for the output of EXPLAIN PLAN FOR SELECT... or DBMS_XPLAN.DISPLAY_CURSOR for the output of an actual/previous sql statement.

http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_xplan.htm

+1.

Did you check your other thread?

GRANT SELECT ON dba_hist_sql_plan

detailed can be read here http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_xplan.htm#CACFJGHG

Using the drill down on the Explain button in the SQL Worksheet toolbar, you can see the same plans that XPLAN would return.

I talk about this here.