Learning is not a spectator sport

May 30, 2012

Index size

Filed under: Uncategorized — connormcdonald @ 8:42 pm

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…

    Comment by Jeffrey Kemp — May 31, 2012 @ 10:02 am

  2. […] Following is a neat little trick if you want to very quickly and cheaply estimate the size of an index if it were to be rebuilt or a new index before you actually create the thing. I meant to blog about this sometime ago but was re- reminded of it when I recently came across this entry in Connor McDonald’s excellent blog. […]

    Pingback by Estimate Index Size With Explain Plan (I Can’t Explain) | Richard Foote's Oracle Blog — April 24, 2014 @ 1:23 pm

RSS feed for comments on this post. TrackBack URI

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

The WordPress Classic Theme. Create a free website or blog at WordPress.com.


Get every new post delivered to your Inbox.

Join 94 other followers

%d bloggers like this: