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 );

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!

About these ads

2 Comments »

  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…

    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. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 78 other followers

%d bloggers like this: