On Commit Preserve Rows taking much time

Database

On Commit Preserve Rows taking much time

A query which runs fine and fetches around 30k records in 15 seconds. If I create a Temp table using  "On commit  preserve row" using that query, takes more time(15 mins)

Any idea why this would happen?

3 REPLIES
Junior Contributor

Re: On Commit Preserve Rows taking much time

It's always the same reason:

The table is created as a SET table plus a bad Primary Index (usually no PI defined, so defaulting to first column)

Enthusiast

Re: On Commit Preserve Rows taking much time

Can you post the ddl and select?.

Thanks and regards,

Yuva.

Enthusiast

Re: On Commit Preserve Rows taking much time

You can use below query to check data distribution on every amp. You would notice that data will not be uniformly distributed, so it takes time to create this volatile table.

sel hashamp(hashbucket(hashrow(your PI column))) as #amp,count(*)

from your_table_name

group by 1

order by 2 desc;

--Samir