Index size

Posted by

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 );
| 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 |
- estimated index size: 5128K bytes

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


  1. 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 ( 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…

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.