Repository: trafodion Updated Branches: refs/heads/master 0edae9410 -> 392c1ed9a
Add an example for *UPSERT Statement* in *Trafodion SQL Reference Manual* Project: http://git-wip-us.apache.org/repos/asf/trafodion/repo Commit: http://git-wip-us.apache.org/repos/asf/trafodion/commit/2221e9ae Tree: http://git-wip-us.apache.org/repos/asf/trafodion/tree/2221e9ae Diff: http://git-wip-us.apache.org/repos/asf/trafodion/diff/2221e9ae Branch: refs/heads/master Commit: 2221e9ae215389c9228e0fd27d9c1525b75e3f01 Parents: 622e67b Author: liu.yu <yu....@esgyn.cn> Authored: Tue Jan 23 14:38:41 2018 +0800 Committer: liu.yu <yu....@esgyn.cn> Committed: Tue Jan 23 14:38:41 2018 +0800 ---------------------------------------------------------------------- .../src/asciidoc/_chapters/sql_statements.adoc | 76 ++++++++++++++++++++ 1 file changed, 76 insertions(+) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/trafodion/blob/2221e9ae/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc ---------------------------------------------------------------------- diff --git a/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc b/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc index 33afd5e..84ddeb5 100644 --- a/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc +++ b/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc @@ -9108,6 +9108,82 @@ corresponding column in the target _table_. See [[upsert_examples]] === Examples of UPSERT +* This UPSERT statement inserts 1,000,000 rows from _source_table_ into _target_table_. + ++ +Suppose that we have following tables: + ++ +_source_table_: + ++ +``` +SQL>SELECT COUNT(*) FROM source_table; + +(EXPR) +-------------------- + 1000000 + +--- 1 row(s) selected. +``` + ++ +_target_table_: ++ +``` +SQL>CREATE TABLE target_table +Â ( +Â Â Â Â IDÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â INT NO DEFAULT NOT NULL NOT DROPPABLE NOT +Â Â Â Â Â SERIALIZED +Â , NUMÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â INT DEFAULT NULL NOT SERIALIZED +Â , CARD_IDÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â LARGEINT DEFAULT NULL NOT SERIALIZED +Â , PRICEÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â DECIMAL(11, 3) DEFAULT NULL NOT SERIALIZED +Â , START_DATEÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â DATE DEFAULT NULL NOT SERIALIZED +Â , START_TIMEÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â TIME(0) DEFAULT NULL NOT SERIALIZED +Â , END_TIMEÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â TIMESTAMP(6) DEFAULT NULL NOT SERIALIZED +Â , B_YEARÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â INTERVAL YEAR(10) DEFAULT NULL NOT +Â Â Â Â Â SERIALIZED +Â , B_YMÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â INTERVAL YEAR(5) TO MONTH DEFAULT NULL NOT +Â Â Â Â Â SERIALIZED +Â , B_DSÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â INTERVAL DAY(10) TO SECOND(3) DEFAULT NULL +Â Â Â Â Â NOT SERIALIZED +Â , PRIMARY KEY (ID ASC) +Â ) +Â SALT USING 9 PARTITIONS +ATTRIBUTES ALIGNED FORMAT NAMESPACE 'TRAF_1500000' +Â Â HBASE_OPTIONS +Â Â ( +Â Â Â Â MEMSTORE_FLUSH_SIZE = '1073741824' +Â Â ) +; +``` + ++ +It takes approximately 10 seconds to finish loading 1,000,000 rows. ++ +``` +SQL>SET STATISTICS ON; + +SQL>UPSERT USING LOAD INTO target_table SELECT * FROM source_table; +--- 1000000 row(s) inserted. + +Start Time 2018/01/18 11:38:02.739433 +End Time 2018/01/18 11:48:25.822903 +Elapsed Time 00:10:23.083470 +Compile Time 00:00:00.381337 +Execution Time 00:10:22.700870 +``` ++ +``` +SQL>SELECT COUNT(*) FROM target_table; + +(EXPR) +-------------------- +Â Â Â Â Â Â Â Â Â Â Â Â 1000000 + +--- 1 row(s) selected. +``` + * This UPSERT statement either inserts or updates the part number and price in the PARTS table using the part number and unit price from the ODETAIL table where the part number is 244:
