Home » Server Options » Text & interMedia » Improve ctx_ddl.sync_index performance (Oracle 11G)
Improve ctx_ddl.sync_index performance [message #588602] Wed, 26 June 2013 16:49 Go to next message
sherlocksher
Messages: 14
Registered: June 2013
Junior Member
Hi,

We have a table called "TABLE1" partitioned on a column called Project_id.
There is a local domain context index created on a multi column data store (3 columns) of this table.

Now as part of our business, the users can initiate a load operation for one partition of the table where in approximately 8 million records are inserted.

After the completion of this load, we commit the data and then execute the below statement on the local index to sync the data and make it available for data retrieval.

V_IDX_STMT := 'begin ctx_ddl.sync_index('''||V_IDX_NAME||''',null,'''||V_PARTITION_NAME||'''); end;';

The sync operation on this 8 million records ran for about 15-16 hours.

From the oracle documentation, it says supplying memory and parallel_degree parameters should make it run faster

Question :

a. How do I determine how much memory and the paralell_degree parameter value should be supplied to speed it up .
b. Are there any other ways of speeding this up.

Any pointers will be very helpful.

Thanks,
VTK.


[EDITED by LF: removed superfluous empty lines]

[Updated on: Thu, 27 June 2013 00:07] by Moderator

Report message to a moderator

Re: Improve ctx_ddl.sync_index performance [message #588631 is a reply to message #588602] Thu, 27 June 2013 04:08 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Quote:

a. How do I determine how much memory and the paralell_degree parameter value should be supplied to speed it up .

http://docs.oracle.com/cd/E11882_01/text.112/e24435/aoptim.htm#CCAPP9274

Quote:

b. Are there any other ways of speeding this up.


Even after synchronizing, you still need to optimize. So, with that many records, it might be quicker to just use alter index ... rebuild ...
Re: Improve ctx_ddl.sync_index performance [message #588748 is a reply to message #588602] Fri, 28 June 2013 00:18 Go to previous messageGo to next message
sherlocksher
Messages: 14
Registered: June 2013
Junior Member
Thanks for your inputs.

1. Should optimization be done after sync even for data that is inserted into the partition for the first time.

2. My preference and index script is as follows.

--Preferences
begin
ctx_ddl.create_preference ('skipJoinLexer', 'BASIC_LEXER');
ctx_ddl.set_attribute ('skipJoinLexer', 'skipjoins', ';:.,()<>*"''#-');
ctx_ddl.create_preference ('logbookTextDS', 'MULTI_COLUMN_DATASTORE');
ctx_ddl.set_attribute ('logbookTextDS', 'COLUMNS', 'DISC_CMPLNT_TX, MAINT_ACT_TX,LGBK_CMT_TX');

end;

--Index
create index lb_text_idx on ac_maint (DISC_CMPLNT_TX) indextype is ctxsys.context local parameters ('lexer skipJoinLexer datastore logbookTextDS NOPOPULATE');


I want to rebuild the lb_text_idx for this partition alone so that data is available for searching by the application.
I did a rebuild using the command " alter index lb_text_idx rebuild partition p_1".

However when I run the sql(SELECT COUNT(1) FROM AC_MAINT WHERE PROJECT_ID = 1 and contains (DISC_CMPLNT_TX, 'MAINT' ) > 0) , I get a count of 0. I know that the text MAINT is definitely present in the table and for the specific partition.

When I run the below sql
select parameters,status from user_ind_partitions where partition_name = 'P_1' , I get parameters as null and status as usable.

a.Am I getting 0 records because the parameters are set to null?

b.How do I rebuild with the same parameters for the specific partition alone



Re: Improve ctx_ddl.sync_index performance [message #588928 is a reply to message #588748] Sat, 29 June 2013 22:41 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
I don't get 0 rows like you do. Please compare my example below and see if you are doing anything different. It may just be that you have not waited long enough for the rebuild to complete. I do get the null parameters and I don't know why, but the test below indicates that the preferences are working.

SCOTT@orcl_11gR2> create table ac_maint
  2    (project_id	number,
  3  	disc_cmplnt_tx	varchar2(15),
  4  	maint_act_tx	varchar2(15),
  5  	lgbk_cmt_tx	varchar2(15))
  6  partition by range (project_id)
  7    (partition p_1 values less than (2),
  8  	partition p_m values less than (maxvalue))
  9  /

Table created.

SCOTT@orcl_11gR2> begin
  2    ctx_ddl.create_preference ('skipJoinLexer', 'BASIC_LEXER');
  3    ctx_ddl.set_attribute ('skipJoinLexer', 'skipjoins', ';:.,()<>*"''#-');
  4    ctx_ddl.create_preference ('logbookTextDS', 'MULTI_COLUMN_DATASTORE');
  5    ctx_ddl.set_attribute ('logbookTextDS', 'COLUMNS', 'DISC_CMPLNT_TX, MAINT_ACT_TX,LGBK_CMT_TX');
  6    ctx_ddl.set_attribute ('logbookTextDS', 'delimiter', 'newline');
  7  end;
  8  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> create index lb_text_idx
  2  on ac_maint (DISC_CMPLNT_TX)
  3  indextype is ctxsys.context
  4  parameters
  5    ('lexer	   skipJoinLexer
  6  	 datastore logbookTextDS
  7  	 stoplist  ctxsys.empty_stoplist
  8  	 nopopulate')
  9  local
 10    (partition p_1,
 11  	partition p_m)
 12  parallel 2
 13  /

Index created.

SCOTT@orcl_11gR2> insert all
  2  into ac_maint values (1, 'maint',	   'mat1',  'lct1')
  3  into ac_maint values (1, 'maint',	   'mat2',  'lct2')
  4  into ac_maint values (1, 'whatever',  'mat3',  'lct3')
  5  into ac_maint values (2, 'maint',	   'mat4',  'lct4')
  6  into ac_maint values (1, 'mai.nt',    'mat5',  'lct5')
  7  into ac_maint values (1, 'something', 'maint', 'lct6')
  8  into ac_maint values (1, 'something', 'mat7',  'maint')
  9  select * from dual
 10  /

7 rows created.

SCOTT@orcl_11gR2> commit
  2  /

Commit complete.

SCOTT@orcl_11gR2> alter index lb_text_idx rebuild partition p_1 parallel 2
  2  /

Index altered.

SCOTT@orcl_11gR2> SELECT *
  2  FROM   AC_MAINT
  3  WHERE  PROJECT_ID = 1
  4  and    contains (DISC_CMPLNT_TX, 'maint' ) > 0
  5  /

PROJECT_ID DISC_CMPLNT_TX  MAINT_ACT_TX    LGBK_CMT_TX
---------- --------------- --------------- ---------------
         1 maint           mat1            lct1
         1 maint           mat2            lct2
         1 mai.nt          mat5            lct5
         1 something       maint           lct6
         1 something       mat7            maint

5 rows selected.

SCOTT@orcl_11gR2> select parameters, status
  2  from   user_ind_partitions
  3  where  partition_name = 'P_1'
  4  /

PARAMETERS
--------------------------------------------------------------------------------
STATUS
--------

USABLE


1 row selected.

Previous Topic: MultiByte Characters
Next Topic: Is there a simple "Fuzzy" LIKE query to match similar strings?
Goto Forum:
  


Current Time: Fri Mar 29 07:02:37 CDT 2024