Comments on: Index Sizehttps://jonathanlewis.wordpress.com/2009/05/22/index-size/
Just another Oracle weblogSat, 01 Aug 2015 21:31:55 +0000hourly1http://wordpress.com/By: jcon.no: Oracle Blog - When to rebuild an Oracle index?https://jonathanlewis.wordpress.com/2009/05/22/index-size/#comment-44065
Thu, 05 Jan 2012 10:26:55 +0000http://jonathanlewis.wordpress.com/?p=1291#comment-44065[…] next following days). So the question is “What indexes should be rebuilt?”. I just read this article from Jonathan Lewis that might just give us a possible answer. In 10g Oracle introduced the […]
]]>By: Index size bug « Oracle Scratchpadhttps://jonathanlewis.wordpress.com/2009/05/22/index-size/#comment-44029
Wed, 04 Jan 2012 17:32:00 +0000http://jonathanlewis.wordpress.com/?p=1291#comment-44029[…] a follow-up to a post I did some time ago about estimating the size of an index before you create it. The note describes dbms_stats.create_index_cost() procedure, and how it […]
]]>By: Jonathan Lewishttps://jonathanlewis.wordpress.com/2009/05/22/index-size/#comment-33230
Wed, 03 Jun 2009 18:44:26 +0000http://jonathanlewis.wordpress.com/?p=1291#comment-33230George,

The important difference between create_table_cost and create_index_cost is that you supply the correct DDL for creating an index on an existing table to create_index_cost – and Oracle uses the table-related stats in the data dictionary to do its arithmetic – whereas you simply provide a list of column types and sizes (or just a row size) with some other numbers to use create_table_cost.

It shouldn’t be too difficult for Oracle to include a routine to take a DDL statement for ‘create table’ and do the same sort of thing. (In fact, I think I’ve seen one such routine but I can’t remember where). Of course, you could just take the top line output from dbms_xplan.display() for ‘explain for create table…’ and use the Rows and Bytes columns to work out the space requirement by hand.

]]>By: georgehttps://jonathanlewis.wordpress.com/2009/05/22/index-size/#comment-33221
Tue, 02 Jun 2009 17:03:41 +0000http://jonathanlewis.wordpress.com/?p=1291#comment-33221unlike create_table_cost, create_index_cost doesn’t contain a parameter row_count , so how does estimation work for different row count?
]]>By: Jonathan Lewishttps://jonathanlewis.wordpress.com/2009/05/22/index-size/#comment-33155
Tue, 26 May 2009 11:07:40 +0000http://jonathanlewis.wordpress.com/?p=1291#comment-33155Randolf,
Thanks for the comment and link.
]]>By: Jonathan Lewishttps://jonathanlewis.wordpress.com/2009/05/22/index-size/#comment-33154
Tue, 26 May 2009 11:07:19 +0000http://jonathanlewis.wordpress.com/?p=1291#comment-33154The most obvious problem with that analyze call is that it tries to lock the table in share mode (mode 4) using the nowait option. If anyone is updating the table the analyze fails with Oracle error ORA-00054; if the lock is acquired then anyone attempting to update the table has to wait for the analyze to complete.
]]>By: Two Excellent Index Related Blog Posts « Richard Foote’s Oracle Bloghttps://jonathanlewis.wordpress.com/2009/05/22/index-size/#comment-33145
Mon, 25 May 2009 11:15:10 +0000http://jonathanlewis.wordpress.com/?p=1291#comment-33145[…] first is by Jonathan Lewis who discusses in a post called Index Size how one might determine which indexes to perhaps consider for an index rebuild by using the […]
]]>By: PdVhttps://jonathanlewis.wordpress.com/2009/05/22/index-size/#comment-33132
Sat, 23 May 2009 19:06:16 +0000http://jonathanlewis.wordpress.com/?p=1291#comment-33132For estimating new indexes, I’d welcome a good guesstimate.

But I am weary of any (space-, size-)esitmate.
Reality tends to invalidate any size-estimate by roughly a factor of 10 (pi-square rounded to a whole number) before the year is over.

Index-Compression (of suitable indexes) is an under-used feature and a bit of promotion for that would be good.

But for existing indexes,
what about the old-fashioned:

SQL> analyze index abc_pk VALIDATE STRUCTURE ;

Followed by a select from INDEX_STATS ?

(also a bit of work, as it needs to be done one-at-a-time, and I’m fairly lazy myself)

I think the EXPLAIN PLAN on CREATE INDEX functionality is really quite limited. It starts with the dependency on the available statistics, not considering the number of nulls defined for the column (at least for the “ROWS” shown, may be indirectly for the size by using the AVG_COL_LEN) and doesn’t end with no reasonable support for function-based indexes and their expressions (which is challenging I have to admit).

Not to mention the shortcomings you’ve pointed out: No compression considered and the “tablespace” oddity.