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 <[email protected]> Authored: Thu Apr 26 16:57:34 2018 +0800 Committer: liu.yu <[email protected]> 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]]
