Repository: trafodion Updated Branches: refs/heads/master f92887964 -> b04bfda91
Add *populate-option* for *CREATE INDEX Statement* and fix typos 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/7d0ffe8e Tree: http://git-wip-us.apache.org/repos/asf/trafodion/tree/7d0ffe8e Diff: http://git-wip-us.apache.org/repos/asf/trafodion/diff/7d0ffe8e Branch: refs/heads/master Commit: 7d0ffe8e7c42c0dc4a60cd6354c5cbee28524754 Parents: 397fd2d Author: liu.yu <[email protected]> Authored: Wed May 23 16:41:10 2018 +0800 Committer: liu.yu <[email protected]> Committed: Wed May 23 16:41:10 2018 +0800 ---------------------------------------------------------------------- .../src/asciidoc/_chapters/sql_statements.adoc | 32 ++++++++++++++++++-- .../src/asciidoc/_chapters/sql_utilities.adoc | 2 +- 2 files changed, 30 insertions(+), 4 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/trafodion/blob/7d0ffe8e/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 5a76ffb..2fe09e8 100644 --- a/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc +++ b/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc @@ -1707,9 +1707,10 @@ inside a user-defined transaction (BEGIN WORK…COMMIT WORK) or when AUTOCOM AUTOCOMMIT must be turned ON (the default) for the session. ``` -CREATE [VOLATILE | UNIQUE] INDEX index ON table +CREATE [VOLATILE | UNIQUE] INDEX index-name ON table (column-name [ASC[ENDING] | DESC[ENDING]] [,column-name [ASC[ENDING] | DESC[ENDING]]]...) + [populate-option] [HBASE_OPTIONS (hbase-options-list)] [SALT LIKE TABLE] @@ -1733,7 +1734,7 @@ Unique index not only improves the query performance, but also provides data int + NOTE: Multiple NULL values are treated as duplicate values for a column which can contain only one NULL value. -* `_index_` +* `_index-name_` + is an SQL identifier that specifies the simple name for the new index. You cannot qualify _index_ with its schema name. Indexes have their own name space within a schema, so an index name might be the same as a table or constraint @@ -1755,6 +1756,23 @@ for the first column, the values in the second column are used to order the rows rows occur in a non-unique index, their order is based on the sequence specified for the columns of the key of the underlying table. For ordering (but not for other purposes), nulls are greater than other values. +* `populate-option` + +** `NO POPULATE` ++ +specifies that the index is not to be populated when it is created. ++ +The indexâs partition(s) are created, but no data is written to the index, and it is marked âofflineâ. You can drop an offline index with the DROP INDEX statement. +The DROP TABLE statement also drops offline indexes of the specified table. +DML statements have no effect on offline indexes. ++ +If an index is created with the intention of using it for a constraint, it must be populated before creating the constraint. +You can populate an offline index and remove its offline designation by using the POPULATE INDEX utility. + +** `POPULATE` ++ +specifies that the index is to be created and populated. + * `HBASE_OPTIONS (_hbase-option_ = '_value_'[, _hbase-option_ = '_value_']…)` + a list of HBase options to set for the index. These options are applied independently of any HBase options set for @@ -1936,6 +1954,14 @@ SQL>create unique index idx_t25 on t25(b); *** ERROR[1053] Unique index TRAFODION.SEABASE.IDX_T25 could not be created because the specified column(s) contain duplicate data. [2017-01-12 17:17:51] ``` +* This exmaple creates the index _index_test_ which is not to be populated on the column _c1_ of the table _test_. ++ +``` +SQL>create index index_test on test (c1) no populate; + +--- SQL operation complete. +``` + <<< [[create_library_statement]] == CREATE LIBRARY Statement @@ -1972,7 +1998,7 @@ and <<database_object_names,database object names>>. + specifies the full path of a deployed library file, which either an SPJ's jar file or a UDF's library file. + -note: make sure to upload the library file to the trafodion cluster and then copy the library file to the +NOTE: Make sure to upload the library file to the trafodion cluster and then copy the library file to the same directory on all the nodes in the cluster before running the create library statement. otherwise, you will see an error message indicating that the jar or dll file was not found. http://git-wip-us.apache.org/repos/asf/trafodion/blob/7d0ffe8e/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 992b955..5bcde53 100644 --- a/docs/sql_reference/src/asciidoc/_chapters/sql_utilities.adoc +++ b/docs/sql_reference/src/asciidoc/_chapters/sql_utilities.adoc @@ -2141,7 +2141,7 @@ directs {project-name} SQL to create a persistent sample table and store the random sample in it. This table can then be used later for updating statistics incrementally. -**** `PERIODIC _size_ ROWS EVERY _period_ ROW` +**** `PERIODIC _size_ ROWS EVERY _period_ ROWS` + directs {project-name} SQL to choose the first _size_ number of rows from each _period_ of rows. The value _size_ must be an integer that is
