trafodion-codereview mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From DaveBirdsall <...@git.apache.org>
Subject [GitHub] trafodion pull request #1416: [TRAFODION-2929] Add *REBUILD INDEXES* for LOA...
Date Sun, 28 Jan 2018 05:03:15 GMT
Github user DaveBirdsall commented on a diff in the pull request:

    https://github.com/apache/trafodion/pull/1416#discussion_r164289112
  
    --- 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 --
    
    "The former takes less time than the latter" is a bit better.


---

Mime
View raw message