This content has been marked as final. Show 12 replies
Not quite sure what you're asking. Do you want to change the interval for certain partitions, so different partitions have different sync intervals?
Yes I want to have different repeat_interval values for different sets of partitions. The most recent 4 days on the "regular cycle of every 30 seconds), and older (10 days) perhaps 4 times a day and the rest every month or so or even disabled.
I would like to just modify the repeat interval value for the Job as opposed to having to modify the index metadata for sync.
I'm not really sure without trying it. Does it currently create four separate jobs if you have four partitions?
If you can do it by changing the index metadata, that would definitely be supported. If you have to go into the scheduler and change it there, I think you're on somewhat shaky grounds with regard to it being "supported".
was Roger's answer unclear, why is the same question raised in the SR ?
There is a Job created for each partition. There is a repeat_interval parameter set for the Job from the SYNC(EVERY ...) The memory and degreel_parallel parameters go into the Job PLSQL definition
If you change the index then the value in the parameters column from user_ind_partitions only has the most recent value (it seems) and eventually it can get confusing.
REPEAT_INTERVAL is a JOB thing and modifying that works just as well **and** keeps the parameters values intact as when the index was created.
Also, I found **and** it could be something in the syntax I had, that if I had loaded data and the job had not kicked in yet, I could not modify the index for any partition. We load data continuously and we have daily*4 partitions (for 5 years). So quite a few partitions and Jobs. Disabling a Job would be much simpler.
Interessting that I have "raised" an SR when I do not even have an active CID to work with.
well, do you may know or are working for COMMUNICATIONS SECURITY EST from Canada who logged a SR for the same issue
changing the index metadata is supported, ie
alter index ... rebuild partition ... parameters('replace metadata SYNC (EVERY "NEXT_DAY(TRUNC(SYSDATE), ''MONDAY'') + 15/24") ');
Well I am just wondering why once that the Job has been created by the SYNC(EVERY ...) That I cannot do
Achieves the same thing without having to do an alter index ... DDL
BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE ( name => 'DR#xxxxx$J' ,attribute => 'repeat_interval' ,value => 'freq=secondly;interval=30' ); END; /
Actually I do not want to rebuild the index just modify. Rebuild will make a mess in that the the current Window (lets say, 7 days) index would get rebuilt and queries suffer if I only want to keep the last 4 days active for synch index. Maybe modify index
ALTER INDEX xyz MODIFY PARTITION px PARAMETERS ( 'sync(EVERY .... memory 200M parallel 16)');
I imagine that would work fine. But what might happen if you, say, exported and imported the index? Or changed other metadata on the index? Or split the partition? Would that reset your changes, or not?
We can't be sure of all the implications without working through the code carefully, so that's why we're reluctant to say that it's supported. If you're prepared to take the risk of it breaking if you do something unusual with the index, then go ahead. If you want to be 100% sure what you do is supported, go with updating the index metadata.
it looks like you are not familiar with index metadata replacement.
The METADATA keyword in alter index rebuild changes the index metadata without reindexing.