vertica split column to rows take forever

Theo

I have a long column in Vertica need split into rows. I have below SQL to do split on COL column

 

create table TEST.TABLE_RES as
select ID, index, SPLIT_PART(COL, ' ', index) as res
from TEST.TABLE
, (SELECT ROW_NUMBER() OVER () AS index
FROM TEST.TABLE limit 6000) row_nums
WHERE SPLIT_PART(COL, ' ', index) <> ''

;

 

It can do split quite fast on small number rows. But there are lots data in the table, the query could not finished. Is there any optimization I could take to get the query finish? 

K_Krutiy

 

 

ROW_NUMBER() OVER () will force vertica to push all data to single node so that node will do generation of unique row numbers.

 

Try to generate row number in some other way. Not in query.

 

May be use IDENTITY column in your destanation table.