Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    54

    Question Unanswered: Proper Index Sizing

    We're in the middle of rebuilding some indexes. Could someone refresh me one what each setting means. A good sample statement is:


    CREATE INDEX "STORE_ORD_LN3" ON "STORE_ORD_LN" ("SKU_NUM" )
    PCTFREE 10 INITRANS 99 MAXTRANS 255
    STORAGE(INITIAL 500M NEXT 50M MINEXTENTS 1 MAXEXTENTS
    2147483645 PCTINCREASE 0 FREELISTS 99 FREELIST GROUPS 1 BUFFER_POOL
    DEFAULT)
    TABLESPACE "INDEXES" NOLOGGING


    ...what is the best tool for determining the proper sizing for indexes? I'm on Oracle 8.1.7, riding od AIX 4.3.3.

  2. #2
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    Following makes you REBUILDING of indexes (Usually)

    (1) Hight of the indexes (should be <=3).


    PHP Code:

          SELECT index_name
    blevel
          FROM dba_indexes
          WHERE blevel 
    3
    2) Wasted space in the index is mopre than 20% (According to Oracle)

    PHP Code:

             ALTER INDEX 
    <index_nameVALIDATE STRUCTURE;

             
    SELECT DEL_LF_ROWS_LEN/LF_ROWS/LEN) * 100 "Free Space"
             
    FROM index_stats
             WHERE index_name 
    = <index_name>; 
    For CREATE INDEX commands/options please read Oracel Manuals.

    http://www.csee.umbc.edu/help/oracle...ch4f.htm#39410
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  3. #3
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Your MAXEXTENTS 2147483645 suggests to me that you've pulled this code from one of the GUI tools which generates scripts from the data dictionaries. The value is as near as dammit the max of a 32 bit signed int... Oracle's storage of this particular "UNLIMITED" value always appears a little confusing.

    I'm afraid that in answer to your question, there is no simple 'list' of what each storage clause means. Oracle devotes numerous chapters to explaining this. I fear your only option is to read the oracle docs, available at www.oracle.com

    Incidentally, the best tool for sizing is knowledge of the index usage and growth, combined with understanding of the storage parameters. I don't think there is any tool in the world that can do this for you.

    Hth
    Bill
    Last edited by billm; 02-19-04 at 20:02.
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •