We all know the difference between Primary Index and Secondary Index. However let us see how using either or none in the query can make difference. Primary Index helps in data distribution and data retrieval from the table. Secondary Index provides an alternate path to fetch the data and helps in avoiding full table scan. Primary Index is 1 AMP operation where as Unique Secondary Index is 2 AMP operation. Non-Unique Secondary index is an ALL AMP operation. Also how collect stats can help while using Non Unique Secondary Index. Let us see this with a very simple example. Create a table first. CREATE MULTISETRead More →

One very common question people ask is about possible reasons for long running queries in Teradata. First thing you should understand is Teradata is very good in handling huge volume of data and handling several million or even billion records should not break it. If your query is taking unusual long time then your query needs tuning. Listing down few most common reasons for long running queries in Teradata: Bad Query: This is the most common reason for long running query. Your query is not using any kind of index: Primary Index, Secondary Index or Partition Primary Index. The tables are getting redistributed and thoseRead More →

How to measure Sql Query Performance in Teradata

Before we actually start with SQL Performance Tuning we should know how to measure performance of SQL Query. The most common answer we get is the Query which takes most time is the worst and we should optimize it. This should not hold good for all the cases. There can be various reasons possible for long running queries which we will see later. Now coming back to the question if it is not the real time in sec, min or hours then what should be  the deciding criteria to determine query eligible for SQL optimization? The first thing you should check is AMPCPUTIME.  This is AMPRead More →