Index size

I think this feature came around ages ago (10.2?) but I’m still always impressed by it every time I use it.

You can run EXPLAIN on a CREATE INDEX command

SQL> explain plan for create index IX on MY_TABLE ( bet_account_num );

Explained.

--------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | CREATE INDEX STATEMENT |             |   213K|   626K|   833   (1)| 00:00:11 |
|   1 |  INDEX BUILD NON UNIQUE| IX          |       |       |            |          |
|   2 |   SORT CREATE INDEX    |             |   213K|   626K|            |          |
|   3 |    TABLE ACCESS FULL   | MY_TABLE    |   213K|   626K|   712   (2)| 00:00:09 |
--------------------------------------------------------------------------------------

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

and you get a nice little estimate of how big your index will be…..Awesome!

2 comments

Jeffrey Kemp says:

May 31, 2012 at 10:02 am

Very nice! FWIW I thought I’d see if my GUI tools would be nice enough to support this…

Oracle SQL Developer (v3.0.04) supports it no problem.

In my version of Toad (11.0.0.116) the plan for the query under the cursor can be obtained by clicking the “ambulance” button or by pressing Ctrl+E. Unfortunately, if it’s a “CREATE INDEX”, Toad refuses to show the plan unless I first highlight the entire “CREATE INDEX” statement.

The only issue is, both these tools don’t show the “notes” section so I can’t see the “estimated index size”. Oh well – back to SQL*Plus then…

Reply
Pingback: Estimate Index Size With Explain Plan (I Can’t Explain) | Richard Foote's Oracle Blog