Netezza — Optimising Data Types
“I want my system to work faster”.
How many times have we heard this? Well, some have the approach of adding more resources: KIWI (Kill It With Iron)
But Netezza is so fast already as our loved song below says - “[…] Faster with Blaaaazing Speeeeed”!
(Couldn’t resist myself from posting this video)
In my case I am the kind of person that loves to explore ways on making the current system faster by going through the usual best practices. Yet Netezza is really fast out of the box you sometimes want to make sure to get the most out of it.
One of them is using Zone-mappable INT data types instead of NUMERIC(x,0) which does not have them out of the box.
For this I usually create the following view that tells me the candidate columns for each table to perform INT optimisation.
-- OPTIMIZE DATATYPESCREATE OR REPLACE VIEW OPTIMIZE_INTEGERS AS
SELECT TABLE_CATALOG,
TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
CASE
WHEN NUMERIC_PRECISION between 1 and 2 THEN 'BYTEINT'
WHEN NUMERIC_PRECISION between 3 and 4 THEN 'SMALLINT'
WHEN NUMERIC_PRECISION between 5 and 9 THEN 'INTEGER'
WHEN NUMERIC_PRECISION between 10 and 18 THEN 'BIGINT'
END OPTIMAL_DATATYPE
FROM COLUMNS
WHERE TABLE_CATALOG = CURRENT_CATALOG
AND NUMERIC_PRECISION <= 18
AND NUMERIC_SCALE = 0;
Usually the main reason behind using NUMERIC(x,0) is just that developers ported it from other databases DML (and not because of a strict requirement in the number of digits) so must of the times the move is a win for them.
Another option that works really well if you don’t want to mess around is to have the relevant columns used in your queries as the organising key of a CBT (Clustered Base Table), this will also add the first 8 bytes of [N]CHAR/[N]VARCHAR/ data and it will also add Zonemaps not only to NUMERIC but also to FLOAT,DOUBLE and BOOLEAN datatypes!
And finally if you are a fan of MVs (Materalized Views) the same applies for the columns used in the ORDER BY.
