All Forums Database
muthusivan 1 post Joined 03/15
17 Mar 2015
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?

dnoeth 4628 posts Joined 11/04
17 Mar 2015

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)

Dieter

yuvaevergreen 93 posts Joined 07/09
18 Mar 2015

Can you post the ddl and select?.
 
Thanks and regards,
Yuva.

teradatauser2 236 posts Joined 04/12
22 Mar 2015

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

You must sign in to leave a comment.