Understand how partitioning improves query performance using Teradata EXPLAIN

Today, we will see if adding a partition-by clause improves our SELECT queries in Teradata.

Let us go through the example below:

DATABASE RETAIL;

SELECT * FROM CUSTOMER;


ALTER TABLE CUSTOMER ADD JOINING_DT DATE FORMAT 'YYYY-MM-DD';

UPDATE CUSTOMER SET JOINING_DT = '2015-02-01' WHERE CUST_ID = 1;
UPDATE CUSTOMER SET JOINING_DT = '2015-05-01' WHERE CUST_ID = 201;

/* lET US RUN A SELECT FOR A PARTICULAR MONTH OF THE YEAR */

SELECT * FROM CUSTOMER WHERE JOINING_DT BETWEEN '2015-01-01' AND '2015-01-31';

/* "  1) First, we lock a distinct RETAIL.""pseudo table"" for read on a"
     RowHash to prevent global deadlock for RETAIL.CUSTOMER.
"  2) Next, we lock RETAIL.CUSTOMER for read."
  3) We do an all-AMPs RETRIEVE step from RETAIL.CUSTOMER by way of an
     all-rows scan with a condition of ("(RETAIL.CUSTOMER.JOINING_DT <=
     DATE '2015-01-31') AND (RETAIL.CUSTOMER.JOINING_DT >= DATE
"     '2015-01-01')"") into Spool 1 (group_amps), which is built locally"
     on the AMPs.  The size of Spool 1 is estimated with no confidence
     to be 2 rows (102 bytes).  The estimated time for this step is
     0.03 seconds.
*/

As we see in STEP-3, the SELECT with range based WHERE results in a Full Table Scan. This is not a great way to query large tables.

Let us explore the alternate approach.

CREATE TABLE CUSTOMER_1
(
CUST_ID INTEGER
,CUST_NAME VARCHAR(30)
,JOINING_DT DATE FORMAT 'YYYY-MM-DD'
)
PRIMARY INDEX(JOINING_DT)
PARTITION BY (RANGE_N(
JOINING_DT BETWEEN '2014-01-01' AND '2014-12-31' EACH INTERVAL '1' YEAR
,'2015-01-01' AND '2015-12-31' EACH INTERVAL '1' MONTH
, NO RANGE OR UNKNOWN))
;

We insert the same data as was present in Customer table.

INSERT INTO CUSTOMER_1 SELECT * FROM CUSTOMER;

SELECT * FROM CUSTOMER_1 WHERE JOINING_DT BETWEEN '2015-01-01' AND '2015-01-31';

/* IF WE RERUN THE QUERY ON CUSTOMER TABLE ON THE NEW PARTIIONED TABLE THE EXPLAIN CHANGES AS BELOW

"  1) First, we lock a distinct RETAIL.""pseudo table"" for read on a"
     RowHash to prevent global deadlock for RETAIL.CUSTOMER_1.
"  2) Next, we lock RETAIL.CUSTOMER_1 for read."
  3) We do an all-AMPs RETRIEVE step from a single partition of
     RETAIL.CUSTOMER_1 with a condition of (
     "(RETAIL.CUSTOMER_1.JOINING_DT <= DATE '2015-01-31') AND
     (RETAIL.CUSTOMER_1.JOINING_DT >= DATE '2015-01-01')") into Spool 1
"     (group_amps), which is built locally on the AMPs.  The size of"
     Spool 1 is estimated with low confidence to be 4 rows (204 bytes).
     The estimated time for this step is 0.03 seconds.

*/

As we see now that the partition is leveraged when the partition column is used in the WHERE clause of a SELECT query. This results in faster query execution.

Join us in the classroom for more stimulating sessions on Teradata.

Amazing Interview Question - Round 3 - Solved using Teradata Reset when functionality

The below scenario was given to calculate the series of ball_nos for which the highest no. of dot balls were bowled.

/* Amazing Interview round 3 */

create multiset table retail.cricket
(
Ball_no integer,
Runs_scored varchar(5)
) primary index(ball_no);

database retail;

insert into cricket  values(1,1);
insert into cricket  values(2,2);
insert into cricket  values(3,0);
insert into cricket  values(4,0);
insert into cricket  values(5,0);
insert into cricket  values(6,4);
insert into cricket  values(7,0);
insert into cricket  values(8,0);
insert into cricket  values(9,'W');
insert into cricket  values(10,1);
insert into cricket  values(11,0);
insert into cricket  values(12,0);
insert into cricket  values(13,0);
insert into cricket  values(14,1);
insert into cricket  values(15,4);

select * from cricket;

Dot balls are balls where 0 runs are scored

alter table cricket add modified_ball_no integer;
drop table cricket_modifed;
create table cricket_modified
as
(
select ball_no
,runs_scored
, row_number() over(partition by runs_scored order by ball_no
reset when ball_no - 1 > /* this statement gets the ball no of last row and resets the rank when the balls are not consecutive */
sum(ball_no) over(partition by runs_scored order by ball_no rows between 1 preceding and 1 preceding)) as rnk
from cricket
) with data
primary index(ball_no)
;

/* Maximum consecutive dot balls */
select (ball_no - max(rnk) + 1) as dot_ball_start , ball_no, rnk as count_of_dot_balls from cricket_modified where rnk in (select max(rnk) from cricket_modified)
group by ball_no, rnk;

The series of balls where only 0 runs were scored consecutively:

Dot Balls using Teradata Reset function

Like us on Google+, Twitter or Facebook if this post was helpful. For classroom/online training, call at the number given at the top. Mention the Discount code for offers.

Find the combination of strings in all orders and delete the duplicate records - Amazing Interview Question

This was a question posted to me in the in-the premises round of a leading E-commerce company.

Problem; Find the count of strings  and their combinations in a table, and count all the combination of letters as duplicates.

If AB is a string, then BA is a duplicate string. And count(AB) should be 2.


The Solution attempted by me is as follows:

create table table1
(
value1 varchar(30));

Now , in the list below, AB and BA are treated as duplicates.
Similarly, 'CD' and 'DC' are duplicates.
We need to find the count of all combinations possible for 'AB' and 'CD' .


insert into table1 values ('AB');
insert into table1 values ('BA');
insert into table1 values ('CD');
insert into table1 values ('DC');

select * from table1;

select A.value1,count(*) from table1 as t1
inner join
(
select
case when value1 > value_ref then value_ref else value1 end as value1
, value_ref
, value1 as val1

from
(
select value1, concat(substr(value1,2,1),substr(value1,1,1)) as value_ref from table1
group by value_ref
) as A) as A
 on t1.value1 = A.value_ref
group by A.value1;

The trick is to create a lookup with all the values in the table.
The inner query does this trick as shown below:

Lookup table of values and their combination

Result set will be:

Count of all combinations possible

Write your solution in the comment section.

Joins in Teradata - TD join strategies and the join confidences

Teradata internally joins all tables as AMP-local joins. So, even if the data lies in separate AMPs they will re-distributed based on some metadata information stored in the DBC tables. Redistribution is performed to make the joins behave like AMP-local joins.

Now, the join strategy can be defined by many factors. A few frequently occurring confidence levels can tell you about the join performances.

Index Join confidence: 2 tables are joined based on a column A, A being an index in TableA. If the join mentions A=B and column B is neither an index, nor has stats collected, we get a index join confidence. So the optimizer knows that one side is an index, but the confidence is low since information about the other column (B) is not available. This is better than a no confidence. 
Index join confidence will lead to data being read using the index sub tables.

Low confidence: Teradata has partial information about the column demographics and hence the confidence is low. Collect stats on the column will lead to higher confidence.

High Confidence: If all the columns in the where clause or the join condition have updated statistics information, then Teradata optimizer can predict the space and time required to complete each step with confidence. This is the best confidence level.

The most common join strategies are:

• Product Join
• Merge Join
• Exclusion Join
• Hash Join
• Nested Join

Each join strategy has its own pros and cons, and it's hard to say which one is the best, depending on different circumstances. The optimizer will choose the best join strategy based on data demographics, statistics and indexes if any of them are available. Using EXPLAIN can help find out what join strategies are to be adopted.

No matter which join strategy, it is always applied between two tables. The more tables, the more join steps. Rows must be on the same AMP to be joined. So row distribution or duplication is unavoidable for some join strategies.

1. Product Join

This is the most basic and straightforward join strategy. In order to find a match between two tables with a join condition which is not based on equality (>, <, <>), or join conditions are ORed together.

The reason why we call it "Product" join is that, the number of comparisons required is the "product" of the number of rows of both tables. For example, table t1 has 10 rows, and table t2 has 25 rows, then it would require 10x25=250 comparisons to find the matching rows.

When the WHERE clause is missing, it will cause a special product join, called Cartesian Join or Cross Join, which will return all the combination of rows from both tables. In the above example, 250 rows will be returned as the result.

It is referred to as Nested-loops Join by vendors like IBM and Oracle, which also makes sense, when mapping it to the algorithm.

2. Merge Join

This is a much more efficient join strategy. It is adopted when the join conditions are based on equality (=). There is a prerequisite though: the two tables must be sorted based on the join column in advance (actually it's sorted based on the join column row hash sequence). That's why Oracle calls it Sort-Merge Join. That brings a great advantage for this type of join: both tables only need to be scanned once, in an interleaved manner.

Merge join is not necessarily always better than product join, due to the fact that sorting is required. If both tables are huge, sorting can be a tremendous effort.

3. Exclusion Join

This join strategy is used to find non-matching rows. If the query contains "NOT IN" or "EXCEPT", exclusion join will be picked. As a matter of fact, this kind of join can be done as either Merge Join or Product Join.

One thing worth noticing: exclusion merge join is based on set subtraction operation, and a three-value logic (TRUE, FALSE, UNKNOWN) will be used when comparisons is done on nullable columns (or temporary result set).

4. Hash Join

Hash Join gets its name from the fact that one smaller table is built as "hash-table", and potential matching rows from the second table are searched by hashing against the smaller table.

Usually optimizer will first identify a smaller table, and then sort it by the join column row hash sequence. If the smaller table is really small and can fit in the memory, the performance will be best. Otherwise, the sorted smaller table will beduplicated to all the AMPs. Then the larger table is processed one row at a time by doing a binary search of the smaller table for a match.

Hash Join is also based on equality condition (=).

5. Nested Join

Don't get confused with "Nested-loops Join", which is the term used by Oracle, IBM and Microsoft. In Teradata, Product Join is the counterpart of "Nested-loops Join" in other RDBMS.

However, Nested Join can be seen as an enhanced version of the common "Nested-loops Join", where Teradata takes advantage of its index structure. In order to make Nested Join picked, the following conditions must be satisfied:
  1) The join condition is based on equality;
  2) The join column is a unique index on one table;
  3) The join column is any index on another table.

Based on conditions above it is not hard to infer how Nested Join works. First only one single row will be retrieved from one table with the help of the unique index, and then based on the row hash of that row, another table is accessed by some index.

Nested Join is the most efficient join method in Teradata. It is also the only join method that don't always use all the AMPs.

Recursive query in Teradata - Definition and Example using the WITH RECURSIVE keyword

Recursive Queries use the SEED query to iterate over the RECURSIVE block until the block is empty. 

Please go through the example below to understand the implementation.

/* Create a table to hold the data */

create multiset table financial.employee

(emp_id integer,

mgr_id integer,

ename varchar(50)

)primary index(emp_id);

insert into financial.employee(2000,,'Oliver');
insert into financial.employee(2001,2000,'tom');
insert into financial.employee(2002,2001,'santosh');
insert into financial.employee(2003,2001,'preeti');
insert into financial.employee(2004,2001,'sree');
insert into financial.employee(2005,2001,'tapas');
insert into financial.employee(2006,2001,'mani');
insert into financial.employee(2007,2002,'prabhu');

select * from financial.employee;

database financial;

As we see in the above data, each employee has a manager assigned to him/her. If the employee with id 2007 wants to query the table to know his managers (Level 1, Level 2 and so on), he should use a recursive query.

The RECURSIVE block (not in bold) will be executed and a row will be inserted in mgr_tbl( the recursive table). The seed is for employee 'Prabhu'. The iteration will continue till the time all the depth are not covered.

/* Let us now write a recursive query to find all the employee manager ladders */

/* The SEED part is marked in bold. The recursive part is executed till the time it returns rows */

WITH RECURSIVE mgr_tbl(emp_id, mgr_id, mgr_name, depth) AS

(

  SELECT a.emp_id, a.mgr_id, a1.ename as mgr_name, 1 as depth

  FROM  employee a

  inner join employee a1

  on a.mgr_id = a1.emp_id

  where a.emp_id = 2007

  UNION ALL

  SELECT mgr_tbl.emp_id, a.mgr_id, a1.ename

  ,mgr_tbl.depth+1

  FROM   mgr_tbl inner join employee a

  on mgr_tbl.mgr_id = a.emp_id

  inner join

  employee a1

  on a.mgr_id = a1.emp_id

  ---WHERE  a.mgr_id = b.emp_id

)

select * from mgr_tbl;

Result will be as follows:

 

The Depth represents Level of each Manager for employee id 2007 using recursive query

Please like us on Facebook or Google+ if you like our post. Leave your comments below.