EXPLAIN PLAN on DDLs

Update May 2009: Inspired by Jonathan Lewis' post about index sizes I've revisited this and added some more cases that covers Jonathan's points and some more experiments regarding 11g's virtual columns and bitmap indexes.

Although it's mentioned in the official documentation of the EXPLAIN PLAN command, I believe it's a not so well known fact that you can EXPLAIN the following DDL commands:

CREATE TABLE
CREATE INDEX
ALTER INDEX REBUILD

What is the benefit of doing so? First of all in case of a complex CREATE TABLE AS SELECT (CTAS) statement you'll get the execution plan, and when using the Cost Based Optimizer this information will include the number of estimated rows that are going to be generated and the estimated time it takes (from 10g on, prior to 10g you need to turn the reported cost yourself into time if you like).

Of course usually this plan is supposed to correspond to the plan of the SELECT statement alone, provided that you're using the default ALL_ROWS optimizer mode, but I have already encountered situations where the plans were different although this usually was due to some buggy behaviour.

In case of a CREATE INDEX DDL you'll get again a cost and time indication, along with a row estimate. Unfortunately it doesn't show an estimation of the required TEMP space for sorting. Additionally it doesn't work very well for function-based indexes where the number of rows actually indexed might be far less than the number of rows of the underlying table. The same applies to NULLABLE columns, it would be nice if this information would be used to come up with the correct number of rows that the index will cover.

As you will see in the sample script provided below, the optimizer's cardinality estimates are not used at full extent when generating indexes, in particular function-based indexes. The cardinality estimates of a query are significantly different from that of a corresponding index expression.

Starting with Oracle 10.2 you'll get an indication of the size of the index based on the dictionary statistics in the "Notes" section, so the estimate is only as good as your statistics allow for, in addition above points apply regarding the accuracy of the estimate in case of null values or function-based indexes. The size estimate is obviously based on the average column length recorded in the statistics.

Interestingly the CREATE TABLE AS SELECT estimation doesn't provide such a size estimation which could be quite handy, too.

Explaining an ALTER INDEX REBUILD shows similar information to that of a CREATE INDEX, but it doesn't show the size estimate, which is a pity because it might provide an indication of the size reduction that might be the result of a rebuild. Of course you can help yourself by explaining the corresponding CREATE INDEX statement.

One potentially interesting information is what is going to be used to perform the create or rebuild index, e.g. the database might be capable of using another index to read the information from rather than performing a full table scan (although you should check in this case if the index isn't redundant).

Other DDLs like ALTER INDEX COALESCE obviously are not supported and a simple CREATE TABLE doesn't add any value, so it's supported but useless.

The following script shows some test results of a EXPLAIN PLAN on DDLs in 11.1.0.7:

SQL> create table explain_ddl_test
2 as
3 select * from all_objects
4 where rownum <= 1000;

Table created.

SQL>
SQL> exec dbms_stats.gather_table_stats(null, 'explain_ddl_test')

PL/SQL procedure successfully completed.

SQL>
SQL> -- create table DDL explain
SQL> explain plan for
2 create table explain_ddl_test2
3 as
4 select * from explain_ddl_test;

Explained.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3524376278

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | 1000 | 84000 | 9 (0)| 00:00:01 |
| 1 | LOAD AS SELECT | EXPLAIN_DDL_TEST2 | | | | |
| 2 | TABLE ACCESS FULL | EXPLAIN_DDL_TEST | 1000 | 84000 | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

9 rows selected.

SQL>
SQL> -- create index explain
SQL> -- includes used and estimated index bytes
SQL> explain plan for
2 create index explain_ddl_test_idx
3 on explain_ddl_test (object_id);

Explained.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 932678384

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | CREATE INDEX STATEMENT | | 1000 | 4000 | 7 (0)| 00:00:01 |
| 1 | INDEX BUILD NON UNIQUE| EXPLAIN_DDL_TEST_IDX | | | | |
| 2 | SORT CREATE INDEX | | 1000 | 4000 | | |
| 3 | TABLE ACCESS FULL | EXPLAIN_DDL_TEST | 1000 | 4000 | 5 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Note
-----
- estimated index size: 65536 bytes

14 rows selected.

SQL>
SQL> -- but the estimated index size
SQL> -- doesn't use the correct tablespace
SQL> -- since the user has a default tablespace
SQL> -- with 1MB uniform extent allocation
SQL> create index explain_ddl_test_idx
2 on explain_ddl_test (object_id);

Index created.

SQL>
SQL> select
2 bytes/1024 as KB
3 from
4 user_segments
5 where
6 segment_name = 'EXPLAIN_DDL_TEST_IDX'
7 and segment_type = 'INDEX';

KB
----------
1024

1 row selected.

SQL>
SQL> drop index explain_ddl_test_idx;

Index dropped.

SQL>
SQL> -- Now the estimated index size information
SQL> -- is correct when explicitly
SQL> -- stating the tablespace
SQL> explain plan for
2 create index explain_ddl_test_idx
3 on explain_ddl_test (object_id)
4 tablespace test_8k;

Explained.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 932678384

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | CREATE INDEX STATEMENT | | 1000 | 4000 | 7 (0)| 00:00:01 |
| 1 | INDEX BUILD NON UNIQUE| EXPLAIN_DDL_TEST_IDX | | | | |
| 2 | SORT CREATE INDEX | | 1000 | 4000 | | |
| 3 | TABLE ACCESS FULL | EXPLAIN_DDL_TEST | 1000 | 4000 | 5 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Note
-----
- estimated index size: 1048K bytes

14 rows selected.

SQL>
SQL> -- what about compression?
SQL> explain plan for
2 create index explain_ddl_test_idx
3 on explain_ddl_test (object_type, object_id);

Explained.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 932678384

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | CREATE INDEX STATEMENT | | 1000 | 11000 | 7 (0)| 00:00:01 |
| 1 | INDEX BUILD NON UNIQUE| EXPLAIN_DDL_TEST_IDX | | | | |
| 2 | SORT CREATE INDEX | | 1000 | 11000 | | |
| 3 | TABLE ACCESS FULL | EXPLAIN_DDL_TEST | 1000 | 11000 | 5 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Note
-----
- estimated index size: 65536 bytes

14 rows selected.

SQL>
SQL> -- not considered in the estimate
SQL> explain plan for
2 create index explain_ddl_test_idx
3 on explain_ddl_test (object_type, object_id)
4 compress;

Explained.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 932678384

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | CREATE INDEX STATEMENT | | 1000 | 11000 | 7 (0)| 00:00:01 |
| 1 | INDEX BUILD NON UNIQUE| EXPLAIN_DDL_TEST_IDX | | | | |
| 2 | SORT CREATE INDEX | | 1000 | 11000 | | |
| 3 | TABLE ACCESS FULL | EXPLAIN_DDL_TEST | 1000 | 11000 | 5 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Note
-----
- estimated index size: 65536 bytes

14 rows selected.

SQL>
SQL> -- The size estimate
SQL> -- seems to be based
SQL> -- on num_rows * sum(avg_col_len)
SQL> exec dbms_stats.set_table_stats(null, 'explain_ddl_test', numblks=>10000000, numrows=>100000000)

PL/SQL procedure successfully completed.

SQL>
SQL> explain plan for
2 create index explain_ddl_test_idx
3 on explain_ddl_test (object_id);

Explained.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 932678384

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | CREATE INDEX STATEMENT | | 100M| 381M| 2809K (1)| 09:21:58 |
| 1 | INDEX BUILD NON UNIQUE| EXPLAIN_DDL_TEST_IDX | | | | |
| 2 | SORT CREATE INDEX | | 100M| 381M| | |
| 3 | TABLE ACCESS FULL | EXPLAIN_DDL_TEST | 100M| 381M| 2730K (1)| 09:06:07 |
-----------------------------------------------------------------------------------------------

Note
-----
- estimated index size: 2415M bytes

14 rows selected.

SQL>
SQL> exec dbms_stats.gather_table_stats(null, 'explain_ddl_test')

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_stats.set_column_stats(null, 'explain_ddl_test', 'object_id', avgclen=>50)

PL/SQL procedure successfully completed.

SQL>
SQL> -- it looks like that a certain overhead
SQL> -- is included in the estimated index size
SQL> -- otherwise this should fit into a
SQL> -- single 64kb extent
SQL> explain plan for
2 create index explain_ddl_test_idx
3 on explain_ddl_test (object_id);

Explained.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 932678384

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | CREATE INDEX STATEMENT | | 1000 | 50000 | 8 (0)| 00:00:01 |
| 1 | INDEX BUILD NON UNIQUE| EXPLAIN_DDL_TEST_IDX | | | | |
| 2 | SORT CREATE INDEX | | 1000 | 50000 | | |
| 3 | TABLE ACCESS FULL | EXPLAIN_DDL_TEST | 1000 | 50000 | 5 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Note
-----
- estimated index size: 131K bytes

14 rows selected.

SQL>
SQL> exec dbms_stats.gather_table_stats(null, 'explain_ddl_test')

PL/SQL procedure successfully completed.

SQL>
SQL> -- what about function-based indexes?
SQL> -- this is the default 5% hard-coded
SQL> -- selectivity for functions
SQL> -- by the optimizer
SQL> explain plan for
2 select
3 *
4 from
5 explain_ddl_test
6 where
7 case when object_id = 1 then 1 else null end is not null;

Explained.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 119225828

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 4200 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EXPLAIN_DDL_TEST | 50 | 4200 | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(CASE "OBJECT_ID" WHEN 1 THEN 1 ELSE NULL END IS NOT NULL)

13 rows selected.

SQL>
SQL> -- but it's not reflected
SQL> -- in the create index
SQL> explain plan for
2 create index explain_ddl_test_idx
3 on explain_ddl_test (case when object_id = 1 then 1 else null end);

Explained.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 932678384

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | CREATE INDEX STATEMENT | | 1000 | 4000 | 7 (0)| 00:00:01 |
| 1 | INDEX BUILD NON UNIQUE| EXPLAIN_DDL_TEST_IDX | | | | |
| 2 | SORT CREATE INDEX | | 1000 | 4000 | | |
| 3 | TABLE ACCESS FULL | EXPLAIN_DDL_TEST | 1000 | 4000 | 5 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Note
-----
- estimated index size: 65536 bytes

14 rows selected.

SQL>
SQL> -- same here
SQL> explain plan for
2 select
3 *
4 from
5 explain_ddl_test
6 where
7 decode(object_id, 1, 1, null) is not null;

Explained.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 119225828

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 4200 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EXPLAIN_DDL_TEST | 50 | 4200 | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(DECODE("OBJECT_ID",1,1,NULL) IS NOT NULL)

13 rows selected.

SQL>
SQL> explain plan for
2 create index explain_ddl_test_idx
3 on explain_ddl_test (decode(object_id, 1, 1, null));

Explained.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 932678384

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | CREATE INDEX STATEMENT | | 1000 | 4000 | 7 (0)| 00:00:01 |
| 1 | INDEX BUILD NON UNIQUE| EXPLAIN_DDL_TEST_IDX | | | | |
| 2 | SORT CREATE INDEX | | 1000 | 4000 | | |
| 3 | TABLE ACCESS FULL | EXPLAIN_DDL_TEST | 1000 | 4000 | 5 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Note
-----
- estimated index size: 65536 bytes

14 rows selected.

SQL>
SQL> -- virtual columns
SQL> -- in 11g should help
SQL> alter table explain_ddl_test
2 add expr1 as (decode(object_id, 1, 1, null));

Table altered.

SQL>
SQL> -- let's create statistics
SQL> -- for the virtual column
SQL> begin
2 dbms_stats.gather_table_stats(
3 null,
4 'explain_ddl_test',
5 method_opt => 'for columns expr1 size auto'
6 );
7 end;
8 /

PL/SQL procedure successfully completed.

SQL>
SQL> -- now the optimizer estimate
SQL> -- should use the extended statistics
SQL> explain plan for
2 select
3 *
4 from
5 explain_ddl_test
6 where
7 decode(object_id, 1, 1, null) is not null;

Explained.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 119225828

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 84 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EXPLAIN_DDL_TEST | 1 | 84 | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("EXPLAIN_DDL_TEST"."EXPR1" IS NOT NULL)

13 rows selected.

SQL>
SQL> -- now the optimizer estimate
SQL> -- should use the extended statistics
SQL> explain plan for
2 select
3 *
4 from
5 explain_ddl_test
6 where
7 expr1 is not null;

Explained.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 119225828

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 84 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EXPLAIN_DDL_TEST | 1 | 84 | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("EXPR1" IS NOT NULL)

13 rows selected.

SQL>
SQL> -- what about a CREATE INDEX?
SQL> -- still not used
SQL> explain plan for
2 create index explain_ddl_test_idx
3 on explain_ddl_test (expr1);

Explained.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 932678384

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | CREATE INDEX STATEMENT | | 1000 | 7000 | 7 (0)| 00:00:01 |
| 1 | INDEX BUILD NON UNIQUE| EXPLAIN_DDL_TEST_IDX | | | | |
| 2 | SORT CREATE INDEX | | 1000 | 7000 | | |
| 3 | TABLE ACCESS FULL | EXPLAIN_DDL_TEST | 1000 | 7000 | 5 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Note
-----
- estimated index size: 65536 bytes

14 rows selected.

SQL>
SQL> -- What about NULL values in the statistics?
SQL> alter table explain_ddl_test modify object_id null;

Table altered.

SQL>
SQL> update
2 explain_ddl_test
3 set
4 object_id = NULL
5 where
6 rownum <= 500;

500 rows updated.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> exec dbms_stats.gather_table_stats(null, 'explain_ddl_test')

PL/SQL procedure successfully completed.

SQL>
SQL> explain plan for
2 select count(*)
3 from explain_ddl_test
4 where object_id is not null;

Explained.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2446556867

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 5 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| EXPLAIN_DDL_TEST | 500 | 1500 | 5 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OBJECT_ID" IS NOT NULL)

14 rows selected.

SQL> -- not used in ROWS estimated in the CREATE INDEX
SQL> -- but the AVG_COL_LEN reflects the nulls
SQL> explain plan for
2 create index explain_ddl_test_idx
3 on explain_ddl_test (object_id);

Explained.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 932678384

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | CREATE INDEX STATEMENT | | 1000 | 3000 | 7 (0)| 00:00:01 |
| 1 | INDEX BUILD NON UNIQUE| EXPLAIN_DDL_TEST_IDX | | | | |
| 2 | SORT CREATE INDEX | | 1000 | 3000 | | |
| 3 | TABLE ACCESS FULL | EXPLAIN_DDL_TEST | 1000 | 3000 | 5 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Note
-----
- estimated index size: 65536 bytes

14 rows selected.

SQL>
SQL> create index explain_ddl_test_idx on explain_ddl_test (object_id);

Index created.

SQL>
SQL> explain plan for alter index explain_ddl_test_idx rebuild;

Explained.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 346799211

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | ALTER INDEX STATEMENT | | 1000 | 3000 | 5 (0)| 00:00:01 |
| 1 | INDEX BUILD NON UNIQUE| EXPLAIN_DDL_TEST_IDX | | | | |
| 2 | SORT CREATE INDEX | | 1000 | 3000 | | |
| 3 | INDEX FAST FULL SCAN| EXPLAIN_DDL_TEST_IDX | | | | |
-----------------------------------------------------------------------------------------------

10 rows selected.

SQL>
SQL> explain plan for alter index explain_ddl_test_idx rebuild online;

Explained.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 932678384

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | ALTER INDEX STATEMENT | | 1000 | 3000 | 5 (0)| 00:00:01 |
| 1 | INDEX BUILD NON UNIQUE| EXPLAIN_DDL_TEST_IDX | | | | |
| 2 | SORT CREATE INDEX | | 1000 | 3000 | | |
| 3 | TABLE ACCESS FULL | EXPLAIN_DDL_TEST | 1000 | 3000 | 5 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

10 rows selected.

SQL>
SQL> explain plan for create table test (col1 number);

Explained.

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

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

----------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
----------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | 0 (0)|
----------------------------------------------------

6 rows selected.

SQL>
SQL> explain plan for alter index explain_ddl_test_idx coalesce;
explain plan for alter index explain_ddl_test_idx coalesce
*
ERROR at line 1:
ORA-00900: invalid SQL statement


SQL>
SQL> -- what about bitmap indexes vs. b-tree indexes
SQL> exec dbms_stats.set_table_stats(null, 'explain_ddl_test', numblks=>10000000, numrows=>100000000)

PL/SQL procedure successfully completed.

SQL>
SQL> explain plan for
2 create index explain_ddl_test_idx
3 on explain_ddl_test (object_type);

Explained.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 932678384

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | CREATE INDEX STATEMENT | | 100M| 953M| 2810K (1)| 09:22:10 |
| 1 | INDEX BUILD NON UNIQUE| EXPLAIN_DDL_TEST_IDX | | | | |
| 2 | SORT CREATE INDEX | | 100M| 953M| | |
| 3 | TABLE ACCESS FULL | EXPLAIN_DDL_TEST | 100M| 953M| 2731K (1)| 09:06:18 |
-----------------------------------------------------------------------------------------------

Note
-----
- estimated index size: 2415M bytes

14 rows selected.

SQL>
SQL> -- no difference
SQL> explain plan for
2 create bitmap index explain_ddl_test_idx
3 on explain_ddl_test (object_type);

Explained.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3686177077

------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | CREATE INDEX STATEMENT | | 100M| 953M| 2810K (1)| 09:22:10 |
| 1 | INDEX BUILD NON UNIQUE | EXPLAIN_DDL_TEST_IDX | | | | |
| 2 | BITMAP COMPACTION | | | | | |
| 3 | SORT CREATE INDEX | | 100M| 953M| | |
| 4 | BITMAP CONSTRUCTION | | | | | |
| 5 | TABLE ACCESS FULL | EXPLAIN_DDL_TEST | 100M| 953M| 2731K (1)| 09:06:18 |
------------------------------------------------------------------------------------------------

Note
-----
- estimated index size: 2415M bytes

16 rows selected.

SQL>

In summary the added value and functionality of explaining the supported DDLs is rather limited, given the limitations mentioned above, although the information provided can be helpful in some cases.

Create Table As Select (CTAS) and the Shared Pool

One of the more subtle changes between 9iR2 and 10g is that you can now actually gather information about the current SQL of a CTAS operation being executed by a session.

In 9iR2 the actual SQL was (often?) not available as information in V$SESSION (SQL_ADDRESS, PREV_SQL_ADDR etc. columns), you saw instead some recursive SQL executed by Oracle to maintain the data dictionary (e.g. adding a row to sys.seg$ or similar) while the statement was being executed which was not really helpful in case you wanted to monitor your sessions.

If you created the table in parallel then at least you could see the internally by Oracle generated SQL for each of the parallel slaves to create its allocated segment of the final table. This has changed with 10g, where all the participating processes (coordinator and parallel slaves) show the same original CTAS SQL while the statement is being executed.

What really is special about the CTAS is the fact that it is often used as part of a regular batch process which for instance transforms data. So you might find yourself in the situation where you would like to analyse why a particular run of your batch process took that long.

If you attempt to get more detailed information about a CTAS that recently took place by querying the shared pool dynamic views you'll notice that you won't find any information about the CTAS at all. I came across this issue while implementing and testing the interface to DBMS_XPLAN.DISPLAY_CURSOR in my open source database frontend tool SQLTools++. It seems that a CTAS is treated like regular DDL and therefore is discarded from the shared pool right after the execution completes.

While this makes perfectly sense for regular DDL which you probably don't want to waste memory in the shared pool, in case of a CTAS this is not really helpful as you don't have a chance to perform any further analysis once the statement has completed.

Of course, one potential workaround could be to split your operation into two separate steps, one CTAS operation to create an empty table (e.g. by adding the predicate "1 = 2" to the original SQL) and a second INSERT /*+ APPEND */ SELECT DML operation which does the actual work and should be equal in terms of performance and resource consumption, but this adds further complexity to your process that is basically unnecessary and poses the potential risk that the two operations don't behave the same as one single CTAS, e.g. the CTAS to create the empty table might take longer depending on how and when the predicate "1 = 2" is evaluated or the INSERT might use a different plan than the single CTAS.

Consider this sample script which illustrates the issue by using the new 10g feature DBMS_XPLAN.DISPLAY_CURSOR:

SQL>
SQL> alter session set statistics_level = all;

Session altered.

SQL>
SQL> column plan_table_output format a100
SQL>
SQL> drop table test_source_data purge;

Table dropped.

SQL>
SQL> create table test_source_data as select * from all_objects;

Table created.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_IDִִ607vxpr6ct1qv,ִchildִnumberִ0

Anִuncaughtִerrorִhappenedִinִprepare_sql_statementִ:ִORA-01403:ִnoִdataִfound

NOTE:ִcannotִfetchִplanִforִSQL_ID:ִ607vxpr6ct1qv,ִCHILD_NUMBER:ִ0
ִִִִִִPleaseִverifyִvalueִofִSQL_IDִandִCHILD_NUMBER;
ִִִִִִItִcouldִalsoִbeִthatִtheִplanִisִnoִlongerִinִcursorִcacheִ(checkִv$sql_plan)


8 rows selected.

SQL>
SQL> drop table test_table purge;

Table dropped.

SQL>
SQL> create table test_table as select * from test_source_data where 1 = 2;

Table created.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_IDִִ55ktv4yducfz9,ִchildִnumberִ0

Anִuncaughtִerrorִhappenedִinִprepare_sql_statementִ:ִORA-01403:ִnoִdataִfound

NOTE:ִcannotִfetchִplanִforִSQL_ID:ִ55ktv4yducfz9,ִCHILD_NUMBER:ִ0
ִִִִִִPleaseִverifyִvalueִofִSQL_IDִandִCHILD_NUMBER;
ִִִִִִItִcouldִalsoִbeִthatִtheִplanִisִnoִlongerִinִcursorִcacheִ(checkִv$sql_plan)


8 rows selected.

SQL>
SQL> insert /*+ append */ into test_table select * from test_source_data;

98444 rows created.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_IDִִaqkc449zk58tn,ִchildִnumberִ0
-------------------------------------
insertִ/*+ִappendִ*/ִintoִtest_tableִselectִ*ִfromִtest_source_data

Planִhashִvalue:ִ1351963248

---------------------------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִ|ִNameִִִִִִִִִִִִִ|ִRowsִִ|ִBytesִ|ִCostִ(%CPU)|ִTimeִִִִִ|
---------------------------------------------------------------------------------------
|ִִִ0ִ|ִINSERTִSTATEMENTִִִ|ִִִִִִִִִִִִִִִִִִ|ִִִִִִִ|ִִִִִִִ|ִִִ284ִ(100)|ִִִִִִִִִִ|
|ִִִ1ִ|ִִLOADִASִSELECTִִִִ|ִִִִִִִִִִִִִִִִִִ|ִִִִִִִ|ִִִִִִִ|ִִִִִִִִִִִִ|ִִִִִִִִִִ|
|ִִִ2ִ|ִִִTABLEִACCESSִFULL|ִTEST_SOURCE_DATAִ|ִִִ100K|ִִִִ12M|ִִִ284ִִִ(3)|ִ00:00:04ִ|
---------------------------------------------------------------------------------------

Note
-----
ִִִ-ִdynamicִsamplingִusedִforִthisִstatement


18 rows selected.

SQL>
SQL> spool off


Another approach in that sense might be that you change your processes in such a way that the tables are all pre-created at the beginning and the actual work is then done as INSERTs, this is especially true if the table layout is already known and does not change frequently.

Either way you decide to go, the big advantage is that you now have a chance to gather information about the executed INSERTs afterwards by querying the corresponding shared pool dynamic views as long as the corresponding information has not been aged out of the shared pool.