Skip to Main Content
  • Questions
  • can't we use nologging clause with domain index

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Ranga Prasad.

Asked: June 08, 2016 - 1:15 pm UTC

Last updated: February 26, 2019 - 1:37 pm UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

Hi Tom,

I am trying to create domain index on table, I am getting ORA-29850: invalid option for creation of domain indexes. Can you please explain why can't we create domain index with nologging option.

SQL> begin
  2   ctx_ddl.drop_preference('EmployeeSearch');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> Drop Index IDX_EmployeeEmpID;

Index dropped.

SQL>
SQL> Begin
  2   ctx_ddl.create_preference('EmployeeSearch','user_datastore');
  3   ctx_ddl.set_attribute('EmployeeSearch','procedure','ContextSearchfilter.EmployeeSearch_Filter');
  4  end;
  5  /

PL/SQL procedure successfully completed.

SQL>
SQL> create index IDX_EmployeeEmpID on Employee(EmpID) indextype is ctxsys.context
  2  parameters('lexer jadoo_lexer datastore EmployeeSearch sync (on commit)') Nologging;
parameters('lexer jadoo_lexer datastore EmployeeSearch sync (on commit)') Nologging
                                                                                  *
ERROR at line 2:
ORA-29850: invalid option for creation of domain indexes


Thaks in advance,
S Ranga Prasad.

and Chris said...

You do this by setting storage parameters. You need to set this for each of the various tables Oracle Text creates.

First you need to create the storage preference. Then assign the storage attributes to this:

begin
ctx_ddl.create_preference('mystore', 'BASIC_STORAGE');
ctx_ddl.set_attribute('mystore', 'I_TABLE_CLAUSE',
                        'nologging'); 
ctx_ddl.set_attribute('mystore', 'K_TABLE_CLAUSE',
                        'nologging'); 
ctx_ddl.set_attribute('mystore', 'R_TABLE_CLAUSE',
                        'nologging');
ctx_ddl.set_attribute('mystore', 'N_TABLE_CLAUSE',
                        'nologging'); 
ctx_ddl.set_attribute('mystore', 'I_INDEX_CLAUSE',
                        'nologging');
ctx_ddl.set_attribute('mystore', 'P_TABLE_CLAUSE',
                        'nologging'); 
ctx_ddl.set_attribute('mystore', 'S_TABLE_CLAUSE',
                        'nologging');
end;
/


You can also use this to change the tablespace etc.

With this in place, specify this preference in the storage parameter:

create table employee (
  empid varchar2(1000)
);

create or replace procedure employeesearch_filter (
  r in rowid,
  c in out nocopy varchar2 )
is
begin
  null;
end;
/     

create index IDX_EmployeeEmpID on Employee(EmpID) indextype is 
ctxsys.context
parameters('datastore EmployeeSearch storage mystore sync (on commit)') ;

select table_name, logging from dba_tables
where  table_name like 'DR$%$%'
and    owner = user;

TABLE_NAME                     LOG
------------------------------ ---
DR$IDX_EMPLOYEEEMPID$R         NO 
DR$IDX_EMPLOYEEEMPID$N            
DR$IDX_EMPLOYEEEMPID$K            
DR$IDX_EMPLOYEEEMPID$I         NO 

select index_name, logging from dba_indexes
where  index_name like 'DR$%$%'
and    owner = user;

INDEX_NAME                     LOG
------------------------------ ---
DR$IDX_EMPLOYEEEMPID$X         NO

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

For R table Data column type Blob is still Logging true

S Ranga Prasad, February 26, 2019 - 12:46 pm UTC

Hi Tom,

Even nologging used when as suggested above observed R table Data column of type Blob is Logging true, can be seen in user_lobs. Can you please tell why Data column logging is True.


Chris Saxon
February 26, 2019 - 1:37 pm UTC

I'm not sure what you're asking here...

Could you post an example?

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library