Problem using create view and explain plan

Question

I want to test the efficiency of this query using explain plan for.

EXPLAIN PLAN FOR
(CREATE VIEW TOPTEAM AS  
    SELECT T1.Team TEAMS, COUNT(*) TIMES 
    FROM TeamsDivision T1
    WHERE (P1.Division = 1 AND
          (2 = (SELECT T2.Division
                FROM TeamsDivision T2
                WHERE (T2.Team = T1.Team AND T2.Season = (T1.Season + 1) )))
    )
    GROUP BY T1.Team     
    ORDER BY TIMES DESC;

SELECT TEAMS, TIMES 
FROM  TOPTEAM
WHERE ROWNUM < 6;

DROP VIEW TOPTEAM);

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

The query works without the explain plan but when I use it I get this errors:

CREATE VIEW TOPTEAM AS
 *
ERROR at line 2:
ORA-00928: missing SELECT keyword


FROM  TOPTEAM
      *
ERROR at line2:
ORA-00942: table or view does not exist

I used explain plan on queries without create view and they worked. I don't know what is causing the error I have tried changing the parenthesis but I get the same errors. The only time it works is when I put explain plan before SELECT TEAMS, TIMES. Thank you!

Answer 1

The documentation of EXPLAIN PLAN says

The EXPLAIN PLAN statement displays execution plans that the optimizer chooses for SELECT, UPDATE, INSERT, and DELETE statements.

Source: SQL Tuning Guide

You can probably fish out the execution plan of the CREATE VIEW statement, but it will be uninteresting. Only the syntax of the query is checked, your access to the tables, and the query will be stored in USER_VIEWS.

I guess what you want to know is the performance of the query itself. You'd do this with

EXPLAIN PLAN FOR
SELECT TEAMS, TIMES 
  FROM TOPTEAM
 WHERE ROWNUM < 6;