Recently Raghav reached out to us on our Facebook page asking for a SQL query in Teradata to identify the highest AMPCPU, I/O and SPOOL usage for each hour during a given time period. He was actually working on some activity to find out the consumption of resources on Teradata box on hourly basis. The purpose was to find out time range when Teradata is most heavily loaded and when it is relatively free. He was looking for information in…Continue Reading
How Teradata distribute rows ? It is very important to understand how Teradata actually distribute rows among AMPs. We all know that PRIMARY INDEX is used for data distribution. All the AMPs in the Teradata system maintains a portion of each table and this forms the basis of ‘parallel architecture and share nothing‘ architecture of Teradata. We will see how exactly it happens and what all hash functions are used to determine the respective AMP which will hold the table.…Continue Reading
Do you want to learn Teradata however have no clue how to start and what all to read then this post is for you. We receive so many requests from our blog readers asking if we can help them in learning Teradata. And when we ask what exactly they want to learn or any specific topic in Teradata they are looking for then they go mum. Understanding all the aspects of TERADATA RDBMS is not simple and definitely requires good…Continue Reading
We so often get the requirement to show numbers in some specific format or to add leading zeros if the length is short. Let us see an example: CREATE MULTISET VOLATILE TABLE vt_add_zeros ( COL1 INTEGER NOT NULL ) PRIMARY INDEX(COL1) ON COMMIT PRESERVE ROWS; INSERT INTO vt_add_zeros SEL 1; INSERT INTO vt_add_zeros SEL 10; INSERT INTO vt_add_zeros SEL 100; INSERT INTO vt_add_zeros SEL 1000; Now the data may look like: 1 10 100 1000 However if the requirement is…Continue Reading
You can build Views over Table or Tables or some other views. To see the view definition just use the SHOW command. Syntax: SHOW VIEW VIEW_NAME; Now if the view is built on several views and it is taking multiple SHOW commands to identify and reach to base tables then use below query to see all the underlying VIEW definitions. Syntax SHOW SELECT * FROM VIEW_NAME; This will list down all the intermediate view definitions till the base tables provided…Continue Reading
One of the most common problem faced by people working on Databases is :
TO INSERT RECORDS IN A TABLE ONLY IF IT IS A NEW RECORD ELSE UPDATE THE EXISTING RECORD.
Many people give different possible solution for it. Some even suggests using some inbuilt functions specific to some Databases. However I thought of sharing a generic method which can be easily applied to any database and without any dependency.
The easiest way to implement INSERT RECORDS ONLY IF IT IS NEW ELSE UPDATE is by adding new column name FLAG which will help in determining whether we need to insert record or update it.
A query implementing it in a general form can be: