We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


Opimized Merge with a view as a source — Vertica Forum

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