You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@trafodion.apache.org by db...@apache.org on 2018/04/30 15:28:02 UTC
[1/3] trafodion git commit: Add Syntax and Examples of
*ENABLE/DISABLE INDEX (ALTER TABLE Statement)* in Trafodion SQL Reference
Manual
Repository: trafodion
Updated Branches:
refs/heads/master 60d717b7b -> a3c56bc81
Add Syntax and Examples of *ENABLE/DISABLE INDEX (ALTER TABLE Statement)* 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/2f2c741f
Tree: http://git-wip-us.apache.org/repos/asf/trafodion/tree/2f2c741f
Diff: http://git-wip-us.apache.org/repos/asf/trafodion/diff/2f2c741f
Branch: refs/heads/master
Commit: 2f2c741f172d58cc4a28df01030ae30bba471bbe
Parents: 06d38d5
Author: liu.yu <qw...@hotmail.com>
Authored: Thu Apr 26 16:57:34 2018 +0800
Committer: liu.yu <qw...@hotmail.com>
Committed: Thu Apr 26 16:57:34 2018 +0800
----------------------------------------------------------------------
.../sql_functions_and_expressions.adoc | 4 +-
.../src/asciidoc/_chapters/sql_statements.adoc | 279 ++++++++++++++++++-
2 files changed, 280 insertions(+), 3 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/trafodion/blob/2f2c741f/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
----------------------------------------------------------------------
diff --git a/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc b/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
index b86f4c8..d5d2aa4 100644
--- a/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
+++ b/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
@@ -2608,7 +2608,7 @@ FROM DUAL;
```
[[date_difference_in_year]]
-=== Date Difference in YEAR
+==== Date Difference in YEAR
* This function returns the value of 0.
+
@@ -2633,7 +2633,7 @@ FROM DUAL;
<<<
[[dateformat_function]]
-=== DATEFORMAT Function
+== DATEFORMAT Function
The DATEFORMAT function returns a datetime value as a character string
literal in the DEFAULT, USA, or EUROPEAN format. The data type of the
http://git-wip-us.apache.org/repos/asf/trafodion/blob/2f2c741f/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 72bfac6..dc84dbc 100644
--- a/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc
+++ b/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc
@@ -379,6 +379,10 @@ ALTER TABLE table-name alter-action
alter-action is:
ADD [IF NOT EXISTS][COLUMN] column-definition
| ADD [CONSTRAINT constraint-name] table-constraint
+ | ENABLE ALL [UNIQUE] INDEXES
+ | ENABLE INDEX index-name
+ | DISABLE ALL [UNIQUE] INDEXES
+ | DISABLE INDEX index-name
| DROP CONSTRAINT constraint-name [RESTRICT]
| RENAME TO new-name
| DROP COLUMN [IF EXISTS] column-name
@@ -442,6 +446,10 @@ ref-spec is:
column-list is:
column-name[, column-name]...
+
+index-name is:
+ normal-index-name
+ | unique-index-name
```
<<<
@@ -577,6 +585,59 @@ the foreign key in _table_ is the column being defined; with the foreign key cla
the foreign key clause. for information about _ref-spec_, see references _ref-spec_ not enforced.
<<<
+* ENABLE ALL [UNIQUE] INDEXES
+
+** ENABLE ALL UNIQUE INDEXES
++
+enables all unique indexes on the table.
+
+** ENABLE ALL INDEXES
++
+enables all indexes (including normal indexes and unique indexes) on the table.
+
+* _index-name_
+** _normal-index-name_
++
+is equal to _index-name_, which is an SQL identifier that specifies the simple name for the index. You cannot qualify index-name 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 name. However, no two indexes in a schema can have the same name.
+
+** _unique-index-name_
++
+is the name of the unique index.
+
+* ENABLE INDEX _normal-index-name_
++
+enables a normal index on the table.
+
+* ENALBE INDEX _unique-index-name_
++
+enables an unique index on the table.
+
+* DISABLE ALL [UNIQUE] INDEXES
+** DISABLE ALL UNIQUE INDEXES
++
+disables all unique indexes on the table.
+
+** DISABLE ALL INDEXES
++
+disables all indexes (including normal indexes and unique indexes) on the table.
+
+* DISABLE INDEX _index-name_
+
+** DISABLE INDEX _normal-index-name_
++
+disables a normal index on the table.
+The index still exists in the data, but it will not
+be chosen by the optimizer as an access path.
+
+** DISABLE INDEX _unique-index-name_
++
+disables an unique index on the table.
+The unique index still exists in the data, but it will not
+be chosen by the optimizer as an access path.
+
+<<<
* `drop constraint _constraint-name_ [restrict]`
+
drops a constraint from the table. +
@@ -716,6 +777,222 @@ Alter TABLE PRODUCT
ALTER COLUMN vend_id RENAME TO cstm_id;
```
+* The following table _orders_ has two normal indexes
+(_index_orders1_ and _index_orders2_) and
+two unique indexes (_unique_index_orders1_ and _unique_index_orders2_).
+
++
+```
+SQL>SHOWDDL orders;
+CREATE TABLE TRAFODION.SEABASE.ORDERS
+ (
+ ORDERNUM NUMERIC(6, 0) DEFAULT NULL NOT SERIALIZED
+ , ORDER_DATE DATE DEFAULT NULL NOT SERIALIZED
+ , DELIV_DATE DATE DEFAULT NULL NOT SERIALIZED
+ , SALESREP NUMERIC(4, 0) DEFAULT NULL NOT SERIALIZED
+ , CUSTNUM NUMERIC(4, 0) DEFAULT NULL NOT SERIALIZED
+ )
+ ATTRIBUTES ALIGNED FORMAT
+;
+
+CREATE INDEX INDEX_ORDERS1 ON TRAFODION.SEABASE.ORDERS
+ (
+ ORDER_DATE ASC
+ )
+ ATTRIBUTES ALIGNED FORMAT
+;
+
+CREATE INDEX INDEX_ORDERS2 ON TRAFODION.SEABASE.ORDERS
+ (
+ DELIV_DATE ASC
+ )
+ ATTRIBUTES ALIGNED FORMAT
+;
+
+CREATE UNIQUE INDEX UNIQUE_INDEX_ORDERS1 ON TRAFODION.SEABASE.ORDERS
+ (
+ SALESREP ASC
+ )
+ ATTRIBUTES ALIGNED FORMAT
+;
+
+CREATE UNIQUE INDEX UNIQUE_INDEX_ORDERS2 ON TRAFODION.SEABASE.ORDERS
+ (
+ CUSTNUM ASC
+ )
+ ATTRIBUTES ALIGNED FORMAT
+;
+
+-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.ORDERS TO DB__ROOT WITH GRANT OPTION;
+
+--- SQL operation complete.
+```
+
++
+Disable one unique index (_unique_index_orders1_) and verify that all normal indexes (_index_orders1_ and _index_orders2_)
+and the rest unique index (_unique_index_orders2_) are still enabled.
+
++
+```
+SQL>ALTER TABLE ORDERS DISABLE INDEX unique_index_orders1;
+--- SQL operation complete.
+
+SQL>SHOWDDL orders;
+CREATE TABLE TRAFODION.SEABASE.ORDERS
+ (
+ ORDERNUM NUMERIC(6, 0) DEFAULT NULL NOT SERIALIZED
+ , ORDER_DATE DATE DEFAULT NULL NOT SERIALIZED
+ , DELIV_DATE DATE DEFAULT NULL NOT SERIALIZED
+ , SALESREP NUMERIC(4, 0) DEFAULT NULL NOT SERIALIZED
+ , CUSTNUM NUMERIC(4, 0) DEFAULT NULL NOT SERIALIZED
+ )
+ ATTRIBUTES ALIGNED FORMAT
+;
+
+CREATE INDEX INDEX_ORDERS1 ON TRAFODION.SEABASE.ORDERS
+ (
+ ORDER_DATE ASC
+ )
+ ATTRIBUTES ALIGNED FORMAT
+;
+
+CREATE INDEX INDEX_ORDERS2 ON TRAFODION.SEABASE.ORDERS
+ (
+ DELIV_DATE ASC
+ )
+ ATTRIBUTES ALIGNED FORMAT
+;
+
+CREATE UNIQUE INDEX UNIQUE_INDEX_ORDERS2 ON TRAFODION.SEABASE.ORDERS
+ (
+ CUSTNUM ASC
+ )
+ ATTRIBUTES ALIGNED FORMAT
+;
+
+-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.ORDERS TO DB__ROOT WITH GRANT OPTION;
+
+--- SQL operation complete.
+```
+
++
+Disable one normal index (_index_orders1_) and verify that the rest normal index (_index_orders2_)
+and the rest unique index (_unique_index_orders2_) are still enabled.
+
++
+```
+SQL>ALTER TABLE ORDERS DISABLE INDEX index_orders1;
+--- SQL operation complete.
+
+SQL>SHOWDDL orders;
+CREATE TABLE TRAFODION.SEABASE.ORDERS
+ (
+ ORDERNUM NUMERIC(6, 0) DEFAULT NULL NOT SERIALIZED
+ , ORDER_DATE DATE DEFAULT NULL NOT SERIALIZED
+ , DELIV_DATE DATE DEFAULT NULL NOT SERIALIZED
+ , SALESREP NUMERIC(4, 0) DEFAULT NULL NOT SERIALIZED
+ , CUSTNUM NUMERIC(4, 0) DEFAULT NULL NOT SERIALIZED
+ )
+ ATTRIBUTES ALIGNED FORMAT
+;
+
+CREATE INDEX INDEX_ORDERS2 ON TRAFODION.SEABASE.ORDERS
+ (
+ DELIV_DATE ASC
+ )
+ ATTRIBUTES ALIGNED FORMAT
+;
+
+CREATE UNIQUE INDEX UNIQUE_INDEX_ORDERS2 ON TRAFODION.SEABASE.ORDERS
+ (
+ CUSTNUM ASC
+ )
+ ATTRIBUTES ALIGNED FORMAT
+;
+
+-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.ORDERS TO DB__ROOT WITH GRANT OPTION;
+
+--- SQL operation complete.
+```
+
++
+Disable all indexes and verify that all indexes (including normal indexes and unique indexes) are disabled.
+
++
+```
+SQL>ALTER TABLE ORDERS DISABLE ALL INDEXES;
+--- SQL operation complete.
+
+SQL>SHOWDDL orders;
+CREATE TABLE TRAFODION.SEABASE.ORDERS
+ (
+ ORDERNUM NUMERIC(6, 0) DEFAULT NULL NOT SERIALIZED
+ , ORDER_DATE DATE DEFAULT NULL NOT SERIALIZED
+ , DELIV_DATE DATE DEFAULT NULL NOT SERIALIZED
+ , SALESREP NUMERIC(4, 0) DEFAULT NULL NOT SERIALIZED
+ , CUSTNUM NUMERIC(4, 0) DEFAULT NULL NOT SERIALIZED
+ )
+ ATTRIBUTES ALIGNED FORMAT
+;
+
+-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.ORDERS TO DB__ROOT WITH GRANT OPTION;
+
+--- SQL operation complete.
+```
+
++
+Enable all indexes on the table _orders_ and verify the result.
+
++
+```
+SQL>ALTER TABLE orders ENABLE ALL INDEXES;
+--- SQL operation complete.
+
+SQL>SHOWDDL orders;
+CREATE TABLE TRAFODION.SEABASE.ORDERS
+ (
+ ORDERNUM NUMERIC(6, 0) DEFAULT NULL NOT SERIALIZED
+ , ORDER_DATE DATE DEFAULT NULL NOT SERIALIZED
+ , DELIV_DATE DATE DEFAULT NULL NOT SERIALIZED
+ , SALESREP NUMERIC(4, 0) DEFAULT NULL NOT SERIALIZED
+ , CUSTNUM NUMERIC(4, 0) DEFAULT NULL NOT SERIALIZED
+ )
+ ATTRIBUTES ALIGNED FORMAT
+;
+
+CREATE INDEX INDEX_ORDERS1 ON TRAFODION.SEABASE.ORDERS
+ (
+ ORDER_DATE ASC
+ )
+ ATTRIBUTES ALIGNED FORMAT
+;
+
+CREATE INDEX INDEX_ORDERS2 ON TRAFODION.SEABASE.ORDERS
+ (
+ DELIV_DATE ASC
+ )
+ ATTRIBUTES ALIGNED FORMAT
+;
+
+CREATE UNIQUE INDEX UNIQUE_INDEX_ORDERS1 ON TRAFODION.SEABASE.ORDERS
+ (
+ SALESREP ASC
+ )
+ ATTRIBUTES ALIGNED FORMAT
+;
+
+CREATE UNIQUE INDEX UNIQUE_INDEX_ORDERS2 ON TRAFODION.SEABASE.ORDERS
+ (
+ CUSTNUM ASC
+ )
+ ATTRIBUTES ALIGNED FORMAT
+;
+
+-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.ORDERS TO DB__ROOT WITH GRANT OPTION;
+
+--- SQL operation complete.
+```
+
<<<
[[alter_user_statement]]
== ALTER USER Statement
@@ -5272,7 +5549,7 @@ to running a list command from an HBase shell, but without having to start and c
GET HBASE OBJECTS is a {project-name} SQL extension.
```
-GET [ USER | SYSTEM | EXTERNAL | ALL } HBASE OBJECTS
+GET { USER | SYSTEM | EXTERNAL | ALL } HBASE OBJECTS
```
[[get_hbase_objects_syntax]]
[3/3] trafodion git commit: Merge [TRAFODION-3044] PR 1542 Add
ENABLE/DISABLE INDEX examples to SQL Ref
Posted by db...@apache.org.
Merge [TRAFODION-3044] PR 1542 Add ENABLE/DISABLE INDEX examples to SQL Ref
Project: http://git-wip-us.apache.org/repos/asf/trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/trafodion/commit/a3c56bc8
Tree: http://git-wip-us.apache.org/repos/asf/trafodion/tree/a3c56bc8
Diff: http://git-wip-us.apache.org/repos/asf/trafodion/diff/a3c56bc8
Branch: refs/heads/master
Commit: a3c56bc81fc75d6b813a1af308cbc5d137a10ba8
Parents: 60d717b f04d557
Author: Dave Birdsall <db...@apache.org>
Authored: Mon Apr 30 15:27:10 2018 +0000
Committer: Dave Birdsall <db...@apache.org>
Committed: Mon Apr 30 15:27:10 2018 +0000
----------------------------------------------------------------------
.../sql_functions_and_expressions.adoc | 4 +-
.../src/asciidoc/_chapters/sql_statements.adoc | 258 ++++++++++++++++++-
2 files changed, 259 insertions(+), 3 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/trafodion/blob/a3c56bc8/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
----------------------------------------------------------------------
[2/3] trafodion git commit: Incorporate Comments 1
Posted by db...@apache.org.
Incorporate Comments 1
Project: http://git-wip-us.apache.org/repos/asf/trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/trafodion/commit/f04d557d
Tree: http://git-wip-us.apache.org/repos/asf/trafodion/tree/f04d557d
Diff: http://git-wip-us.apache.org/repos/asf/trafodion/diff/f04d557d
Branch: refs/heads/master
Commit: f04d557ddfcb2fe6cc2fafda3679f09305f1db9e
Parents: 2f2c741
Author: liu.yu <qw...@hotmail.com>
Authored: Fri Apr 27 11:29:39 2018 +0800
Committer: liu.yu <qw...@hotmail.com>
Committed: Fri Apr 27 11:29:39 2018 +0800
----------------------------------------------------------------------
.../src/asciidoc/_chapters/sql_statements.adoc | 43 +++++---------------
1 file changed, 11 insertions(+), 32 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/trafodion/blob/f04d557d/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 dc84dbc..5a76ffb 100644
--- a/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc
+++ b/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc
@@ -446,10 +446,6 @@ ref-spec is:
column-list is:
column-name[, column-name]...
-
-index-name is:
- normal-index-name
- | unique-index-name
```
<<<
@@ -593,26 +589,17 @@ enables all unique indexes on the table.
** ENABLE ALL INDEXES
+
-enables all indexes (including normal indexes and unique indexes) on the table.
+enables all indexes (including both non-unique and unique indexes) on the table.
* _index-name_
-** _normal-index-name_
+
-is equal to _index-name_, which is an SQL identifier that specifies the simple name for the index. You cannot qualify index-name with its schema name.
+is an SQL identifier that specifies the simple name for the index. You cannot qualify index-name 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 name. However, no two indexes in a schema can have the same name.
-** _unique-index-name_
-+
-is the name of the unique index.
-
-* ENABLE INDEX _normal-index-name_
-+
-enables a normal index on the table.
-
-* ENALBE INDEX _unique-index-name_
+* ENABLE INDEX _index-name_
+
-enables an unique index on the table.
+enables an index on the table.
* DISABLE ALL [UNIQUE] INDEXES
** DISABLE ALL UNIQUE INDEXES
@@ -621,22 +608,14 @@ disables all unique indexes on the table.
** DISABLE ALL INDEXES
+
-disables all indexes (including normal indexes and unique indexes) on the table.
+disables all indexes (including both non-unique and unique indexes) on the table.
* DISABLE INDEX _index-name_
-
-** DISABLE INDEX _normal-index-name_
+
-disables a normal index on the table.
+disables an index on the table.
The index still exists in the data, but it will not
be chosen by the optimizer as an access path.
-** DISABLE INDEX _unique-index-name_
-+
-disables an unique index on the table.
-The unique index still exists in the data, but it will not
-be chosen by the optimizer as an access path.
-
<<<
* `drop constraint _constraint-name_ [restrict]`
+
@@ -777,7 +756,7 @@ Alter TABLE PRODUCT
ALTER COLUMN vend_id RENAME TO cstm_id;
```
-* The following table _orders_ has two normal indexes
+* The following table _orders_ has two non-unique indexes
(_index_orders1_ and _index_orders2_) and
two unique indexes (_unique_index_orders1_ and _unique_index_orders2_).
@@ -829,7 +808,7 @@ CREATE UNIQUE INDEX UNIQUE_INDEX_ORDERS2 ON TRAFODION.SEABASE.ORDERS
```
+
-Disable one unique index (_unique_index_orders1_) and verify that all normal indexes (_index_orders1_ and _index_orders2_)
+Disable one unique index (_unique_index_orders1_) and verify that all non-unique indexes (_index_orders1_ and _index_orders2_)
and the rest unique index (_unique_index_orders2_) are still enabled.
+
@@ -876,7 +855,7 @@ CREATE UNIQUE INDEX UNIQUE_INDEX_ORDERS2 ON TRAFODION.SEABASE.ORDERS
```
+
-Disable one normal index (_index_orders1_) and verify that the rest normal index (_index_orders2_)
+Disable one non-unique index (_index_orders1_) and verify that the rest non-unique index (_index_orders2_)
and the rest unique index (_unique_index_orders2_) are still enabled.
+
@@ -916,7 +895,7 @@ CREATE UNIQUE INDEX UNIQUE_INDEX_ORDERS2 ON TRAFODION.SEABASE.ORDERS
```
+
-Disable all indexes and verify that all indexes (including normal indexes and unique indexes) are disabled.
+Disable all indexes and verify that all of them (including both non-unique and unique indexes) are disabled.
+
```
@@ -941,7 +920,7 @@ CREATE TABLE TRAFODION.SEABASE.ORDERS
```
+
-Enable all indexes on the table _orders_ and verify the result.
+Enable all indexes and verify the result.
+
```