[
https://issues.apache.org/jira/browse/TRAFODION-2929?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16342910#comment-16342910
]
ASF GitHub Bot commented on TRAFODION-2929:
-------------------------------------------
Github user liuyu000 commented on a diff in the pull request:
https://github.com/apache/trafodion/pull/1416#discussion_r164338657
--- Diff: docs/sql_reference/src/asciidoc/_chapters/sql_utilities.adoc ---
@@ -1173,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.
--- End diff --
Thanks Dave, your comment has been incorporated :)
> Add *Rebuild Indexes* for LOAD Statement in *Trafodion SQL Reference Manual*
> ----------------------------------------------------------------------------
>
> Key: TRAFODION-2929
> URL: https://issues.apache.org/jira/browse/TRAFODION-2929
> Project: Apache Trafodion
> Issue Type: Documentation
> Reporter: Liu Yu
> Assignee: Liu Yu
> Priority: Major
>
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
