Q
Problem solve Get help with specific problems with your technologies, process and projects.

Calculating initial, next and max extents

I have a table DTBRN_VEN_MODEL_MST in which there are six lac records. To increase the performance, I have created indexes as given below. I have to specify the stroage clause. How should I calculate the inital extents, next and max extents?

 CREATE INDEX  DTBRN_VEN_MODEL_MST_IDX ON
 DTBRN_VEN_MODEL_MST
 (CMP_CD,RGN_CD,BRN_CD,VEN_CD,TAG,TBL_MODE)
 /

Personally, I stopped calculating extent sizes ever since Oracle 8i and the Locally Managed Tablespace (LMT) feature was implemented. Now, I just have uniform LMTs with different extent sizes (a 64KB extent, a 256 KB extent, and maybe a 1 MB extent) and I just put objects in the tablespace depending on their expected size, small, medium, or large.

Before 8i, the way I calculated extent sizes for indexes was to create the index with a very small INITIAL=NEXT and PCTINCREASE=0. So start with a small INITIAL=16K, NEXT=16K and PCTINCREASE=0. Then create the index. Now query DBA_SEGMENTS to find out how many blocks were allocated. Drop the index and when you recreate it, make the INITIAL at least the size you figured our from DBA_SEGMENTS. Allow some room for growth. Your NEXT extent size should allow for a percentage of your expected growth rate.

For More Information

This was last published in August 2003

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close