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

Comments

  •  

     

    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
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.