Partitioning by row and column

Database
Enthusiast

Partitioning by row and column

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 

(

SELECT

t1.*

FROM

rtl.SpeedTest3 t1

WHERE dt BETWEEN '2010-01-01' AND '2016-01-01'

) WITH DATA PRIMARY INDEX (dt, country, product, channel)

PARTITION BY (

RANGE_N(dt BETWEEN '2010-01-01' AND '2016-01-01' EACH INTERVAL '1' DAY));

Should I change the interval to '7' DAY?

Also, how do I partition on columns as well?

When I try this:

DATA UNIQUE INDEX (dt, country, product, channel)

PARTITION BY (COLUMNRANGE_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?

Tags (1)
2 REPLIES
Senior Apprentice

Re: Partitioning by row and column

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

PARTITION BY (COLUMN, RANGE_N(dt BETWEEN '2010-01-01' AND '2016-01-01' EACH INTERVAL '7' DAY))

,UNIQUE INDEX (dt, country, product, channel);

But why do you want a USI on those columns? It's just CPU/IO/perm overhead and probbaly never used. And if this combination is unique why it's not defined as UPI in your first create?

Do you need to change the PI, otherwise it should be faster because there's no redistribution needed.

Btw, if you run TD15.10 there are additional options for Columnar tables like Primary AMP Index...
Enthusiast

Re: Partitioning by row and column

Thanks Dieter,

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.