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
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our applications, PL/SQL, database administration and data warehousing gurus are waiting to answer your toughest questions.