[
https://issues.apache.org/jira/browse/TRAFODION-2781?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16217824#comment-16217824
]
ASF GitHub Bot commented on TRAFODION-2781:
-------------------------------------------
GitHub user DaveBirdsall opened a pull request:
https://github.com/apache/incubator-trafodion/pull/1276
[TRAFODION-2781] Omit LOB columns from update stats sample tables
UPDATE STATISTICS does not support statistics on LOB columns. However, it
was including LOB columns when it creates sample tables. This is unnecessary
overhead, which could be eliminated.
This set of changes eliminates the LOB columns from the sample tables, and
eliminates the logic that populates them. There are two parts to the changes:
1. A new clause, WITHOUT LOB COLUMNS, has been added to the CREATE TABLE
LIKE syntax. So, one can say, CREATE TABLE T1 LIKE T WITHOUT LOB COLUMNS. The
target table contains all the columns of the source table except for LOB
columns. UPDATE STATS code was changed to use this new syntax when creating
sample tables.
2. Logic in UPDATE STATS code to populate sample tables is now careful to
generate an explicit select list rather than relying on "SELECT *". It was
already doing this for the case of over-sized character type columns; now it
does so always. LOB columns are excluded from the select list.
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/DaveBirdsall/incubator-trafodion
incStatsAndLobs
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/incubator-trafodion/pull/1276.patch
To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:
This closes #1276
----
commit 328c1b2c8662cd11662f73e6a072cf5bf941be37
Author: Dave Birdsall <[email protected]>
Date: 2017-10-24T22:31:10Z
[TRAFODION-2781] Omit LOB columns from update stats sample tables
----
> UPDATE STATS unnecessarily creates LOB columns in sample tables
> ---------------------------------------------------------------
>
> Key: TRAFODION-2781
> URL: https://issues.apache.org/jira/browse/TRAFODION-2781
> Project: Apache Trafodion
> Issue Type: Improvement
> Components: sql-cmp
> Affects Versions: 2.3-incubating
> Environment: All
> Reporter: David Wayne Birdsall
> Assignee: David Wayne Birdsall
> Priority: Minor
>
> When UPDATE STATISTICS creates a persistent sample table, it uses CREATE
> TABLE LIKE, which means that the sample table will get LOB columns even
> though we don't support statistics on LOBs. Moreover, we will populate these
> LOB columns. This is an unnecessary overhead.
> We could improve the efficiency of UPDATE STATISTICS on tables with LOB
> columns by omitting the LOB columns when creating the sample table, and
> omitting the logic to populate them.
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
