1 Reply Latest reply: Jul 15, 2014 2:49 PM by spur230 RSS

    how to  create index (indextype is ctxsys.context )with NOLOGGING

    883677

      Hi Gurus,

      Help please
      : How can I specify NOLOGGING option? In the index command? In the attribute? What is the exact syntax if NOLOGGING is allowed at ctxsys.context?

      Oracle version: 11.2.0.3.8


      use the NOLOGGIN option of the create index command which is very simple to use at column indexes.
      Which unclear to me how should I use in CREATE INDEX .. INDEXTYPE IS CTXSYS.CONTEXT command.
      Following the CREATE INDEX command syntax seems oubvious - but it provides sytax error.
      Maybe this is why CREATE INDEX .. INDEXTYPE IS CTXSYS.CONTEXT needs something different for options.

       

      Thanks

        • 1. Re: how to  create index (indextype is ctxsys.context )with NOLOGGING
          spur230

          You have to specify nologging in  storage preference.

           

          See Avoiding Redo Logging for an Oracle Text Domain Index (Doc ID 432762.1)

           

          Example:

          begin
          ctx_ddl.drop_preference('my_storage');
          end;
          /

          begin
          ctx_ddl.create_preference('my_storage','BASIC_STORAGE');
          end;
          /

          begin
          ctx_ddl.set_attribute('my_storage', 'i_table_clause','tablespace foo1 storage (initial 5M next 10M pctincrease 0 freelists 24 freelist groups 24) nologging');
          ctx_ddl.set_attribute('my_storage', 'k_table_clause','tablespace foo2 storage (initial 5M next 10M pctincrease 0) nologging');
          ctx_ddl.set_attribute('my_storage', 'r_table_clause','tablespace foo3 storage (initial 5M next 10M pctincrease 0) lob(DATA) store as (nocache nologging) nologging');
          ctx_ddl.set_attribute('my_storage', 'n_table_clause','tablespace foo4 storage (initial 5M next 10M pctincrease 0) nologging');
          ctx_ddl.set_attribute('my_storage', 'p_table_clause','tablespace foo5 storage (initial 5M next 10M pctincrease 0) nologging');
          ctx_ddl.set_attribute('my_storage', 'i_index_clause','tablespace foo6 storage (initial 5M next 10M pctincrease 0) compress 2 nologging');
          end;
          /