Plan table as GTT

Oracle 10 now offers a global temporary table for a plan table. If you're planning on using this for Oracle 9, take care the the table is 'on commit preserve rows' otherwise some strange things might happen. For example

SQL> explain plan into plan_table for
  2  with T1 as
  3  ( select   deptno, avg(sal) c
  4    from emp
  5    group by deptno )
  6  select *
  7  from T1;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------

-------------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     3 |    15 |     4  (50)|
|   1 |  SORT GROUP BY       |             |     3 |    15 |     4  (50)|
|   2 |   TABLE ACCESS FULL  | EMP         |    14 |    70 |     3  (34)|
-------------------------------------------------------------------------

This works because Oracle did not have the actually materialize the with-subquery, it just stuffed into the main query (ie, what an INLINE hint forces if need).


SQL> explain plan into plan_table for
  2  with T1 as
  3  ( select /*+ materialize */  deptno, avg(sal) c
  4    from emp
  5    group by deptno )
  6  select *
  7  from T1;
( select /*+ materialize */  deptno, avg(sal) c
              *
ERROR at line 3:
ORA-00604: error occurred at recursive SQL level 1
ORA-14450: attempt to access a transactional temp table already in use


Note that the hints Materialize and Inline are undocumented because typically they're only used within recursive SQL, not intended for users.