Skip to main content

Opimized Merge with a view as a source

  • June 30, 2014
  • 2 replies
  • 19 views

Pe_Leroy
Forum|alt.badge.img
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


2 replies

Siddarth
Forum|alt.badge.img+1
  • Participating Frequently
  • June 30, 2014

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


Pe_Leroy
Forum|alt.badge.img
  • Author
  • Participating Frequently
  • June 30, 2014
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;