I currently have a table with about 500 million rows. Each row represents one transaction and includes 10 financial columns. In order to support international business we need to add the ability to support optionally having different currencies for the buyer and the seller -- this means I, in effect, need to have either:
CREATE TABLE SAMPLE_WITH_INLINE_DATA
(
... some non-financial columns ...
-- Note financials are stored in bigints because we store fractional pennies.
financial_column_1 BIGINT NOT NULL,
financial_column_2 BIGINT NOT NULL,
financial_column_3 BIGINT NOT NULL,
financial_column_4 BIGINT NOT NULL,
financial_column_5 BIGINT NOT NULL,
financial_column_6 BIGINT NOT NULL,
financial_column_7 BIGINT NOT NULL,
financial_column_8 BIGINT NOT NULL,
financial_column_9 BIGINT NOT NULL,
financial_column_10 BIGINT NOT NULL,
buyer_financial_column_1 BIGINT,
buyer_financial_column_2 BIGINT,
buyer_financial_column_3 BIGINT,
buyer_financial_column_4 BIGINT,
buyer_financial_column_5 BIGINT,
buyer_financial_column_6 BIGINT,
buyer_financial_column_7 BIGINT,
buyer_financial_column_8 BIGINT,
buyer_financial_column_9 BIGINT,
buyer_financial_column_10 BIGINT,
seller_financial_column_1 BIGINT,
seller_financial_column_2 BIGINT,
seller_financial_column_3 BIGINT,
seller_financial_column_4 BIGINT,
seller_financial_column_5 BIGINT,
seller_financial_column_6 BIGINT,
seller_financial_column_7 BIGINT,
seller_financial_column_8 BIGINT,
seller_financial_column_9 BIGINT,
seller_financial_column_10 BIGINT
) DISTRIBUTE ON {whatever};
-OR-
CREATE TABLE SAMPLE_WITH_CONVERSION_RATES
(
... some non-financial columns ...
-- Note financials are stored in bigints because we store fractional pennies.
financial_column_1 BIGINT NOT NULL,
financial_column_2 BIGINT NOT NULL,
financial_column_3 BIGINT NOT NULL,
financial_column_4 BIGINT NOT NULL,
financial_column_5 BIGINT NOT NULL,
financial_column_6 BIGINT NOT NULL,
financial_column_7 BIGINT NOT NULL,
financial_column_8 BIGINT NOT NULL,
financial_column_9 BIGINT NOT NULL,
financial_column_10 BIGINT NOT NULL,
buyer_currency_conversion_factor BIGINT,
seller_currency_conversion_factor BIGINT
) DISTRIBUTE ON {whatever};
The "_INLINE" table will be larger on the disk, and therefore will incur more i/o to load and process. The "_WITH_CONVERSION_RATES" table will be smaller on the disk (less i/o), but I'll have to do 20 multiplications for each row each time I use it. My experience with other "traditional" DBMS systems leads me to prefer the "_INLINE" option for performance/scale reasons, but I've come to discover that Netezza doesn't really resemble "traditional" DBMS systems when it comes to issues of scale.
I've done some benchmarking on my Netezza box and, frankly, I cannot detect much difference in response time between the two approaches. I thought I'd ask here to see what best practices have fallen out of everyone's experience with this sort of issue.
