Query Performance

Database
Enthusiast

Query Performance

Hi All,

When i run my query(insert/sel), performance of the query on all joining tables are fine. but the problem is while loading to the target table it is taking more time.

Below is the step in the Explain plan takes time:

"We do a MERGE into TABLE table_A FROM spool 14755."

My target table has NUPI defined on one column, there is no duplicates in that column. So i don think Table skew will be the problem here.

Can anybody pls suggest me, what must be the reason for this and how can i improve the performance....

Thanks,
Sen

3 REPLIES
Enthusiast

Re: Query Performance

Is the target table a SET table?

The overhead if NUPI is defined on a SET table is , it will have check all the columns of a row for duplicates; which will have an impact on performance.

One way out is , you can define the table as MULTISET;

Thanks,

Shiyamala
Enthusiast

Re: Query Performance

Thanks a lot Shiyamala,

I changed target table to MULTISET table, now the query works fine
Enthusiast

Re: Query Performance

Hi All,

Can anyone pls clarify, how about the performance of update query on SET table with NUPI defined on it? will this impact or not..?