All Forums Database
ciw916 8 posts Joined 11/12
11 Dec 2013
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) ;

Qaisar Kiani 337 posts Joined 11/05
11 Dec 2013

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?

M.Saeed Khurram 544 posts Joined 09/12
11 Dec 2013

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

Sun_shine_jgd 39 posts Joined 07/13
12 Dec 2013

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

You must sign in to leave a comment.