Skewed Query

Database
Enthusiast

Skewed Query

I seem to be having trouble with a query that I inherited from another developer.  I'm told this is running a 99% skew. Would anybody be able to tell me what looks so skewed about this table?

create table ntl_prd_qmtmptbls.calls_tz_cw as (

select

cust_id,

cust_line_seq_id,

acss_call_dt,

call_start_tm,

call_end_tm,

acss_call_ctr_time_zn,

acss_call_id,

call_start_dttm,

call_end_dttm,

acss_user_id,

--cast (format dh.datekey as date 'yyyy-mm-dd') as datekey_formatted,

CASE

WHEN c.acss_call_ctr_time_zn = 'mst' and dh.isindaylightsavings = '1' THEN c.call_start_dttm + interval '3' hour

WHEN c.acss_call_ctr_time_zn = 'mst' and dh.isindaylightsavings = '0' THEN c.call_start_dttm + interval '2' hour

WHEN c.acss_call_ctr_time_zn LIKE 'H%' and dh.isindaylightsavings = '1' THEN c.call_start_dttm + interval '5' hour

WHEN c.acss_call_ctr_time_zn LIKE 'H%' and dh.isindaylightsavings = '0' THEN c.call_start_dttm + interval '4' hour

WHEN c.acss_call_ctr_time_zn = 'mdt' THEN c.call_start_dttm + interval '2' hour

WHEN c.acss_call_ctr_time_zn LIKE 'C%' THEN c.call_start_dttm + interval '1' hour

WHEN c.acss_call_ctr_time_zn LIKE 'P%' THEN c.call_start_dttm + interval '3' hour

ELSE c.call_start_dttm END acss_call_start_dttm_est,

CASE

WHEN c.acss_call_ctr_time_zn = 'mst' and dh.isindaylightsavings = '1' THEN c.call_end_dttm + interval '3' hour

WHEN c.acss_call_ctr_time_zn = 'mst' and dh.isindaylightsavings = '0' THEN c.call_end_dttm + interval '2' hour

WHEN c.acss_call_ctr_time_zn LIKE 'H%' and dh.isindaylightsavings = '1' THEN c.call_end_dttm + interval '5' hour

WHEN c.acss_call_ctr_time_zn LIKE 'H%' and dh.isindaylightsavings = '0' THEN c.call_end_dttm + interval '4' hour

WHEN c.acss_call_ctr_time_zn = 'mdt' THEN c.call_end_dttm + interval '2' hour

WHEN c.acss_call_ctr_time_zn LIKE 'C%' THEN c.call_end_dttm + interval '1' hour

WHEN c.acss_call_ctr_time_zn LIKE 'P%' THEN c.call_end_dttm + interval '3' hour

ELSE c.call_end_dttm END acss_call_end_dttm_est

from ntl_prd_qmtmptbls.calls_cw c

join ntl_prd_qmtbls.date_help dh

on c.acss_call_dt = cast(datekey as date format 'YYYYMMDD')

) with data primary index (cust_id, cust_line_seq_id) ;

Tags (3)
3 REPLIES
Enthusiast

Re: Skewed Query

The index of the target table seems to be different and could be very non-unique.

Check the uniqueness of the columns for the target table you identified as PI, how much non-unique they are?

Enthusiast

Re: Skewed Query

Hi,

Can you please share the definitions of both the source tables? Or at least the Primary index information? 

Also share the row count of both the tables. 

Khurram
Teradata Employee

Re: Skewed Query

use the below query and identify the PI has skew or now. Or when u join whe column which is getting redistributed or duplicated has skew or not.

sel hashamp(hashbucket(hashrow(<PI columns>))), count(*) from table

group by 1

-Aravind