user646034 wrote:Partitioning may not necessarily improve performance.
Does partition improve insert query performance.
One of the insert which is accessing gl_balance and gl_code_combinations tables is taking time.
After partitioning the gl_balances tables. It still taking same time.
My question does partition improve insert query performance.
We are trying to insert into a single table by joining gl_balances and gl_code_combinations.How big is the table? How many records are there?
It is taking more than 1 hour to complete.
I created a partition on column period_name of gl_balances but still no improvement.
user646034 wrote:Is the referesh MV program a seeded program or a custom one? If it is seeded, please log a SR and upload the trace/tkprof to Oracle support.
Jobs runs which insert the data into gl tables is fast. When we refresh the materialized view.
It is very slow. MV is created on gl tables out of which only gl_balances has 40 crores record as per the execution plan which i posted earlier.
After that i craeated a range partition of gl_balances and gl_code_optimizations still it is taking same time.
The insert query which run during MV refresh is taking more than 1 hr.
user646034 wrote:If there are no performance issues with the seeded codes then there is nothing you need to do with the tables and you have to tune your MV code. Please make sure you use a supported API to insert/update the data rather than using a direct insert/update.
I already SR with oracle. Oracle say it they does not support as it is a custom MV.