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

Database
N/A

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
N/A

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
N/A

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
N/A

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