I am trying to create a table with both row and column partitioning to see if it will help speed up my queries.
The original table has 500 million rows of data.
When I try to create a partitioned table like this, it runs out of CPU time and does not complete:
CREATE TABLE rtl.SpeedTest4 AS
DATA UNIQUE INDEX (dt, country, product, channel)
PARTITION BY (COLUMN, RANGE_N(dt BETWEEN '2010-01-01' AND '2016-01-01' EACH INTERVAL '7' DAY));
I get the error: "Partitioning is not applicable to a secondary index."
Do I have to create a hash that's unique, and then index on that?
runs out of CPU time and does not complete = workload CPU limit?
Changing the granularity to '7' DAY will not help, it's still sorting the same number of rows.
Columnar will not help, too, as it needs more CPU.
You probably need to change the index order to
It turns on my DBAs don't want us creating COLUMNAR partitions in Teradata, as they have no primary indexes. I'll end this discussion here.