That would be a fantastic feature, Thanks for digging into this Dave! Eric -----Original Message----- From: Dave Birdsall [mailto:[email protected]] Sent: Wednesday, April 12, 2017 2:54 PM To: [email protected] Subject: Embedding an insert/select inside an insert/select?
Hi, Trafodion supports a notion of embedded IUD statements. I can, for example, do: Select * from (insert into t2 select * from t1) as tx where tx.a = 5; This statement inserts all the rows from t1 into t2, and returns those rows where A = 5 as its result. Just for fun, I tried the following: Insert into t3 select * from (insert into t2 select * from t1) as tx where tx.a = 5; This fails with the following error: *** ERROR[4171] Embedded INSERT statements are not supported in INSERT statements. This comes from GenericUpdate::bindNode in optimizer/BindRelExpr.cpp. Just for fun, I commented out the code that raises this error. I got some reasonable plans. For example: >>prepare ss from insert into t3 select * from (insert into t2 select * from t1) +> as tx where tx.a = 5; --- SQL command prepared. >>explain options 'f' ss; LC RC OP OPERATOR OPT DESCRIPTION CARD ---- ---- ---- -------------------- -------- -------------------- --------- 8 . 9 root x 5.00E+005 7 . 8 esp_exchange 1:4(hash2) 5.00E+005 5 6 7 tuple_flow 5.00E+005 . . 6 trafodion_insert T3 1.00E+000 4 . 5 sort 5.00E+005 2 3 4 nested_join 5.00E+005 . . 3 trafodion_insert T2 9.99E-006 1 . 2 sort 1.00E+006 . . 1 trafodion_scan T1 1.00E+006 --- SQL operation complete. >> In this example, T1 has 1 million rows. T1, T2 and T3 are simple tables with just two integer columns, A and B, primary key A. I had done UPDATE STATISTICS on t1 after populating it. I salted each table 4 ways on the primary key. This looks like a nice parallel plan, though I wonder about the need for the sort nodes, since the partitions should line up. This is a lot of background for my question: Why does the binder forbid such statements? Are there some holes in the handling of nested IUDs that need to be filled before such a feature is turned on? Thanks, Dave
