The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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
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? 




    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.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file