Add Examples of *REBUILD INDEXES*
Project: http://git-wip-us.apache.org/repos/asf/trafodion/repo Commit: http://git-wip-us.apache.org/repos/asf/trafodion/commit/517ad43d Tree: http://git-wip-us.apache.org/repos/asf/trafodion/tree/517ad43d Diff: http://git-wip-us.apache.org/repos/asf/trafodion/diff/517ad43d Branch: refs/heads/master Commit: 517ad43d6a76a4ecbf7491a05f1854102dfd2b20 Parents: 9d81ec6 Author: liu.yu <[email protected]> Authored: Fri Jan 26 16:09:06 2018 +0800 Committer: liu.yu <[email protected]> Committed: Fri Jan 26 16:09:06 2018 +0800 ---------------------------------------------------------------------- .../src/asciidoc/_chapters/sql_utilities.adoc | 356 +++++++++++++++++++ 1 file changed, 356 insertions(+) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/trafodion/blob/517ad43d/docs/sql_reference/src/asciidoc/_chapters/sql_utilities.adoc ---------------------------------------------------------------------- diff --git a/docs/sql_reference/src/asciidoc/_chapters/sql_utilities.adoc b/docs/sql_reference/src/asciidoc/_chapters/sql_utilities.adoc index eb6110d..d5b6266 100644 --- a/docs/sql_reference/src/asciidoc/_chapters/sql_utilities.adoc +++ b/docs/sql_reference/src/asciidoc/_chapters/sql_utilities.adoc @@ -1186,6 +1186,362 @@ SQL> POPULATE INDEX index_target_table4 ON target_table4; SQL> DROP INDEX index_target_table4; --- SQL operation complete. ``` + +[[rebuild_indexes_examples]] +==== Examples of `REBUILD INDEXES` + +Suppose that we have following tables: + +_source_table_: + +``` +SQL>select count(*) from source_table; +(EXPR) +-------------------- +Â Â Â Â Â Â Â Â Â Â Â Â 1000000 + +--- 1 row(s) selected. +``` + +_target_table1_ has the same structure as _target_table2_, here takes _target_table1_ for example: + +``` +SQL>CREATE TABLE target_table1 +Â ( +Â Â Â Â 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' +Â Â ) +; +``` + +* This example compares the execution time of using LOAD Statement without options and +using `LOAD WITH REBUILD INDEXES` when the CQD `TRAF_LOAD_ALLOW_RISKY_INDEX_MAINTENANCE` +is turned *OFF* by default. These two statements take almost the same time. + ++ +``` +SQL>CREATE INDEX index_target_table1 ON target_table1(id); +--- SQL operation complete. + +SQL>SET STATISTICS ON; + +SQL>LOAD INTO target_table1 SELECT * FROM source_table WHERE id < 301; + +UTIL_OUTPUT +--------------------------------------------------------------------------------- +Task: LOAD Status: Started Object: TRAFODION.SEABASE.TARGET_TABLE1 +Task: CLEANUP Status: Started Time: 2018-01-18 13:33:52.310 +Task: CLEANUP Status: Ended Time: 2018-01-18 13:33:52.328 +Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.019 +Task: DISABLE INDEXE Status: Started Time: 2018-01-18 13:33:52.328 +Task: DISABLE INDEXE Status: Ended Time: 2018-01-18 13:34:04.709 +Task: DISABLE INDEXE Status: Ended Elapsed Time: 00:00:12.381 +Task: LOADING DATA Status: Started Time: 2018-01-18 13:34:04.709 + Rows Processed: 300 + Error Rows: 0 +Task: LOADING DATA Status: Ended Time: 2018-01-18 13:34:21.629 +Task: LOADING DATA Status: Ended Elapsed Time: 00:00:16.919 +Task: COMPLETION Status: Started Time: 2018-01-18 13:34:21.629 + Rows Loaded: 300 +Task: COMPLETION Status: Ended Time: 2018-01-18 13:34:22.436 +Task: COMPLETION Status: Ended Elapsed Time: 00:00:00.808 +Task: POPULATE INDEX Status: Started Time: 2018-01-18 13:34:22.436 +Task: POPULATE INDEX Status: Ended Time: 2018-01-18 13:34:31.116 +Task: POPULATE INDEX Status: Ended Elapsed Time: 00:00:08.680 +--- SQL operation complete. + +Start Time 2018/01/18 13:33:51.478782 +End Time 2018/01/18 13:34:31.549491 +Elapsed Time 00:00:40.070709 +Compile Time 00:00:00.510024 +Execution Time 00:00:39.559433 + +SQL>LOAD INTO target_table1 SELECT * FROM source_table WHERE id > 300; +UTIL_OUTPUT +--------------------------------------------------------------------------------- +Task: LOAD Status: Started Object: TRAFODION.SEABASE.TARGET_TABLE1 +Task: CLEANUP Status: Started Time: 2018-01-18 13:35:01.804 +Task: CLEANUP Status: Ended Time: 2018-01-18 13:35:01.823 +Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.018 +Task: DISABLE INDEXE Status: Started Time: 2018-01-18 13:35:01.823 +Task: DISABLE INDEXE Status: Ended Time: 2018-01-18 13:35:13.840 +Task: DISABLE INDEXE Status: Ended Elapsed Time: 00:00:12.017 +Task: LOADING DATA Status: Started Time: 2018-01-18 13:35:13.840 + Rows Processed: 999700 + Error Rows: 0 +Task: LOADING DATA Status: Ended Time: 2018-01-18 13:35:19.720 +Task: LOADING DATA Status: Ended Elapsed Time: 00:00:05.879 +Task: COMPLETION Status: Started Time: 2018-01-18 13:35:19.720 + Rows Loaded: 999700 +Task: COMPLETION Status: Ended Time: 2018-01-18 13:35:22.436 +Task: COMPLETION Status: Ended Elapsed Time: 00:00:02.717 +Task: POPULATE INDEX Status: Started Time: 2018-01-18 13:35:22.436 +Task: POPULATE INDEX Status: Ended Time: 2018-01-18 13:35:33.346 +Task: POPULATE INDEX Status: Ended Elapsed Time: 00:00:10.910 +--- SQL operation complete. + +Start Time 2018/01/18 13:35:00.624490 +End Time 2018/01/18 13:35:33.779394 +Elapsed Time 00:00:33.154904 +Compile Time 00:00:00.825703 +Execution Time 00:00:32.321890 + +SQL>SET PARSERFLAGS 1; +--- SQL operation complete. + +SQL>SELECT COUNT(*) FROM TABLE(INDEX_TABLE index_target_table1); +(EXPR) +-------------------- + 1000000 +--- 1 row(s) selected. +``` + ++ +``` +SQL>CREATE INDEX index_target_table2 ON target_table2(id); +--- SQL operation complete. + +SQL>SET STATISTICS ON; + +SQL>LOAD WITH REBUILD INDEXES INTO target_table2 SELECT * FROM source_table WHERE id < 301; +UTIL_OUTPUT +--------------------------------------------------------------------------------- +Task: LOAD Status: Started Object: TRAFODION.SEABASE.TARGET_TABLE2 +Task: CLEANUP Status: Started Time: 2018-01-18 13:34:37.836 +Task: CLEANUP Status: Ended Time: 2018-01-18 13:34:37.847 +Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.011 +Task: DISABLE INDEXE Status: Started Time: 2018-01-18 13:34:37.847 +Task: DISABLE INDEXE Status: Ended Time: 2018-01-18 13:34:45.445 +Task: DISABLE INDEXE Status: Ended Elapsed Time: 00:00:07.598 +Task: LOADING DATA Status: Started Time: 2018-01-18 13:34:45.445 + Rows Processed: 300 + Error Rows: 0 +Task: LOADING DATA Status: Ended Time: 2018-01-18 13:35:03.576 +Task: LOADING DATA Status: Ended Elapsed Time: 00:00:18.131 +Task: COMPLETION Status: Started Time: 2018-01-18 13:35:03.577 + Rows Loaded: 300 +Task: COMPLETION Status: Ended Time: 2018-01-18 13:35:04.873 +Task: COMPLETION Status: Ended Elapsed Time: 00:00:01.296 +Task: POPULATE INDEX Status: Started Time: 2018-01-18 13:35:04.873 +Task: POPULATE INDEX Status: Ended Time: 2018-01-18 13:35:12.461 +Task: POPULATE INDEX Status: Ended Elapsed Time: 00:00:07.589 +--- SQL operation complete. + +Start Time 2018/01/18 13:34:37.053647 +End Time 2018/01/18 13:35:12.893891 +Elapsed Time 00:00:35.840244 +Compile Time 00:00:00.435855 +Execution Time 00:00:35.402620 + +SQL>LOAD WITH REBUILD INDEXES INTO target_table2 SELECT * FROM source_table WHERE id > 300; +UTIL_OUTPUT +--------------------------------------------------------------------------------- +Task: LOAD Status: Started Object: TRAFODION.SEABASE.TARGET_TABLE2 +Task: CLEANUP Status: Started Time: 2018-01-18 13:35:25.480 +Task: CLEANUP Status: Ended Time: 2018-01-18 13:35:25.493 +Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.013 +Task: DISABLE INDEXE Status: Started Time: 2018-01-18 13:35:25.493 +Task: DISABLE INDEXE Status: Ended Time: 2018-01-18 13:35:38.844 +Task: DISABLE INDEXE Status: Ended Elapsed Time: 00:00:12.591 +Task: LOADING DATA Status: Started Time: 2018-01-18 13:35:38.845 + Rows Processed: 999700 + Error Rows: 0 +Task: LOADING DATA Status: Ended Time: 2018-01-18 13:35:43.491 +Task: LOADING DATA Status: Ended Elapsed Time: 00:00:05.407 +Task: COMPLETION Status: Started Time: 2018-01-18 13:35:43.491 + Rows Loaded: 999700 +Task: COMPLETION Status: Ended Time: 2018-01-18 13:35:45.920 +Task: COMPLETION Status: Ended Elapsed Time: 00:00:01.601 +Task: POPULATE INDEX Status: Started Time: 2018-01-18 13:35:45.920 +Task: POPULATE INDEX Status: Ended Time: 2018-01-18 13:35:56.322 +Task: POPULATE INDEX Status: Ended Elapsed Time: 00:00:11.230 +--- SQL operation complete. + +Start Time 2018/01/18 13:35:24.693410 +End Time 2018/01/18 13:35:56.754441 +Elapsed Time 00:00:32.061031 +Compile Time 00:00:00.449236 +Execution Time 00:00:31.611112 + +SQL>SET PARSERFLAGS 1; +--- SQL operation complete. + +SQL>SELECT COUNT(*) FROM TABLE(INDEX_TABLE index_target_table2); +(EXPR) +-------------------- + 1000000 + +--- 1 row(s) selected. +``` + +* This example compares the execution time of using LOAD Statement without options and +using `LOAD WITH REBUILD INDEXES` when the CQD `TRAF_LOAD_ALLOW_RISKY_INDEX_MAINTENANCE` +is turned *ON*. The former takes shorter time than the latter. + ++ +``` +SQL>CQD TRAF_LOAD_ALLOW_RISKY_INDEX_MAINTENANCE 'ON'; +--- SQL operation complete. + +SQL>CREATE INDEX index_target_table1 ON target_table1(id); +--- SQL operation complete. + +SQL>SET STATISTICS ON; + +SQL>LOAD INTO target_table1 SELECT * FROM source_table WHERE id < 301; +UTIL_OUTPUT +--------------------------------------------------------------------------------- +Task: LOAD Status: Started Object: TRAFODION.SEABASE.TARGET_TABLE1 +Task: CLEANUP Status: Started Time: 2018-01-18 13:46:01.730 +Task: CLEANUP Status: Ended Time: 2018-01-18 13:46:01.756 +Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.027 +Task: LOADING DATA Status: Started Time: 2018-01-18 13:46:01.756 + Rows Processed: 300 + Error Rows: 0 +Task: LOADING DATA Status: Ended Time: 2018-01-18 13:46:22.415 +Task: LOADING DATA Status: Ended Elapsed Time: 00:00:20.659 +Task: COMPLETION Status: Started Time: 2018-01-18 13:46:22.415 + Rows Loaded: 300 +Task: COMPLETION Status: Ended Time: 2018-01-18 13:46:26.353 +Task: COMPLETION Status: Ended Elapsed Time: 00:00:03.938 +--- SQL operation complete. + +Start Time 2018/01/18 13:46:00.954518 +End Time 2018/01/18 13:46:26.795757 +Elapsed Time 00:00:25.841239 +Compile Time 00:00:00.455681 +Execution Time 00:00:25.384158 + +SQL>LOAD INTO target_table1 SELECT * FROM source_table WHERE id > 300; +UTIL_OUTPUT +--------------------------------------------------------------------------------- +Task: LOAD Status: Started Object: TRAFODION.SEABASE.TARGET_TABLE1 +Task: CLEANUP Status: Started Time: 2018-01-18 13:46:57.811 +Task: CLEANUP Status: Ended Time: 2018-01-18 13:46:57.870 +Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.058 +Task: LOADING DATA Status: Started Time: 2018-01-18 13:46:57.870 + Rows Processed: 999700 + Error Rows: 0 +Task: LOADING DATA Status: Ended Time: 2018-01-18 13:47:12.411 +Task: LOADING DATA Status: Ended Elapsed Time: 00:00:14.541 +Task: COMPLETION Status: Started Time: 2018-01-18 13:47:12.411 + Rows Loaded: 999700 +Task: COMPLETION Status: Ended Time: 2018-01-18 13:47:16.292 +Task: COMPLETION Status: Ended Elapsed Time: 00:00:03.881 +--- SQL operation complete. + +Start Time 2018/01/18 13:46:57.400422 +End Time 2018/01/18 13:47:16.738970 +Elapsed Time 00:00:19.338548 +Compile Time 00:00:00.010545 +Execution Time 00:00:19.321781 + +SQL>SET PARSERFLAGS 1; +--- SQL operation complete. + +SQL>SELECT COUNT(*) FROM TABLE(INDEX_TABLE index_target_table1); +(EXPR) +-------------------- + 1000000 +--- 1 row(s) selected. +``` + ++ +``` +SQL>CREATE INDEX index_target_table2 ON target_table2(id); +--- SQL operation complete. + +SQL>SET STATISTICS ON; + +SQL>LOAD WITH REBUILD INDEXES INTO target_table2 SELECT * FROM source_table WHERE id < 301; +UTIL_OUTPUT +--------------------------------------------------------------------------------- +Task: LOAD Status: Started Object: TRAFODION.SEABASE.TARGET_TABLE2 +Task: CLEANUP Status: Started Time: 2018-01-18 13:46:28.303 +Task: CLEANUP Status: Ended Time: 2018-01-18 13:46:28.796 +Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.049 +Task: DISABLE INDEXE Status: Started Time: 2018-01-18 13:46:28.796 +Task: DISABLE INDEXE Status: Ended Time: 2018-01-18 13:46:38.479 +Task: DISABLE INDEXE Status: Ended Elapsed Time: 00:00:10.400 +Task: LOADING DATA Status: Started Time: 2018-01-18 13:46:38.479 + Rows Processed: 300 + Error Rows: 0 +Task: LOADING DATA Status: Ended Time: 2018-01-18 13:46:58.143 +Task: LOADING DATA Status: Ended Elapsed Time: 00:00:19.665 +Task: COMPLETION Status: Started Time: 2018-01-18 13:46:58.144 + Rows Loaded: 300 +Task: COMPLETION Status: Ended Time: 2018-01-18 13:47:00.186 +Task: COMPLETION Status: Ended Elapsed Time: 00:00:02.043 +Task: POPULATE INDEX Status: Started Time: 2018-01-18 13:47:00.187 +Task: POPULATE INDEX Status: Ended Time: 2018-01-18 13:47:09.966 +Task: POPULATE INDEX Status: Ended Elapsed Time: 00:00:08.910 +--- SQL operation complete. + +Start Time 2018/01/18 13:46:27.063642 +End Time 2018/01/18 13:47:09.529257 +Elapsed Time 00:00:42.465615 +Compile Time 00:00:00.532541 +Execution Time 00:00:41.928812 + +SQL>LOAD WITH REBUILD INDEXES INTO target_table2 SELECT * FROM source_table WHERE id > 300; +UTIL_OUTPUT +--------------------------------------------------------------------------------- +Task: LOAD Status: Started Object: TRAFODION.SEABASE.TARGET_TABLE2 +Task: CLEANUP Status: Started Time: 2018-01-18 13:47:18.187 +Task: CLEANUP Status: Ended Time: 2018-01-18 13:47:18.198 +Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.011 +Task: DISABLE INDEXE Status: Started Time: 2018-01-18 13:47:18.198 +Task: DISABLE INDEXE Status: Ended Time: 2018-01-18 13:47:30.670 +Task: DISABLE INDEXE Status: Ended Elapsed Time: 00:00:12.472 +Task: LOADING DATA Status: Started Time: 2018-01-18 13:47:30.670 + Rows Processed: 999700 + Error Rows: 0 +Task: LOADING DATA Status: Ended Time: 2018-01-18 13:47:39.311 +Task: LOADING DATA Status: Ended Elapsed Time: 00:00:08.641 +Task: COMPLETION Status: Started Time: 2018-01-18 13:47:39.311 + Rows Loaded: 999700 +Task: COMPLETION Status: Ended Time: 2018-01-18 13:47:40.497 +Task: COMPLETION Status: Ended Elapsed Time: 00:00:01.186 +Task: POPULATE INDEX Status: Started Time: 2018-01-18 13:47:40.497 +Task: POPULATE INDEX Status: Ended Time: 2018-01-18 13:47:52.367 +Task: POPULATE INDEX Status: Ended Elapsed Time: 00:00:11.539 +--- SQL operation complete. + +Start Time 2018/01/18 13:47:17.447093 +End Time 2018/01/18 13:47:52.469190 +Elapsed Time 00:00:35.022097 +Compile Time 00:00:00.412718 +Execution Time 00:00:34.608571 + +SQL>SET PARSERFLAGES 1; +--- SQL operation complete. + +SQL>SELECT COUNT(*) FROM TABLE(INDEX_TABLE index_target_table2); +(EXPR) +-------------------- + 1000000 +--- 1 row(s) selected. +``` + [[loading_data_from_hive_table_examples]] ==== Examples of Loading data from Hive Table
