vertica split column to rows take forever
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
, (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?