Teradata tip of the day 05/19

A simple tip for today:

Not sure if the stats have been collected well? Is your query performing badly inspite of tuning?
Run the command below and then run EXPLAIN plan on that query:
DIAGNOSTIC HELPSTATS ON FOR SESSION;

You will see suggestions from the optimizer on what columns stats need to be collected, at the end of EXPLAIN plan. See an example below:

     BEGIN RECOMMENDED STATS ->
  8) "COLLECT STATISTICS working.DW_KWDM_PRTNR_MPX_REV_W INDEX (KW ,ROTATION_ID ,PRTNR_CREATIVE_ID ,PRTNR_PRDCT_ID ,FTR_ITEM_ID ,CLICK_DT)".  (HighConf)

  9) "COLLECT STATISTICS working.DW_KWDM_PRTNR_MPX_REV_W COLUMN (TRANS_DT ,CNTRY_ID)".  (HighConf)
 10) "COLLECT STATISTICS working.DW_KWDM_PRTNR_MPX_REV_W COLUMN (CONTENT_MATCH_YN_ID ,TRANS_DT ,CNTRY_ID)".  (HighConf)
     <- END RECOMMENDED STATS

Similarly,

To get a more detailed explain plan, run the command below before executing EXPLAIN:
DIAGNOSTIC VERBOSEEXPLAIN ON FOR SESSION;

~Meena

Teradata tip of the day 05/18

Today's find:
Doing modulus to split digits: This is a simple query, worked well in 'SELECT' clause. But gave out error when Volatile table was created out of it:

A sample of SELECT that ran fine:
SELECT
6789 a
,MOD((a/1000),10) (INT) AS BIT_POS1
, MOD((a/100),10) (INT) AS BIT_POS2
, MOD((a/10),10) (INT) AS BIT_POS3
, MOD(a,10) (INT) AS BIT_POS4;

Volatile table that gave out error:
CREATE VOLATILE TABLE DIGIT_VT AS
(
SELECT
6789 a
,MOD((a/1000),10) (INT) AS BIT_POS1
, MOD((a/100),10) (INT) AS BIT_POS2
, MOD((a/10),10) (INT) AS BIT_POS3
, MOD(a,10) (INT) AS BIT_POS4;
) WITH DATA
PRIMARY INDEX(a)
ON COMMIT PRESERVE ROWS;

Modified format:
CREATE VOLATILE TABLE DIGIT_VT AS
(
SELECT
6789 a
,((a/1000) MOD 10) (INT) AS BIT_POS1
,((a/100) MOD 10) (INT) AS BIT_POS2
,((a/10) MOD 10) (INT) AS BIT_POS3
,(a MOD 10) (INT) AS BIT_POS4
) WITH DATA
PRIMARY INDEX(a)
ON COMMIT PRESERVE ROWS;

Note: Method1 didn’t work when selected from derived table/subquery as well :(

~Meena

Teradata tip of the day

Hi Friends,

I thought of sharing some of the nuances I learn in teradata as a part of my project with you.
Hope this initiative helps :)

While exploring on how to convert hexadecimal value to interger and viceversa, my team was not able to find a direct function on Teradata.

This is the alternative we used (a hack :)) -

SELECT
  1234567890 AS x

 ,SUBSTRING('0123456789ABCDEF' FROM x / (16**7) MOD 16 + 1 FOR 1) ||
  SUBSTRING('0123456789ABCDEF' FROM x / (16**6) MOD 16 + 1 FOR 1) ||
  SUBSTRING('0123456789ABCDEF' FROM x / (16**5) MOD 16 + 1 FOR 1) ||
  SUBSTRING('0123456789ABCDEF' FROM x / (16**4) MOD 16 + 1 FOR 1) ||
  SUBSTRING('0123456789ABCDEF' FROM x / (16**3) MOD 16 + 1 FOR 1) ||
  SUBSTRING('0123456789ABCDEF' FROM x / (16**2) MOD 16 + 1 FOR 1) ||
  SUBSTRING('0123456789ABCDEF' FROM x / (16**1) MOD 16 + 1 FOR 1) ||
  SUBSTRING('0123456789ABCDEF' FROM x / (16**0) MOD 16 + 1 FOR 1) AS Int2HexString

 ,Int2HexString as s

 ,POSITION(SUBSTRING(s FROM 8 FOR 1) IN '123456789ABCDEF') * ((16**0) (INT)) +
  POSITION(SUBSTRING(s FROM 7 FOR 1) IN '123456789ABCDEF') * ((16**1) (INT)) +
  POSITION(SUBSTRING(s FROM 6 FOR 1) IN '123456789ABCDEF') * ((16**2) (INT)) +
  POSITION(SUBSTRING(s FROM 5 FOR 1) IN '123456789ABCDEF') * ((16**3) (INT)) +
  POSITION(SUBSTRING(s FROM 4 FOR 1) IN '123456789ABCDEF') * ((16**4) (INT)) +
  POSITION(SUBSTRING(s FROM 3 FOR 1) IN '123456789ABCDEF') * ((16**5) (INT)) +
  POSITION(SUBSTRING(s FROM 2 FOR 1) IN '123456789ABCDEF') * ((16**6) (INT)) +
  POSITION(SUBSTRING(s FROM 1 FOR 1) IN '123456789ABCDEF') * ((16**7) (INT)) AS HexString2Int
;

Try it out. Also, if you guys know any direct method, let me know.

I keep this initative to a closed group of friends working in DW, so we can share each other stuff we know and broaden our knowledge on TD :)

This chain can be used for learning any new technical info.

~Meena