Index creation in context
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Index creation in context

Hybrid View

  1. #1
    Join Date
    Oct 2000
    Posts
    24
    Hi,

    When I create a context index ,it is always created in the System tablespace.

    Is it the default or there is any way of modifying it.

    My second question is if Oracle indexes(btree)and context indexes are in the same tablespace is there aproblem?


    Thanks

    Anand Devaraj

  2. #2
    Join Date
    Aug 2000
    Location
    Belgium
    Posts
    342

    Arrow

    first off all, a context index is a combination of different 'real' tables and indexes.

    Where the different segments are created, and what the storage parameters are, that you must define in the attributes off the context index.

    example taken from our creation script.
    just the storage part :

    ctx_ddl.set_attribute ('INDEX_MEMORY', 40000000 );
    ---------------
    -- I1T
    ---------------
    ctx_ddl.set_attribute ('I1T_TABLESPACE', 'ORADATA' );
    ctx_ddl.set_attribute ('I1T_STORAGE',' INITIAL 33550336 NEXT 33550336 MAXEXTENTS 249 PCTINCREASE 0');
    ctx_ddl.set_attribute ('I1T_OTHER_PARMS',' PCTFREE 20');
    ctx_ddl.set_attribute ('I1T_OTHER_PARMS',' PARALLEL 2');

    ctx_ddl.set_attribute ('I1I_TABLESPACE', 'ORAINDEX');
    ctx_ddl.set_attribute ('I1I_STORAGE', ' INITIAL 33550336 NEXT 33550336 MAXEXTENTS 249 PCTINCREASE 0');
    ctx_ddl.set_attribute ('I1I_OTHER_PARMS',' PARALLEL 2');
    ctx_ddl.set_attribute ('I1I_OTHER_PARMS',' PCTFREE 10');
    ---------------
    -- KTB
    ---------------
    ctx_ddl.set_attribute ('KTB_TABLESPACE', 'ORADATA' );
    ctx_ddl.set_attribute ('KTB_STORAGE',' INITIAL 520192 NEXT 520192 MAXEXTENTS 249 PCTINCREASE 0');
    ctx_ddl.set_attribute ('KTB_OTHER_PARMS',' PCTFREE 20');

    ctx_ddl.set_attribute ('KID_TABLESPACE', 'ORAINDEX');
    ctx_ddl.set_attribute ('KID_STORAGE', ' INITIAL 520192 NEXT 520192 MAXEXTENTS 249 PCTINCREASE 0');
    ctx_ddl.set_attribute ('KID_OTHER_PARMS',' PARALLEL 2');
    ctx_ddl.set_attribute ('KID_OTHER_PARMS',' PCTFREE 10');

    ctx_ddl.set_attribute ('KIK_TABLESPACE', 'ORAINDEX');
    ctx_ddl.set_attribute ('KIK_STORAGE', ' INITIAL 520192 NEXT 520192 MAXEXTENTS 249 PCTINCREASE 0');
    ctx_ddl.set_attribute ('KIK_OTHER_PARMS',' PARALLEL 2');
    ctx_ddl.set_attribute ('KIK_OTHER_PARMS',' PCTFREE 10');

    ---------------
    -- LST
    ---------------
    ctx_ddl.set_attribute ('LST_TABLESPACE', 'ORADATA' );
    ctx_ddl.set_attribute ('LST_STORAGE',' INITIAL 520192 NEXT 520192 MAXEXTENTS 249 PCTINCREASE 0');
    ctx_ddl.set_attribute ('LST_OTHER_PARMS',' PCTFREE 20');

    ctx_ddl.set_attribute ('LIX_TABLESPACE', 'ORAINDEX');
    ctx_ddl.set_attribute ('LIX_STORAGE', ' INITIAL 520192 NEXT 520192 MAXEXTENTS 249 PCTINCREASE 0');
    ctx_ddl.set_attribute ('LIX_OTHER_PARMS',' PARALLEL 2');
    ctx_ddl.set_attribute ('LIX_OTHER_PARMS',' PCTFREE 10');
    ---------------
    -- SQR
    ---------------
    ctx_ddl.set_attribute ('SQR_TABLESPACE', 'ORADATA' );
    ctx_ddl.set_attribute ('SQR_STORAGE',' INITIAL 520192 NEXT 520192 MAXEXTENTS 249 PCTINCREASE 0');
    ctx_ddl.set_attribute ('SQR_OTHER_PARMS',' PCTFREE 20');

    ctx_ddl.set_attribute ('SRI_TABLESPACE', 'ORAINDEX');
    ctx_ddl.set_attribute ('SRI_STORAGE', ' INITIAL 520192 NEXT 520192 MAXEXTENTS 249 PCTINCREASE 0');
    ctx_ddl.set_attribute ('SRI_OTHER_PARMS',' PARALLEL 2');
    ctx_ddl.set_attribute ('SRI_OTHER_PARMS',' PCTFREE 10');


    --> all the 'context index tables' are created in tablespace ORADATA
    --> all the 'context index indexes' are create in tablespace ORAINDEX

    Hope this helps
    Gert

  3. #3
    Join Date
    Oct 2000
    Posts
    24
    Hi Gert,

    Thanks for your detailed reply . I'll get back to you after I have tried these changes.


    Regards

    Anand

Posting Permissions

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



Click Here to Expand Forum to Full Width