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  |
 |
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 #588748 is a reply to message #588602] |
Fri, 28 June 2013 00:18  |
|
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 |
 |
Barbara Boehmer
Messages: 9104
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.
|
|
|
|
Goto Forum:
Current Time: Thu Jan 30 18:55:50 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
