What is the use of Partition By function while inserting data into table.

Database
Fan

What is the use of Partition By function while inserting data into table.

The query looks like this.

insert into ABC(
ATRBT47_VARCHR_UNI,
ATRBT48_VARCHR_UNI,
)
select
ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY 1), CPNT_COMPLTN_DT FROM
LCD_MYLEARNING_S;

Please let me know what use of PARTITION BY 1 in the above query, as the result of the select query gets inserted into table, where Teradata loads data into randomly across all the AMPs.

Regards
Jang
3 REPLIES
Junior Contributor

Re: What is the use of Partition By function while inserting data into table.

Hi Jang,
this is a totally stupid way to create a kind of random, but sequential value :-)

Stupid, because all the rows will probably be processed by a single AMP:
ORDER BY 1 is *not* ordering by column 1, but by the value 1.
PARTITION BY 1 is exactly the same as no partition at all.

If you really need a that random sequence then you might use:
select
ROW_NUMBER() OVER (ORDER BY rand), dt.*
FROM
(select
RANDOM(-2000000000,2000000000) AS rand,
CPNT_COMPLTN_DT
from LCD_MYLEARNING_S
) as dt;

Otherwise choose partitioning and ordering columns as appropriate.

Dieter
Fan

Re: What is the use of Partition By function while inserting data into table.

Hi S being,

Please let me know what is the use of Over( Partition by col1, order by col1) while inserting the answer set for this query is inserted into a table.

Thanks in advance.

regards
jang
Junior Contributor

Re: What is the use of Partition By function while inserting data into table.

Hi Jang,
Over( Partition by col1, order by col1) will create a kind of randomly assigned sequence number for each col1 value.

Simply repeatedly submit the select part of that query and see how the numbering changes.

Dieter