CODEWITHSUNDEEP
sqlnetezza
TheGoat
TheGoat

Reputation: 2877

Split table in Netezza using count of rows

I have a table in Netezza that is 100,000 rows by 17 columns.

I wish to create 10 separate views where each view slices the original table into 10000 x 17 chunks.

I have read about the row_number function but I don't want to partition my original table using a particular column(s).

How can I create such a view in Netezza?

Upvotes: 0

Views: 446

Answers (1)

Lars G Olsen
Lars G Olsen

Reputation: 1118

What is the purpose of this ‘split’?

I suppose you want the 17 chunks to be roughly the same size, so depending on your purpose I’ll suggest these two approaches using different ‘build in’ columns that exist on all Netezza tables:

1) select * from TABLE where hash(rowid) % 17 = 0 —- replace thE number zero by all other numbers 1,2,3,4..16

2) select * from TABLE where datasliceid in (select dslice from _v_dual_dslice where dslice %17 = 0) —- replace thE number zero by all other numbers 1,2,3,4..16

The second option (if I remember the column names correctly) may outperform the first, but the first may give a more even ‘bucket size’

Upvotes: 1

Related Questions