Did you know that it’s possible to use EXPLAIN PLAN FOR CREATE INDEX ON table(col1,col2,col3) syntax for explaining what exactly would be done when an index is created?
That’s useful for example for seeing the Oracle’s estimated index size without having to actually create the index.
You can also use EXPLAIN PLAN FOR ALTER INDEX i REBUILD to see whether this operation would use a FULL TABLE SCAN or a FAST FULL INDEX SCAN (offline index rebuilds of valid indexes can use this method).
Anyway, you can experiment with this yourself, but here’s a little quiz (with a little gotcha :)
What kind of index creation statement would create such an execution plan?
SQL> explain plan for create index hack_index on hack_table ....the rest is a secret for now....
Explained.
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 457720527
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | CREATE INDEX STATEMENT | | 88868 | | 657 (1)| 00:00:12 |
| 1 | INDEX BUILD NON UNIQUE | HACK_INDEX | | | | |
| 2 | SORT AGGREGATE | | 1 | | | |
| 3 | VIEW | HACK_VIEW | 74062 | | 318 (1)| 00:00:06 |
|* 4 | HASH JOIN | | 74062 | 1012K| 210 (1)| 00:00:04 |
| 5 | TABLE ACCESS FULL | TEST_USERS | 46 | 368 | 4 (0)| 00:00:01 |
| 6 | INDEX FAST FULL SCAN| IDX2_INDEXED_OBJECTS | 74062 | 433K| 206 (1)| 00:00:04 |
| 7 | SORT CREATE INDEX | | 88868 | | | |
| 8 | TABLE ACCESS FULL | HACK_TABLE | 88868 | | 544 (1)| 00:00:10 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("U"."USERNAME"="O"."OWNER")
Note
-----
- estimated index size: 3145K bytes
