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 :)
---
