You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "ASF GitHub Bot (JIRA)" <ji...@apache.org> on 2017/10/24 22:37:00 UTC
[jira] [Commented] (TRAFODION-2781) UPDATE STATS unnecessarily
creates LOB columns in sample tables
[ 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 <db...@apache.org>
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)