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 2016/03/15 21:20:01 UTC
[2/7] incubator-trafodion git commit: Publication of new
documentation.
Publication of new documentation.
Added Load and Transform Guide and CQD Reference
Guide. Added a link to the Trafodion videos under the Project tab. Renamed
misnamed logo file, which corrects the broken link on the logo page.
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/223db1e4
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/223db1e4
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/223db1e4
Branch: refs/heads/master
Commit: 223db1e413dda706a2ab42b9b11175d954195ced
Parents: 1eb6051
Author: Gunnar Tapper <gt...@esgyn.local>
Authored: Fri Mar 11 23:16:54 2016 -0700
Committer: Gunnar Tapper <gt...@esgyn.local>
Committed: Fri Mar 11 23:16:54 2016 -0700
----------------------------------------------------------------------
docs/cqd_reference/pom.xml | 8 +-
.../src/asciidoc/_chapters/debugging.adoc | 95 ++++++
.../src/asciidoc/_chapters/histograms.adoc | 38 +--
.../src/asciidoc/_chapters/introduction.adoc | 13 +-
.../_chapters/operational_controls.adoc | 138 +++++++-
.../src/asciidoc/_chapters/query_execution.adoc | 239 ++++++++++++++
.../src/asciidoc/_chapters/query_plans.adoc | 330 +++++++++++++++----
.../asciidoc/_chapters/runtime_controls.adoc | 4 +-
.../src/asciidoc/_chapters/schema_controls.adoc | 24 +-
.../asciidoc/_chapters/table_definition.adoc | 142 ++++++++
.../src/asciidoc/_chapters/transactions.adoc | 4 +-
.../src/asciidoc/_chapters/update_stats.adoc | 31 +-
docs/cqd_reference/src/asciidoc/index.adoc | 3 +
docs/load_transform/pom.xml | 8 +-
.../src/asciidoc/_chapters/about.adoc | 8 +-
.../src/asciidoc/_chapters/bulk_load.adoc | 37 ++-
.../src/asciidoc/_chapters/introduction.adoc | 14 +-
.../src/asciidoc/_chapters/tables_indexes.adoc | 18 +-
.../src/asciidoc/_chapters/trickle_load.adoc | 12 +-
.../src/asciidoc/_chapters/troubleshoot.adoc | 4 +-
docs/src/site/markdown/documentation.md | 66 ++--
docs/src/site/markdown/logo.md | 2 +-
.../resources/images/logos/pb-Trafodion.png | Bin 49089 -> 0 bytes
.../resources/images/logos/pb-trafodion.png | Bin 0 -> 49089 bytes
docs/src/site/site.xml | 1 +
pom.xml | 6 +-
26 files changed, 1049 insertions(+), 196 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/223db1e4/docs/cqd_reference/pom.xml
----------------------------------------------------------------------
diff --git a/docs/cqd_reference/pom.xml b/docs/cqd_reference/pom.xml
index 9fbb88c..2075c15 100644
--- a/docs/cqd_reference/pom.xml
+++ b/docs/cqd_reference/pom.xml
@@ -236,15 +236,15 @@
- target/docs/<version>/<document> contains the PDF version and the web book. The web book is named index.html
-->
<!-- Copy the PDF file to its target directories -->
- <copy file="${basedir}/target/index.pdf" tofile="${basedir}/../target/docs/cqd_reference_guide/Trafodion_CQD_Reference_Guide.pdf" />
- <copy file="${basedir}/target/index.pdf" tofile="${basedir}/../target/docs/${project.version}/cqd_reference_guide/Trafodion_CQD_Reference_Guide.pdf" />
+ <copy file="${basedir}/target/index.pdf" tofile="${basedir}/../target/docs/cqd_reference/Trafodion_CQD_Reference_Guide.pdf" />
+ <copy file="${basedir}/target/index.pdf" tofile="${basedir}/../target/docs/${project.version}/cqd_reference/Trafodion_CQD_Reference_Guide.pdf" />
<!-- Copy the Web Book files to their target directories -->
- <copy todir="${basedir}/../target/docs/cqd_reference_guide">
+ <copy todir="${basedir}/../target/docs/cqd_reference">
<fileset dir="${basedir}/target/site">
<include name="**/*.*"/> <!--All sub-directories, too-->
</fileset>
</copy>
- <copy todir="${basedir}/../target/docs/${project.version}/cqd_reference_guide">
+ <copy todir="${basedir}/../target/docs/${project.version}/cqd_reference">
<fileset dir="${basedir}/target/site">
<include name="**/*.*"/> <!--All sub-directories, too-->
</fileset>
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/223db1e4/docs/cqd_reference/src/asciidoc/_chapters/debugging.adoc
----------------------------------------------------------------------
diff --git a/docs/cqd_reference/src/asciidoc/_chapters/debugging.adoc b/docs/cqd_reference/src/asciidoc/_chapters/debugging.adoc
new file mode 100644
index 0000000..3d50322
--- /dev/null
+++ b/docs/cqd_reference/src/asciidoc/_chapters/debugging.adoc
@@ -0,0 +1,95 @@
+////
+/**
+* @@@ START COPYRIGHT @@@
+*
+* Licensed to the Apache Software Foundation (ASF) under one
+* or more contributor license agreements. See the NOTICE file
+* distributed with this work for additional information
+* regarding copyright ownership. The ASF licenses this file
+* to you under the Apache License, Version 2.0 (the
+* "License"); you may not use this file except in compliance
+* with the License. You may obtain a copy of the License at
+*
+* http://www.apache.org/licenses/LICENSE-2.0
+*
+* Unless required by applicable law or agreed to in writing,
+* software distributed under the License is distributed on an
+* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+* KIND, either express or implied. See the License for the
+* specific language governing permissions and limitations
+* under the License.
+*
+* @@@ END COPYRIGHT @@@
+ */
+////
+
+[[debugging]]
+= Debugging
+
+This section describes CQDs that are used for debugging controls.
+
+[[udr-debug-flags]]
+== UDR_DEBUG_FLAGS
+
+[cols="25%h,75%"]
+|===
+| *Description* | Used when debugging user-defined functions (UDFs).
+| *Values* |
+*0-n* +
+ +
+Default: *'0'*.
+| *Usage* |
+See See https://cwiki.apache.org/confluence/display/TRAFODION/Tutorial%3A+The+object-oriented+UDF+interface#Tutorial:Theobject-orientedUDFinterface-DebuggingUDFcode[UDF Tutorial].
+| *Production Usage* | Consult the Trafodion community.
+| *Impact* | Not applicable.
+| *Level* | Session.
+| *Conflicts/Synergies* | Not applicable.
+| *Real Problem Addressed* | Not applicable.
+| *Introduced In Release* | Trafodion 1.3.0.
+| *Deprecated In Release* | Not applicable.
+|===
+
+<<<
+[[udr_jvm_debug_port]]
+== UDR_JVM_DEBUG_PORT
+
+[cols="25%h,75%"]
+|===
+| *Description* | Used when debugging user-defined functions (UDFs).
+| *Values* |
+*0-n* +
+ +
+Default: *'0'*.
+| *Usage* |
+See See https://cwiki.apache.org/confluence/display/TRAFODION/Tutorial%3A+The+object-oriented+UDF+interface#Tutorial:Theobject-orientedUDFinterface-DebuggingUDFcode[UDF Tutorial].
+| *Production Usage* | Consult the Trafodion community.
+| *Impact* | Not applicable.
+| *Level* | Session.
+| *Conflicts/Synergies* | Not applicable.
+| *Real Problem Addressed* | Not applicable.
+| *Introduced In Release* | Trafodion 1.3.0.
+| *Deprecated In Release* | Not applicable.
+|===
+
+<<<
+[[udr-jvm-debug-timeout]]
+== UDR_JVM_DEBUG_TIMEOUT
+
+[cols="25%h,75%"]
+|===
+| *Description* | Used when debugging user-defined functions (UDFs).
+| *Values* |
+*0-n* +
+ +
+Default: *'0'*.
+| *Usage* |
+See See https://cwiki.apache.org/confluence/display/TRAFODION/Tutorial%3A+The+object-oriented+UDF+interface#Tutorial:Theobject-orientedUDFinterface-DebuggingUDFcode[UDF Tutorial].
+| *Production Usage* | Consult the Trafodion community.
+| *Impact* | Not applicable.
+| *Level* | Session.
+| *Conflicts/Synergies* | Not applicable.
+| *Real Problem Addressed* | Not applicable.
+| *Introduced In Release* | Trafodion 1.3.0.
+| *Deprecated In Release* | Not applicable.
+|===
+
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/223db1e4/docs/cqd_reference/src/asciidoc/_chapters/histograms.adoc
----------------------------------------------------------------------
diff --git a/docs/cqd_reference/src/asciidoc/_chapters/histograms.adoc b/docs/cqd_reference/src/asciidoc/_chapters/histograms.adoc
index 53d1539..2126f53 100644
--- a/docs/cqd_reference/src/asciidoc/_chapters/histograms.adoc
+++ b/docs/cqd_reference/src/asciidoc/_chapters/histograms.adoc
@@ -33,9 +33,8 @@ This section describes CQDs that are used to manage histograms.
[cols="25%h,75%"]
|===
-| *Category* | Manage Histograms
-| *Description* | Defines the time interval after which timestamps for cached histograms are checked to be refreshed.
-| *Values* | Unsigned integer Unit is seconds. +
+| *Description* | Defines the time interval after which timestamps for cached histograms are checked for refresh processing.
+| *Values* | Unsigned integer in seconds. +
+
The default value is *'3600'* (1 hour).
| *Usage* | Histogram statistics are cached so that the compiler can avoid access to the metadata tables, thereby reducing compile times.
@@ -51,10 +50,10 @@ being updated during that time. On the other hand, if you are updating statistic
enough, and you want these to be picked up immediately by the compiler because you have seen this to have a dramatic impact on plan quality, then you can
make the refresh more frequent.
| *Production Usage* | Not applicable.
-| *Impact* | Longer histogram refresh intervals can improve compile times. However, the longer the refresh interval the more obsolete
-the histograms. That could result in poor performance for queries that could leverage recently updated statistics.
+| *Impact* | Longer histogram refresh intervals can improve compile times. However, longer refresh intervals yield more obsolete
+the histograms. More obsolete histograms may result in poor performance for queries that could leverage recently updated statistics.
| *Level* | System or Service.
-| *Conflicts/Synergies* | Frequency of update statistics run either using MAINTAIN or using Update Statistics Automation Server.
+| *Conflicts/Synergies* | Frequency of update statistics run either using MAINTAIN.
| *Real Problem Addressed* | Not applicable.
| *Introduced In Release* | Trafodion 1.3.0.
| *Deprecated In Release* | Not applicable.
@@ -66,22 +65,21 @@ the histograms. That could result in poor performance for queries that could lev
[cols="25%h,75%"]
|===
-| *Category* | Manage Histograms
-| *Description* | Controls the level of missing statistics warnings that should be displayed. The warnings impacted are 6007, 6008, 6010 and 6011.
+| *Description* | Controls the level of missing statistics warnings that should be displayed. The warnings impacted are 6007, 6008, 6010, and 6011.
| *Values* |
-*'0'*: Display no warnings. Update Statistics Automation Server still continues to record missing column statistics. +
+*'0'*: Display no warnings. The Update Statistics Automation Server still continues to record missing column statistics. +
+
*'1'*: Display only missing single column statistics warnings. These include 6008 and 6011. +
+
*'2'*: Display all missing single and multi-column statistics warnings for scans only. +
+
*'3'*: Display all missing single and multi-column statistics warnings for scans and join operators only.
-Update Statistics Automation Server continues to record missing column statistics. +
+The Update Statistics Automation Server continues to record missing column statistics. +
+
*'4'*: Display all missing single and multi-column statistics warnings. +
+
The default value is *'4'*.
-| *Usage* | Update Statistics Automation Server captures these warnings and gathers appropriate statistics.
+| *Usage* | The Update Statistics Automation Server (USAS) captures these warnings and gathers appropriate statistics.
If you do not want to see these warnings, then change the setting to '0'. If you want to track the warnings, then you have a
choice of which warnings you want to track. Each setting gives you the ability to filter the warnings seen for missing single
or multi-column statistics for join or scan operations. This controls the resulting number of warning messages. +
@@ -101,8 +99,8 @@ to get several warnings. Not all warnings may contribute to plan improvements. T
based on the search path, some of which may not even impact the plan quality. Also, the cost of gathering statistics on those columns
may not bring commensurate benefit to a large number of queries.
| *Level* | System.
-| *Conflicts/Synergies* | Update Statistics Automation Server does capture these warnings and gathers statistics felt appropriate
-for query performance. If you are using USAS then you may set the warning level to low or 0.
+| *Conflicts/Synergies* | The USAS does capture these warnings and gathers statistics felt appropriate
+for query performance. If you are using USAS, then you may set the warning level to 1 or 0.
| *Real Problem Addressed* | Not applicable.
| *Introduced In Release* | Trafodion 1.3.0.
| *Deprecated In Release* | Not applicable.
@@ -114,9 +112,8 @@ for query performance. If you are using USAS then you may set the warning level
[cols="25%h,75%"]
|===
-| *Category* | Manage Histograms
| *Description* | Defines the time interval after which the fake histograms in the cache should be refreshed unconditionally.
-| *Values* | Unsigned integer Unit is seconds. +
+| *Values* | Unsigned integer. Unit is seconds. +
+
The default value is *'3600'* (1 hour).
| *Usage* | Histogram statistics are "fake" when update statistics is not being run, but instead the customer is
@@ -143,7 +140,6 @@ then the compiler may be working with obsolete histogram statistics, potentially
[cols="25%h,75%"]
|===
-| *Category* | Manage Histograms
| *Description* | Influences the compiler to pre-fetch the histograms and save them in cache.
| *Values* |
*'ON'*: Pre-fetches the histograms. +
@@ -154,7 +150,7 @@ The default value is *'ON'*.
| *Production Usage* | Not applicable.
| *Impact* | Though it makes compilation time faster, it may result in the histogram cache to be filled with histograms that may never be used.
| *Level* | System or Service.
-| *Conflicts/Synergies* | Use this CQD with CACHE_HISTOGRAMS. If CACHE_HISTOGRAMS is OFF, then this CQD has no effect. $$$
+| *Conflicts/Synergies* | Use this CQD with <<cache-histograms,CACHE_HISTOGRAMS>>. If CACHE_HISTOGRAMS is OFF, then this CQD has no effect.
| *Real Problem Addressed* | Not applicable.
| *Introduced In Release* | Trafodion 1.3.0.
| *Deprecated In Release* | Not applicable.
@@ -166,9 +162,8 @@ The default value is *'ON'*.
[cols="25%h,75%"]
|===
-| *Category* | Manage Histograms
-| *Description* | Specifies the minimum row count for which the optimizer needs histograms, in order to compute better cardinality estimates.
-The optimizer does not issue any missing statistics warnings for tables whose size is smaller than the value of this CQD.
+| *Description* | Specifies the minimum row count for which the Trafodion Optimizer needs histograms, in order to compute better cardinality estimates.
+The Optimizer does not issue any missing statistics warnings for tables whose size is smaller than the value of this CQD.
| *Values* | Integer. +
+
The default value is *'50000'*.
@@ -190,7 +185,6 @@ then this CQD does not have any effect. Also, for tables having fewer rows than
[cols="25%h,75%"]
|===
-| *Category* | Manage Histograms
| *Description* | Enables the Compile Time Stats feature. Compile Time Stats are produced during query plan generation by executing a
subset of the query on a subset of data to gather more accurate cardinality estimations.
| *Values* |
@@ -208,7 +202,7 @@ It can also be disabled if the extra collection of statistics seems to be advers
| *Impact* | The feature improves cardinality estimates for Scan operators thus improving the plan quality.
However, it can also increase the compile time.
| *Level* | Any.
-| *Conflicts/Synergies* | In order to use the feature in its default form, sample tables should exist in `public_access_schema`. $$$
+| *Conflicts/Synergies* | In order to use the feature in its default form, sample tables should exist in `public_access_schema`.
| *Real Problem Addressed* | Not applicable.
| *Introduced In Release* | Trafodion 1.3.0.
| *Deprecated In Release* | Not applicable.
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/223db1e4/docs/cqd_reference/src/asciidoc/_chapters/introduction.adoc
----------------------------------------------------------------------
diff --git a/docs/cqd_reference/src/asciidoc/_chapters/introduction.adoc b/docs/cqd_reference/src/asciidoc/_chapters/introduction.adoc
index 5ba441e..fe3be20 100644
--- a/docs/cqd_reference/src/asciidoc/_chapters/introduction.adoc
+++ b/docs/cqd_reference/src/asciidoc/_chapters/introduction.adoc
@@ -27,7 +27,7 @@
= Introduction
Refer to the
-http://trafodion.apache.org/docs/sql_reference/index.html#control_query_cancel_statement[Trafofion SQL Reference Manual]
+http://trafodion.apache.org/docs/sql_reference/index.html#control_query_cancel_statement[Trafodion SQL Reference Manual]
full documentation of the CQD (CONTROL QUERY DEFAULT) statement.
The CONTROL QUERY DEFAULT statement changes the default settings for the current process. You can execute
@@ -71,22 +71,13 @@ CONTROL QUERY DEFAULT HBASE_MAX_COLUMN_NAME_LENGTH RESET;
```
<<<
-[[introduction-cqd-descriptin]]
+[[introduction-cqd-descriptions]]
== CQD Descriptions
The following information is provided for each CQD:
[cols="25%h,75%"]
|===
-| *Category* | Identifies one of these categories with which the CQD is associated: +
- +
-• Influence Query Plans +
-• Manage Histograms +
-• Transaction control and locking +
-• Run time controls +
-• Schema controls +
-• Update Statistics and Reorg +
-• Operational controls
| *Description* | Describes the purpose of the CQD.
| *Values* | Identifies this information: +
+
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/223db1e4/docs/cqd_reference/src/asciidoc/_chapters/operational_controls.adoc
----------------------------------------------------------------------
diff --git a/docs/cqd_reference/src/asciidoc/_chapters/operational_controls.adoc b/docs/cqd_reference/src/asciidoc/_chapters/operational_controls.adoc
index aad0e7f..50d95a2 100644
--- a/docs/cqd_reference/src/asciidoc/_chapters/operational_controls.adoc
+++ b/docs/cqd_reference/src/asciidoc/_chapters/operational_controls.adoc
@@ -33,15 +33,15 @@ This section describes CQDs that are used for operational controls.
[cols="25%h,75%"]
|===
-| *Category* | Operational Controls
| *Description* | Indicates whether a warning should be issued when a query is retried, in case a failed query is automatically retried.
-| *Values* | *'ON'* or *'OFF'* +
+| *Values* |
+*'ON'* or *'OFF'* +
+
The default value is *'OFF'*.
-| *Usage* | There are certain cases, such as when a node failure occurs, where a query may fail midstream. In many of such failure scenarios,
-if the query has not returned any data, then it is retried automatically. +
+| *Usage* | There are certain cases, such as when a node failure occurs, where a query may fail midstream. The query is usually
+retried automatically if it has not returned any data. +
+
-When such retries happen, you may want to see a warning that an automatic retry took place. That would be a reason to turn this on.
+When such retries happen, then you may want to see a warning that an automatic retry took place. That would be a reason to turn this on.
| *Production Usage* | Not applicable.
| *Impact* | You get a warning message every time a query is automatically retried due to a failure. When there is a node failure,
then a large number of queries may be impacted. Therefore, you need to assess if you want to see a flood of warnings. The warning is returned after the query completes.
@@ -53,12 +53,89 @@ then a large number of queries may be impacted. Therefore, you need to assess if
|===
<<<
+[[explain-description-column-size]]
+== EXPLAIN_DESCRIPTION_COLUMN_SIZE
+
+[cols="25%h,75%"]
+|===
+| *Description* | Specifies maximum length of DESCRIPTION column for EXPLAIN virtual tables.
+| *Values* |
+Positive integer > 10,000 +
+ +
+Default: -1, indicating a maximum size of 10,000.
+| *Usage* | Query-plan information for a SQL DML statement is stored temporarily in the Explain virtual table.
+For large queries or queries with complex predicates, the default size of 10 KB may be insufficient to describe certain nodes in the query plan.
+Specifying a larger value for this CQD allows more bytes to be stored in the description column. +
+ +
+Change this setting if you see explain plan being undesirably truncated only.
+| *Production Usage* | Yes.
+| *Impact* | The explain plan truncation is reduced or removed.
+| *Level* | System.
+| *Conflicts/Synergies* | Not applicable.
+| *Real Problem Addressed* | Not applicable.
+| *Introduced In Release* | Trafodion 1.3.0.
+| *Deprecated In Release* | Not applicable.
+|===
+
+<<<
+[[hbase-region-server-max-heap-size]]
+== HBASE_REGION_SERVER_MAX_HEAP_SIZE
+
+[cols="25%h,75%"]
+|===
+| *Description* | Enables Trafodion to use HBase block cache in an optimal manner. Defines the maximum Java
+heap size (-Xmx option) the HBase Region Server are assigned, in MB units.
+| *Values* |
+Positive integer. +
+ +
+Default: *'1024'*.
+| *Usage* | If the HBase Region servers are configured with a maximum Java heap size different than 1 GB, then
+set this attribute so that Trafodion is aware of the actual maximum heap size of the Region Servers.
+| *Production Usage* | Yes.
+| *Impact* | When set correctly, this CQD ensures that HBase block cache are be used optimally. Small scans
+are cached and larger scans are not cached to avoid cache trashing.
+| *Level* | System.
+| *Conflicts/Synergies* | Not applicable.
+| *Real Problem Addressed* | Not applicable.
+| *Introduced In Release* | Trafodion 1.3.0.
+| *Deprecated In Release* | Not applicable.
+|===
+
+<<<
+[[hive-metadata-refresh-interval]]
+== HIVE_METADATA_REFRESH_INTERVAL
+
+[cols="25%h,75%"]
+|===
+| *Description* | Controls the metadata cache for Hive tables.
+| *Values* |
+*-1*: Never invalidate any cache entries. +
+*0*: Always check the latest meta-data from Hive. +
+*> 0*: The cached Hive meta-data is valid only for <value> seconds. +
+ +
+Default: *'0'*.
+| *Usage* | Use of value -1 when the Hive tables are read-only and no updates are possible to avoid reading the
+Hive metadata when the same table is referenced in queries again. +
+ +
+Use of value 0 when frequent update of Hive tables are possible. +
+ +
+Use of a positive value when when infrequent update of Hive tables can take place and the value can be set so that it is impossible
+for the Hive tables to be updated within next <value> seconds.
+| *Production Usage* | Consult with the Trafodion community.
+| *Impact* | Compilation time.
+| *Level* | Session.
+| *Conflicts/Synergies* | Not applicable.
+| *Real Problem Addressed* | Not applicable.
+| *Introduced In Release* | Trafodion 1.3.0.
+| *Deprecated In Release* | Not applicable.
+|===
+
+<<<
[[query-cache]]
== QUERY_CACHE
[cols="25%h,75%"]
|===
-| *Category* | Operational Controls $$$
| *Description* | Attempts to reduce compilation times by storing and reusing previously compiled query plans.
It maximizes the chances of plan reuse by parameterizing literals in equality predicates. Two equality predicates, "col = val1" and "col = val2",
are considered to match if their selectivities match. +
@@ -108,3 +185,52 @@ If the plan object is not produced due to a text cache miss, then the plan is st
| *Introduced In Release* | Trafodion 1.3.0.
| *Deprecated In Release* | Not applicable.
|===
+
+<<<
+[[traf-load-allow-risky-index-maintenance]]
+== TRAF_LOAD_ALLOW_RISKY_INDEX_MAINTENANCE
+
+[cols="25%h,75%"]
+|===
+| *Description* | Allows incremental index maintenance during bulk load.
+| *Values* |
+*'ON'*: Incremental index maintenance enabled. +
+*'OFF'*: Incremental index maintenance disabled. +
+ +
+Default: *'OFF'*.
+| *Usage* | When this CQD is ON during a bulk load, then any indexes on a table are maintained incrementally.
+New rows are added to the base table and all the indexes in HFiles and then during LOAD COMPLETE phase all new files are moved to HBase.
+Indexes are not offline. However, it causes the index to be inconsistent with the base table if any of the new rows have the same key value as an existing.
+Change the default to ON only when certain that new rows do not have a conflict with existing rows in table.
+| *Production Usage* | Yes.
+| *Impact* | Bulk load into tables with index is faster, when the attribute is set to ON.
+| *Level* | System.
+| *Conflicts/Synergies* | Not applicable.
+| *Real Problem Addressed* | Not applicable.
+| *Introduced In Release* | Trafodion 1.3.0.
+| *Deprecated In Release* | Not applicable.
+|===
+
+<<<
+[[traf-load-flush-size-in-kb]]
+== TRAF_LOAD_FLUSH_SIZE_IN_KB
+
+[cols="25%h,75%"]
+|===
+| *Description* | Specifies the flush size used by bulk load when writing to HFiles.
+| *Values* |
+Positive integer. +
+ +
+Default: *'2014'*.
+| *Usage* | If the system is not memory constrained, then specifying a larger value may make the write phase of the LOAD statement to proceed faster. +
+ +
+Consider using lower values if the table has several indexes and is memory constrained.
+| *Production Usage* | Yes.
+| *Impact* | Affects memory usage patterns and write performance of LOAD.
+| *Level* | System.
+| *Conflicts/Synergies* | Not applicable.
+| *Real Problem Addressed* | Not applicable.
+| *Introduced In Release* | Trafodion 1.3.0.
+| *Deprecated In Release* | Not applicable.
+|===
+
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/223db1e4/docs/cqd_reference/src/asciidoc/_chapters/query_execution.adoc
----------------------------------------------------------------------
diff --git a/docs/cqd_reference/src/asciidoc/_chapters/query_execution.adoc b/docs/cqd_reference/src/asciidoc/_chapters/query_execution.adoc
new file mode 100644
index 0000000..c2553f1
--- /dev/null
+++ b/docs/cqd_reference/src/asciidoc/_chapters/query_execution.adoc
@@ -0,0 +1,239 @@
+////
+/**
+* @@@ START COPYRIGHT @@@
+*
+* Licensed to the Apache Software Foundation (ASF) under one
+* or more contributor license agreements. See the NOTICE file
+* distributed with this work for additional information
+* regarding copyright ownership. The ASF licenses this file
+* to you under the Apache License, Version 2.0 (the
+* "License"); you may not use this file except in compliance
+* with the License. You may obtain a copy of the License at
+*
+* http://www.apache.org/licenses/LICENSE-2.0
+*
+* Unless required by applicable law or agreed to in writing,
+* software distributed under the License is distributed on an
+* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+* KIND, either express or implied. See the License for the
+* specific language governing permissions and limitations
+* under the License.
+*
+* @@@ END COPYRIGHT @@@
+ */
+////
+
+[[query-execution]]
+= Query Execution
+
+This section describes CQDs that are used to influence query execution.
+
+[[hbase-async-operation]]
+== HBASE_ASYNC_OPERATION
+
+[cols="25%h,75%"]
+|===
+| *Description* | Allows index maintenance to be performed concurrently with base table operation.
+| *Values* | *'ON'* Index maintenance is allowed. +
+*'OFF'* Index maintenance is not allowed. +
+ +
+The default value is *'ON'*.
+| *Usage* | HBase `put` operations are blocking. When the table has one or more indexes,
+then the insert/update/delete (IUD) operation response time is improved by executing the index maintenance
+operations concurrently with the base table operation: the put operations to these HBase tables are executed
+in different threads.
+| *Production Usage* | Yes. It is 'ON' by default. This feature can be disabled by setting this CQD to 'OFF'.
+| *Impact* | IUD operations on tables with one or more indexes can become slower.
+| *Level* | Query.
+| *Conflicts/Synergies* | Not applicable.
+| *Real Problem Addressed* | Not applicable.
+| *Introduced In Release* | Trafodion 1.3.0.
+| *Deprecated In Release* | Not applicable.
+|===
+
+<<<
+[[hbase-cache-blocks]]
+== HBASE_CACHE_BLOCKS
+
+[cols="25%h,75%"]
+|===
+| *Description* | Influences HBase to retain the data blocks in memory after they are read.
+| *Values* |
+*'ON'/'OFF'/'SYSTEM'* +
+ +
+The default value is *'SYSTEM'*.
+| *Usage* | HBase maintains the block cache structure to retain the data blocks in memory after they are read.
+In LRU block cache configuration, the amount of block cache retained in memory is proportional to the amount of reserved maximum
+Java heap size of the region server. LRU Block Cache is the default in HBase. +
+ +
+The Trafodion Optimizer determines whether a sequential scan of the HBase table in a query would cause the full eviction of the
+data blocks cached earlier thereby impacting the performance of the random reads. The cache blocks option is turned off for the table
+in such a case. +
+ +
+Set the CQD <<hbase-region-server-max-heap-size,HBASE_REGION_SERVER_MAX_HEAP_SIZE>> value to reflect the amount of java heap size reserved for the region servers.
+This CQD is used by the Trafodion Optimizer to evaluate if the block cache to be turned off.
+| *Production Usage* | Leave the setting to be 'SYSTEM' when HBase is configured to use LRU block cache. If needed,
+you can override this settings with 'ON' or 'OFF'. +
+ +
+With other HBase configurations, you need to set HBASE_CACHE_BLOCKS to 'ON' or 'OFF' based on your application needs.
+| *Impact* | Automatically retains the random read performance.
+| *Level* | Query.
+| *Conflicts/Synergies* | Not applicable.
+| *Real Problem Addressed* | Not applicable.
+| *Introduced In Release* | Trafodion 1.3.0.
+| *Deprecated In Release* | Not applicable.
+|===
+
+<<<
+[[hbase-filter-preds]]
+== HBASE_FILTER_PREDS
+
+[cols="25%h,75%"]
+|===
+| *Description* | Allows push down of predicates to HBase Region Servers using HBase filters and optimize the columns retrieved
+from Region Servers. Only supported for NON ALIGN FORMAT tables.
+| *Values* |
+*'OFF'*: Predicates are never pushed down. +
+*'ON'*: A first implementation targeted for deprecation is enabled. Support simple predicate formed by a combination of AND only.
+Could be counter-productive when applied on nullable columns. +
+*'1'*: Same as *'ON'*. +
+*'2'*: Full feature is enabled. +
+ +
+An explain plan can show whether predicates are successfully pushed down to the Region Servers and what columns are really retrieved. +
+ +
+The default value is *'OFF'*.
+| *Usage* | Used to improve performance by reducing the number of columns retrieved to a strict minimum
+and filter out rows as early as possible.
+| *Production Usage* | Please consult the Trafodion community.
+| *Impact* | Using this CQD increases the amount of work done in the HBase Region Servers.
+| *Level* | System or Session.
+| *Conflicts/Synergies* | Not applicable.
+| *Real Problem Addressed* | Not applicable.
+| *Introduced In Release* | Trafodion 2.0.0.
+| *Deprecated In Release* | Not applicable.
+|===
+
+<<<
+[[hbase-hash2-partitioning]]
+== HBASE_HASH2_PARTITIONING
+
+[cols="25%h,75%"]
+|===
+| *Description* | Treat salted Trafodion tables as hash-partitioned on the salt columns.
+| *Values* |
+*'OFF'*: Salted Trafodion tables are not hash-partitioned on the salt columns. +
+*'ON'*: Salted Trafodion tables are hash-partitioned on the salt columns. +
+ +
+The default value is *'ON'*.
+| *Usage* | If, for any reason, there are issues with parallel plans on salted tables (especially with data skew) then try setting this CQD to OFF.
+| *Production Usage* | Yes.
+| *Impact* | Not applicable.
+| *Level* | System or Session.
+| *Conflicts/Synergies* | Not applicable.
+| *Real Problem Addressed* | Not applicable.
+| *Introduced In Release* | Trafodion 2.0.0.
+| *Deprecated In Release* | Not applicable.
+|===
+
+<<<
+[[hbase_num_cache_rows_max]]
+== HBASE_NUM_CACHE_ROWS_MAX
+
+[cols="25%h,75%"]
+|===
+| *Description* | Determines the number of rows obtained from HBase in one RPC call to the HBase Region Server in a sequential scan operation,
+| *Values* |
+Numeric value. +
+ +
+The default value is *'10000'*.
+| *Usage* | This CQD can be used to tune the query to perform optimally by reducing the number of interactions to the HBase Region Servers during
+a sequential scan of a table. +
+ +
+You need to consider how soon the maximum number of rows are materialized on the Region Servers. When filtering is pushed down to Region Servers,
+then it can take a longer time depending upon the query and the predicates involved. This can result in HBase scanner timeouts.
+| *Production Usage* | Use the default setting and reduce the value to avoid HBase scanner timeouts. Consult with the Trafodion community
+if you think that you need to use this CQD.
+| *Impact* | Not applicable.
+| *Level* | Query.
+| *Conflicts/Synergies* | Not applicable.
+| *Real Problem Addressed* | Not applicable.
+| *Introduced In Release* | Trafodion 1.3.0.
+| *Deprecated In Release* | Not applicable.
+|===
+
+<<<
+[[hbase-rowset-vssb-opt]]
+== HBASE_ROWSET_VSSB_OPT
+
+[cols="25%h,75%"]
+|===
+| *Description* | Allows INSERT, UPDATE, and DELETE (IUD) operations to be performed as an HBase batch `put` operation.
+| *Values* |
+*'ON'*: Perform IUD operations as an HBase batch `put` operation. +
+*'OFF'*: Do not perform IUD operations as an HBase batch `put` operation. +
+ +
+The default value is *'ON'*.
+| *Usage* | When IUD operation involves multiple tuples, then the Trafodion Optimizer evaluates whether these operations
+can be done in a batch manner at the HBase level thereby reducing the network interactions between the client applications and the HBase Region Servers. +
+ +
+If possible, then the query plan involves VSBB operators. The Virtual Sequential Block Buffer(VSBB) name is retained in Trafodion though it is unrelated to HBase.
+| *Production Usage* | Yes.
+| *Impact* | IUD operations can become slower if this CQD is set to 'OFF'.
+| *Level* | Query.
+| *Conflicts/Synergies* | Not applicable.
+| *Real Problem Addressed* | Not applicable.
+| *Introduced In Release* | Trafodion 1.3.0.
+| *Deprecated In Release* | Not applicable.
+|===
+
+<<<
+[[hbase-rowset-vssb-size]]
+== HBASE_ROWSET_VSSB_SIZE
+
+[cols="25%h,75%"]
+|===
+| *Description* | Determines the maximum number of rows in a batch `put` operation to HBase.
+| *Values* |
+Numeric value.
+ +
+The default value is *'1024'*.
+| *Usage* | The Trafodion execution engine already adjusts the number of rows in a batch depending upon how fast
+the queue to IUD (INSERT,UPDATE,DELETE) operator is filled up in the data flow architecture of Trafodion. +
+ +
+You can adjust the maximum size to suit your application needs and thus tune it to perform optimally.
+| *Production Usage* | Yes. You can disable this feature by setting the HBASE_ROWSET_VSSB_OPT CQD to 'OFF'.
+| *Impact* | The performance of your application may be affected by setting this CQD too low.
+| *Level* | Query.
+| *Conflicts/Synergies* | Not applicable.
+| *Real Problem Addressed* | Not applicable.
+| *Introduced In Release* | Trafodion 1.3.0.
+| *Deprecated In Release* | Not applicable.
+|===
+
+<<<
+[[hbase-small-scanner]]
+== HBASE_SMALL_SCANNER
+
+[cols="25%h,75%"]
+|===
+| *Description* | Enables Trafodion to leverage the HBase small scanner optimization. This optimization reduces I/O usage with up to 66%
+and enables non-blocking reads for higher concurrency support. When a scan is known to require less than a HBASE BLOCK SIZE (default is 64K),
+then enabling the HBase small scanner optimization increases performance.
+| *Values* |
+*'OFF'*: Never use the HBase small scanner optimization. +
+*'SYSTEM'*: Only enable the HBase small scanner optimization when the Trafodion Compiler determines that the scan size will fit in the table's HBASE BLOCK SIZE +
+*'ON'*: Enable the HBase small scanner optimization regardless of the size of scan. +
+ +
+The default value is *'OFF'*.
+| *Usage* | Consider using this CQD to improve the performance of your queries.
+| *Production Usage* | Consult with the Trafodion community.
+| *Impact* | The performance of small scan may increase by 1.4x. This CQD can be very useful for MDAM scans.
+| *Level* | System or Session.
+| *Conflicts/Synergies* | MDAM performance may be improved by 1.4x when correctly picking HBase block size so that each MDAM scan operation fit within a HBASE BLOCK SIZE boundary. +
+ +
+If you enable small scanner on large size scan incorrectly, then you are likely to see a 6% performance decrease. The returned results will still be correct.
+| *Real Problem Addressed* | Not applicable.
+| *Introduced In Release* | Trafodion 2.0.0.
+| *Deprecated In Release* | Not applicable.
+|===
+
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/223db1e4/docs/cqd_reference/src/asciidoc/_chapters/query_plans.adoc
----------------------------------------------------------------------
diff --git a/docs/cqd_reference/src/asciidoc/_chapters/query_plans.adoc b/docs/cqd_reference/src/asciidoc/_chapters/query_plans.adoc
index 33bc8d6..c72aa46 100644
--- a/docs/cqd_reference/src/asciidoc/_chapters/query_plans.adoc
+++ b/docs/cqd_reference/src/asciidoc/_chapters/query_plans.adoc
@@ -23,8 +23,8 @@
*/
////
-[[influence-query-plans]]
-= Influence Query Plans
+[[query-plans]]
+= Query Plans
This section describes CQDs that are used to influence query plans.
@@ -33,24 +33,29 @@ This section describes CQDs that are used to influence query plans.
[cols="25%h,75%"]
|===
-| *Category* | Influence Query Plans
-| *Description* | Defines the minimum size for query operator parallelism.
-The optimizer may choose an adaptive segment size that is equal to, or the multiple of,
-the value of this CQD, depending on the maximum estimated resource consumed by any single
-operator in the query. The optimizer may also decide to run the query with no parallelism
-if the resource consumption estimate is very low.
-| *Values* | Unsigned Integer. +
+| *Description* | Defines the minimum size of the adaptive segment; that is, the number of processors
+available for query operator parallelism. The optimizer may choose an adaptive-segment size that is equal to, or the multiple of,
+the value of this CQD depending on the maximum estimated resource consumed by any single operator in the query.
+The optimizer may also decide to run the query with no parallelism if the resource consumption estimate is very low.
+| *Values* |
+Unsigned Integer. +
+ +
The default value is *'16'*.
-| *Usage* | For clusters running at higher levels of concurrency with
-workloads that include a large number of small queries, reducing the default degree of
-parallelism may help achieve higher throughput.
+| *Usage* | For systems running at higher levels of concurrency with workloads that include a large
+number of small queries, reducing the default degree of parallelism may help achieve higher throughput. +
+ +
+With the default of 16, for 32-node systems, adaptive segmentation can use two 16-node virtual segments to execute queries that
+do not require a degree of parallelism of 32. This default setting can, for example, be changed to 8 for a 16-node system,
+to allow adaptive segmentation to leverage a lower degree of parallelism.
+
| *Production Usage* | Not applicable.
| *Impact* | Lowering the value of this CQD can increase the throughput of
high-concurrency small-query workloads, but has the potential disadvantage of increasing the
-elapsed time for some of the longer running queries.
-| *Level* | System.
+elapsed time for some of the longer running queries that leverage adaptive segmentation.
+| *Level* | System. There may be scenarios where you want to influence the degree of adaptive segmentation
+parallelism only for a certain set of queries and use it at the service level.
| *Conflicts/Synergies* | Not applicable.
-| *Real Problem Sddressed* | Not applicable.
+| *Real Problem Addressed* | Not applicable.
| *Introduced In Release* | Trafodion 1.3.0.
| *Deprecated In Release* | Not applicable.
|===
@@ -61,19 +66,68 @@ elapsed time for some of the longer running queries.
[cols="25%h,75%"]
|===
-| *Category* | Influence Query Plans
-| *Description* | Determines if Hash Join is considered by the optimizer to generate an execution plan.
-| *Values* | *'ON'* Hash Join is considered. +
-'OFF Hash Join is disabled. +
+| *Description* | Determines whether the Trafodion Optimizer considers Hash Join when generating an execution plan.
+| *Values* |
+*'ON'* Hash Join is considered. +
+*'OFF'* Hash Join is not considered. +
+
The default value is *'ON'*.
-| *Usage* | Use this CQD when you want to force a query plan not to use any Hash Joins.
+| *Usage* | Use this CQD when you want to force the optimizer to generate a query plan that does not use any Hash Joins.
| *Production Usage* | Hash Join is an important join implementation strategy for most complex queries.
-It is highly recommended not to turn Hash Join OFF. It should only be used to force a query plan for a particular query on an exception basis.
-| *Impact* | Turning Hash Join OFF may result in very inefficient query plans with expensive nested joins or sorts for merge joins.
+It is highly recommended that you do not turn HASH_JOINS OFF; that is, this CQD should be used to force a query plan for a particular query on an exception basis only.
+| *Impact* | Turning HASH_JOINS OFF may result in very inefficient query plans that use expensive nested joins or sorts for merge joins.
| *Level* | Query.
-| *Conflicts/Synergies* | Avoid turning all the three join implementations OFF (Hash Joins, Nested Joins, and Merge Joins).
-This may result in the compiler failing to generate query plans.
+| *Conflicts/Synergies* | If you turn all three join implementations OFF (Hash Joins, Nested Joins, and Merge Joins), then the compiler may fail to generate query plans.
+| *Real Problem Addressed* | Not applicable.
+| *Introduced In Release* | Trafodion 1.3.0.
+| *Deprecated In Release* | Not applicable.
+|===
+
+<<<
+[[hbase-coprocessors]]
+== HBASE_COPROCESSORS
+
+[cols="25%h,75%"]
+|===
+| *Description* | Allow HBase coprocessors to be used when computing aggregates.
+| *Values* |
+*'ON'*: Use HBase coprocessors. +
+*'OFF'*: Do not use HBase coprocessors. +
+ +
+The default value is *'ON'*.
+| *Usage* | Enables Trafodion to use HBase coprocessors to do early aggregation and filtering
+at the HBase Region Server level. This CQD does not affect Transaction coprocessors used by Trafodion. +
+ +
+As of Release 2.0 only COUNT(*) queries will be affected by this attribute.
+| *Production Usage* | Yes.
+| *Impact* | Network traffic between Region Server and Trafodion processes is reduced but
+the Region Server can become very busy when aggregating over large tables.
+| *Level* | Query.
+| *Conflicts/Synergies* | Not applicable.
+| *Real Problem Addressed* | Not applicable.
+| *Introduced In Release* | Trafodion 1.3.0.
+| *Deprecated In Release* | Not applicable.
+|===
+
+<<<
+[[hive-num-esps-per-datanode]]
+== HIVE_NUM_ESPS_PER_DATANODE
+
+[cols="25%h,75%"]
+|===
+| *Description* | Determines number of ESP processes used to scan a Hive table, per HDFS DataNode.
+| *Values* |
+Positive integer. +
+ +
+The default value is *'2'*.
+| *Usage* | Use this CQD to increase or decrease the number of scanners that process a single Hive table.
+If a Hive scan is found to be the bottleneck for a particular query, then increasing this attribute to; for example, 4 or higher will help.
+On the other hand decreasing the attribute to 1 could help with concurrency.
+| *Production Usage* | Yes.
+| *Impact* | Controls number of ESPs and, therefore, affects query execution time and system workload.
+| *Level* | Query.
+| *Conflicts/Synergies* | The CQD <<hive-min-bytes-per-esp-partition,HIVE_MIN_BYTES_PER_ESP_PARTITION>> (default = 67108864) may need to be adjusted downward
+when this attribute is used to increase the parallelism of scanning smaller Hive tables.
| *Real Problem Addressed* | Not applicable.
| *Introduced In Release* | Trafodion 1.3.0.
| *Deprecated In Release* | Not applicable.
@@ -85,10 +139,10 @@ This may result in the compiler failing to generate query plans.
[cols="25%h,75%"]
|===
-| *Category* | Influence Query Plans
| *Description* | Enables or disables the join order in which the optimizer joins the tables to be the sequence of the
tables in the FROM clause of the query.
-| *Values* | *'ON'* Join order is forced. +
+| *Values* |
+*'ON'* Join order is forced. +
*'OFF'* Join order is decided by the optimizer. +
+
The default value is *'OFF'*.
@@ -105,6 +159,67 @@ specified in the FROM clause, the plan generated may not be the optimal one.
| *Deprecated In Release* | Not applicable.
|===
+<<<
+[[mc-skew-sensitivity-threshold]]
+== MC_SKEW_SENSITIVITY_THRESHOLD
+
+[cols="25%h,75%"]
+|===
+| *Description* | Define the multi-column skew sensitivity threshold T used by multi-column skew-insensitive hash join (Skew Buster). +
+ +
+Let `f` be the occurrence frequency of a skew value `v`, `DoP` be the degree of parallelism of a hash join operator, and `RC` be the row count of
+the source data (for example, fact table) where the skew originates. +
+ +
+The hash join will run in the anti-skew mode for `v` if `f >= T * DoP / RC`.
+| *Values* |
+*< 0*: Disable the multi-column skew buster. +
+*>= 0*: Define the threshold T. +
+ +
+Default value: *0.1*.
+| *Usage* | Use of a *negative value* to disable multi-column anti-skew hash joins. This may slow down query performance when
+multi-column skew values are present in the fact table. +
+ +
+A value of *0* treats every multi-column value as skew values. This may increase network traffic since skewed values are broadcasted from the inner
+side child of the hash join to all join processes. +
+ +
+A value *greater than 0* selects those multi-column values as skewed values if their occurrence frequencies are high enough.
+| *Production Usage* | Consult the Trafodion community.
+| *Impact* | This CQD impacts runtime performance.
+| *Level* | Session.
+| *Conflicts/Synergies* | Not applicable.
+| *Real Problem Addressed* | Not applicable.
+| *Introduced In Release* | Trafodion 1.3.0.
+| *Deprecated In Release* | Not applicable.
+|===
+
+<<<
+[[mdam-no-stats-positions-threshold]]
+== MDAM_NO_STATS_POSITIONS_THRESHOLD
+
+[cols="25%h,75%"]
+|===
+| *Description* | This CQD effects the behavior of the query optimizer when there are no statistics available for a query
+having range predicates on key columns. The Trafodion Optimizer calculates the worst case number of seeks that the MDAM access method
+would do if chosen for the query. +
+ +
+If this number is greater than the value of MDAM_NO_STATS_POSITIONS_THRESHOLD, then MDAM is not considered for the query execution plan.
+| *Values* |
+Any integer greater than equal to zero. +
+ +
+Default value: *10*.
+| *Usage* | In certain situations, queries on tables lacking statistics may not be optimal because MDAM was not chosen.
+Increasing the value for this CQD allows MDAM to be chosen in more cases. On the other hand, if the value is made too high and the worst case
+scenario actually occurs, an MDAM plan may perform poorly.
+| *Production Usage* | Consult the Trafodion community.
+| *Impact* | Table scans on tables lacking statistics may improve by varying the value of this CQD.
+Results vary depending on the actual data in the table and the semantics of the query.
+| *Level* | Query.
+| *Conflicts/Synergies* | If <<mdam-scan-method,MDAM_SCAN_METHOD>> is set to *'OFF'*, then this CQD has no effect.
+| *Real Problem Addressed* | Perform UPDATE STATISTICS on the table (at the very least on key columns) to obtain statistics.
+| *Introduced In Release* | Trafodion 1.3.0.
+| *Deprecated In Release* | Not applicable.
+|===
+
<<<
[[mdam-scan-method]]
@@ -112,15 +227,15 @@ specified in the FROM clause, the plan generated may not be the optimal one.
[cols="25%h,75%"]
|===
-| *Category* | Influence Query Plans
-| *Description* | Enables or disables the Multi-Dimensional Access Method.
-| *Values* | *'ON'* MDAM is considered. +
+| *Description* | Enables or disables the Multi-Dimensional Access Method (MDAM).
+| *Values* |
+*'ON'* MDAM is considered. +
*'OFF'* MDAM is disabled. +
+
The default value is *'ON'*.
-| *Usage* | In certain situations, the optimizer might choose MDAM inappropriately, causing poor performance.
+| *Usage* | In certain situations, the Trafodion Optimizer might choose MDAM inappropriately, causing poor performance.
In such situations you may want to turn MDAM OFF for the query it is effecting.
-| *Production Usage* | Not applicable.
+| *Production Usage* | Yes.
| *Impact* | Table scans with predicates on non-leading clustering key column(s) could benefit from MDAM access
method if the leading column(s) has a small number of distinct values. Turning MDAM off results in a longer scan time for such queries.
| *Level* | Set this CQD at the query level when MDAM is not working efficiently for a specific query. However,
@@ -138,24 +253,26 @@ to set it at the service or system level.
[cols="25%h,75%"]
|===
-| *Category* | Influence Query Plans
| *Description* | Determines if Merge Join is considered by the optimizer to generate an execution plan.
-| *Values* | *'ON'* Merge Join is considered. +
-*'OFF'* Merge Join is disabled. +
+| *Values* |
+*'ON'*: Merge Join is considered. +
+*'OFF'*: Merge Join is disabled. +
+
The default value is *'ON'*.
| *Usage* | Use this CQD when you want to force a query plan not to use Merge Joins. This is useful as a workaround
-for query plans with very expensive sorts for Merge Joins. Turning Merge Join OFF also has the advantage of reducing the query compile time.
+for query plans with very expensive sorts for Merge Joins. Turning MERGE_JOINS OFF also has the advantage of reducing the query compile time.
| *Production Usage* | Merge Join is an efficient join implementation strategy if the physical schema was designed to take advantage
of it. For example, large tables are physically ordered based on the most frequently joined column(s).
-| *Impact* | Turning Merge Join OFF may result in the optimizer not considering potentially efficient query plans,
-for queries with large joins on tables that are physically ordered by the join column(s). Turning Merge Join ON causes an increase in compile
+| *Impact* | Turning MERGE_JOINS OFF may result in the optimizer not considering potentially efficient query plans,
+for queries with large joins on tables that are physically ordered by the join column(s). +
+ +
+Turning MERGE_JOINS ON causes an increase in compile
time because the optimizer now has to consider many more join options.
| *Level* | Set this CQD at the query level when a Merge Join is not working efficiently for a specific query. However,
there may be cases (usually a defect) where a larger set of queries is being negatively impacted by Merge Joins. In those cases you may want to
set it at the service or system level.
| *Conflicts/Synergies* | Avoid turning all the three join implementations OFF (Hash Joins, Nested Joins, and Merge Joins).
-This may result in the compiler failing to generate query plans.
+This may result in the Trafodion Compiler failing to generate query plans.
| *Real Problem Addressed* | Not applicable.
| *Introduced In Release* | Trafodion 1.3.0.
| *Deprecated In Release* | Not applicable.
@@ -167,24 +284,24 @@ This may result in the compiler failing to generate query plans.
[cols="25%h,75%"]
|===
-| *Category* | Influence Query Plans
| *Description* | Determines if Nested Join is considered by the optimizer to generate an execution plan.
-| *Values* | *'ON'* Nested Join is considered. +
-*'OFF'* Nested Join is disabled. +
+| *Values* |
+*'ON'*: Nested Join is considered. +
+*'OFF'*: Nested Join is disabled. +
+
The default value is *'ON'*.
| *Usage* | Use this CQD when you want to force a query plan not to use Nested Joins. This is useful
as a workaround for query plans with very expensive Nested Joins, which may occur if the optimizer fails to estimate the
cost of a Nested Join correctly.
| *Production Usage* | Nested Join is an important join implementation strategy for many complex queries. It is
-recommended not to turn Nested Join OFF. It should only be used to force a query plan for a particular query on an exception basis.
-| *Impact* | Turning Nested Join OFF may result in inefficient query plans for certain type of queries,
+recommended not to turn NESTED_JOINS OFF. It should only be used to force a query plan for a particular query on an exception basis.
+| *Impact* | Turning NESTED_JOINS OFF may result in inefficient query plans for certain type of queries,
such as light workloads and star join queries.
| *Level* | Query.
| *Conflicts/Synergies* | Avoid turning all the three join implementations OFF (Hash Joins, Nested Joins, and Merge Joins).
This may result in the compiler failing to generate query plans.
| *Real Problem Addressed* | The problem of inefficient Nested Joins can be better handled using a higher degree of query plan
-robustness as set by the ROBUST_QUERY_OPTIMIZATION CQD.
+robustness as set by the <<robust-query-optimization,ROBUST_QUERY_OPTIMIZATION>> CQD.
| *Introduced In Release* | Trafodion 1.3.0.
| *Deprecated In Release* | Not applicable.
|===
@@ -195,11 +312,12 @@ robustness as set by the ROBUST_QUERY_OPTIMIZATION CQD.
[cols="25%h,75%"]
|===
-| *Category* | Influence Query Plans
| *Description* | Controls the optimizer resources and time spent for optimizing a query plan, with level 0 indicating
the least amount of optimization effort and level 5 indicating the most. Lower optimization levels produce lower plan quality with
minimal compile time, while higher optimization levels cause the compiler to spend more compilation time to produce better plan quality.
-| *Values* | '0', '2', '3', '5'
+| *Values* |
+'0', '2', '3', '5' +
+ +
The default value is *'3'*.
| *Usage* | Reduce the optimization level when compile time is longer than desired and queries have relatively small
execution cost and are simple in structure.
@@ -219,8 +337,7 @@ Increasing the optimization level over the system default may result in very hig
[cols="25%h,75%"]
|===
-| *Category* | Influence Query Plans
-| *Description* | Controls the maximum number of parallel ESPs that work on a particular operation, like a join.
+| *Description* | Controls the maximum number of parallel ESPs that work on a particular operation; for example, a join.
| *Values* | Unsigned Integer: The maximum number of ESPs that should be used for a particular operation.
The value must be less than the number of nodes in the cluster. +
+
@@ -245,9 +362,9 @@ query workloads, but has the potential disadvantage of increasing the elapsed ti
[cols="25%h,75%"]
|===
-| *Category* | Influence Query Plans
| *Description* | Influences the optimizer to choose other types of joins over nested joins, by making a nested join plan relatively more expensive.
-| *Values* | Any positive fractional value. +
+| *Values* |
+Any positive fractional value. +
+
The default value is *'1.2'*.
| *Usage* | Review <<robust-query-optimization,ROBUST_QUERY_OPTIMIZATION>> before considering the use of this CQD. +
@@ -261,7 +378,7 @@ may be used to influence the optimizer to consider another join instead, such as
+
NESTED_JOINS OFF could turn nested joins off completely. However, there are many cases where nested joins do provide better performance than hash
joins, and turning them off completely may negatively impact the performance of queries that can do a lot better with nested joins.
-| *Production Usage* | Not applicable.
+| *Production Usage* | Consult with the Trafodion community.
| *Impact* | Specifying a risk premium insures against nested joins being chosen when they should not have been. However,
this can also result in nested joins not being chosen where the cardinality estimation was in fact accurate and a nested join could have
performed better. Therefore, this setting should be used with care in order to get robustness with a net gain in performance.
@@ -281,9 +398,9 @@ addressing nested join issues and need to use this setting independent of that C
[cols="25%h,75%"]
|===
-| *Category* | Influence Query Plans
| *Description* | Influences the optimizer to choose a parallel plan over a serial plan, by making a serial plan relatively more expensive.
-| *Values* | Any positive fractional value. +
+| *Values* |
+Any positive fractional value. +
+
The default value is *'1.2'*.
| *Usage* | Review <<robust-query-optimization,ROBUST_QUERY_OPTIMIZATION>> before considering the use of this CQD. +
@@ -294,7 +411,7 @@ allowed to win over competing parallel plans. +
+
If it is determined that the optimizer is using serial plans often enough where these plans are resulting in poor performance, then this CQD
may be used to influence the optimizer to consider parallel plans instead in some of those cases. +
-| *Production Usage* | Not applicable.
+| *Production Usage* | Consult with the Trafodion community.
| *Impact* | Specifying a risk premium insures against serial plans being chosen when they should not have been.
However, this can also result in serial plans not being chosen where the cardinality estimation was in fact accurate and a serial plan
could have performed better. Therefore, this setting should be used with care in order to get robustness with a net gain in performance.
@@ -309,12 +426,37 @@ addressing serial plan issues and need to use this setting independent of that C
|===
<<<
+[[risk-premium-serial-scaleback-maxcard-threshold]]
+== RISK_PREMIUM_SERIAL_SCALEBACK_MAXCARD_THRESHOLD
+
+[cols="25%h,75%"]
+|===
+| *Description* | Defines the minimal estimated max cardinality or row count of any relational operators in a query above
+which the risk premium for serial plan is applied. +
+ +
+A serial query plan is favored by the Trafodion Compiler when it estimates the query reads and processes small amount of data.
+The estimation error could become large when some operator is calculated to produce many rows yielding a non-optimal serial plan.
+This CQD helps prevent utilizing serial plan in such cases.
+| *Values* | An unsigned integer value.
+| *Usage* | Adjust this CQD when necessary only. +
+ +
+Use of a value smaller than the default (10,000) to penalize more serial plans or favor more parallel plans for operators produce less number of rows.
+Otherwise, use of a larger value.
+| *Production Usage* | Consult with the Trafodion community.
+| *Impact* | This CQD impacts plan quality.
+| *Level* | Session.
+| *Conflicts/Synergies* | Not applicable.
+| *Real Problem Addressed* | Not applicable.
+| *Introduced In Release* | Trafodion 1.3.0.
+| *Deprecated In Release* | Not applicable.
+|===
+
+<<<
[[robust-query-optimization]]
== ROBUST_QUERY_OPTIMIZATION
[cols="25%h,75%a"]
|===
-| *Category* | Influence Query Plans
| *Description* | Provides a simpler way to influence the optimizer's choice of query plans. The optimizer chooses query plans
based on cardinality estimates (the number of result rows estimated at each step of a query execution plan). Actual cardinalities encountered
at query execution often differ from estimates. The optimizer considers risky choices and exacts risk premiums before it chooses a plan that
@@ -397,7 +539,6 @@ optimizer may warn about. However, cardinality underestimations may still happen
[cols="25%h,75%"]
|===
-| *Category* | Influence Query Plans
| *Description* | Turns on the reporting of anti-skew join plan details in EXPLAIN or EXPLAIN OPTIONS 'f'.
| *Values* |
*'OFF'*: Disables the use of SKEW_EXPLAIN. +
@@ -420,7 +561,6 @@ The default value is *'OFF'*.
[cols="25%h,75%"]
|===
-| *Category* | Influence Query Plans
| *Description* | The optimizer looks for skewed values and address that skew if the number of rows in the table exceeds this threshold.
| *Values* |
*'n'*: where n is the number of rows +
@@ -460,7 +600,6 @@ is relevant if this threshold allows a skew to be detected only.
[cols="25%h,75%"]
|===
-| *Category* | Influence Query Plans
| *Description* | Used to specify a threshold that determines whether a value in the join column is considered skewed.
| *Values* |
*'-1'*: Disables the use of skew buster. +
@@ -489,12 +628,85 @@ However, there may be design opportunities that could help address the problem a
|===
<<<
+[[subquery_unnesting]]
+== SUBQUERY_UNNESTING
+
+[cols="25%h,75%"]
+|===
+| *Description* | Allows correlated subqueries in a SQL statement to be unnested, so that they can be executed efficiently.
+| *Values* |
+*'ON'*: Unnesting enabled. +
+*'OFF'*: Unnesting disabled. +
+ +
+The default value is *'ON'*.
+| *Usage* | Turn this CQD OFF when (in rare cases) unnesting a correlated subquery causes performance to degrade.
+If this attribute has to be turned OFF, then that could indicate a bug in the Trafodion Optimizer. Consult with the Trafodion community.
+| *Production Usage* | Yes.
+| *Impact* | Turn OFF with caution at a system level, as other queries which rely on un-nesting could be adversely impacted.
+| *Level* | Query.
+| *Conflicts/Synergies* | Not applicable.
+| *Real Problem Addressed* | Not applicable.
+| *Introduced In Release* | Trafodion 1.3.0.
+| *Deprecated In Release* | Not applicable.
+|===
+
+<<<
+[[traf-allow-esp-colocation]]
+== TRAF_ALLOW_ESP_COLOCATION
+
+[cols="25%h,75%"]
+|===
+| *Description* | Enables or disables whether ESP are colocated with HBase Region Servers, which minimizes the inter-node
+network traffic between the ESP processes and the HBase Region Servers.
+| *Values* |
+*'ON'*: Colocation enabled. +
+*'OFF'*: Colocation disabled. +
+ +
+The default value is *'OFF'*.
+| *Usage* | Enable the feature when each region server serves approximately equal amount of data, and/or reducing network traffic is important.
+| *Production Usage* | Consult with the Trafodion community.
+| *Impact* | Plan quality.
+| *Level* | Session.
+| *Conflicts/Synergies* | Not applicable.
+| *Real Problem Addressed* | Not applicable.
+| *Introduced In Release* | Trafodion 1.3.0.
+| *Deprecated In Release* | Not applicable.
+|===
+
+<<<
+[[traf-upsert-with-insert-default-semantics]]
+== TRAF_UPSERT_WITH_INSERT_DEFAULT_SEMANTICS
+
+[cols="25%h,75%"]
+|===
+| *Description* | Enables population of omitted columns in an UPSERT statement with default values when the table is created in aligned-row format mode.
+| *Values* |
+*'ON'*: Default population enabled. +
+*'OFF'*: Default population disabled. +
+ +
+The default value is *'ON'*.
+| *Usage* | When a column with default value is omitted in an UPSERT statement of a table in aligned row format mode,
+then the statement is transformed to merge. +
+ +
+If the row already exists, then the omitted columns are populated with values from the existing row. +
+ +
+If the row doesn't exist, then the omitted columns are populated with default values. This default behavior can be changed by setting this CQD to 'ON',
+which improves the performance of the UPSERT statements with omitted default value columns.
+| *Production Usage* | Yes.
+| *Impact* | Improved upsert performance of aligned row format tables.
+| *Level* | Query.
+| *Conflicts/Synergies* | Not applicable.
+| *Real Problem Addressed* | Not applicable.
+| *Introduced In Release* | Trafodion 1.3.0.
+| *Deprecated In Release* | Not applicable.
+|===
+
+<<<
[[upd-ordered]]
== UPD_ORDERED
[cols="25%h,75%"]
|===
-| *Category* | Influence Query Plans
| *Description* | Controls whether rows should be inserted, updated, or deleted in clustering key order.
| *Values* |
*'ON'*: The optimizer generates and considers plans where the rows are inserted, updated, or deleted in clustering key order. +
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/223db1e4/docs/cqd_reference/src/asciidoc/_chapters/runtime_controls.adoc
----------------------------------------------------------------------
diff --git a/docs/cqd_reference/src/asciidoc/_chapters/runtime_controls.adoc b/docs/cqd_reference/src/asciidoc/_chapters/runtime_controls.adoc
index 6f1c7c7..4196115 100644
--- a/docs/cqd_reference/src/asciidoc/_chapters/runtime_controls.adoc
+++ b/docs/cqd_reference/src/asciidoc/_chapters/runtime_controls.adoc
@@ -33,7 +33,6 @@ This section describes CQDs that are used for runtime controls.
[cols="25%h,75%"]
|===
-| *Category* | Runtime Controls
| *Description* | Ensures that all parts of the query plan are executed but no rows are returned by the query.
| *Values* | *'ON'* or *'OFF'*. +
+
@@ -56,9 +55,8 @@ is the same as when you do not use the [LAST 0] clause in the query.
[cols="25%h,75%"]
|===
-| *Category* | Runtime Controls
| *Description* | Use to limit the amount of CPU time that a query is allowed to use in any one server process (MXESP)
-including the NDCS server (MXOSRVR). Also includes the CPU time the query spends in the disk process (ESAM). $$$ +
+including the NDCS server (MXOSRVR). Also includes the CPU time the query spends in the disk process (ESAM). +
+
If a query exceeds the limit, then an error is raised and the query is terminated. This is a way to limit the impact on the
system of a poorly written or badly optimized query.
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/223db1e4/docs/cqd_reference/src/asciidoc/_chapters/schema_controls.adoc
----------------------------------------------------------------------
diff --git a/docs/cqd_reference/src/asciidoc/_chapters/schema_controls.adoc b/docs/cqd_reference/src/asciidoc/_chapters/schema_controls.adoc
index 0112b9a..0f84bb0 100644
--- a/docs/cqd_reference/src/asciidoc/_chapters/schema_controls.adoc
+++ b/docs/cqd_reference/src/asciidoc/_chapters/schema_controls.adoc
@@ -28,12 +28,34 @@
This section describes CQDs that are used for schema controls.
+[[catalog]]
+== Catalog
+
+[cols="25%h,75%"]
+|===
+| *Description* | Specifies the default catalog name for all DDL and DML statements.
+| *Values* | Any valid ANSI name, including delimited names. +
+ +
+The default is *'TRAFODION'*.
+| *Usage* | Trafodion tables must be in a catalog called TRAFODION. If you mostly access Hive or native HBase tables,
+then the catalog could be changed to HIVE or HBASE respectively. +
+ +
+The default setting is overridden by any catalog name specified in a SQL statement.
+| *Production Usage* | Yes.
+| *Impact* | Not applicable.
+| *Level* | Any.
+| *Conflicts/Synergies* | Alternately you can use the SET CATALOG statement.
+| *Real Problem Addressed* | Not applicable.
+| *Introduced In Release* | Trafodion 1.3.0.
+| *Deprecated In Release* | Not applicable.
+|===
+
+<<<
[[schema]]
== Schema
[cols="25%h,75%"]
|===
-| *Category* | Influencing Query Plans
| *Description* | Sets the default schema for the session.
| *Values* | SQL identifier. +
+
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/223db1e4/docs/cqd_reference/src/asciidoc/_chapters/table_definition.adoc
----------------------------------------------------------------------
diff --git a/docs/cqd_reference/src/asciidoc/_chapters/table_definition.adoc b/docs/cqd_reference/src/asciidoc/_chapters/table_definition.adoc
new file mode 100644
index 0000000..0a93dce
--- /dev/null
+++ b/docs/cqd_reference/src/asciidoc/_chapters/table_definition.adoc
@@ -0,0 +1,142 @@
+////
+/**
+* @@@ START COPYRIGHT @@@
+*
+* Licensed to the Apache Software Foundation (ASF) under one
+* or more contributor license agreements. See the NOTICE file
+* distributed with this work for additional information
+* regarding copyright ownership. The ASF licenses this file
+* to you under the Apache License, Version 2.0 (the
+* "License"); you may not use this file except in compliance
+* with the License. You may obtain a copy of the License at
+*
+* http://www.apache.org/licenses/LICENSE-2.0
+*
+* Unless required by applicable law or agreed to in writing,
+* software distributed under the License is distributed on an
+* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+* KIND, either express or implied. See the License for the
+* specific language governing permissions and limitations
+* under the License.
+*
+* @@@ END COPYRIGHT @@@
+ */
+////
+
+[[table_definition]]
+= Table Definition
+
+This section describes CQDs that are used for table definition
+
+[[auto-query-retry-warnings]]
+== ALLOW_NULLABLE_UNIQUE_KEY_CONSTRAINT
+
+[cols="25%h,75%"]
+|===
+| *Description* | Allow Trafodion tables to be created with NULLABLE columns in the PRIMARY or STORE BY key.
+| *Values* | *'ON'* or *'OFF'* +
+ +
+The default value is *'OFF'*.
+| *Usage* | Allows NULLABLE columns to be included in the PRIMARY or STORE BY key for Trafodion tables.
+This CQD must be set prior to creating the table. It is not necessary to specify this CQD during DML operations on the table. +
+ +
+When performing UPDATE STATISTICS with SAMPLE option on such tables, this CQD must be set so that an appropriate sample table can be created.
+| *Production Usage* | Please consult with the Trafodion community.
+| *Impact* | Two bytes are added to the key for each nullable column.
+| *Level* | Session.
+| *Conflicts/Synergies* | Not applicable.
+| *Real Problem Addressed* | Not applicable.
+| *Introduced In Release* | Trafodion 1.3.0.
+| *Deprecated In Release* | Not applicable.
+|===
+
+<<<
+[[hbase-block-size]]
+== HBASE_BLOCK_SIZE
+
+[cols="25%h,75%"]
+|===
+| *Description* | Allow Trafodion tables to be created with specified HBase block size.
+| *Values* | Positive integer. +
+ +
+The default value is *'65536'*.
+| *Usage* | The value of this attribute is passed on to HBase when a Trafodion table is created in HBase.
+See the https://hbase.apache.org/book.html[Apache HBase(TM) Reference Guide] for usage information .
+| *Production Usage* | Yes.
+| *Impact* | The impact depends on the type of table access. Choose a block size that is appropriate
+for how the table is primarily accessed.
+| *Level* | System.
+| *Conflicts/Synergies* | Not applicable.
+| *Real Problem Addressed* | Not applicable.
+| *Introduced In Release* | Trafodion 1.3.0.
+| *Deprecated In Release* | Not applicable.
+|===
+
+<<<
+[[hive-default-charset]]
+== HIVE_DEFAULT_CHARSET
+
+[cols="25%h,75%"]
+|===
+| *Description* | Defines what character set the columns of Hive tables should have.
+| *Values* |
+*'ISO88591'* or *'UTF8'* +
+ +
+Default: *'UTF8'*
+| *Usage* | Set this to ISO88591 when reading from Hive tables with ISO8859-1 data.
+| *Production Usage* | Yes.
+| *Impact* | Not applicable.
+| *Level* | System.
+| *Conflicts/Synergies* | Not applicable.
+| *Real Problem Addressed* | Not applicable.
+| *Introduced In Release* | Trafodion 1.3.0.
+| *Deprecated In Release* | Not applicable.
+|===
+
+<<<
+[[hive-file-charset]]
+== HIVE_FILE_CHARSET
+
+[cols="25%h,75%"]
+|===
+| *Description* | For certain character sets that are not supported in Trafodion, you can specify the character set here
+causing Trafodion to automatically convert the data to the character set specified in the HIVE_DEFAULT_CHARSET CQD. +
+ +
+This feature is currently supported only for *'GBK'*, and only if HIVE_DEFAULT_CHARSET is set to *'UTF8'*.
+| *Values* |
+empty or *'GBK'* +
+ +
+Default: empty
+| *Usage* | Leave this blank, unless you want to access GBK data in Hive tables.
+| *Production Usage* | Yes.
+| *Impact* | Not applicable.
+| *Level* | System.
+| *Conflicts/Synergies* | Only applicable if HIVE_DEFAULT_CHARSET is set to *'UTF8'*.
+| *Real Problem Addressed* | Not applicable.
+| *Introduced In Release* | Trafodion 1.3.0.
+| *Deprecated In Release* | Not applicable.
+|===
+
+<<<
+[[hive-max-string-length]]
+== HIVE_MAX_STRING_LENGTH
+
+[cols="25%h,75%"]
+|===
+| *Description* | Hive columns of type STRING have a maximum length in Trafodion, which you can specify with this CQD. +
+ +
+*NOTE* For UTF-8 data, this length is specified in bytes, not UTF-8 characters.
+| *Values* |
+1-n. +
+ +
+Default: 32000.
+| *Usage* | Set this to the lowest possible value to improve system performance.
+| *Production Usage* | Yes.
+| *Impact* | Not applicable.
+| *Level* | System.
+| *Conflicts/Synergies* | Not applicable.
+| *Real Problem Addressed* | Not applicable.
+| *Introduced In Release* | Trafodion 1.3.0.
+| *Deprecated In Release* | Not applicable.
+|===
+
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/223db1e4/docs/cqd_reference/src/asciidoc/_chapters/transactions.adoc
----------------------------------------------------------------------
diff --git a/docs/cqd_reference/src/asciidoc/_chapters/transactions.adoc b/docs/cqd_reference/src/asciidoc/_chapters/transactions.adoc
index 5f590fb..6a18aab 100644
--- a/docs/cqd_reference/src/asciidoc/_chapters/transactions.adoc
+++ b/docs/cqd_reference/src/asciidoc/_chapters/transactions.adoc
@@ -33,7 +33,6 @@ This section describes CQDs that are used for transaction control and locking.
[cols="25%h,75%"]
|===
-| *Category* | Transaction Control and Locking
| *Description* | Specifies the default transaction isolation level that queries use.
| *Values* | *'READ UNCOMMITTED'*, *'READ COMMITTED'*, *'REPEATABLE READ'*, or *'SERIALIZABLE'*. +
+
@@ -76,7 +75,6 @@ then its value is used as the isolation level for updates and DDL statements. UP
[cols="25%h,75%"]
|===
-| *Category* | Transaction Control and Locking
| *Description* | Specifies the default transaction isolation level for these update operations: INSERT, UPDATE, or DELETE.
| *Values* | *'READ UNCOMMITTED'*, *'READ COMMITTED'*, *'REPEATABLE READ'*, or *'SERIALIZABLE'*. +
+
@@ -86,7 +84,7 @@ The default value is *'READ COMMITTED'* (ANSI).
| *Impact* | When set, this CQD prevents users from doing any of these update operations: INSERT, UPDATE, or DELETE.
| *Level* | Service.
| *Conflicts/Synergies* | Works with the ISOLATION_LEVEL setting. Both settings are READ COMMITTED by default.
-ISOLATION_LEVEL can be set to READ UNCOMMITTED. This CQD still remains READ COMMITTED. You can change it to READ UNCOMMITED to prevent queries
+ISOLATION_LEVEL can be set to READ UNCOMMITTED. This CQD still remains READ COMMITTED. You can change it to READ UNCOMMITTED to prevent queries
running at the service level to not perform any updates.
| *Real Problem Addressed* | Not applicable.
| *Introduced In Release* | Trafodion 1.3.0.
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/223db1e4/docs/cqd_reference/src/asciidoc/_chapters/update_stats.adoc
----------------------------------------------------------------------
diff --git a/docs/cqd_reference/src/asciidoc/_chapters/update_stats.adoc b/docs/cqd_reference/src/asciidoc/_chapters/update_stats.adoc
index 51c0204..9695e3f 100644
--- a/docs/cqd_reference/src/asciidoc/_chapters/update_stats.adoc
+++ b/docs/cqd_reference/src/asciidoc/_chapters/update_stats.adoc
@@ -31,7 +31,6 @@
[cols="25%h,75%"]
|===
-| *Category* | Update Statistics and Reorg
| *Description* | When performing update statistics with the NECESSARY keyword or with automation, this is
the number of minutes that are allowed to have elapsed since a histogram was marked as read for it to be regenerated. +
+
@@ -48,7 +47,7 @@ However, if a table is not used frequently, then this CQD influences how frequen
+
A smaller setting reduces the number of histograms being updated if there are many tables that have not been used within that interval.
A larger setting updates histogram for many more tables that are not being accessed that often.
-| *Production Usage* | Not applicable.
+| *Production Usage* | Consult the Trafodion community.
| *Impact* | Influences the number of histograms that need to be regenerated and therefore the time it takes for
update statistics automation to regenerate histograms for all the tables that so qualify.
| *Level* | System.
@@ -67,7 +66,6 @@ This CQD qualifies the histogram to be regenerated; it is the maximum time since
[cols="25%h,75%"]
|===
-| *Category* | Update Statistics and Reorg
| *Description* | Sets the minimum rows that need to be in a table before sampling is used to update statistics for that table.
If a table has a fewer rows than the value of this CQD, then the SAMPLE option is silently ignored when performing update statistics.
| *Values* | 1 through max unsigned integer. +
@@ -76,7 +74,7 @@ The default value is 10000.
| *Usage* | Influences for what tables sampling is used for histogram statistics generation. If the setting is smaller,
then more tables qualify for sampling. If the setting is larger, then fewer tables qualify for sampling. Sampling can result in faster update
statistics run times. But for smaller tables, it can also result in poor histogram accuracy which could result in poor query plans.
-| *Production Usage* | Not applicable.
+| *Production Usage* | Consult the Trafodion community.
| *Impact* | Setting this CQD to a smaller value means that sampling is used for tables with fewer rows, when the SAMPLE option is
specified as part of update statistics. This can result in less accurate histograms and poor query plans, because the sample size may be too small to
generate good estimates for histograms. +
@@ -90,3 +88,28 @@ However, these tables may also have more accurate histograms.
| *Deprecated In Release* | Not applicable.
|===
+<<<
+[[ustat-min-rowcount-for-low-sample]]
+== USTAT_MIN_ROWCOUNT_FOR_LOW_SAMPLE
+
+[cols="25%h,75%"]
+|===
+| *Description* | Defines the behavior of the UPDATE STATISTICS utility. It places a lower limit on the number of sample rows that will be used when sampling. +
+ +
+If the number of rows in the table is less than this value and sampling is used, then the sample size used will be that specified by
+the <<hist-default-sample-min,HIST_DEFAULT_SAMPLE_MIN>> CQD. (which defaults to 10,000).
+| *Values* | This CQD may take on any integer value greater than zero. +
+ +
+The default for this CQD is *1,000,000*.
+| *Usage* | Prevents accidental use of too-small samples when generating statistics on tables. If sample sizes are too small,
+then histogram statistics will be less accurate, leading to potentially less efficient query plans.
+| *Production Usage* | Consult the Trafodion community.
+| *Impact* | Setting this CQD to lower values may result in smaller sample sizes for small tables.
+This may slightly improve UPDATE STATISTICS run time, but at the cost of potentially less efficient queries.
+| *Level* | System.
+| *Conflicts/Synergies* | <<hist-default-sample-min,HIST_DEFAULT_SAMPLE_MIN>> effects the behavior of this CQD.
+| *Real Problem Addressed* | Not applicable.
+| *Introduced In Release* | Trafodion 1.3.0.
+| *Deprecated In Release* | Not applicable.
+|===
+
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/223db1e4/docs/cqd_reference/src/asciidoc/index.adoc
----------------------------------------------------------------------
diff --git a/docs/cqd_reference/src/asciidoc/index.adoc b/docs/cqd_reference/src/asciidoc/index.adoc
index f67e482..9de403c 100644
--- a/docs/cqd_reference/src/asciidoc/index.adoc
+++ b/docs/cqd_reference/src/asciidoc/index.adoc
@@ -61,9 +61,12 @@ specific language governing permissions and limitations under the License.
include::asciidoc/_chapters/about.adoc[]
include::asciidoc/_chapters/introduction.adoc[]
include::asciidoc/_chapters/query_plans.adoc[]
+include::asciidoc/_chapters/query_execution.adoc[]
include::asciidoc/_chapters/histograms.adoc[]
include::asciidoc/_chapters/transactions.adoc[]
include::asciidoc/_chapters/runtime_controls.adoc[]
include::asciidoc/_chapters/schema_controls.adoc[]
+include::asciidoc/_chapters/table_definition.adoc[]
include::asciidoc/_chapters/update_stats.adoc[]
include::asciidoc/_chapters/operational_controls.adoc[]
+include::asciidoc/_chapters/debugging.adoc[]
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/223db1e4/docs/load_transform/pom.xml
----------------------------------------------------------------------
diff --git a/docs/load_transform/pom.xml b/docs/load_transform/pom.xml
index ef00fd5..d54b0e8 100644
--- a/docs/load_transform/pom.xml
+++ b/docs/load_transform/pom.xml
@@ -234,15 +234,15 @@
- target/docs/<version>/<document> contains the PDF version and the web book. The web book is named index.html
-->
<!-- Copy the PDF file to its target directories -->
- <copy file="${basedir}/target/index.pdf" tofile="${basedir}/../target/docs/load_transform_guide/Trafodion_Load_Transform_Guide.pdf" />
- <copy file="${basedir}/target/index.pdf" tofile="${basedir}/../target/docs/${project.version}/load_transform_guide/Trafodion_Load_Transform_Guide.pdf" />
+ <copy file="${basedir}/target/index.pdf" tofile="${basedir}/../target/docs/load_transform/Trafodion_Load_Transform_Guide.pdf" />
+ <copy file="${basedir}/target/index.pdf" tofile="${basedir}/../target/docs/${project.version}/load_transform/Trafodion_Load_Transform_Guide.pdf" />
<!-- Copy the Web Book files to their target directories -->
- <copy todir="${basedir}/../target/docs/load_transform_guide">
+ <copy todir="${basedir}/../target/docs/load_transform">
<fileset dir="${basedir}/target/site">
<include name="**/*.*"/> <!--All sub-directories, too-->
</fileset>
</copy>
- <copy todir="${basedir}/../target/docs/${project.version}/load_transform_guide">
+ <copy todir="${basedir}/../target/docs/${project.version}/load_transform">
<fileset dir="${basedir}/target/site">
<include name="**/*.*"/> <!--All sub-directories, too-->
</fileset>