Why JOIN implies in MERGE_CONVERTED_TO_UNION
I have three tables, each with 175 mi of lines each one, two tables has two columns (col_id_1 and col_id_2) that together forms the PK and own has only one PK column .(col_id_1).
I want to create a table that is this three tables joined, all tables are sorted by the same KEY (col_id_1 and col_id_2), so I write my SQL as follows:
CREATE TABLE all_together AS
SELECT * FROM TABLE1 T1
JOIN TABLE2 T2 ON T1.col_id_1 = T2.col_id_1 AND T1.col_id_2 = T2.col_id_1
JOIN TABLE2 T3 ON T1.col_id_1 = T3.col_id_1
When I run an EXPLAIN in this query Vertica does not shows a SORT phase, instead of the information is that the inputs are presorted (due to the projections ordered by col_id_1/col_id_2 and col_id_1.
But the query execution gave me an execution event with this description: "Merge converted to union, followed by sort." and event_type "MERGE_CONVERTED_TO_UNION" meaning a SORT on T1
Why vertica are performing a SORT ?
Thanks in advance,