You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@trafodion.apache.org by "Liu, Yuan (Yuan)" <yu...@esgyn.cn> on 2018/11/08 05:40:03 UTC
trafodion_upsert vs trafodion_vsbb_upsert
Hi Trafodioneers,
For table scan, we have trafodion_scan and trafodion_vsbb_scan.
For table delete, we have trafodion_delete and trafodion_vsbb_delete(delete with no rollback).
For upsert, we also support trafodion_upsert and trafodion_vsbb_upsert.
I am trying to upsert data into a table and I get trafodion_vsbb_upsert. But how to get a trafodion_upsert plan? And what's the difference between trafodion_upsert and trafodion_vsbb_upsert?
SQL>explain options 'f' upsert into DMA_ENTTYPE_STAT_30W_P8 select * from DMA_ENTTYPE_STAT;
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------
4 . 5 root x 5.03E+006
2 3 4 tuple_flow 5.03E+006
. . 3 trafodion_vsbb_upser DMA_ENTTYPE_STAT_30W 1.00E+000
1 . 2 sort 5.03E+006
. . 1 trafodion_scan DMA_ENTTYPE_STAT 5.03E+006
Best regards
Yuan
RE: trafodion_upsert vs trafodion_vsbb_upsert
Posted by "Liu, Yuan (Yuan)" <yu...@esgyn.cn>.
Thanks a lot, Sandhya.
Best regards
Yuan
-----Original Message-----
From: Sandhya Sundaresan <sa...@esgyn.com>
Sent: Friday, November 09, 2018 5:50 AM
To: dev@trafodion.apache.org
Subject: RE: trafodion_upsert vs trafodion_vsbb_upsert
Hi Yuan,
VSBB operations are more efficient - they are performed as a set of rows. So in general when there is a set of rows to upsert it'll perform better.
To turn it off and get a trafodion_upsert, you can set CQD INSERT_VSBB 'OFF'; and HBASE_ROWSET_VSBB_OPT 'OFF' for the other operators like select, delete . But in general you don't want to do that. The compiler should pick trafodion_upsert if there is only one row to upsert.
Thanks
Sandhya
-----Original Message-----
From: Liu, Yuan (Yuan) <yu...@esgyn.cn>
Sent: Wednesday, November 7, 2018 9:40 PM
To: dev@trafodion.apache.org
Subject: trafodion_upsert vs trafodion_vsbb_upsert
Hi Trafodioneers,
For table scan, we have trafodion_scan and trafodion_vsbb_scan.
For table delete, we have trafodion_delete and trafodion_vsbb_delete(delete with no rollback).
For upsert, we also support trafodion_upsert and trafodion_vsbb_upsert.
I am trying to upsert data into a table and I get trafodion_vsbb_upsert. But how to get a trafodion_upsert plan? And what's the difference between trafodion_upsert and trafodion_vsbb_upsert?
SQL>explain options 'f' upsert into DMA_ENTTYPE_STAT_30W_P8 select * from DMA_ENTTYPE_STAT;
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------
4 . 5 root x 5.03E+006
2 3 4 tuple_flow 5.03E+006
. . 3 trafodion_vsbb_upser DMA_ENTTYPE_STAT_30W 1.00E+000
1 . 2 sort 5.03E+006
. . 1 trafodion_scan DMA_ENTTYPE_STAT 5.03E+006
Best regards
Yuan
RE: trafodion_upsert vs trafodion_vsbb_upsert
Posted by Sandhya Sundaresan <sa...@esgyn.com>.
Hi Yuan,
VSBB operations are more efficient - they are performed as a set of rows. So in general when there is a set of rows to upsert it'll perform better.
To turn it off and get a trafodion_upsert, you can set CQD INSERT_VSBB 'OFF'; and HBASE_ROWSET_VSBB_OPT 'OFF' for the other operators like select, delete . But in general you don't want to do that. The compiler should pick trafodion_upsert if there is only one row to upsert.
Thanks
Sandhya
-----Original Message-----
From: Liu, Yuan (Yuan) <yu...@esgyn.cn>
Sent: Wednesday, November 7, 2018 9:40 PM
To: dev@trafodion.apache.org
Subject: trafodion_upsert vs trafodion_vsbb_upsert
Hi Trafodioneers,
For table scan, we have trafodion_scan and trafodion_vsbb_scan.
For table delete, we have trafodion_delete and trafodion_vsbb_delete(delete with no rollback).
For upsert, we also support trafodion_upsert and trafodion_vsbb_upsert.
I am trying to upsert data into a table and I get trafodion_vsbb_upsert. But how to get a trafodion_upsert plan? And what's the difference between trafodion_upsert and trafodion_vsbb_upsert?
SQL>explain options 'f' upsert into DMA_ENTTYPE_STAT_30W_P8 select * from DMA_ENTTYPE_STAT;
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------
4 . 5 root x 5.03E+006
2 3 4 tuple_flow 5.03E+006
. . 3 trafodion_vsbb_upser DMA_ENTTYPE_STAT_30W 1.00E+000
1 . 2 sort 5.03E+006
. . 1 trafodion_scan DMA_ENTTYPE_STAT 5.03E+006
Best regards
Yuan