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 thoughts on “Index size

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

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s