Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Opimized Merge with a view as a source

Hi,

I am facing an issueswhen tentatively writing an Optimized Merge query as described in 7.0 manual.
I follow the requirements as stated in the doc:
Conditions for an Optimized MERGE
HP Vertica prepares an optimized query plan when all of the following conditions are true:
  • The target table's join column has a unique or primary key constraint
  • UPDATE and INSERT clauses include every column in the target table
  • UPDATE and INSERT clause column attributes are identical
But my source is a view.

And when I run my request, with or without EXPLAIN, I have following error :

[Vertica][VJDBC](3594) INTERNAL: Internal Optimizer Error (11) [Vertica][VJDBC]Detail: !isView(rte->relid)

Are views not supported as source of the merge?
Is there another workaround to make it work?

Tx in advance,
Br


Comments

  • Hi Pe, Views are supported as source in Merge. You have hit a bug. Can you give the exact scenario?

  • The MERGE is working ok when I am not in the conditions of the optimized merge.
    But as soon as I make additional changes to be under these conditions, the error occurs.


    Here is my merge request, which is working fine:
    MERGE INTO FCT_SESSIONS dest         
    USING FCT_SESSIONS_VIEW src
    ON (src.IMSI= dest.IMSI AND src.CHARGING_ID=dest.CHARGING_ID AND src.SESSION_ID=dest.SESSION_ID)
    WHEN MATCHED THEN 
    UPDATE SET 
    ...
    TOTAL_VOLUME_DOWN=src.TOTAL_VOLUME_DOWN,
    TOTAL_VOLUME_UP=src.TOTAL_VOLUME_UP
    WHEN NOT MATCHED THEN
     INSERT VALUES (
    src.IMSI,
    src.CHARGING_ID,
    src.SESSION_ID,
    ...
    src.TOTAL_VOLUME_DOWN,
    src.TOTAL_VOLUME_UP);



    This one is rejected: 
    MERGE INTO FCT_SESSIONS dest         
    USING FCT_SESSIONS_VIEW src
    ON (src.IMSI= dest.IMSI AND src.CHARGING_ID=dest.CHARGING_ID AND src.SESSION_ID=dest.SESSION_ID)
    WHEN MATCHED THEN 
        UPDATE SET  
    IMSI = src.IMSI,
    CHARGING_ID = src.CHARGING_ID,
    SESSION_ID = src.SESSION_ID,
    .... (same as above)
    TOTAL_VOLUME_DOWN=src.TOTAL_VOLUME_DOWN,
    TOTAL_VOLUME_UP=src.TOTAL_VOLUME_UP
    WHEN NOT MATCHED THEN
    INSERT VALUES (
    src.IMSI,
    src.CHARGING_ID,
    src.SESSION_ID,
    ....
    src.TOTAL_VOLUME_DOWN,
    src.TOTAL_VOLUME_UP);


    My view:
    CREATE OR REPLACE VIEW FCT_SESSIONS_VIEW AS SELECT SESSION_ID, CHARGING_ID, IMSI, TOTAL_VOLUME_DOWN,TOTAL_VOLUME_UP FROM
     (SELECT
    row_number() OVER w_stop AS rank_0,
    SESSION_ID,
        CHARGING_ID,
    IMSI,
    ....
    TOTAL_VOLUME_DOWN,
    TOTAL_VOLUME_UP
    FROM MBBQOE.S_SESSION 
      WHERE  IMSI <> 'UNKNOWN'
      WINDOW
    w_stop AS (PARTITION BY SESSION_ID,CHARGING_ID,IMSI ORDER BY STOP_TIME DESC),
    w_start AS (PARTITION BY SESSION_ID,CHARGING_ID,IMSI ORDER BY START_TIME) 
      ORDER BY SESSION_ID,CHARGING_ID,IMSI 
    ) ranked
    WHERE rank_0 = 1;

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.